INF: Multiple Active Microsoft SQL Server Statements

ID: Q140896

The information in this article applies to:

Microsoft SQL Server versions 6.0, 6.5
Microsoft Open Database Connectivity, version 2.50

SUMMARY

This article discusses when multiple active statement handles are allowed on an ODBC connection handle with Microsoft SQL Server 6.0 or later. You can find additional information on this topic in the chapter on the Microsoft SQL Server driver in the book "Inside ODBC" by Kyle Geiger, published by Microsoft Press.

MORE INFORMATION

This information 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. If you are using third party SQL Server ODBC drivers, you should review the documentation accompanying the driver to see if it supports multiple active statements with SQL Server 6.0.

Prior to SQL Server 6.0, Microsoft SQL Servers did not support multiple active statement handles on a single ODBC connection handle. This is due to the architecture of SQL Server's network layers. After the server has built a result set, it has to send the entire result set to the client before it will accept a new operation from the client. Client fetch requests do not have to be sent back to the server, they simply read the next row available in the network buffer. The server will accept only one type of request from a client before the result set has been completely sent -- a 'cancel' command canceling the processing of the result set. Because of this architecture, no SQL Server clients, either DB-Library or ODBC, can process more than one result set at a time on a connection handle.

SQL Server 6.0 does not change this network architecture, but it does introduce server-side cursors that can be used to open multiple cursors on a single connection handle. This can be done because each cursor operation in the ODBC driver generates one individual cursor command which is sent to SQL Server. When the result set for each cursor command has been received back at the client, SQL Server considers the command to have completed and it will accept another command from another statement handle over that connection 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 Server ODBC driver will issue a cursor open request. When SQL Server completes the cursor open, it considers the command to be finished and will allow the application 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 request issued by the client, it considers the statement to be completed. If at this 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 five rows of the result set. Once the server has transferred the five rows to the driver, it considers the fetch processing completed and will accept new requests. The application could then do a fetch on the cursor opened for the other statement handle:


   SQLExtendedFetch(hstmt2, SQL_FETCH_NEXT, 1, ...);

SQL Server will accept this second command on the connection handle because, as far as it is concerned, it has completed the last command on the connection handle, which was the fetch of the first five rows of the rows for hstmt1.

This process only works when the ODBC driver uses server cursors. As documented in the Microsoft SQL Server ODBC Driver documentation, the driver uses server cursors except when the following statement options are set:


   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 when executing a single SELECT statement, or a stored procedure that contains only a single SELECT statement. The current implementation of server cursors in SQL Server 6.0 itself does not support batches of SQL statements or stored procedures that have more than one SELECT statement.

Additional query words: sql6 odbc concurrent

Keywords : kbenv kbinterop kbusage ODBC SSrvProg SSrvStProc
Version : 2.5 6.0
Platform : WINDOWS
Issue type :