Change database SQL Update

Keywords: 	 Change database SQL Update count rows result set

---

Questions:

Is there any way to change the database which the data source points to ? I want to use the same DATA Source all the time (SQL Server) but I want to switch the Database that ODBC Connects to through Winbatch...is there a way to do this?

And

Is there a way to count the number of rows that an qExecDirect statement returns ?

Answers:

TO change the database which the data source points to, you can modify what database the DSN points to, or create a seperate DSN. Take a look at the function qConfigData.....

OR

When you setup a DSN for SQL Server, you define what your default database will be. If you need to query a different DB on the same server, you can exec a USE database.

The way to count the number of rows, that an qExecDirect {UPDATE, INSERT, or DELETE} statement returns, would be to make a dllcall to SQLRowCount...

Note: Only some data sources may be able to return the number of rows returned by a SELECT statement or a catalog function before fetching the rows.

Note: Many data sources cannot return the number of rows in a result set before fetching them; for maximum interoperability, applications should not rely on this behavior.

Here is an example of making a dllcall to SQLRowCount....

AddExtender("wwodb34I.dll")

;general section to set up handles
henv = qAllocEnv()
If henv == -1
  retcode = qLastCode()  
  Message("Could Not Create ODBC Environment", retcode)
  Exit
Endif

hdbc = qAllocConnect(henv) 
If hdbc == -1
  retcode = qLastCode()
  Message("qAllocConnect failed", retcode)
  Exit
Endif

;Now, connect to DSN
retcode = qConnect(hdbc, "AccessDSN", "", "")
If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
  Message("Connection To Data Source Failed", retcode)
  Exit
Endif

hstmt = qAllocStmt(hdbc)
If hstmt != 0
  retcode = qLastCode()
  Message("qAllocStmt failed", retcode)
  Exit
Endif



retcode = qExecDirect(hstmt, "UPDATE Customers SET CustomerId = 111111 WHERE City = 'Warszawa'")

If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
  ret=qError(hstmt,2)
  Message("MS ACCESS Error", ret)
  Exit
Endif

handle  = qSpecial(2,hstmt,0,0,0)
cDLL    = qSpecial(1,0,0,0,0)
bb      = BinaryAlloc(4)
x     = DllCall(cDLL, long:"SQLRowCount",long:handle,lpbinary:bb)
count=BinaryPeek4(bb,0)
message("count",Count)

More:

qFetch is the Winbatch function designed to retrieve rows in a result set.

counter=0

While @TRUE

  ;FETCHES A ROW OF DATA FROM A RESULT SET

  retcode = qFetch(hstmt)  


  If retcode == @qNoData Then Break

   counter=counter+1

EndWhile


Message("NUMBER OF ROWS IN RESULT SET",counter)

OR

If you need a count of the number of rows a query will return you could exec a SELECT COUNT(*) FROM tablename WHERE parameters, but it requires about the same amount of time to exec as does the query to return the column data, so it will add processing time.

---

---

Article ID:   W14426

Filename:   Change database then Update then count rows.txt