SQL/MP Programming Manual for COBOL85

Error and Status Reporting
HP NonStop SQL/MP Programming Manual for COBOL85—429326-004
9-15
Returning Information From the SQLCA
This constraint in this example examines two columns within the same row and
ensures that the employee termination date is equal to or greater than the date of hire:
EXEC SQL CREATE CONSTRAINT VALIDATE_CONST ON EMPLOYEE
CHECK TERM_DATE >= HIRE_DATE END-EXEC.
Before coding constraint-checking logic into requester programs, consider that
constraints can change over time and that such changes mean recoding the
requesters with new checks to match the changed constraints. Also, consider that
operators learn quickly what causes data-entry errors and can be trained during
system testing to avoid such errors. Constraints are a database protection mechanism,
not a substitute for operator training and proper system documentation.
Instead of allowing constraints to check for errors, you can code a Pathway requester
to check entered data for compliance with the constraints. If certain data is prone to
operator-entry error, a constraint detects the error only when the server attempts to
update the database. A requester program can detect such operator-entry errors
before sending the data to the server.
When the requester checks for errors rather than letting constraints do the checking,
error message traffic decreases between the requester and server and between the
server and disk process, and performance improves. Also, if there are many
constraints on the same table, the server might find it difficult to determine which
constraint caused which error, making it difficult to return a specific message to the
terminal. It is easier for a requester to determine which entered value caused an error.
System designers must evaluate each application to determine whether or not to code
constraint logic in programs to detect errors. When making this decision, consider
these points:
•
Requester checks that parallel constraints on entered data are very efficient;
however, such checks require extensive coding and must be recoded to match
changes to constraints.
•
Constraints protect the database from operator error in applications and from
update error in SQLCI; however:
°
Constraint checks are less efficient because they increase the message traffic.
°
When multiple constraint errors occur in a program, it is difficult to determine
which entered value violated which constraint.
•
Server checks on entered data are sometimes required; for example, if the result of
the check affects subsequent processing.
Displaying and Storing Errors and Warnings
When you display SQL errors and warnings, you must consider where to display or
store the error. For example, do you send the error message to a terminal, a file, or an
SQL table? Usually, sending the error message to an SQL table is the preferred
method.