G
GavinScott
Guest
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.
Continue reading...
G.Continue reading...