Re: SPL question about error handling.

"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> wrote on 02/28/2019
06:23:37 PM:

Newbie to SPL.
Really trying to get the basics of error handling.

Other than a global exception handler, there are two ways to
locally "trap" errors in an SQL stored procedure. One is to check for
errors immediately after the pertinent SQL statement execution -- as
follows.

Get Current Diagnostics Condition 1 CurState = Returned_SqlState;
If SubStr(CurState,1,2) = '02' Then ... (etc.)

The presence of this statement prevents SQL from terminating when
it throws an exception. But, this statement only applies to the preceding
SQL statement execution and only at the current execution level (i.e.,
between the current BEGIN ... END pair).

The second way (other than a global exception handler) is to wrap
the statement or statements in a BEGIN ... END pair that contains its own
(local) handler -- for example (this also includes a global exception
handler for more severe errors):


Declare Exit Handler for SQLEXCEPTION
Begin
Get Current Diagnostics Condition 1
ErrState = Returned_SqlState;
Set ErrText = ErrText || ' State=' || ErrState;
If CursorOpen = True Then
Set CursorOpen = False;
Close Company_Cursor;
End If;
Signal SqlState '88W00' Set Message_Text = ErrText;
End;

Begin
Declare Continue Handler for SQLSTATE '42704' Begin End; -- notfnd
Set ErrText = 'Initial cleanup of alias failed.';
Drop Alias QTEMP/IMLFADJTA;
End;

Sincerely,

Dave Clark