SQL/MP Programming Manual for COBOL

Error and Status Reporting
HP NonStop SQL/MP Programming Manual for COBOL—529758-003
9-15
Returning Information From the SQLCA
Using Constraints to Check for Errors
SQL/MP supports constraints to protect the integrity of base tables. A constraint is a
condition that must be met before data is added to a row in the base table to which the
condition applies. For example, a constraint might restrict employee numbers to the
range between 0001 and 9999. If you try to enter a value outside this range, SQL/MP
returns an error when it executes the UPDATE or INSERT statement.
You can create or drop constraints at any time. Creating or dropping a constraint,
however, causes the system to invalidate all SQL program files that use the underlying
table. Ensure that these files are explicitly SQL compiled to avoid automatic
recompilation every time a program runs.
Constraints are also a replacement for program code for all programs that refer to a
table to which constraints apply. For example, constraints can be used to establish
value ranges for columns, true or false conditions, and so forth.
Consider this example:
EXEC SQL CREATE CONSTRAINT MGRNUM_CONST ON DEPT
CHECK MANAGER BETWEEN MIN_MANAGER AND MAX_MANAGER END-EXEC.
In this example, the constraint on the DEPT table restricts the value of the MANAGER
column to the range shown. A program does not have to check the value for each
insertion or update to this table. SQL/MP ensures the value is within the range. If the
value is not within the range, SQL/MP returns an error message and aborts current
TMF transactions. Constraints are a database protection mechanism. A program does
not have to perform the checks to avoid corrupting the database.
FILLER Filler item.
PARAMS-BUFFER Information about warnings and errors. PARAMS-
BUFFER-LEN is the length in bytes. You can use the
SQLCA_DISPLAY2_ procedure to read information
returned to this buffer. Each parameter is stored as a string
of ASCII printable characters terminated by a byte
containing binary 0.
SRC-NAME-BUFFER Name of the program source file. This buffer contains the
name of a file specified with a SOURCE directive. When
the SQL statement source code is in the input, this buffer
is empty.
Note. When you add a constraint, SQL/MP checks all rows in the table. For large tables, the
CREATE CONSTRAINT operation can cause performance problems.
Table 9-1. SQLCA Structure Fields (page 3 of 3)
Field Name Description