Purpose

COMMIT() is a transaction processing command which requests an immediate commit (checkpoint) of the database.
 

Syntax

#variable = COMMIT()
 

Operation

#variable is a numeric scratch variable.  This is set to zero if the commit is successful.
 

Remarks

A COMMIT() is effective only for previously written records, and not for any record currently being processed.

A COMMIT() command is actioned irrespective of the state of the #@SUPP-COMM flag.  

If the COMMIT() fails, PROIV attempts to perform a database rollback and link to the rollback function @RFUNCT. It is normally sufficient to rely on the implicit commits issued by PROIV.

Potential problems involving application with extremely large transactions on systems with limited rollback space can be alleviated by specifying an iteration counter. PROIV issues a COMMIT() command after processing the specified number of iterations. The use of the iteration counter precludes the possibility of rollback after the first group of records has been written.

A COMMIT() command used in a PROIV Global screen (WINDOW) function directs the database to close all open ODBC (e.g. SELECT) cursors.

If both of the following conditions are TRUE then the ODBC driver will return error ‘cursor does not exist’ when a SQL UPDATE is executed:

  1. SQL_TRANSACTION_ERROR=N (e.g. SQL transaction boundary check is disabled) or the SQL transaction is structured in a particularly complex manner, the PROIV Virtual Machine fails to detect a SQL transaction boundary violation due to an explicit COMMIT() call.

  2. An explicit COMMIT() command directs the database to close an ODBC (SELECT) cursor that is linked to the SQL UPDATE cursor.

Oracle (e.g. SELECT) cursors are not explicitly closed by COMMIT() calls. A COMMIT() command on Oracle clears all cursors so the fetch for the next record on the master file fails and this results in an Oracle error:

'ORA-01002: fetch out of sequence. '

Please refer to Oracle documentation to determine the effect of an explicit COMMIT() on open Oracle cursors. The COMMIT() does not necessarily close the cursor, it depends on the type of cursor.

Example

IF $COMPLETE = ‘Y’, ‘y’
THEN
#TEMP = COMMIT()
FNEXIT
ENDIF

This example commits the current transaction and exits the function if the flag is set appropriately. 
 

 

PROIV normally issues a COMMIT when a function exits.

 

Comment on this topic

Topic ID: 520032