SQL statements always set a value in the field SQLSTATE (included in the SQLCA control structure) to indicate the results when the statement is executed. This includes SQL control statements written in SQL Procedural Language. However, the result of each individual statement will not automatically be passed up to a procedure at a higher level. Likewise, a non-zero SQLSTATE will not automatically trigger a procedure to terminate it's logic path and to return control to the caller.
There are three principal components for managing errors that may result when an SQL Procedural Language statement is executed:
1- A column should be included in the procedure definition for the exclusive use of passing the SQLSTATE value from a lower-level called procedure back to the higher-level caller.
2- Each SQL Procedure should use the SIGNAL control statement to specify any non-zero SQLSTATE which you want to be passed back to the caller.
3- A compound statement specifying a Handler declaration for each non-zero SQLSTATE specified in a SIGNAL statement.
The CA IDMS SQL Reference documents the syntax and usage of the SIGNAL statement. In summary, it specifies:
- the value for SQLSTATE that is to be signaled; this cannot be '00000', as that is the value of SQLSTATE for successful completion.
- an optional condition-name that is to be assigned to the specified SQLSTATE value. This condition name can then be referenced in the procedure code.
- An optional SET MESSAGE_TEXT clause that specifies a text to be returned when this SQLSTATE is encountered.
A Handler declaration is included in a Compound Statement, which is a specific type of SQL Control Statement that is also documented in the CA IDMS SQL Reference. In brief, a handler routine receives control when the execution of an SQL statement fails or terminates with a condition for which the handler has been defined. The handler declaration contains 3 main parts:
· The condition name or SQLSTATE value for which this handler is to be invoked;
· A Procedure-language statement which is to be performed when this condition-name or SQLSTATE is encountered;
· An indication of how the procedure is to proceed after executing the specified procedure statement; the options are CONTINUE, EXIT, or UNDO.
When the intention of the Handler declaration is to return an SQLSTATE value to the caller, then the procedure statement mentioned above is most likely to be a SET statement that moves a value to the column in the procedure definition that has been defined for this use.
When this approach is implemented, each routine that calls another should test the value of the column containing the returned SQLSTATE value as the first action following the call.