At 04:52 AM 12/02/2005 +0000, you wrote:

> >
> > That's why you should use an external table for logging if you
>want the log
> > to survive an unhandled exception. Writes to an external table
>don't get
> > undone by a rollback.
> >
> > ./hb
>
>Hi,
>
>No, it don't have to be that way. The way i do it inserting in SP1
>it will survive the exception because it is done in the when clause
>eating up the custom exceptions i want to respond too.

Right. You use the WHEN handler to "eat the exception".

>If it's some
>other exception that i don't respond too it won't be logged
>(inserted) and doesn't need to be because SP1 will be exitted and
>client will get the exact errormessage.

That's right too.

>My point is why isn't there
>an 'exception_message' object that contains the used messagetext
>related to the exception that occurred, just like the 'exception'
>object.

But there is. All of the exceptions thrown by the engine return two codes
and a list of messages to the client in the Status vector.

>It's there somewhere (because the client will get the exact
>message back incase of a non handled exception) but u just can't
>reference it nowhere inside the SP.

Arrrghhh!! It would be a crazy, terrible design to make execution
decisions by parsing error text! What if the error text got changed by
some minor corrective surgery in a sub-release (this happens!!) What if
the firebird.msg file is not in English? The API shelters you from this
sort of craziness.

You *can* access the SQLCODE and GDSCODE inside the SP, by reading either
the GDSCODE or the SQLCODE context variable (not both) inside a WHEN ANY
handler that immediately follows the block where the exception occurs. You
can reraise any exception.

So, for example:

create exception when_any 'xyz';

begin
.....
insert into blah values(:pk, :blah1, "blah2);
end
when any do
begin
if (gdscode = unique_key_violation) then
/* re-raise the exception */
exception when_any 'A record with a key of ' || cast(pk as
varchar(18)) || ' already exists';
end

when gdscode unique_key_violation do
begin
/* re-raise the exception */
exception when_any 'A record with a unique of ' || cast(pk as
varchar(18)) || ' already exists';
end

Or you could handle the gdscode directly instead of testing for it inside
the block:

>Would be a lot nicer then to have to refer to external tables.

Well, since (after all) it's not logging you really want to do, what is the
problem? The client will receive the text for the unhandled exception as
it is passed though the API.

If, on the other hand, you want to pass a "message" back to the client to
indicate a handled exception that you have skipped over, make provision for
it in a return value. This seems to take us back days (or is weeks?) to
your original inquiry....IF you swallow an exception, then you can pass
back whatever you like to replace the values in the empty fields on that
output tuple. If you have a varchar of at least 78 characters in that
tuple, it can be a custom string that you push into that field. What it
*can't* be is the message text of an internal exception, since that is not
available except as a code that you could convert to a local variable.

./hb