Effect of Transactions on Cursors and Prepared Statements

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

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:

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.