COMMIT

Function

Causes a database commit.

Syntax

COMMIT

Description

COMMIT is useful for multiple inserts, updates, or deletes in SQL paragraphs. A database commit releases the locks on inserted, updated, or deleted records. If used in an active SELECT paragraph, unpredictable results may occur.

When the application completes, COMMIT is performed automatically unless ROLLBACK was done or, for callable Production Reporting, the -XC flag was set.

Other commands or options, such as CONNECT and the use of DDL statements for some databases with a BEGIN-SQL paragraph, can also cause the database to do a commit.

COMMIT is an Production Reporting command and should not be used within an SQL paragraph. If used in an SQL paragraph, unpredictable errors can occur.

Note:

COMMIT can be used with DB2, ODBC, DDO, Teradata, and Oracle. For Sybase, use BEGIN TRANSACTION and COMMIT TRANSACTION within SQL paragraphs as in the following code segment.

Examples

add 1 to #updates_done
if #updates_done > 50
  commit
  move 0 to #updates_done
end-if

For Sybase:

...   ! Begin Transaction occurred previously
begin-sql
  insert into custlog values (&cust_num, &update_date)
end-sql
add 1 to #inserts
if #inserts >= 50
  begin-sql
    commit transaction;! Commit every 50 rows
    begin transaction  ! Begin next transaction
  end-sql
  move 0 to #inserts
end-if

...   ! One more Commit Transaction is needed

Caution!

Any data changed by a current transaction is locked by the database and cannot be retrieved in a SELECT paragraph until the transaction is completed by a COMMIT or ROLLBACK statement (or COMMIT TRANSACTION or ROLLBACK TRANSACTION statement for Sybase and Microsoft SQL Server backends).