Database transactions

Work with the database for all the actions in D2000 environment (ESL script, D2000 HI environment) is implemented by process D2000 DBManager. Execution of individual actions (SQL commands) is performed within some physical connection to the database. Several connections to one database may be created concurrently.

The existence of a connection is important during controlling the transaction access to the database. According to the mode of connection settings, the individual actions (SQL commands) are executed:

• immediately after their typing - Auto Commit mode,
• after the execution of the command COMMIT, that, in principle, finishes the transaction and applies all changes caused by the previous actions into the database. All the changes before the execution of the command COMMIT are regarded as pending.

Transaction mode, in many cases, is necessary to work with the database and may be controlled by ESL facilities.

ESL defines sets of commands for accessing a database table. Each of the sets is characterized (and is limited) by the existence of the logical operation CONNECT and DISCONNECT (e.g. DB_CONNECT and DB_DISCONNECT). These operations always create an access to a database table by means of an existing Connection. If Connection is not determined during the execution of the action CONNECT, process D2000 DBManager will use so-called Automatic connection (Default connection). This works in Auto Commit mode and is shared (other CONNECT without the determination of Connect uses again the existing Automatic connection). Process D2000 DBManager will generate it automatically.

The following actions are intended to work with Connection:

By default, every access to database table in D2000 environment is done by means of Automatic connection, if not stated differently. This fact is represented in the following figure, where:

• HI process uses Automatic connection, for example during the work with the table in Database tables.
• Script 1 connections to table using the action DB_CONNECT without determining the Connection, and all consecutive action DB_* will be executed within Automatic connection.
• Script 2 creates a new Connection. It executes the action DB_CONNECT within the Connection. After performing the required changes (which must be confirmed by the action DB_TRANS_COMMIT), it terminates the connection using the action DB_TRANS_CLOSE.
• Script 3 uses the actions SQL_*for accessing the database. In this case, the database is defined by so-called connection string and therefore D2000 DBManager creates a new Connection. If the connection string contains the key word ACD (Auto Commit Disable), Connection is in the transaction mode and it is necessary to use the SQL command COMMIT.