4 Database Manipulation

Transactions

The Eloquence A.06.00 data base provides transactions. Transactions are used to ensure data base integrity. After a DBBEGIN statement, all data base modifications are no longer stored permanently in the data base. A subsequent DBCOMMIT statement is required to make any pending operations permanent in the database.

The DBROLLBACK statement provides a rollback operation, that reverts pending database operations. After a successful (top level) commit, the transaction is guaranteed to be present in the data base, even in case of a server crash. So it can be guaranteed, that either all dependend operations are saved entirely or no modifications are done.

Pending data base changes are neither visible to other users nor can they be changed concurrently. All pending data base records are locked automatically by the data base server and any attempt to modify them will cause the concurrent task to become paused.

Transactions can be nested. The DBCOMMIT or DBROLLBACK statements usually operate on the last (sub-) transaction. Data base modifications are not stored permanently in the data base, until a top level DBCOMMIT is executed. The DBROLLBACK statement can be used to undo all pending modifications until a specific checkpoint.

Please note, that the transaction handling statements do not operate on a particular data base. Instead they operate on all data bases at once. A pending commit or rollback is even performed after closing the data base. In case a data base server connection is lost (for example, because the server has been killed), all pending modifications on all data bases are automatically reverted.

Each transaction gets a unique is assigned with the DBBEGIN statement and a name of this transaction can be defined to address this transaction later on.

The DBBEGIN Statement

The DBBEGIN statement begins a new (sub-) transaction. When this is the first transaction, it is called top level transaction. No modifications are permanently saved in the Eloquence database until the top level transaction is committed. A subsequent DBBEGIN begins a new subtransaction, which can be controlled separately with the DBCOMMIT and DBROLLBACK statements.

Up to 20 transactions can be nested. Each DBBEGIN statement returns a unique (process specific) transaction id, which can be used with the DBROLLBACK statement to revert all modifications until this state.

DBBEGIN (comment, mode, status(*))

The parameters are:

comment
A string variable containing a comment which can be used to give this transaction a name. The name is optional and can be empty.
mode
A numeric expression equal to 1.
status
An integer array variable that returns status information after DBOPEN is executed. The array must contain at least ten elements in its right-most dimension.
 
Array Element Value Description
10CW.
20Transaction ID
30Transaction Level

The DBCOMMIT Statement

The DBCOMMIT statement commits a transaction. If this is a top level transaction, modifications are made permanently in the data base. If a subtransaction is committed, it becomes part of its parent transaction.

DBCOMMIT (mode, status(*))

The parameters are:

mode
A numeric expression equal to:
1

commit all transactions

2

commit top level transaction

status
An integer array variable that returns status information after DBCOMMIT is executed. The array must contain at least ten elements in its right-most dimension.
 
Array Element Value Description
10CW.

The DBROLLBACK Statement

The DBROLLBACK statement is used to undo a pending transaction. If this is a top level transaction, all pending modifications are reverted. If applied to a subtransaction all modifications including the enclosing DBBEGIN statement are reverted.

DBROLLBACK (ID, mode, status(*))

The parameters are:

ID
Transaction ID, used with mode 2.
mode
A numeric expression equal to:
1

Rollback current (sub-)transaction

2

Rollback given transaction

3

Rollback top level transaction

status
An integer array variable that returns status information after the DBROLLBACK is executed. The array must contain at least ten elements in its right-most dimension.
In Mode 2, a transaction id must be specified which was obtained from DBBEGIN. This can be used to revert up to a specific checkpoint.

 
Array Element Value Description
10CW.
30Transaction Level

Eloquence Database Manual - 19 DEC 2002