Transactions
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);