HP 3000 Manuals

ALLBASE/SQL Release F.0 Application Programming Bulletin for MPE/iX

Testing SQLCODE and SQLWARN0 on Return from a Procedure 

Upon return from an EXECUTE PROCEDURE call, the value of SQLCODE
indicates the success of procedure execution itself, not of any
individual statement in the procedure.  After returning from an EXECUTE
PROCEDURE statement, your application should test SQLCODE to determine if
the proceedure was successful and SQLWARN0 for possible errors, warnings,
and informational messages.

A non-zero SQLCODE is returned from procedure execution in the following
situations:

   *   The procedure was not executed at all.  Possible reasons are that
       the procedure was not found or that the user does not have the
       appropriate authority. 
   *   An error occurred in the procedure when a WHENEVER SQLERROR STOP
       statement was active.  Such an error causes the procedure to
       terminate, and the current transaction is rolled back.
   *   An error occurred in evaluating an IF or WHILE condition or an
       expression in an assignment statement.  Such an error causes the
       procedure to terminate, and previously executed statements are not
       rolled back (unless a severe error occurred).
   *   An error occurred in copying output values to the user's host
       variables.  For example, a null value might be returned, but a
       null indicator variable was not provided in the parameter list.

In all other cases, SQLCODE is 0 on return from a procedure, including 
cases in which errors occurred in a procedure and did not cause the 
procedure to stop.  Messages for any errors from the last SQL statement
executed by the procedure are available on return from the procedure by
testing SQLWARN0 for a value of 'W' and using SQLEXPLAIN.

Checking for All Errors and Warnings 

The following type of routine is recommended on return from an EXECUTE
PROCEDURE statement in C:

     while (sqlca.sqlcode < 0 || sqlca.sqlwarn[0]=='W')
     do {
     EXEC SQL SQLEXPLAIN :SQLMessage;
     printf("%s\n",SQLMessage);
     }

The following is a similar routine in COBOL:

     IF SQLCODE IS NOT ZERO OR SQLWARN0 = "W" THEN
        PERFORM M100-DISPLAY-MESSAGE
        UNTIL SQLCODE IS ZERO AND SQLWARN0 <> "W".

     :

     M100-DISPLAY-MESSAGE.
        EXEC SQL SQLEXPLAIN :SQLMESSAGE END-EXEC.
        DISPLAY SQLMESSAGE.
     M100-EXIT.
        EXIT.

This routine tests both SQLCODE and SQLWARN0 for the presence of error
conditions, warnings, and messages of all kinds, including those
generated by PRINT and RAISE ERROR statements and resulting from
PRINTRULES being set on.

MPE/iX 5.0 Documentation