Kbase P99345: How to handle errors using Stored Procedures with DataServers
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
06/04/2010 |
|
Status: Verified
GOAL:
How to use PROC-STATUS function
GOAL:
Understanding RUN STORED-PROCEDURE with NO-ERROR/ERROR-STATUS:ERROR
GOAL:
PROC-STATUS versus NO-ERROR/ERROR-STATUS:ERROR
GOAL:
How to handle errors using Stored Procedures
FACT(s) (Environment):
All Supported Operating Systems
DataServers
All Supported Operating Systems
FIX:
There are two types of errors that stored procedures can encounter.
Type 1 - Errors that can be trapped by the stored procedure within itself.
Type 2 - Errors that are caught by the RUN STORED-<STRONG>PROC</STRONG> statement and cause the procedure to stop. These errors might be due to a compilation error, attempt to close a stored procedure that did not start or another error that is not handled.
To handle errors Type 1 - Use PROC-STATUS function
PROC-STATUS function returns the return status from a stored procedure.
The return status is an integer value that indicates whether a stored procedure failed and why.
The stored procedure can call SQL raise_application_exception statement with a value ranging from -20000 to -20999 and continue processing.
The DataServer checks whether an error is the result of raise_application_error.
If this is the case, the DataServer stores the value, which you can retrieve with the PROC-STATUS function when you close the stored procedure.
To handle errors Type 2 - Use NO-ERROR option in RUN STORED-PROCEDURE statement supports the NO-ERROR option, followed by ERROR-STATUS:ERROR
BY using NO-ERROR option for RUN STORED-PROCEDURE statement it specifies that any ERROR condition that the RUN STORED-PROCEDURE statement produces will be suppressed.
To check for suppressed errors you must check the ERROR-STATUS handle before you close a stored procedure.
The PROC-STATUS clause of the CLOSE STORED-PROCEDURE statement allows the DataServer to retrieve the text of an error message that was passed to raise_application_error.
Use the ERROR-STATUS:GET-MESSAGE handle to retrieve the message as in the following example:
DEFINE VARIABLE st AS INTEGER INITIAL 0.
DEFINE VARIABLE h AS INTEGER.
RUN STORED-PROC p1 h = PROC-HANDLE NO-ERROR.
CLOSE STORED-PROC p1 st = PROC-STATUS WHERE PROC-HANDLE = h.
DISPLAY st.
IF ERROR-STATUS:ERROR
THEN
MESSAGE ERROR-STATUS:GET-MESSAGE(1) ERROR-STATUS:GET-NUMBER(1)
VIEW-AS ALERT-BOX.
In this example, the PROC-STATUS clause is necessary to enable the ERROR-STATUS:GET-MESSAGE handle to retrieve the text of the error message.