COMMIT

Syntax

COMMIT

Description

Causes a database commit.

COMMIT is useful when you are doing many inserts, updates, or deletes in an SQL paragraph. A database commit releases the locks on the records that have been inserted, updated, or deleted. Used with some databases, it also has other effects. For this reason, it should not be used within the scope of an active SELECT paragraph or results will be unpredictable.

When the application finishes, a commit is performed automatically unless a ROLLBACK was done or, for callable SQR, the -XC flag was set.

Other commands or options, such as the CONNECT command 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 SQR command and should not be used within an SQL paragraph. If COMMIT is used in an SQL paragraph, results will be unpredictable.

Note:

The COMMIT command can be used with SQR servers for Oracle, DB2, and ODBC. For Microsoft SQL Server, use BEGIN TRANSACTION and COMMIT TRANSACTION within SQL paragraphs as in the following code segment.

Example

The following example illustrates the COMMIT command:

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

For Microsoft SQL Server:

...      ! 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

WARNING:

Any data being 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 Microsoft SQL Server).