COMMIT

Commits the current transaction.

 

Usage

Embedded, Interactive, Module, Procedural.

Description

The current transaction is terminated. Database alterations requested in the transaction build-up are executed against the database, provided that no transaction conflict is detected and that no deferred constraints are unsatisfied.

If the commit statement fails, no changes are made in the database, and an error code is set. A transaction conflict causes the SQLSTATE 40000 being raised while an unsatisfied deferred constraint causes the SQLSTATE 40002 being raised.

All cursors opened by the current connection are closed, except cursors that are defined WITH HOLD.

Cursors that are defined WITH HOLD remain open, but the cursor is no longer positioned on a row. A FETCH statement is required to position the cursor on a row before another DELETE CURRENT or UPDATE CURRENT statement can be executed.

If there is no currently active transaction, any cursors opened by the current ident are closed (except WITH HOLD cursors), but the COMMIT statement is otherwise ignored. No error code is returned in this case.

Committing a BACKUP transaction performs online backup for all databanks for which a CREATE BACKUP command has been performed since START BACKUP. Please note that this command may be lengthy if backups for large databank files are made.

Restrictions

The COMMIT statement cannot be used in a result set procedure because this would close the cursor which is calling it.

The COMMIT statement cannot be used within an atomic compound SQL statement, see COMPOUND STATEMENT.

The COMMIT BACKUP statement can only be used when a corresponding START BACKUP command has been given. The COMMIT BACKUP statement is not supported in procedural mode.

Notes

See the Mimer SQL Programmer's Manual, Transaction Handling and Database Security, for a detailed discussion of transaction control.

Example

EXEC SQL SET TRANSACTION START EXPLICIT

 

LOOP

  EXEC SQL FETCH C1 INTO :var1,:var2,...,:varn;

  DISPLAY var1,var2,...,varn;

  PROMPT "Update row?";

  EXIT when answer = "yes";

END LOOP

 

EXEC SQL START;

EXEC SQL UPDATE table SET ...

        WHERE col1 = :var1,

              col2 = :var2, ...

EXEC SQL COMMIT;

Standard Compliance