This article discusses when multiple active statement handles are allowedon an ODBC connection handle with Microsoft SQL Server 6.0 or later. Youcan find additional information on this topic in the chapter on theMicrosoft SQL Server driver in the book "Inside ODBC" by Kyle Geiger,published by Microsoft Press.
This information applies to Microsoft SQL Server ODBC Drivers version2.50.0121 or later when running against Microsoft SQL Server version 6.0 orlater. Earlier versions of the Microsoft driver or Microsoft SQL Server donot support multiple active statements on a connection handle. If you areusing third party SQL Server ODBC drivers, you should review thedocumentation accompanying the driver to see if it supports multiple activestatements with SQL Server 6.0.
Prior to SQL Server 6.0, Microsoft SQL Servers did not support multipleactive statement handles on a single ODBC connection handle. This is due tothe architecture of SQL Server's network layers. After the server has builta result set, it has to send the entire result set to the client before itwill accept a new operation from the client. Client fetch requests do nothave to be sent back to the server, they simply read the next row availablein the network buffer. The server will accept only one type of request froma client before the result set has been completely sent -- a 'cancel'command canceling the processing of the result set. Because of thisarchitecture, no SQL Server clients, either DB-Library or ODBC, can processmore than one result set at a time on a connection handle.
SQL Server 6.0 does not change this network architecture, but it doesintroduce server-side cursors that can be used to open multiple cursors ona single connection handle. This can be done because each cursor operationin the ODBC driver generates one individual cursor command which is sent toSQL Server. When the result set for each cursor command has been receivedback at the client, SQL Server considers the command to have completed andit will accept another command from another statement handle over thatconnection handle.
For example, an application can:
SQLAllocEnv(&henv): SQLAllocConnect(henv, &hdbc); SQLAllocStmt(hdbc, &hstmt1); SQLAllocStmt(hdbc, &hstmt2); SQLSetConnectOption(hdbc, SQL_CURSOR_TYPE, SQL_CURSOR_DYNAMIC); SQLSetConnectOption(hdbc, SQL_ROWSET_SIZE, 5); SQLExecDirect(hstmt1, "select * from authors", SQL_NTS);
When the SQLExecDirect() on hstmt1 is executed, the Microsoft SQL ServerODBC driver will issue a cursor open request. When SQL Server completes thecursor open, it considers the command to be finished and will allow theapplication to then issue a command on another hstmt:
SQLExecDirect(hstmt2, "select * from titles", SQL_NTS);
Once again, after the server has finished with the cursor open requestissued by the client, it considers the statement to be completed. If atthis point the ODBC application does:
SQLExtendedFetch(hstmt1, SQL_FETCH_NEXT, 1, ...);
the ODBC driver will send to SQL Server a cursor fetch for the first fiverows of the result set. Once the server has transferred the five rows tothe driver, it considers the fetch processing completed and will accept newrequests. The application could then do a fetch on the cursor opened forthe other statement handle:
SQLExtendedFetch(hstmt2, SQL_FETCH_NEXT, 1, ...);
SQL Server will accept this second command on the connection handlebecause, as far as it is concerned, it has completed the last command onthe connection handle, which was the fetch of the first five rows of therows for hstmt1.
This process only works when the ODBC driver uses server cursors. Asdocumented in the Microsoft SQL Server ODBC Driver documentation, thedriver uses server cursors except when the following statement options areset:
SQL_CONCURRENCY = SQL_CONCUR_READ_ONLY SQL_CURSOR_TYPE = SQL_CURSOR_FORWARD_ONLY SQL_ROWSET_SIZE = 1
The ODBC driver does use server-side cursors for all other options whenexecuting a single SELECT statement, or a stored procedure that containsonly a single SELECT statement. The current implementation of servercursors in SQL Server 6.0 itself does not support batches of SQL statementsor stored procedures that have more than one SELECT statement.