Cursors get closed after explicit SQLEndTran() but not impli

Posted by GavinScott on 27-May-2014 15:02

We have a COBOL application running on Linux using a home-grown middleware layer that talks to MS SQLServer (2008 and other versions) using the ODBC API via DataDirect's Connect for ODBC verison 5.3.

The program opens a dynamic server-side cursor on a table and proceeds to read forward through it fetching a block of records at a time. Eventually it finds a record it wants to update, and proceeds to do another server-side cursor based select on the same table and reads related records and then proceeds to do UPDATE and INSERTs (which are condition based and not based on CURRENT OF either cursor).

An older version of this system works fine using implicit transactions. Performing the INSERT and UPDATEs does not seem to affect the first cursor that was created.

The new version of the middleware library is doing an explicit SQLEndTran() based transaction around the updates, and this is closing all the existing cursors, causing the application to fail when it tries to fetch the next block of records using the original cursor.

I am attempting to understand the behavior of all this and what options there are that would not involve changing the original logic in the COBOL code (obviously there are lots of ways to do that).

We have tried setting various options on the cursors, the statements and the connections to try to affect the SQL_CURSOR_COMMIT_BEHAVIOR, SQL_COPT_SS_PRESERVE_CURSORS, etc. Using SET CURSOR_CLOSE_ON_COMMIT via T-SQL, etc.

In all cases when we ask SQLGetInfo() what the state of SQL_CURSOR_COMMIT_BEHAVIOR we get back SQL_CB_CLOSE, indicating that a commit will close all cursors on the connection.

So my questions are:

1) Is there a way to get cursors to survive an SQLEndTran(), and if so what is the proper set of options that need to be in place on the cursor/connection/statement to make this work.

2) Why would it be working in the case of implicit transactions (when the documentation suggests this should probably close cursors as well).

Thanks for any answers, suggestions, or other advice :)

G.

All Replies

Posted by eugenel on 27-May-2014 21:16

You can use the following sql to check your database settings for SQL_CURSOR_COMMIT_BEHAVIOR:

   select name, is_cursor_close_on_commit_on from sys.databases

and use the command below to set it:

   SET CURSOR_CLOSE_ON_COMMIT ON | OFF

SET CURSOR_CLOSE_ON_COMMIT controls the same behavior as the CURSOR_CLOSE_ON_COMMIT database option. If CURSOR_CLOSE_ON_COMMIT is set to ON or OFF, that setting is used on the connection. If SET CURSOR_CLOSE_ON_COMMIT has not been specified, the value in the is_cursor_close_on_commit_on column in the sys.databases catalog view applies.

The SQL Native Client OLE DB Provider for SQL Server and the SQL Native Client ODBC driver both set CURSOR_CLOSE_ON_COMMIT to OFF when they connect. DB-Library does not automatically set the CURSOR_CLOSE_ON_COMMIT value.

When SET ANSI_DEFAULTS is ON, SET CURSOR_CLOSE_ON_COMMIT is enabled.

Hope this helps.

This thread is closed