Transactions in ODBC are managed at the connection level. When an application completes a transaction, it commits or rolls back all work done through all statement handles on that connection. To commit or roll back a transaction, applications should call SQLEndTran rather than submitting a COMMIT or ROLLBACK statement.
An application calls SQLSetConnectAttr to switch between the two ODBC modes of managing transactions:
Each individual statement is automatically committed when it completes successfully. When running in autocommit mode no other transaction management functions are needed.
All executed statements are included in the same transaction until it is specifically terminated by calling SQLEndTran.
Autocommit mode is the default transaction mode for ODBC. When a connection is made, it is in autocommit mode until SQLSetConnectAttr is called to switch to manual-commit mode by setting autocommit mode off. When an application turns autocommit off, the next statement sent to the database starts a transaction. The transaction then remains in effect until the application calls SQLEndTran with either the SQL_COMMIT or SQL_ROLLBACK options. The command sent to the database after SQLEndTran starts the next transaction.
If an application switches from manual-commit to autocommit mode, the driver commits any transactions currently open on the connection.
ODBC applications should not use Transact-SQL transaction statements (such as BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION) because this can result in indeterminate behavior in the driver. An ODBC application should either:
| SQLEndTran | SQLSetConnectAttr |