2.10 Transaction Processing

Although the databases supported by ODBC differ, ODBC provides a standard API for controlling transactions and setting isolation levels. The DBTools.h++ ODBC access library maps its transaction control and isolation level methods to the corresponding ODBC methods. These methods are described in the following two sections.

2.10.1 Setting Isolation Levels

You can use the isolation() method of RWDBConnection to set the isolation level of the connected server. Table 5 shows the mapping between the argument you pass for IsolationType, and the isolation level set by the ODBC driver using the SQLSetConnectionOption() call:

Table 5 -- Setting the isolation level

RWDBConnection:: IsolationType	ODBC Isolation Level
Unknown	`SQL_TXN_READ_UNCOMMITTED`
ANSILevel1	`SQL_TXN_READ_COMMITTED`
ANSILevel2	`SQL_TXN_REPEATABLE_READ`
ANSILevel3	`SQL_TXN_SERIALIZABLE`

Note that not all ODBC drivers support the notion of isolation levels, and those that support isolation levels do not necessarily support all isolation levels. Special care should be taken when setting isolation levels, as this could affect all users of a server.

To determine the current isolation level, call RWDBConnection::isolation() without an argument.

2.10.2 Using Transaction Control Methods

You can explicitly control transactions through the following methods:

RWDBConnection::beginTransaction()
RWDBConnection::rollbackTransaction()
RWDBConnection::commitTransaction()

These methods have straightforward implementations that correspond to the following ODBC functions:

SQLSetConnectOption(SQLHDBC, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF)
SQLEndTran(..., SQL_ROLLBACK)
SQLSetConnectAttr(...,SQL_ATTR_AUTOCOMMIT,
                  (SQLPOINTER)SQL_AUTOCOMMIT_ON,...)

An application can add the DBTools.h++ transaction methods to its code to take explicit control of its transaction blocks. The following code demonstrates how these methods can be used to commit or to rollback transactions:

// Assume we have a table myTable(c int) with no rows in it.
RWDBInserter ins= myTable.inserter(); 

cn1.beginTransaction ();         // Begin transaction..

(ins << 1 ).execute(cn1);        // First insertion
...
cn1.commitTransaction();         // Save first insertion

cn1.beginTransaction();          // Begin another transaction
(ins << 2 ).execute(cn1);        // Second insertion
(ins << 3 ).execute(cn1);        // Third insertion
cn1.rollbackTransaction();       // Rollback transaction erasing 
                                 // second and third insertions..

// The above program results in myTable holding one row of data.
// Its value is 1.

Please note that nested transactions and savepoint features are not supported by ODBC.

NOTE: ODBC does not support nested transactions and savepoint features.

Attempting to create a nested transaction generates a server error. Attempting to use the savepoint features of the DBTools.h++ API generates the error RWDBStatus::notSupported for the connection that tried.