HP 3000 Manuals HP 3000 Manuals
Additional Error and Message Handling
The use of procedures in application programming can result in errors and
messages at preprocessing time and at run time. At preprocessing time,
syntax errors appear in the SQLMSG file. These are like other syntax
errors detected by the preprocessor. Here is an example:
DBEnvironment = PartsDBE
Module Name = RULEPROC
CREATE PROCEDURE PurchDB.RemovePart (PartNum CHAR (16) not null) as begin
delete from purchdb.inventory where PartNumber = :PartNum; delet from
|
*** Error in SQL statement ending in line 33.
*** ALLBASE/SQL statement parser error. (DBERR 10978)
*** Unexpected keyword. (DBERR 1006)
At run time four kinds of messages are generated by procedures.
Following an EXECUTE PROCEDURE statement, your application can check the
message buffer for these types of messages:
* 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.
As in an application, the most recent or severe ALLBASE/SQL message
catalog number is stored in the SQLCODE field of the SQLCA. (It is the
number of the first error generated by a statement or the most severe
error, if a more severe error occurs after the first.)
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:
* BEGIN
* ENDIF
* ELSE
* ENDWHILE
* END
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" to 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".
:
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" to the ALLBASE/SQL Message Manual.