Getting Started › Procedures and Triggers › Parameter Styles and Error Handling

Parameter Styles and Error Handling

The PARAMETER STYLE clause of the CREATE PROCEDURE statement defines how parameters are passed between an application program, or a trigger, and the procedure that is being called. How errors are handled also depends upon the parameter style chosen.

GENERAL

This parameter style specifies that the user parameter list is passed to the procedure devoid of null indicators (nulls are not allowed). Since no formal method is provided for passing error information back to the caller, the success or failure of the CALL procedure statement is determined by the contents of the SQL internal SQLCODE variable following the last SQL request made by the procedure. This also applies to parameter style GENERAL WITH NULLS.

GENERAL WITH NULLS

This parameter style differs from GENERAL only in that a null indicator is passed to the procedure for each user parameter.

DATACOM SQL

This parameter style passes nulls to the procedure as does GENERAL WITH NULLS and SQL, but it also passes some additional parameters. These parameters are modeled after those passed for the ANSI SQL3 parameter style SQL, but with this difference: instead of a SQLSTATE, DATACOM SQL passes an SQLCODE in the corresponding parameter.

Following are the additional parameters for parameter style DATACOM SQL (the first four are modeled after SQL3):

• SQLCODE—passed to the procedure as 0 and used to set the SQLCODE of the CALL PROCEDURE statement on output.
• A variable-length character string containing the name of the procedure.
• A variable-length character string reserved for future use.
• A variable-length character string containing 80 blanks on input, and an 80-byte or shorter error message on output. This error message is placed in the SQLCA and used as the SQL error message for the CALL PROCEDURE statement.
• A two-byte fixed-length character string containing the CA Datacom/DB external error code on output.
• A single character containing the CA Datacom/DB internal error code on output.

The logic inside SQL for parameter style DATACOM SQL is as follows:

When SQL gains control after a successful execution of the procedure, that is, after the procedure code loaded, ran, and did not abend, the internal SQL code and error message are reset to the values returned in the user's parameter list, regardless of whether it was triggered or called, even if this means a non-zero SQLCODE becomes zero or is replaced.

In order to minimize the confusion that a newly-defined trigger can cause for a pre-existing application that uses the navigational (record-at-a-time) API rather than SQL, we handle the DB return codes for DATACOM SQL style procedures as follows.

If the SQLCODE returned from the procedure is zero or positive, a non-blank CA Datacom/DB return code is ignored. If the procedure returned a negative SQLCODE and was explicitly called (as opposed to being triggered), the CA Datacom/DB external and internal return codes are reset to the values returned through the procedure's parameter list. If the procedure returned a negative SQLCODE and was triggered, we store the SQLCODE at offset 26 decimal (signed binary fullword) into the user's Request Area, force the CA Datacom/DB return code(s) to 94(100), then document the CA Datacom/DB return codes returned from the procedure at offsets 30 decimal (2 byte character) and 32 decimal (one byte unsigned binary) into the user's Request Area for the external and internal return codes, respectively. This is done to allow users of navigational programs to differentiate between failures inside procedures and those related to their specific CA Datacom/DB requests, because they generally do not have logic to interpret an SQLCODE.

You have complete control (and responsibility) in deciding whether what occurs constitutes a success. You must set the SQLCODE and error message parameters on exit in one of these three ways:

• If for some reason you want to fail even if all SQL requests received an SQLCODE=0, set SQLCODE -534 and provide an error message containing 80 bytes as desired, or
• Supply the SQLCODE, error message, and CA Datacom/DB external and internal return codes exactly as SQL returned it to the procedure, or
• Pass back an SQLCODE forced to 0 at your discretion.

SQL

When a procedure is created using PARAMETER STYLE DATACOM SQL in the CREATE PROCEDURE statement, the SQLSTATE status indicator is returned in the SQLCA. For detailed information about the SQLSTATE status indicator, see SQL States.

Parameter style SQL passes nulls to the procedure as does GENERAL WITH NULLS and DATACOM SQL. It also passes these additional four parameters that are added to the end of the parameter/null indicator list:

• The SQLSTATE (INOUT, but always passed in as 00000, similar to the SQLCODE in style DATACOM SQL, that is, it is passed to the procedure as 00000 and used to set the SQLSTATE of the CALL PROCEDURE statement on output).
• Authid.procedure-name (IN, same as in style DATACOM SQL, that is a variable-length character string containing the name of the procedure).
• Authid.specific name (IN, same as in style DATACOM SQL, that is, a variable-length character string reserved for future use).
• Error message text (INOUT, passed in as 0-length string, same as DATACOM SQL, that is, a variable-length character string containing 80 blanks on input, and an 80-byte or shorter error message on output that is placed in the SQLCA and used as the SQL error message for the CALL PROCEDURE statement).

Unlike style DATACOM SQL, the CA Datacom/DB external and internal return codes are not a part of this parameter list but are encoded in the generated SQLSTATE value. For example, the SQLSTATE that equates to SQL return code -117 is Seeii, and the SQLSTATE that equates to SQL return code -118 is Reeii, where ee represents the 2-byte external CA Datacom/DB return code, and ii is the CA Datacom/DB internal return code in hexadecimal characters.

As an aid in understanding error recovery for procedures, note the following:

• The PARAMETER STYLE being used by the procedure which is executing at each level of nesting controls the SQLCODE seen by the logic that called it, if procedures execute in a nested fashion due either to:
  • CALL PROCEDURE statements inside procedures, or
  • TRIGGERs triggering during execution of a procedure.

  This means that the PARAMETER STYLE of any procedure controls how the success or failure of everything that occurred during execution of the procedure is interpreted, including recursively called procedures whose PARAMETER STYLEs might not match that of the outermost procedure.

• As with all SQL statements, if an SQL statement is issued from a procedure and fails, it has no effect on the database. This rule holds true at every level of recursion. For example, if an INSERT statement, issued by a procedure, triggers procedure calls five layers deep and results in the updating of 500 rows in the database but then fails, not only is the INSERT backed out, the 500 updates that executed during processing of the INSERT are also backed out. Even though hundreds of SQL statements have been rolled back, at the level of the procedure that executed the INSERT, only one statement was backed out. Limited-scope rollbacks such as this (that occur automatically in lower levels of recursion) in no way affect the ability of the higher levels of procedures to either continue processing or to abort and return errors to callers, triggering additional automatic rollbacks as needed. Note, however, that users are not allowed to code their own ROLLBACK or COMMIT statements inside procedures.