Previous Topic Next topic Print topic


COMMIT

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.'
Previous Topic Next topic Print topic