dbxDataEvent_tableName_CheckTableConstraints

Function called to check the table constraints.

Syntax

PUBLIC FUNCTION dbxDataEvent_tableName_CheckTableConstraints(
   p_forUpdate BOOLEAN,
   p_data record-type )
   RETURNS (INTEGER, STRING)

The function has the following parameters:

  1. p_forUpdate. This is a BOOLEAN set to TRUE when the SQL operation is updating a row, or FALSE when inserting a new row.
  2. 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.

Usage

When you select the Check Table Constraints property for the creation of the event, a function shell is created. Enter your code in the function.

This function is called before inserting or updating a row to check if constraints for the database table are met. The BAM generates code to check constraints from the schema of the database table. These include:
  • Column constraints (for example, NOTNULL field values)
  • Unique constraints (for example, primary key or unique index)
  • Foreign key constraints

If you are using a database for which you are setting additional constraints and you cannot do this by updating the database schema, you can ensure constraints and referential integrity checks are implemented as if the schema was designed with them.

For example, you can use this function to check:
  • A field is not null.
  • A field with a unique index or a primary key is not missing
  • The referential integrity of a foreign key on a related table

Example: Check Table Constraints

This example uses the Check Table Constraints code event for the Account table in the OfficeStore demo.

In this example, the value for the "email" field is checked for null. The function returns an error number and message depending on the result. This example uses a user-defined function to verify email is not null. This function would be in a module shared across forms and applications, and imported using the IMPORT FGL statement.

PUBLIC FUNCTION dbxDataEvent_account_CheckTableConstraints(p_forUpdate BOOLEAN, 
    p_data RECORD LIKE account.*)
    RETURNS (INTEGER, STRING)

    DEFINE errNo INTEGER
    DEFINE errMsg STRING

    LET errNo = libdbappCore.ERROR_SUCCESS
 
    DISPLAY "dbxDataEvent_account_CheckTableConstraints (Table scope) is raised"
    CALL myAccountFunc.account_email_null(p_data.email) RETURNING errNo, errMsg
    IF errNo THEN
        # email entered
    ELSE
       LET errNo = libdbappCore.ERROR_FAILURE
       ERROR errMsg
    END IF 
   
    DISPLAY "dbxDataEvent_account_CheckTableConstraints (Table scope) is exited"

    RETURN errNo, errMsg
END FUNCTION