Effect of Transactions on Cursors and Prepared Statements

Committing or rolling back a transaction has the following effect on cursors and access plans:

All cursors are closed and access plans for prepared statements on that connection are deleted.

All cursors are closed and access plans for prepared statements on that connection remain intact.

All cursors remain open and access plans for prepared statements on that connection remain intact.

For example, suppose a data source exhibits the first behavior in this list, the most restrictive of these behaviors: Now suppose an application does the following:

1 Sets the commit mode to manual commit.

2 Creates a result set of sales orders on statement 1.

3 Creates a result set of the lines in a sales order on statement 2 when the user highlights that order.

4 Calls SQLExecute to execute a positioned update statement that has been prepared on statement 3 when the user updates a line.

5 Calls SQLEndTran to commit the positioned update statement.

Because of the data source’s behavior, the call to SQLEndTran in step 5 causes it to close the cursors on statements 1 and 2 and to delete the access plan on all statements. The application must reexecute statements 1 and 2 to re-create the result sets and reprepare the statement on statement 3.

In auto-commit mode, functions other than SQLEndTran commit transactions:

SQLExecute or SQLExecDirect. In the previous example, the call to SQLExecute in step 4 commits a transaction. This causes the data source to close the cursors on statements 1 and 2 and delete the access plan on all statements on that connection.

SQLBulkOperations or SQLSetPos. In the previous example, suppose that in step 4 the application calls SQLSetPos with the SQL_UPDATE option on statement 2 instead of executing a positioned update statement on statement 3. This commits a transaction and causes the data source to close the cursors on statements 1 and 2, and discards all access plans on that connection.

SQLCloseCursor. In the previous example, suppose that, when the user highlights a different sales order, the application calls SQLCloseCursor on statement 2 before creating a result of the lines for the new sales order. The call to SQLCloseCursor commits the SELECT statement that created the result set of lines and causes the data source to close the cursor on statement 1, and discards all access plans on that connection.

Applications, especially screen-based applications in which the user scrolls around the result set and updates or deletes rows, must be careful to code around this behavior.

To determine how a data source behaves when a transaction is committed or rolled back, an application calls SQLGetInfo with the SQL_CURSOR_COMMIT_BEHAVIOR and SQL_CURSOR_ROLLBACK_BEHAVIOR options.