There are two ways to control transactions through Microsoft SQL Server:

• setting isolation levels

• using transaction control methods

Descriptions of these methods follow.

2.11.1 Setting Isolation Levels

Table 5: Setting the isolation level

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

2.11.2 Using Transaction Control Methods

You can explicitly control transactions through the following methods:

RWDBConnection::beginTransaction(const RWCString& savepoint = RWCString()) RWDBConnection::rollbackTransaction(const RWCString& savepoint = RWCString()) RWDBConnection::commitTransaction(const RWCString& savepoint = RWCString()) RWDBConnection::setSavepoint(const RWCString& savepoint)

These methods are implemented using the following Transact-SQL transaction statements:

"begin transaction [savepoint]" "rollback transaction [savepoint]" "commit transaction [savepoint]" "save transaction savepoint"

An application can add the DB Interface Module transaction methods to its code to take explicit control of its transaction blocks. Transactions may be nested and the -savepoint feature is supported. The -savepoint feature allows a current transaction to be partially rolled back to a marked point.

---

At the time of this writing, conflicting information was found in the MS SQL Server ODBC driver documentation regarding the use of -Transact-SQL transaction statements. Please see the Microsoft SQL Server readme for more information regarding transaction savepoints.

---

The example below demonstrates the use of the savepoint feature and the other transaction processing methods of the DB Interface Module.

// Assume we have a table myTable(c int) with no rows in it. RWDBInserter ins = myTable.inserter(); cn.beginTransaction("outmost"); // Begin transaction. // Transaction name is allowed. // Note: unlike savepoint name, // transaction name is not used // by server. ins << 1).execute(cn); // First insertion cn.beginTransaction("inner"); // Nested transaction begins ... cn.setSavepoint("svp1"); // Save first insertion (ins << 2).execute(cn); // Second insertion (ins << 3).execute(cn); // Third insertion cn.rollbackTransaction("svp1"); // Roll back second and // third insertions. cn.commitTransaction("inner"); // Pairing inner transaction cn.commitTransaction("outmost"); // Commit transaction on // part that is not rolled back. // The above program results in myTable holding one row of data. // Its value is 1.