Re: SQL Exception Handlers

"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx> wrote on 01/29/2018
05:46:50 PM:

"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx> wrote on 01/29/2018
05:33:48 PM:

It sounds like you need to add some nested compound statements.

Handlers

are scoped to the BEGIN/END compound statement where they are defined.

You

can have handlers for the same condition in multiple compound

statements,

including the outermost level.

So you're saying that my first DROP of the alias can be wrapped

in

a BEGIN/END block with its own continue handler inside of the block?

I'll

try that. Thanks.

That appears to be working very well. Thanks.


[snippet]
------------------------------------------------------------------------
-- global exit handler
------------------------------------------------------------------------
Declare Exit Handler for SQLEXCEPTION
Begin
Get Current Diagnostics Condition 1
ErrState = Returned_SqlState;
Set ErrText = ErrText || ' State=' || ErrState;
Signal SqlState '88W00' Set Message_Text = ErrText;
End;

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

------------------------------------------------------------------------
-- create temporary work table
------------------------------------------------------------------------
Begin
Declare Continue Handler for SQLSTATE '42710' Begin End; -- exists
Set ErrText = 'Temporary table create failure.';
Create Table QTEMP/DLC_TEMP_TABLE for system name DLCTEMPT
(
Company_Number char(5) not null
,Table_Name char(10)
,Rows_Updated bigint
);
End;

Set ErrText = 'Temporary table delete failure.';
Delete QTEMP/DLC_TEMP_TABLE;


Sincerely,

Dave Clark