begin transaction

Type

Procedural Command

Purpose

The begin transaction command is used to mark the start of a unit of work called a transaction.

A transaction can be a whole procedure or any part of a procedure that enters or modifies data (a procedure that includes an enter a record, modify records, or delete records command). When processing reaches a begin transaction command, DataEase treats the statements that follow as part of the same transaction until it reaches a commit, rollback, or another begin transaction command. A procedure can contain any number of begin transaction commands.

Syntax

begin transaction

Usage

When a DQL Procedure is translated into SQL, DataEase inserts an implicit begin transaction command at the beginning of the procedure. If you insert an explicit begin transaction command outside a for loop or conditional statement, it is ignored when the procedure is translated into SQL. If you insert an explicit begin transaction command inside a for loop or conditional statement, it is interpreted as a savepoint when the procedure is translated into SQL.

The commit command is used to end a transaction and save all the modified data. Once a transaction is committed, it cannot be undone by a rollback command.

Example 1

for RESERVATION AGENTS ;

begin transaction

for DAILY RESERVATIONS with ( POSTED = NO) ;

enter a record in YEARLY RESERVATIONS

copy all from DAILY RESERVATIONS .

modify records

POSTED = YES .

end

if sum of YEARLY RESERVATIONS AMOUNT > 80000

then

modify records

CHRISTMAS BONUS := YES .

end

commit .

end

The procedure in Example 1 contains three operations that are treated as a single transaction. The first operation enters a record in the YEARLY RESERVATIONS table using the values in the DAILY RESERVATIONS table. The second operation modifies the POSTED field in the DAILY RESERVATIONS table to indicate that the record was posted to the YEARLY RESERVATIONS table. The third operation modifies the records in the RESERVATION AGENTS table whose yearly sales total is greater than $80,000.00. When processing reaches the commit command, the updates to the parent record and both child records are saved together. If any part of the transaction fails, the entire transaction is rolled back.

Example 2

for RESERVATIONS ;

begin transaction

modify records in MEMBERS

ACCOUNT BALANCE := ACCOUNT BALANCE +

RESERVATIONS TOTALDUE .

modify records in RESERVATION AGENTS

DAILY TOTAL := DAILYTOTAL +

RESERVATIONS TOTALDUE .

modify records in CLUB ROOMS

VACANCIES := VACANCIES -

RESERVATIONS ROOMSREQUIRED .

modify records

POSTED:= YES .

commit .

if current SQLCODE not = 0 then

list records

RESERVATION ID .

end

end

The procedure in Example 2 contains four modify operations that are treated as a single transaction. The first operation updates a record in the MEMBERS table using the value in the TOTAL DUE field in the RESERVATIONS table. The second operation updates a record in the RESERVATION AGENTS table using the value in the TOTAL DUE field in the RESERVATIONS table. The third operation updates a record in the CLUB ROOMS table using the value in the ROOMS REQUIRED field in the RESERVATIONS table. The fourth operation updates the current record by setting the POSTED field to YES.

When processing reaches the commit command, all four of the modifications are committed together. If any part of the transaction fails, the entire transaction (all four modifications) is rolled back. The current SQLCODE variable is set to zero if the commit is successful and to an SQL engine-specific error code if the commit fails. If DataEase is unable to commit all the modifications for a specific order, the order number is listed in the procedure output.