Re: SQL Exception Handlers

Hi Dave,

I do a ton of SQL script work migrating, extracting, transforming, and
importing databases.

I use two custom SQL script processors:
1) Primary one is an SQL stored procedure that parses the SQL script, runs
each parsed SQL statement, checks the execution result, determines how to
handle errors, and logs the results.
2) Secondary one does the same things, except it is an SQL function.

The nice thing about that design, is 99+% of all my error handling is
centralized in those two script processors.

I have well over 3,000 SQL scripts, and the centralized error handling is
sufficient for at least 99% of those scripts. In rare cases, I'll embed a
call to function RAISE_ERROR in an SQL script to trigger an error in the
processor when some rule is violated.

The processors use a continue handler for all errors to simply capture the
error into local variables that are not replaced upon running the next
processor SQL command. Then, I have special logic to ignore certain
errors. The presence of a SQLCODE = -204 (an undefined object or
constraint name was detected.) for an SQL statement containing DROP TABLE
is one of those errors that I ignore. So, for over 3,000 scripts, DROP
TABLE for a non-existent table is ignored centrally in only two script
processor objects.

SQL errors that occur that aren't specifically and centrally coded to be
ignored, cause SQL script processing to stop.

This design is pretty far outside the box, but it makes handling SQL script
errors a piece of cake.

HTH,

Mike

date: Mon, 29 Jan 2018 17:11:41 -0500
from: dlclark@xxxxxxxxxxxxxxxx
subject: Re: SQL Exception Handlers

"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx> wrote on 01/26/2018
02:30:45 PM:

When you have declared an SQL Exception handler, how do you make
an exception for a particular exception? For example...

I have a script that creates an alias in QTEMP during execution.
But, if any other exception occurs, the alias is left in QTEMP. So, I
want to clean up this possibility when the script first starts. This
works if the alias is there. But, if it is not there an exception

occurs.

I want to detect this situation and ignore the exception.

I even tried doing the DROP within the exception handler itself so
that the alias wouldn't be left in QTEMP at all. However, if an

exception

occurs before the alias first gets created, then the exception handler
throws an error during the DROP and the original exception never gets
displayed.

I also thought of creating an SQL Continue handler for the
specific SQL State, but I don't want this SQL State to always continue.
The potential exists for the same SQL State to occur in other places in
the script and I don't want those situations handled the same.

Is there a way to to be even more selective with handlers --
without having to go to the extreme of doing completely away with all
handlers and then having to do error checking on a

statement-by-statement

basis?

How do I handle all this? Thanks.

No responses on this?


Sincerely,

Dave Clark
--
int.ext: 91078
direct: (937) 531-6378
home: (937) 751-3300

Winsupply Group Services
3110 Kettering Boulevard
Dayton, Ohio 45439 USA
(937) 294-5331