WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

WIL Extenders
ODBC
plus
Sample Code

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

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
File Created: 2001:03:01:15:41:22
Last Updated: 2001:03:01:15:41:22