Autocommit

By default ODBC connections start in auto-commit mode. Each individual SQL statement is executed within its own transaction that is committed when the statement completes.

Explicit transactions can be used by disabling auto-commit with SQLSetConnectAttr. In this mode a SQL statement starts an implicit transaction that runs until the transaction is committed or rolled back. After the commit or rollback another transaction is automatically created.

Only one transaction can be active on the connection at the same time. That transaction is applied to all statements run on the connection no matter what statement handle they are executed from.

SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;

// This creates a new transaction for the next group of SQL statements
SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) 1, 0);

// Both of these statements execute in the same transaction
SQLExecDirect(hstmt, "INSERT INTO ExampleTable(ExampleCol) VALUES ('ExampleVal1')", SQL_NTS);
SQLExecDirect(hstmt, "INSERT INTO ExampleTable(ExampleCol) VALUES ('ExampleVal2')", SQL_NTS);

// This ends the current transaction and begins a new one for the next groupp of statements
SQLEndTran(SQL_HANDLE_DBC, hdbc, ...);

Committing and Rolling Back Transactions

Once auto-commit has been disabled, the active transaction may be committed or rolled back using SQLEndTran. Committing accepts all the changes in the transaction and persists them if they are consistent:

SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);

Rolling back undoes all changes performed in the transaction:

SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_ROLLBACK);