rollback

Type

Procedural Command

Purpose

The rollback command is used to cancel a partially completed transaction when processing data stored in an SQL database. A procedure may contain any number of rollback commands.

The DQL commit and rollback commands are used to divide a procedure into multiple transactions (a transaction can be any command or procedure that changes data). By defining separate transactions within a DQL Procedure, it's possible to rollback partially completed changes that may leave the data in an inconsistent state.

When processing reaches a rollback command, DataEase immediately cancels all changes made to the data since the last commit command was processed. If a transaction accesses more than one server, the rollback command automatically cancels the transaction changes on each of the active servers. Once a transaction is committed, it cannot be undone by a rollback command.

The corresponding SQL command, ROLLBACK TRANSACTION, is used to cancel an embedded exec SQL statement in a DQL Procedure.

Syntax

rollback .

Usage

A rollback command can be used anywhere in a procedure.

The rollback command is often preceded by a conditional if statement. The if condition either checks the current status or current SQLCODE variable to see if the transaction was successful, or tests whether a specific business rule has been violated. If the last transaction was not fully completed or the business rule was violated, the rollback command tells DataEase to cancel the partially completed changes. Otherwise, processing continues.

If you do not include a rollback command in a procedure, DataEase automatically issues a rollback command at the end of any procedure that is interrupted before completion (due to a system malfunction, or when you voluntarily abort because of a resource conflict).

Example :

for RESERVATIONS with POSTED = NO ;

begin transaction

enter a record in INVOICES

copy all from RESERVATIONS .

modify records in MEMBERS

RESERVATIONSTOTAL := RESERVATIONSTOTAL + RESERVATIONS TOTAL DUE ;

if any MEMBERS RESERVATIONS TOTAL > 20,000 then

rollback .

modify records in MEMBERS

RESERVATIONSTOTAL := RESERVATIONSTOTAL - RESERVATIONS TOTAL DUE ;

message " No member can have an account

balance over $20,000. This reservation

has been canceled. " .

else

modify records

POSTED = YES .

commit .

message " Member and Reservation Information

updated. " .

end

end

This first part of this procedure contains a transaction that enters a record in a table that owns an SQL table (INVOICES) and modifies a record in a native DataEase table (MEMBERS). The script enters a record in the INVOICES table for each record in the RESERVATIONS table that has not been posted by entering all the information for each unposted reservation into an invoice.

The script then adds the reservation's total to the member's total. If the member's cumulative reservation total exceeds $20,000, the reservation is canceled, the modifications made to the SQL table are rolled back, and the cost of the reservation is subtracted from the member's total due.

The second part of the transaction modifies the current RESERVATIONS record by setting the POSTED field to yes. This part of the script is executed only if the first part of the transaction is successful. Once the second part is completed, the entire transaction is committed.

Note: When a transaction fails and data changes are rolled back, only the last group of modifications is canceled. Earlier transactions that have already been committed are not canceled. Carefully placed commit and rollback commands in your DQL procedures can minimize the work that must be repeated if an error occurs during processing.