dbxDataEvent_tableName_BeforeUpdateRowByKey

Function called before updating a row in the table.

Syntax

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

The function has the following parameter:

p_data. This is a RECORD type defined according to the structure of the database table.

It returns the following values:

An integer specifying the SQLCA.SQLCODE error number. Codes are defined as constants in the libdbappSql and libdbappCore files in the libdbapp library. If the code is a negative number, an SQL error has occurred. 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
Concurrent access failure	`ERROR_CONCURRENT_ACCESS_FAILURE`	-2
Concurrent access not found	`ERROR_CONCURRENT_ACCESS_NOTFOUND`	-3

A string is returned with the SQLERRMESSAGE error message.

Usage

When you select the Before Update 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 updating 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 Update Row By Key

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

In this example, the function validates data before updating a row in the table. The user-defined function checkUpdateInventory 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_BeforeUpdateRowByKey(p_data RECORD LIKE lineitem.*)
    RETURNS (INTEGER, STRING)

    DEFINE errNo INTEGER
    DEFINE errMsg STRING

    DISPLAY "dbxDataEvent_lineitem_BeforeUpdateRowByKey (Table scope) is raised"
    CALL myAccountFunc.checkUpdateInventory(p_data) RETURNING errNo, errMsg
    DISPLAY "dbxDataEvent_lineitem_BeforeUpdateRowByKey (Table scope) is exited"

    RETURN errNo, errMsg
END FUNCTION

checkUpdateInventory

In this section there is an example of the function checkUpdateInventory 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 checkUpdateInventory(dataRec RECORD LIKE lineitem.*) 
  RETURNS (INTEGER, STRING)

  DEFINE errNo INTEGER
  DEFINE errMsg STRING
  DEFINE _qty, _quantity, requestedQuantity INTEGER

    --Check that the quantity is sufficient in the inventory table
    SELECT qty INTO _qty FROM inventory WHERE @itemid = dataRec.itemid
    SELECT quantity INTO _quantity FROM lineitem 
      WHERE @orderid = dataRec.orderid AND @linenum = dataRec.linenum

    LET requestedQuantity = dataRec.quantity - _quantity
    IF _qty < requestedQuantity THEN
        LET errNo = libdbappCore.ERROR_FAILURE
        LET errMsg = SFMT("quantity requested (%1) unavailable. Remaining quantity:%2 ", 
                           requestedQuantity, _qty)
    ELSE
        --Update quantity of 'inventory'
        LET errNo = libdbapp_begin_work()
        TRY
            UPDATE inventory SET qty = (_quantity + _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