SQL programming / SQL basics |
Manage nested transactions in different parts of a program.
A program can become very complex if a lot of nested functions calls, doing SQL processing within a transactions. You may want to centralize transaction control commands in wrapper functions. The fgldbutl.4gl library contains special functions to manage the beginning and the end of a transaction with an internal counter, in order to implement nested function calls inside a unique transaction.
MAIN IF a() <> 0 THEN ERROR "..." END IF IF b() <> 0 THEN ERROR "..." END IF END MAIN FUNCTION a() DEFINE s INTEGER LET s = db_start_transaction() UPDATE ... LET s = SQLCA.SQLCODE IF s = 0 THEN LET s = b() END IF LET s = db_finish_transaction((s==0)) RETURN s END FUNCTION FUNCTION b() DEFINE s INTEGER LET s = db_start_transaction() UPDATE ... LET s = SQLCA.SQLCODE LET s = db_finish_transaction((s==0)) RETURN s END FUNCTION
In this example, you see in the MAIN block that both functions a() and b() can be called separately. However, the transaction SQL commands will be used only if needed: When function a() is called, it starts the transaction, then calls b(), which does not start the transaction since it was already started by a(). When function b() is called directly, it starts the transaction.
IF s==0 THEN LET s = db_finish_transaction(1) ELSE LET s = db_finish_transaction(0) END IF