From: "Filip Křikava" <krikava@...>

> Hi, I have some questions how exceptions inFB 1.5 works:
>
> 1. I have this stored proc
>
> returns (P integer)
> as
> begin
> P = 0;
> begin
> exception A;
> when exception A do
> P = 1;
> exit;
> when ANY do
> P = 2;
> end
> end
>
> After executing the variable P=2 and it throws an exception SQLCODE -999
> Iterbase Error.

In all IB/FB versions (even FB1.5) there is a bug - you should not mix
WHEN ANY with other WHEN ... clauses in the single block. Try this:

begin
P = 0;
begin
exception A;
when exception A do P = 1;
end
when ANY do P = 2;
end

> 2. In release notes for FB 1.5 there is code:
> WHEN ANY DO
> BEGIN
> INSERT INTO ERROR_LOG (...) VALUES (SQLCODE, ...);
> EXCEPTION;
> END
>
> How can I insert record and call the exception, when if the exception is
> unchatched the inserted record will be rollbacked. I need to make an error
> log.

If it is really what you need (i.e. all actions made by SP are undone,
but log entry is inserted anyway), your only chance is to write log
into external table (because it is not under transaction control).
Or, do not re-raise exception, instead return error code in parameter.

> 3. How can I get text message including for example constraint name from
> SQLCODE exception. From SQLCODE I can find error code but no message with
> current values.

On the client use one of these functions:
isc_sql_interprete() or isc_interprete()

Ivan