Handling errors

The RUN STORED-PROC statement supports the NO-ERROR option. and these examples show 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.

This example assumes that you used the customer table from the demo database to create sp_write_cust procedure that inserts two records into the customer table:

create or replace procedure sp_write_cust(
sp_cust_num in out number,
sp_address in out varchar2
)as
begin
  insert into customer(cust_num,address) values(sp_cust_num, sp_address);
  insert into customer(cust_num,address) values(sp_cust_num, sp_address);
  commit; /* Procedure accidently commits unique cust_num twice */
  select cust_num, address into sp_cust_num, sp_address from customer
    where cust_num= sp_cust_num;
end sp_write_cust;
/

In this example, sp_write_cust procedure execution causes a unique index error because it tries to insert two values for cust_num, a primary key of the customer table. Then, the unique index error is handled by ERROR-STATUS handle in the IF block.

DEF VAR intvar AS INTEGER.
DEF VAR ix AS INTEGER.
DEF VAR num AS INTEGER.
DEF VAR myaddr AS CHARACTER.
num =1.
myaddr = '21 Main Street'.
RUN STORED-PROC sp_write_cust intvar = PROC-HANDLE (PARAM sp_cust_num = num,                                       PARAM sp_address = myaddr).

IF ERROR-STATUS:ERROR AND ERROR-STATUS:NUM-MESSAGES > 0 THEN DO:
  DO ix = 1 TO ERROR-STATUS:NUM-MESSAGES:
    MESSAGE ERROR-STATUS:GET-NUMBER(ix) ERROR-STATUS:GET-MESSAGE(ix).
  END.
  RETURN.
END.
CLOSE STORED-PROCEDURE sp_write_cust WHERE PROC-HANDLE = intvar.
DISPLAY "Customer " + string(num) + " at address " + myaddr + " was added to the database".

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 using 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.

The error in this example can also be handled using a CATCH end block in the procedure block that performs the RUN STORED-PROC statement.

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.

By properly positioning the NO-ERROR in the RUN statement, error information can also be retrieved from attempted SQL execution at the data source when using LOAD-RESULT-INTO. shows how errors are trapped after LOAD-RESULT-INTO stored procedure execution.

DEFINE VARIABLE tables AS HANDLE EXTENT 1 NO-UNDO.

RUN STORED-PROC send-sql-statement LOAD-RESULT-INTO tables NO-ERROR   ("Invalid SQL Statement").

IF ERROR-STATUS:ERROR THEN
  MESSAGE ERROR-STATUS:GET-MESSAGE(1) VIEW-AS ALERT-BOX.
ELSE
  MESSAGE "SQL call was successful." VIEW-AS ALERT-BOX.