From Support Wiki
25 August, 1997
Using Cursor Context Preservation with Sybase System 11
Description:
The System 10/11 router in Centura products supports the concept of Cursor Context Preservation (CCP). However, a recent fix to Sybase bug 95054 in System 11 effectively disables this support.
Status:
Although this problem is caused by a change to the Sybase server, we are tracking it as our own defect 63420 since it represents a failure in a feature we intended to support. Whether we can resolve this problem is currently under review. We recommend not applying a Software Release from Sybase to System 11 that contains this bug fix if you are currently using or developing applications that rely on CCP. If there are other issues affecting your site that are resolved in a Sybase SWR (Software Release) that also contains this bug fix, please consider the workarounds suggested later in this document.
Additional Information:
This information comes from Sybase for bug 95054 in Software Release 6886:
SET CHAINED MODE ON does not close cursors during ROLLBACK/COMMIT TRANSACTION as documented. It should work the same as SET CLOSE ON ENDTRAN ON. This could possibly cause 8201 errors (Keep count of descriptor was expected to be X. Instead Y was found).
In the Sybase SQL Server Reference manual contains information about transactions, chained mode, and SQL standards:
To get transactions that comply with the SQL standards, you must set the chained and transaction isolation level 3 options at the beginning of every application which changes the mode and isolation level for subsequent transactions. If your application uses cursors, you must also set the close on endtran option…. The SQL standards require chained mode, which implicitly begins a transaction before any data retrieval or modification statement. These statements include: delete, insert, open, fetch, select, and update. You must still explicitly end the transaction with commit or rollback…. A cursor's state (open or closed) does not change when a transaction ends through a commit or abort, unless the close on endtran option or the chained mode is set. The set close on endtran statement enforces behavior that is compliant with the SQL standards: it associates an open cursor with its active transaction. Committing or rolling back that transaction automatically closes any open cursors associated with it. Opening a cursor when the chained mode is set starts a transaction. SQL Server closes that cursor when the chained transaction is closed or rolled back.
Indeed, the ANSI SQL specification (1992) states unequivocally that:
For every open cursor CR in any <module> associated with the current SQL-transaction, the following statement is implicitly executed: "close CR".
The standard does not seem to make room for exceptions to this rule.
Centura is continuing to investigate this issue and will update this tech alert when additional information becomes available.
Workarounds
If possible, applications should be designed to avoid the requirement for Cursor Context Preservation. By so doing, the application will stay within the ANSI standards. CCP can be a very useful feature, though. There are several possible workarounds to this problem. For any given application, only its designers and developers in conjunction with their Database Administrator can determine the best option.
1. The bug fix only applies to chained mode, not to explicit transactions. Therefore, an application can enable autocommit (which turns off chained mode) and control the start of transactions by issuing 'begin transaction' statements directly. A good place to start the transaction would be immediately after the commit/rollback of the current transaction. This will behave similarly to chained mode, which starts a transaction prior to the execution of the next statement as described in the SQL Server Reference manual passage quoted earlier in this document. For example:
Call SqlCommit(hSql)
Call SqlPrepareAndExecute(hSql,'begin transaction')
2. For result sets of a reasonable size, enable result set mode and pre-fetch all the results into the front-end result sets (FRS) by using SqlGetResultSetCount(). "Reasonable size" can be defined as the amount of time a user is willing to wait for the results to be read from the server. Once the result set has been accessed in this way, all further access is from the FRS; the status of the cursor from the Sybase perspective is no longer an issue.
3. Customers can manipulate the ENABLEMULTIPLECONNECTIONS parameter programmatically, issuing SELECTs that require CCP on a different connection that is in autocommit mode. The rest of the Sql Handles would belong to a connection which is operating with autocommit off (which sets chained mode on) and would issue the data modification statements (UDPATE, DELETE, INSERT). Note that this might introduce locking and concurrency issues from within the same application since the two (or more) connections are viewed as separate entities from the server's perspective. In such cases, these Sql Handles can be set to read-only (RO) isolation level. Again, the value of this or any approach must be studied and understood by the developers.
Larry Johnson
Senior Staff Consultant
Centura Software
Portions of the preceding are copyrighted by Sybase Corporation and by the American National Standards Institute.