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

ODBC
plus

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

QExecDirect Second handle issue with SQL Server Drivers


Question:

I am having problems accessing and updating one table using 2 handles.

while @true
retcode = qFetch(hstmt) 
If retcode == @qNoData Then break
retcode = qGetData(hstmt, 1, "LOGINID", 15)
If (retcode != @qSuccess) && (retcode != @qSuccessInfo)
Reterror=qError( hstmt,2)
During this loop it does checks against domain to see if record should be deleted from table and if so gosubs off to this routine
errormode(@cancel)
t1 = qAllocStmt(hdbc) 
if t1<>-1 then 
lasterror()
retcode = qExecDirect(t1, sqlt)
If (retcode != @qSuccess) && (retcode != @qSuccessInfo) then
Reterror=qError( t1,2)
Message("qFetch failed", Reterror)
endif
QFreeStmt(t1,1)
endif

return
The problem is I always get the following error:-
S1000 - Connection is busy with results from another hstmt

How I have 2 connections to the same table

I am accessing the SQL Server Driver 2000.81.7713.00

Answer:

The information in the following MS article applies to Microsoft SQL Server ODBC Drivers version 2.50.0121 or later when running against Microsoft SQL Server version 6.0 or later. Earlier versions of the Microsoft driver or Microsoft SQL Server do not support multiple active statements on a connection handle.

Please read:
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q140/8/96.asp&NoWebContent=1 This article suggests, if you are running Microsoft SQL Server ODBC Drivers version 2.50.0121 or later, that calling SQLSetConnectOption with SQL_CURSOR_TYPE and SQL_ROWSET_SIZE, it can be done.

Unfortunately, there is no ODBC extender function that can do this. You will have to make a dllcall.

In researching the api to make the dllcall, I have found that SQLSetConnectOption has been replaced with the function SQLSetConnectAttr in ODBC 3.x.

However, those options (SQL_CURSOR_TYPE and SQL_ROWSET_SIZE) seem to be supported by the SQLSetStmtAttr Function.

http://msdn.microsoft.com/library/en-us/odbc/htm/odch21kpr_2.asp?frame=true

Your options seem to be: make two seperate connections to the database, or write a dllcall to SQLSetStmtAttr Function, as the article indicates.


Article ID:   W15847
File Created: 2017:07:28:12:58:33
Last Updated: 2004:03:30:14:41:07