Connection transaction states

A connection to a database is said to be in one of a number of transaction states. Transaction states show how queries submitted on the connection are committed. Some server operations can only take place within a transaction. For example, updateable cursors can only be opened within a transaction.

The ITConnection class is used to manage connections and includes methods to set and inquire about the transaction state. The following table lists the connection transaction states.


State	Effect of setting this state	Significance when retrieved from ITConnection
None	Not allowed to set	Not connected to a server
Auto	Not allowed to set	In auto commit mode (each SQL statement is a separate transaction)
Begin	Start a transaction	Entered or in a transaction
Commit	Commit the transaction	Last transaction was committed
Abort	Abort the transaction	Last transaction was aborted/rolled back

The csql3.cpp example adds transaction monitoring capabilities to the SQL interpreter example. The following steps point out the transaction monitoring features:

If the session is within a transaction, print "TRANSACTION>" as the prompt. The following code shows the use of the GetTransactionState method to check the transaction state:
```
if (conn.GetTransactionState() == ITConnection::Begin)
{
    cout << "TRANSACTION> ";
}
else
{
    cout << "> ";
}
```
If the session exits while it is within a transaction, stop the transaction. The data is returned to the state it was in when the transaction started. The following code shows the use of the GetTransactionState method to check the transaction state and SetTransactionState to set the state:
```
if (conn.GetTransactionState() == ITConnection::Begin)
{
    cerr << endl
         << "Exit within transaction, aborting transaction"
         << endl;
    conn.SetTransaction(ITConnection::Abort);
}
```

The output from the example is similar to the following, when the user exits after issuing a begin work statement:

% csql3 
Connection established 
> begin work; 
0 rows received, Command:begin work
TRANSACTION> EOF 
Exit within transaction, aborting transaction