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.|
Not enough rooms available.|
Changes have been rolled back . " window.
else begin transaction
modify records
CONFIRMED := YES .
commit .
end
end

This procedure uses the current SQLCODE variable to verify that the first transaction was successfully committed before beginning the second transaction. If the current SQLCODE is not equal to zero (i.e., the first transaction was not successfully committed), the SQL engine automatically rolls back the changes and displays a message that notifies the user that the transaction failed. If the current SQLCODE is zero, the first transaction is committed and DataEase continues processing.