Most RDBMSs can handle sequences of SQL statements as one block: all statements succeed or all fail. This typical behavior is called a transaction mechanism.

In some rare cases, the RDBMS is not capable of handling transactions, because this functionality either is not implemented or not enabled for the database, as with Informix.

A block is delimited by the initiation of the transaction and by its commitment or rollback.

The activation of the transaction mechanism can be checked by a call to one of the functions IldDbms::isTransactionEnabled or IldDbms::getInfo(IldTransactionCapable,...).

Rogue Wave DB Link offers a unified API that avoids RDBMS specificity. The member functions in the API take two optional arguments. However, for portability considerations, we urge you to always pass a value to each optional argument and to stick to the protocol that consists of sending the SQL statements by means of the IldRequest object that initiated the transaction. If you do not follow this rule when using MS SQL Server, you will encounter an unexpected behavior: the request that initiates the transaction is not the one that executes the SQL statements. This amounts to an empty transaction and results in the following:

With Rogue Wave DB Link, you can use member functions of the class IldDbms for any transaction-related command, whatever your target RDBMS, as shown in Table 3.5:

For all these functions, the request argument (a pointer to an IldRequest object) is optional for most of the supported RDBMSs. However, this argument is mandatory for the Sybase and MS SQL Server database systems, which all require the IldRequest object to control the commands that execute the SQL statements enclosed in the transaction.

Some RDBMSs have implemented the “auto-commit mode” feature. When on, this mode commits each SQL statement when it is executed. Each database system has a unique notion of transaction control and, therefore, a unique interface to implement it.

With Sybase and MS SQL Server, the first argument is mandatory for all transaction-control functions. The second argument is used only for Sybase. Sybase TransactSQL allows you to name a transaction. For all other ports, both arguments are ignored. The following items relevant to transactions are described:

To initiate a transaction, call the member function IldDbms::startTransaction, as shown in the following example:

This function takes two arguments, which are optional for most RDBMSs.

With Informix, it is an error to start, commit, or roll back a transaction several times on a connection. Rogue Wave DB Link ensures that superfluous calls to these functions will not raise an error: they simply do nothing. However, you must be aware that the actual transaction still starts with the first call to IldDbms::startTransaction and ends with the first call to either IldDbms::commit or IldDbms::rollback.

To commit a transaction, call the member function IldDbms::commit. This function takes two optional arguments, as shown in the following example.

To roll back all effects of the SQL statements executed since the transaction was initiated, call the member function IldDbms::rollback, as shown in the following example:

This member function takes two optional arguments, which must be the same as those used to initiate the transaction.

To switch the autocommit mode on or off, use the member function IldDbms::autoCommitOn or IldDbms::autoCommitOff. Like other transaction-control functions, these functions take two optional arguments.