--- In firebird-support@yahoogroups.com, mahendra.nepali@i... wrote:

>
> Hi,
>
> I am trying to log unhandled exceptions in firebird. My app calls a
> series of firebird procedures one after the other. If an error occurs in
> one proc then I want it to log that exception in a table containing some
> business related information and at the application level the exception
> should be received. I tried creating a test procedure for this. The
> following is the code.
>
>
>
> CREATE PROCEDURE SP_UPDSVC_EXCEPTIONS
>
> AS
>
>
>
> DECLARE VARIABLE I NUMERIC(3) = 120;
>
> DECLARE VARIABLE J NUMERIC(3) = 0;
>
> DECLARE VARIABLE K VARCHAR(5)='';
>
> BEGIN
>
>
>
> K= '#' || CAST(I AS VARCHAR(3)) || '#';
>
>
>
> IF (J = 0) THEN
>
> I=I/J;
>

<snip>

> WHEN ANY DO
>
> BEGIN
>
> EXECUTE PROCEDURE SP_LOG_DATA_UPATE_EXCPETION
> ('TEST',1,'FF12345678901','SC','159',NULL,SQLCODE,GDSCODE);
>
> EXCEPTION MYEXCEPTION1 + K;
>
> END
>
> END
>

</snip>

Your problem is that stored procedures are executed as an atomic unit.
That means that if there are any unhandled exceptions in the stored
procedure, then the entire stored procedure will be undone.

In your case, the "when any do" will catch all exceptions, then run a
procedure (which my guess is one or more insert statements). It then
raises another exception, which is not caught, so all the work in the
stored procedure (including the logging of the initial exception) is
undone.

There are two ways I can immediately think of to do this. You can
convert your exception table to an external table. External tables can
not be rolled back because they are not in a transaction, so even
though the rest of the stored procedure is undone, the insert(s) into
the external exception logging table are not undone. The second way is
to not raise the second exception, but rather have a flag as an output
parameter if there was a failure.

Adam