INF: SQLGetInfo Returned with SQL_PRESERVE_CURSORS

• Microsoft SQL Server, versions 6.0 & 6.5
• Microsoft Open Data Base Connectivity, version 2.5

SUMMARY

The Microsoft 2.5 SQL Server ODBC drivers allow a user to request that server cursors be preserved on commits and rollbacks when running against SQL Server version 6.0. This article describes why the driver still returns SQL_CB_CLOSE on a call to SQLGetInfo(SQL_CURSOR_COMMIT_BEHAVIOR).

MORE INFORMATION

When the 2.50.0121 or later driver is running against a 6.0 or later SQL Server, applications can call:

   SQLSetConnectOption(hdbc, SQL_PRESERVE_CURSORS, SQL_PC_ON);

   SQLGetInfo(hdbc1, SQL_CURSOR_COMMIT_BEHAVIOR,
              &rgbInfoValue, sizeof(rgbInfoValue), NULL);

The driver reports SQL_CB_CLOSE because the SQL_PRESERVE_CURSORS option only applies to ODBC cursors which can be implemented using SQL Server 6.0 server cursors. Cursors which the driver cannot implement as server cursors must be closed before a commit or rollback can be issued on their connection handle, therefore not all cursors can be preserved across commits and rollbacks.

A discussion of the conditions under which SQL Server implements an ODBC Cursor as a server cursor can be found in the SQL Server ODBC help file, Drvssrvr.Hlp. It is also covered in the ODBC SQL Server Driver section of the SQL Server 6.0 "Books Online," where this is primarily discussed in the sections "Using ODBC Cursors" and "Advantages of Using Server Cursors." The SQL Server 6.5 "Books Online" discusses the same issues in Chapter 7 of "Programming ODBC for SQL Server."