Chapter 5 Using Procedures in Application Programs

This chapter describes the use of procedures in application programs. It highlights features that are available only within application programs and, in particular, methods of passing information between an application and a procedure. The final section compares the use of application code to procedure code to accomplish the same task. The material in this chapter assumes familiarity with the more global presentation of procedures found in the ALLBASE/SQL Reference Manual chapter, "Constraints, Procedures, and Rules."

In the present discussion, the term procedure refers to a database object that you define with a CREATE PROCEDURE statement. Like an application program, a procedure may have stored sections associated with it and may execute transaction statements. As in an application, when a severe error (one having an error code of -4008 or equal to or less than -14024) is encountered in a procedure, any active transaction is automatically rolled back, and the procedure continues.

A difference between a procedure and an application is that the SQL Communication Area (SQLCA) and SQLEXPLAIN cannot be used in a procedure. The built-in variable, ::sqlcode holds the error code for the first message in the message buffer (guaranteed to be the most severe error). When a procedure ends and control returns to the calling application, the SQLCODE field of the SQLCA can be tested. However, only certain types of procedure errors cause SQLCODE to be set. (Refer to the later sections, "Testing SQLCODE and SQLWARN0 on Return from a Procedure" and "Additional Error and Message Handling.")

Within a procedure, you can implement a general form of error checking by coding a WHENEVER SQLERROR STOP statement at the beginning of the procedure. Then whenever an error is encountered during procedure execution, any active transaction is rolled back, the procedure is terminated, and control returns to the application. At this point, SQLCODE contains the error number of the error that caused the WHENEVER SQLERROR STOP statement to be invoked. (Note that, unlike an application, a severe error encountered in a procedure having WHENEVER SQLERROR STOP in effect does not release a DBEnvironment.)

When your application requires more specific information about procedure errors or other information concerning procedure execution, when multiple row result set data is passed from the procedure to the application, and when dynamic parameters are needed to pass data between the application and the procedure, several features are available as described in the following sections:

Note, it is recommended that you handle procedure statement errors and warnings within the procedure.