How to start a transaction in ODBC?

Question

How do you start a transaction in ODBC? Specifically i happen to be dealing with SQL Server, but the question can work for any data source.

In native T-SQL, you issue the command:

BEGIN TRANSACTION
--...
COMMIT TRANSACTION
--or ROLLBACK TRANSACTION

In ADO.net, you call:

DbConnection conn = new SqlConnection();
DbTransaction tx = conn.BeginTransaction();
//...
tx.Commit();
//or tx.Rollback();

In OLE DB you call:

IDBInitialize init = new MSDASQL();
IDBCreateSession session = (init as IDBCreateSession).CreateSession();
(session as ITransactionLocal).StartTransaction(ISOLATIONLEVEL_READCOMMITTED, 0, null, null);
//...
(session as ITransactionLocal).Commit();
//or (session as ITransactionLocal).Rollback();

In ADO you call:

Connection conn = new Connection();
conn.BeginTrans();
//...
conn.CommitTrans();
//or conn.RollbackTrans();

What about ODBC?

For ODBC, Microsoft gives a hint on their page Transactions in ODBC:

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

• Manual-commit mode

  All executed statements are included in the same transaction until it is specifically stopped by calling SQLEndTran.

Which means i just need to know what parameters to pass to SQLSetConnectAttr:

HENV environment;
SQLAllocEnv(&environment);
HDBC conn;
SQLAllocConnect(henv, &conn);

SQLSetConnectAttr(conn, {attribute}, {value}, {stringLength});
//...
SQLEndTran(SQL_HANDLE_ENV, environment, SQL_COMMIT);
//or SQLEndTran(SQL_HANDLE_ENV, environment, SQL_ROLLBACK);

But the page doesn't really give any hint about which parameter will start a transaction. It might be:

SQL_COPT_SS_ENLIST_IN_XA

To begin an XA transaction with an XA-compliant Transaction Processor (TP), the client calls the Open Group tx_begin function. The application then calls SQLSetConnectAttr with a SQL_COPT_SS_ENLIST_IN_XA parameter of TRUE to associate the XA transaction with the ODBC connection. All related database activity will be performed under the protection of the XA transaction. To end an XA association with an ODBC connection, the client must call SQLSetConnectAttr with a SQL_COPT_SS_ENLIST_IN_XA parameter of FALSE. For more information, see the Microsoft Distributed Transaction Coordinator documentation.

But since i've never heard of XA, nor do i need MSDTC to be running, i don't think that's it.

---

maruo answered it. But to clarify:

HENV environment;
SQLAllocEnv(&environment);
HDBC conn;
SQLAllocConnect(henv, &conn);

SQLSetConnectAttr(conn, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, SQL_IS_UINTEGER);
//...
SQLEndTran(SQL_HANDLE_ENV, environment, SQL_COMMIT);
//or SQLEndTran(SQL_HANDLE_ENV, environment, SQL_ROLLBACK);

SQLSetConnectAttr(conn, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_ON, SQL_IS_UINTEGER);

Answer 1

ODBC can operate in two modes: AUTOCOMMIT_ON and AUTOCOMMIT_OFF. Default is AUTOCOMMIT_ON. When Autocommit is ON each command you start using a Session Handle associated with that Connection will be Auto-Committed.

Let's see how "manual commit" (alias AUTOCOMMIT_OFF) works.

First you switch AUTOCOMMIT OFF Using something like this:

if (!SQL_SUCCEEDED(Or=SQLSetConnectAttr(Oc, SQL_ATTR_AUTOCOMMIT,   
                      (SQLPOINTER)SQL_AUTOCOMMIT_OFF,
                       SQL_IS_UINTEGER))) {
// error handling here
}

Where "Oc" is the connection handle.

Second You run all commands as usual: Prepare / Execute statements, Bind Parameters, etc.... There's NO specific command to "START" a transaction. All commands after you switched Autocommit OFF are part of the transaction.

Third You commit:

if (!SQL_SUCCEEDED(Or=SQLEndTran(SQL_HANDLE_DBC, Oc, SQL_COMMIT))) {
// Error handling
}

And - again - all new commands from now on are automatically part of a new transaction that you will have to commit using another SQLEndTran command as show here above.

Finally... to switch AUTOCOMMIT_ON Again:

if (!SQL_SUCCEEDED(Or=SQLSetConnectAttr(Oc, SQL_ATTR_AUTOCOMMIT, 
                  (SQLPOINTER)SQL_AUTOCOMMIT_ON, SQL_IS_UINTEGER))) {
 // Error Handling
}