LucidDbAppLib ENFORCE ROW CONSTRAINTS

From Eigenpedia

1 Syntax
2 Purpose
3 Parameters
4 Output
5 Exceptions
6 Source Code

Syntax

SELECT * FROM TABLE(
  APPLIB.ENFORCE_ROW_CONSTRAINTS(
    CURSOR( select * from ...),
    ROW( constraintTestColumnA, constraintTestColumnB, ...)))

SELECT * FROM TABLE(
  APPLIB.ENFORCE_ROW_CONSTRAINTS(
    CURSOR( select * from ...),
    ROW( constraintTestColumnA, constraintTestColumnB, ...),
    resourceJarName))

SELECT * FROM TABLE(
  APPLIB.ENFORCE_ROW_CONSTRAINTS(
    CURSOR( select * from ...),
    ROW( constraintTestColumnA, constraintTestColumnB, ...),
    resourceJarName,
    tag))

Purpose

LucidDb provides built-in support for row-level exception detection and logging for standard SQL constraint violations such as UNIQUE, NOT NULL, and datatype conversion. However, it does not provide a means for application developers to define and enforce arbitrary constraints of their own. This function adds that capability as a UDX. See LucidDbErrorHandling for background and examples.

Parameters

one table [CURSOR]: provides input columns to be propagated through to output
one row of column names{ROW}: each column name is a constraint test column that represents one logical constraint to be enforced. Constraint test columns must be of type VARCHAR and produce one of the following values:
- null value for rows which do not need to be checked against the application message catalog
- error code, which will be used to lookup the message and severity in the message catalog
resourceJarName [VARCHAR(128)]: optional parameter for the name of the jar that contains the application message catalog. This defaults to AppsMsg. Resource jars will need to be built and placed under well defined directory (by default, the LucidDB plugin directory).
tag [VARCHAR(128)]: optional parameter used in naming of the log files

Output

one table, exactly the same as input table (including the constraint test columns, even though they will normally be projected away downstream) minus any rows which got rejected by at least one constraint
log file output according to the constraint test results

Exceptions

the columns in the constraint row parameter do not exist in the input table or any of the referenced columns has a type other than VARCHAR (precision is not checked)
an error code in any constraint test column has severity 'FATAL' for any row
number of 'REJECT' values exceeds session errorMax parameter
the jar for the message catalog is not found, for both default and specified message catalogs