commit

Type

Procedural Command

Purpose

The commit command is used to mark the end of a transaction. A procedure can contain any number of commit commands.

Syntax

commit .

A transaction can be all or any part of a DQL Procedure that changes data and must be processed as a single unit to maintain integrity (such as a procedure that transfers money from a customer's savings account into his/her checking account). When accessing data in an SQL database, DataEase usually treats a DQL Procedure as a single transaction by default and commits all the changes made to the data simultaneously at the end of the procedure. When you're running a shared application on a network, treating a whole procedure as a single transaction may reduce concurrency. For this reason, DataEase provides the tran off command (to turn transactions off) and the commit command to divide a procedure into several smaller transactions.

Usage

The commit command is used with the begin transaction command to divide a procedure into several transactions. By defining separate transactions within a DQL Procedure, it's possible to rollback any partially completed changes that leave data in an inconsistent state and to recover from system or user-generated errors.

A commit command can be used anywhere in a procedure. When DataEase converts a script into SQL, the position of a commit command in the script determines how DataEase processes it. If the commit command is outside a for loop or conditional statement, DataEase translates the DQL commit into an SQL COMMIT command (this commits all changes made to the data since the last commit or begin transaction command was processed). If the commit is inside a for loop or conditional statement, DataEase inserts the SQL SAVEPOINT command.

The commit command is often followed by a conditional statement that uses the current status, current SQLCODE, or current SQLCOUNT variable to determine if the preceding transaction was committed successfully (see Example 2).

Example 1

for RESERVATIONS ;

begin transaction

modify records

TOTAL DUE := TOTAL DUE - AMOUNT PAID .

commit .

If DEPARTURE DATE < current date - 90 then

begin transaction

modify records

TOTAL DUE := TOTAL DUE * LATE PENALTY .

commit .

end

end

This procedure contains two transactions. The first transaction modifies the RESERVATIONS records by subtracting the AMOUNT PAID from the TOTAL DUE. The second transaction modifies the records of past due accounts (that is, reservations that are not paid 90 days after the RESERVATION DATE) by multiplying the TOTAL DUE by a LATE PENALTY factor. Each transaction is committed as it is completed. The commit commands tell DataEase to post the changes in the appropriate table on the server and then continue processing the procedure.

Example 2

for RESERVATIONS ;

begin transaction

modify records in CLUB ROOMS with

( CLUB ID = RESERVATIONS CLUB ID and

RESERVATION DATE = RESERVATIONS RESERVATIONDATE)

VACANCIES := VACANCIES - ROOMSREQUESTED .

commit .

if current SQLCODE not = 0 then

message " Last transaction was unsuccessful.