Additional Error and Message Handling

• Messages from failure of the EXECUTE PROCEDURE statement.

• Any messages from the last SQL statement executed by the procedure. This includes RAISE ERROR messages.

• Messages from any PRINT statement executed by the procedure.

• Any PRINTRULES messages generated.

Messages from Failure of the EXECUTE PROCEDURE Statement

At run time, the EXECUTE PROCEDURE statement returns an error message if the procedure does not exist, or if a required parameter is not supplied. SQLCODE contains the error number, and SQLWARN0 is set to 'W' if there is also a warning.

The following type of error results from an incorrect procedure call:

   No value was provided for non-nullable parameter VENDORNUMBER in

   procedure PURCHDB.CHECKVENDOR.   (DBERR 2234)

Messages from the Last SQL Statement Executed by the Procedure

Inside a procedure, each SQL statement after the declaration of local variables is assigned a statement number, including all the control flow and status statements. Note that the following do not have statement numbers:

When an SQL statement in a procedure causes the procedure to fail, a message indicating the statement number is loaded into the message buffer. This message is available to your application (by using SQLEXPLAIN) in addition to any other messages in the message buffer. The following is an example of such a message:

   Error occurred executing procedure PURCHDB.DELVENDOR statement 8.  (DBERR 2235)

If a WHENEVER SQLERROR STOP directive is active, an SQL runtime error within the procedure terminates the procedure, causes a rollback and a return to the calling application with SQLCODE set to the error number of the statement that failed.

If no WHENEVER SQLERROR STOP directive is active, the procedure continues to completion if non-severe errors occur. On return from the procedure, SQLCODE is 0, and SQLWARN0 is set to 'W' if the last SQL statement executed by the procedure generated any error or warning messages (or if any PRINT or PRINTRULES messages were generated).

Whether or not WHENEVER SQLERROR STOP is in effect, on exiting the procedure your application can display any messages generated by the procedure by using SQLEXPLAIN.

It is recommended that whenever possible you handle errors within a procedure by examining built-in variables and taking appropriate action. The values returned in built-in variables can be returned to the calling application through OUTPUT parameters or through the RETURN statement. Inside the procedure, only the most serious error encountered is available through the built-in variable ::sqlcode. This value can always be returned to the calling application by means of the return status code, as in the following examples:

In C                   In COBOL:



EXEC SQL :ReturnCode =              EXEC SQL :RETCODE = 

   EXECUTE PROCEDURE                   EXECUTE PROCEDURE

   PurchDB.DelVendor(:VNumber);        PURCHDB.DELVENDOR (:VNUMBER) END-EXEC.

if(sqlca.sqlcode==0) {              IF SQLCODE IS ZERO THEN

   if (ReturnCode != 0)                IF RETCODE IS NOT ZERO THEN

      printf("SQL ERROR\n");               DISPLAY "SQL ERROR."

   }                                   END-IF

                                    END-IF.

Inside procedure PurchDB.DelVendor:



   DELETE FROM PurchDB.Orders 

         WHERE VendorNumber = :VendorNumber;

        RETURN ::sqlcode;

Messages from Errors Caused by the RAISE ERROR Statement

The RAISE ERROR statement provides a way of specifying your own error message numbers and text. This is very useful inside procedures that are triggered by rules, and it is also useful if you wish to build a set of error messages of your own. RAISE ERROR lets you assign an error number and a message, as in the following example:

   RAISE ERROR 7001 MESSAGE 'Vendor number exists in the "Orders" table.';

The number range 7000-7999 is reserved for use by this statement (that is, no ALLBASE/SQL errors appear in this range). In the previous example, when the RAISE ERROR statement executes, the number -7001 is placed in the local variable, ::sqlcode, and you can test for the error within the procedure. After exiting the procedure, SQLCODE is set to -7001 if a WHENEVER SQLERROR STOP statement is in effect.

The following example illustrates the use of a RAISE ERROR statement in an application that tests for errors following the execution of a procedure by a rule triggered by a DELETE statement. An error is raised in procedure PurchDB.DelVendor and displayed on return to the calling application. The calling application includes the following DELETE statement:

   DELETE FROM PurchDB.Vendors WHERE VendorNumber = :VendorNumber

The attempted deletion fires rule PurchDB.CheckVendor, which invokes procedure PurchDB.DelVendor. The procedure allows the deletion to take place only if the vendor number is not found in other tables. If the vendor number does appear in some other table, an error results. SQLCODE is set to the number of the raised error, and messages like the following are returned to the message buffer, from which they can be displayed with SQLEXPLAIN by the application that encountered the error:

   Vendor number exists in the "Orders" table.

   Error occurred executing procedure PURCHDB.DELVENDOR statement 8.  

    (DBERR 2235)

   INSERT/UPDATE/DELETE statement had no effect due to execution errors.  

    (DBERR 2292)

In the first message, the raised error reports that the vendor number exists in the Orders table. A second message in the buffer identifies the location in the procedure of the RAISE ERROR statement that contained the first message. The third message reports the failure of the DELETE statement that fired the PurchDB.CheckVendors rule, which in turn invoked the PurchDB.DelVendor procedure.

Note that for an error raised in a procedure called by an application, SQLCODE and SQLWARN0 are set as described in the previous section, "Messages from Errors Caused by the RAISE ERROR Statement."

RAISE ERROR is the same as other SQL statements in that within or outside of a procedure the message buffer is cleared of other errors before the raised error is stored. (Messages for PRINT and PRINTRULES remain until the procedure returns to the calling application.) Therefore, it is most useful for errors that cause the procedure to return in an error state.

For more information about RAISE ERROR, refer to the section "User Defined Messages" in the "Introduction" section of the ALLBASE/SQL Message Manual.

Messages from the PRINT Statement

Use the PRINT statement to store procedure messages in the SQL message buffer. PRINT is useful for presenting informational messages that do not generate an error code in the procedure and for debugging your procedure. When print messages have been generated, on return to the calling application, SQLWARN0 is set to 'W' and all such messages can be retrieved with SQLEXPLAIN.

Here is a C example that uses PRINT statements:

   if ::sqlcode = 100 then 

      print 'Row was not found';

   else 

      print 'Error in SELECT statement';

   endif;

On returning from the procedure, use SQLEXPLAIN in a loop to extract all the messages generated by PRINT during the operation of the procedure:

   while (sqlcode != 0 || sqlwarn[0]=='W') {

      EXEC SQL SQLEXPLAIN :SQLMessage;

	  printf("%s\n",SQLMessage);

	  }

In COBOL:

   IF SQLCODE IS NOT ZERO OR SQLWARN0 = "W" THEN

      PERFORM M100-DISPLAY-MESSAGE 

      UNTIL SQLCODE IS ZERO AND SQLWARN0 <> "W".



   [vellip]



   M100-DISPLAY-MESSAGE.

      EXEC SQL SQLEXPLAIN :SQLMESSAGE END-EXEC.

      DISPLAY SQLMESSAGE.

   M100-EXIT.

      EXIT.

The above routine displays all warnings and errors, including all messages generated by PRINT and as a result of rules firing when PRINTRULES is set on. Note that any message generated by PRINT or resulting from PRINTRULES being set on is loaded into the message buffer each time such a statement executes. Unlike other SQL statement messages, these are not cleared from the message buffer until return to the calling application and just before the next SQL statement executes.

For more information about PRINT, refer to the section "User Defined Messages" in the "Introduction" section of the ALLBASE/SQL Message Manual.