Ends a transaction, preserving changes made to the database during the transaction.
exec sql [at connection_name] commit [transaction | tran | work] [transaction_name] end-exec
The keywords transaction, trans, and work are interchangeable in the rollback statement, except that only work is ANSI-compliant.
A name assigned to the transaction.
* Using unchained transaction mode to
* synchronize tables on two servers.
*
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 TITLE-ID PIC X(7).
01 NUM-SOLD PIC S9(9).
EXEC SQL END DECLARE SECTION END-EXEC.
...
EXEC SQL CONNECT :UID IDENTIFIED BY :PASS
AT connect1 END-EXEC.
EXEC SQL CONNECT :UID IDENTIFIED BY :PASS
AT connect2 END-EXEC.
...
PERFORM TRY-UPDATE.
TRY-UPDATE.
EXEC SQL AT connect1 BEGIN TRANSACTION END-EXEC.
EXEC SQL AT connect2 BEGIN TRANSACTION END-EXEC.
EXEC SQL AT connect1 SELECT sum(qty) INTO :NUM-SOLD
FROM salesdetail
WHERE title_id = :TITLE-ID END-EXEC.
EXEC SQL AT connect2 UPDATE current_sales
SET num_sold = :NUM-SOLD
WHERE title_id = :TITLE-ID END-EXEC.
EXEC SQL AT connect2 COMMIT TRANSACTION END-EXEC.
EXEC SQL AT connect1 COMMIT TRANSACTION END-EXEC.
IF SQLCODE <> 0
DISPLAY "Oops! Should have used 2-phase commit".
* Using chained transaction mode to synchronize
* tables on two servers.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 TITLE-ID PIC X(7).
01 NUM-SOLD PIX S9(9) COMP.
EXEC SQL END DECLARE SECTION END-EXEC.
...
EXEC SQL WHENEVER SQLERROR PERFORM ABORT-TRAN END-EXEC.
PERFORM TRY-UPDATE.
TRY-UPDATE.
EXEC SQL AT connect1 SELECT sum(qty) INTO :NUM-SOLD
FROM salesdetail
WHERE title_id = :TITLE-ID END-EXEC.
EXEC SQL AT connect2 UPDATE current_sales
SET num_sold = :NUM-SOLD
WHERE title_id = :TITLE-ID END-EXEC.
EXEC SQL AT connect2 COMMIT WORK END-EXEC.
EXEC SQL AT connect1 COMMIT WORK END-EXEC.
IF SQLCODE <> 0
DISPLAY "OOPS! Should have used 2-phase commit".
ABORT-TRAN.
DISPLAY "ERROR! ABORTING TRAN".
DISPLAY "Error code is " SQLCODE.
DISPLAY "Error message is " SQLERRMC.
EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
EXEC SQL AT connect2 ROLLBACK WORK END-EXEC.
EXEC SQL AT connect1 ROLLBACK WORK END-EXEC.
PERFORM TRY-UPDATE.
This reference page mainly describes aspects of the Transact-SQL commit statement that differ when used with Embedded SQL. See the Adaptive Server Enterprise Reference Manual.
Transaction names must conform to the Transact-SQL rules for identifiers. Transaction names are a Transact-SQL extension: they cannot be used with the ANSI-compliant keyword work.
When nesting transactions, assign a transaction name only to the outermost begin transaction statement and its corresponding commit transaction or rollback transaction statement.
begin transaction, commit work, rollback transaction, rollback work
