Transaction Handling

The ODBC specification supports both autocommitting of transactions and explicit committing of transactions. For information, see the sections on transactions in both "Developing Applications" and "Developing Drivers" in the Microsoft ODBC 2.0 Programmer's Reference and SDK Guide. The default for the driver is autocommit mode with a default transaction isolation specified.

The Open Data Services ODBC driver supports three transaction models:

• The DBMS implicitly starts a transaction and an explicit COMMIT/ABORT type statement ends it. DB2 behaves this way.
• The DBMS automatically commits the statement after each execution, an explicit BEGIN TRANSACTION type statement starts an explicit transaction and an explicit COMMIT/ABORT type statement ends the explicit transaction. SQL Server behaves this way.
• The DBMS supports both autocommit and explicit transactions. The AS/400® behaves this way.

The first transaction model requires either the Open Data Services ODBC driver or the gateway itself to issue explicit commits after each SQL statement to simulate autocommit mode. When autocommit is off, the DBMS implicitly starts the transaction and the ODBC application explicitly ends the transaction. If the IDS_AUTOCOMMIT_OFFSET strings are not empty, the Open Data Services ODBC driver sends the appropriate string to the gateway to turn autocommit on or off in the gateway. In this case, the gateway issues its own commits after each SQL statement. If these strings are empty, the Open Data Services ODBC driver assumes responsibility for issuing an explicit commit (see the IDS_TRANSACT_OFFSET string) after each SQL statement when autocommit is turned on. If the gateway is performing the autocommit and if the default for the gateway can be configured to set autocommit off, be sure to include the proper initialization string to set autocommit on in the gateway. For more information, see Gateway Initialization.

The second transaction model requires either the Open Data Services ODBC driver or the gateway itself to issue explicit transaction starts before the first SQL statement after an explicit commit/rollback when the ODBC application is not in autocommit mode. If the IDS_AUTOCOMMIT_OFFSET strings are not empty, the Open Data Services ODBC driver sends the appropriate string to the gateway to turn autocommit on or off in the gateway. In this case, the gateway issues the explicit transaction start before the first SQL statement. If these strings are empty, the Open Data Services ODBC driver issues a transaction start (see the IDS_BEGINTRAN string) before the first SQL statement. If the default for the gateway can be configured to set autocommit off, be sure to include the proper initialization string to set autocommit on in the gateway. For more information, see Gateway Initialization.

The third transaction model has the DBMS doing most of the work. The Open Data Services ODBC driver sends the appropriate string to turn autocommit on or off (see IDS_AUTOCOMMIT_OFFSET strings). If autocommit support is implemented in the DBMS as one of the transaction isolation levels, the gateway is responsible for correctly setting the previous isolation level when autocommit is turned off.

When autocommit is turned off and the application ends a transaction (SQLTransact), the appropriate string to commit or terminate is sent to the gateway. If the SQLTransact option is SQL_COMMIT, the IDS_TRANSACT_OFFSET+SQL_COMMIT string is sent. If the SQLTransact option is SQL_ROLLBACK, the IDS_TRANSACT_OFFSET+SQL_ROLLBACK string is sent.