dbxDataEvent_tableName_BeforeInsertRowByKey

Function called before inserting a row in the table.

Syntax

PUBLIC FUNCTION dbxDataEvent_tableName_BeforeInsertRowByKey(
   p_data record-type)
   RETURNS (INTEGER, STRING, record-type)

The function has the following parameter:

  1. p_data. This is a RECORD type defined according to the structure of the database table.
It returns the following values:
  1. An integer specifying the SQLCA.SQLCODE error number. Codes are defined as constants in the libdbappSql and libdbappCore files in the libdbapp library. The errors that are relevant in this function are shown in the table.
    Table 1. SQLCA.SQLCODE
    Description Constant Value
    Success ERROR_SUCCESS 0
    Failure ERROR_FAILURE -1
  2. A string is returned with the SQLERRMESSAGE error message.
  3. record-type. This is a RECORD type defined according to the structure of the database table.

Usage

When you select the Before Insert Row By Key property for the creation of the event, a function shell is created. Enter your code in the function.

This function is called before inserting a row in the database table. You can use this function if you are using a database for which constraints are missing, and where you cannot update the database schema. To preserve data integrity, you can implement constraints and referential integrity checks as if the schema was designed with them.

For example, you can check to ensure that the field value entered as a foreign key does exist in the related table.

Example: Before Insert Row By Key

This example uses the Before Insert Row By Key code event for the lineitem table in the OfficeStore demo.

In this example, the function validates data before inserting a row in the table. The user-defined function checkInsertInventoryQty is called to check the available quantity of the ordered item in the inventory table. 

# officestore.4gl

-- import user-defined functions
IMPORT FGL myAccountFunc 

PUBLIC FUNCTION dbxDataEvent_lineitem_BeforeInsertRowByKey(p_data RECORD LIKE lineitem.*)
    RETURNS (INTEGER, STRING, RECORD LIKE lineitem.*)

    DEFINE errNo INTEGER
    DEFINE errMsg STRING
    
    DISPLAY "dbxDataEvent_lineitem_BeforeInsertRowByKey (Table scope) is raised"
    CALL myAccountFunc.checkInsertInventoryQty(p_data) RETURNING errNo, errMsg
    DISPLAY "dbxDataEvent_lineitem_BeforeInsertRowByKey (Table scope) is exited"
    
    RETURN errNo, errMsg, p_data.*
END FUNCTION

checkInsertInventoryQty

In this section there is an example of the function checkInsertInventoryQty called to check the inventory table.

The function tries to update the inventory table with the lineitem quantity. Depending on the result of the search for the item in the inventory table and the quantity there, either an error number and message is returned, or an update is attempted. If the update is not successful, the roll back is called.

Transactions are used in the example to isolate the row. However, you can not use the standard Genero BDL BEGIN WORK / COMMIT WORK / ROLLBACK WORK instructions to implement transaction blocks in this function because nested transactions are used by the BAM. Nested transactions grouped in a single transaction are supported in Genero BDL. See the Genero Business Development Language User Guide. Therefore, you use the following:
  • libdbapp_begin_work() instead of BEGIN WORK
  • libdbapp_commit_work() instead of COMMIT WORK
  • libdbapp_rollback_work() instead of ROLLBACK WORK
You need to have an IMPORT FGL libdbappSql statement in the module where the function is created to call these. This library has functions that are essentially wrappers for the functions in the fgldbutl database utility module.

All functions that you would reuse across forms and applications would reside in a module you create. To use these functions in your project, you would import your module into the entity module (for example, AccountForm.4gl) using the IMPORT FGL statement.

# MyAccountFunc.4gl

IMPORT FGL libdbappSql
IMPORT FGL libdbappCore

FUNCTION checkInsertInventoryQty(dataRec RECORD LIKE lineitem.*) 
  RETURNS (INTEGER, STRING)

  DEFINE errNo INTEGER
  DEFINE errMsg STRING
  DEFINE _qty INTEGER

  SELECT qty INTO _qty FROM inventory WHERE @itemid = dataRec.itemid 

  IF _qty - dataRec.quantity < 0 THEN 
     LET errNo = libdbappCore.ERROR_FAILURE
     LET errMsg = SFMT(" - Quantity requested (%1) unavailable. Remaining quantity: %2 ", 
                        dataRec.quantity, _qty)
  ELSE
     --Update quantity of 'inventory'
     LET errNo = libdbapp_begin_work()
     TRY
        UPDATE inventory SET qty = (_qty - dataRec.quantity) WHERE @itemid = dataRec.itemid
     CATCH
        LET errNo = libdbappCore.ERROR_FAILURE
        LET errMsg = " - SQL ERROR: ", sqlca.sqlcode
     END TRY
     IF errNo = libdbappCore.ERROR_SUCCESS THEN
        LET errNo = libdbapp_commit_work()
        LET errMsg = "Inventory updated"
     ELSE
        CALL libdbapp_rollback_work()
     END IF
  END IF
  
  RETURN errNo, errMsg
    
END FUNCTION