When CA IDMS SQL procedures are nested, what's the best way to pass errors back up the calling path?

Products

• IDMS
• IDMS - Database
• IDMS - ADS

Issue/Introduction

CA IDMS SQL applications may include SQL procedures. These can be written in SQL procedural language. They may also be nested in as many levels as desired, with procedures calling other procedures. In these situations managing errors is important; the methodology of how to do so may not be apparent to new users.

When CA IDMS SQL procedures are nested, what's the best way to pass errors back up the calling path?

Environment

SQL Procedures can be implemented in any CA IDMS environment on any operating system.

Resolution

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. 

 

Additional Information

Documentation on the SQL Procedural Language, including the SIGNAL statement and handler declarations, is in the SQL Reference manual, in the section on Control statements. That documentation can be found here:

https://docops.ca.com/ca-idms-ref/19/en/sql-reference/control-statements