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:

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. 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
A string is returned with the SQLERRMESSAGE error message.
record-type. This is a RECORD type defined according to the structure of the database table.

Table 1. SQLCA.SQLCODE
Description	Constant	Value
Success	`ERROR_SUCCESS`	0
Failure	`ERROR_FAILURE`	-1

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 Account table in the OfficeStore demo.

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

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

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