Makes any changes made by the current transaction on the current connection permanent in the database.
Syntax:
>>---EXEC SQL---COMMIT----.--------------.--->
+-WORK---------+
+-TRAN---------+
+-TRANSACTION--+
>---.-----------.---END-EXEC--><
+--RELEASE--+
Parameters:
| WORK
|
WORK, TRAN and TRANSACTION are optional and synonymous.
|
| RELEASE
|
If RELEASE is specified and the transaction was successfully committed, the current connection is closed.
|
Example:
* Ensure that multiple records are not inserted for a
* member of staff whose staff_id is 99
EXEC SQL
DELETE FROM staff WHERE staff_id = 99
END-EXEC
* Insert dummy values into table
EXEC SQL
INSERT INTO staff
(staff_id
,last_name
,first_name
,age
,employment_date)
VALUES
(99
,'Lee'
,'Phil'
,19
,'1992-01-02')
END-EXEC
IF SQLCODE NOT = ZERO
DISPLAY 'Error: Could not insert dummy values.'
DISPLAY SQLERRMC
DISPLAY SQLERRML
EXEC SQL DISCONNECT ALL END-EXEC
STOP RUN
END-IF
EXEC SQL
COMMIT
END-EXEC
* Check it was committed OK
IF SQLCODE = ZERO
DISPLAY 'Error: Could not commit values.'
DISPLAY SQLERRMC
DISPLAY SQLERRML
EXEC SQL DISCONNECT CURRENT END-EXEC
STOP RUN
END-IF
DISPLAY 'Values committed.'
* Delete previously inserted data
EXEC SQL
DELETE FROM staff WHERE staff_id = 99
END-EXEC
IF SQLCODE NOT = ZERO
DISPLAY 'Error: Could not delete dummy values.'
DISPLAY SQLERRMC
DISPLAY SQLERRML
EXEC SQL DISCONNECT ALL END-EXEC
STOP RUN
END-IF
* Check data deleted OK, commit and release the connection
IF SQLCODE NOT = ZERO
DISPLAY 'Error: Could not delete values.'
DISPLAY SQLERRMC
DISPLAY SQLERRML
EXEC SQL DISCONNECT ALL END-EXEC
STOP RUN
END-IF
EXEC SQL
COMMIT WORK RELEASE
END-EXEC
* Check data committed OK and release the connection.
IF SQLCODE NOT = ZERO
DISPLAY 'Error: Could not commit and release.'
DISPLAY SQLERRMC
DISPLAY SQLERRML
EXEC SQL DISCONNECT CURRENT END-EXEC
END-IF
DISPLAY 'Values committed and connection released.'
