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.

The "favcategory" field is a foreign key that references a unique key ("catid") in the category table. The SELECT statement checks for an occurrence of the key in the category table. The function returns an error number and message depending on this result.

PUBLIC FUNCTION dbxDataEvent_account_BeforeUpdateRowByKey(p_data RECORD LIKE account.*)
    RETURNS (INTEGER, STRING)

    DEFINE errNo INTEGER
    DEFINE errMsg STRING
    DEFINE res INTEGER
    
    DISPLAY "dbxDataEvent_account_BeforeUpdateRowByKey (Table scope) is raised"
   
    SELECT 1 INTO res FROM favcategory WHERE @catid = p_data.favcategory
    CASE
       WHEN SQLCA.SQLCODE == 0 -- found
          LET errNo = libdbappCore.ERROR_SUCCESS
          LET errMsg= "Category code is valid"
       WHEN SQLCA.SQLCODE ==NOTFOUND 
          LET errNo = libdbappCore.ERROR_FAILURE
          LET errMsg= "Category code is not valid"
       OTHERWISE
          LET errNo = libdbappCore.ERROR_FAILURE
          LET errMsg = "SQL ERROR: ", SQLCA.SQLCODE
    END CASE 
    DISPLAY "dbxDataEvent_account_BeforeUpdateRowByKey (Table scope) is exited"

    RETURN errNo, errMsg, p_data.*
END FUNCTION