Transactions in ODBC

Transactions in ODBC are managed at the connection level. When an application completes a transaction, it commits or rolls back all work completed through all statement handles on that connection. To commit or roll back a transaction, applications should call SQLEndTran instead of submitting a COMMIT or ROLLBACK statement.

An application calls SQLSetConnectAttr to switch between the two ODBC modes of managing transactions:

• Autocommit mode

  Each statement is automatically committed when it is completed successfully. When you run in autocommit mode, no other transaction management functions are required.

• Manual-commit mode

  All executed statements are included in the same transaction until it is specifically stopped 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, or ROLLBACK TRANSACTION because this can cause indeterminate behavior in the driver. An ODBC application should run in autocommit mode and not use any transaction management functions or statements, or run in manual-commit mode and use the ODBC SQLEndTran function to either commit or roll back transactions.

SQLEndTran Function

Conformance

Version Introduced: ODBC 3.0 Standards Compliance: ISO 92

Summary

SQLEndTran requests a commit or rollback operation for all active operations on all statements associated with a connection. SQLEndTran can also request that a commit or rollback operation be performed for all connections associated with an environment.

Note
For more information about what the Driver Manager maps this function to when an ODBC 3.x application is working with an ODBC 2.x driver, see Mapping Replacement Functions for Backward Compatibility of Applications.

Syntax

---

SQLRETURN SQLEndTran(
     SQLSMALLINT   HandleType,
     SQLHANDLE     Handle,
     SQLSMALLINT   CompletionType);

Arguments

---

HandleType

[Input] Handle type identifier. Contains either SQL_HANDLE_ENV (if Handle is an environment handle) or SQL_HANDLE_DBC (if Handle is a connection handle).

Handle

[Input] The handle, of the type indicated by HandleType, indicating the scope of the transaction. See "Comments" for more information.

CompletionType

[Input] One of the following two values:

SQL_COMMIT SQL_ROLLBACK

Returns

---

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, SQL_INVALID_HANDLE, or SQL_STILL_EXECUTING.

reference:

http://msdn.microsoft.com/en-us/library/ms131281.aspx

http://msdn.microsoft.com/zh-cn/library/ms716544.aspx