The RUN STORED-PROC statement supports the NO-ERROR option.
Trapping errors within a procedure using NO-ERROR option (simple example)
The following shows a simple example how to trap errors within a procedure using the NO-ERROR option.
DEFINE VARIABLE ix AS INTEGER NO-UNDO.
RUN STORED-PROC send-sql-statement NO-ERROR
("select count (*) from customer where name between a and 'z' ").
/*Select statement syntax is incorrect due to type-mismatch*/
IF ERROR-STATUS:ERROR THEN DO:
DO ix = 1 TO ERROR-STATUS:NUM-MESSAGES:
MESSAGE "error" ERROR-STATUS:GET-NUMBER(ix)
ERROR-STATUS:GET-MESSAGE(ix).
END.
RETURN.
END.
CLOSE STORED-PROC send-sql-statement.
Trapping errors within a procedure using NO-ERROR option
The following example assumes that you created the sp_img table and defined the sp_img_out stored procedure with two data types, integer and image, in the MS SQL Server as shown in the following code samples:
create procedure sp_img_out
@ip_int int,
@op_image image out
as
begin
insert into sp_img(p_int,p_image)
values(@ip_int, @op_image)
select p_int,p_image from sp_img where p_int = @ip_int;
end
Go
In the ABL code example that follows, the first (input) parameter literal value "3" is mapped to an INTEGER data type and the second (output) parameter "picture" is mapped to an IMAGE data type in MS SQL Server. As the ODBC drivers and the stored procedures in MS SQL Server do not support the legacy data type IMAGE as an OUTPUT parameter, on executing the sp_img_out stored procedure with the NO-ERROR option, a run-time error results, and is handled by the ERROR-STATUS:ERROR check in the IF block.
Note: IMAGE is not an unsupported data type. It is unsupported only as an OUTPUT parameter.
DEF VAR intvar AS INTEGER.
DEF VAR ix AS INTEGER.
DEF VAR invalid_param AS LOGICAL INITIAL FALSE.
DEF VAR picture as MEMPTR.
RUN STORED-PROC sp_img_out intvar = PROC-HANDLE NO-ERROR (PARAM ip_int = 3,
OUTPUT PARAM op_image = picture ).
IF ERROR-STATUS:ERROR AND ERROR-STATUS:NUM-MESSAGES > 0 THEN DO:
DO ix = 1 TO ERROR-STATUS:NUM-MESSAGES:
IF ERROR-STATUS:GET-NUMBER(ix) = 16684 THEN DO:
MESSAGE ERROR-STATUS:GET-NUMBER(ix) ERROR-STATUS:GET-MESSAGE(ix).
invalid_param = TRUE.
END.
END.
IF invalid_param = FALSE THEN DO:
STOP.
END.
END.
ELSE DO:
CLOSE STORED-PROCEDURE sp_img_out WHERE PROC-HANDLE = intvar.
COPY-LOB FROM op_image TO FILE "C:\myicons\icon.ico".
END.
The DataServer internally closes the stored procedure handle when the stored procedure fails to execute. You must ensure that the error handling code block immediately follows the RUN STORED-PROCEDURE call so that errors captured by NO-ERROR are not lost by the execution of another statement. Including the CLOSE STORED-PROCEDURE statement in the error handling block causes a run-time error because closure is implicit. Only if the procedure is run successfully will a procedure handle be established, and only then do you need to execute CLOSE STORED-PROCEDURE on the open handle.
Trapping errors within a procedure using CATCH option
The error demonstrated in the previous example can also be handled using a CATCH end block in the procedure block that performs the RUN STORED-PROC statement. The following code shows how to trap errors within a procedure using a CATCH end block.
DEF VAR intvar AS INTEGER.
RUN STORED-PROC sp_img_out intvar = PROC-HANDLE (3, OUTPUT ?).
CLOSE STORED-PROCEDURE sp_img_out WHERE PROC-HANDLE = intvar. /* This statement will not be executed on error */
CATCH eSysError AS Progress.Lang.ProError:
MESSAGE eSysError:GetMessageNum(1) SKIP
eSysError:GetMessage(1) SKIP
eSysError:GetMessage(2) SKIP
eSysError:GetMessage(3) VIEW-AS ALERT-BOX BUTTONS OK.
/* Handler code for SysError condition. */
END.
For more information on the NO-ERROR option, CATCH statement or CATCH end block, in OpenEdge ABL, see OpenEdge Development: ABL Reference.
