At 10:05 AM 24/04/2005 +1000, you wrote:

>Hi All,
>Is it possible to obtain the value of the GDSCODE when an exception occurs
>within a trigger without explicitly using "when GDS_ERRNO....." syntax?
>Somtimes I need to catch all GDS errors and log.
>
>Background:
>Last week I had a situation where an insert was failing. Investigating
>showed one of the tables triggers was generating an SQLCODE = -901.
>This error can caused be many (>100, I lost count) GDS errors.
>
>I am logging errors such as this to a table using the following structure
>within my triggers:
>
>begin
> for select......
>...
>...
> where any begin
> insert into error_log (SQLERR, GDSERR, stamp) values (SQLCODE,
> GDSCODE, current_datetime);
> end;
>end;
>
>As documented SQLCODE returns the errror correctly, however, as also
>documented GDSCODE returns 0, which doesnt help to isolate the cause of
>the error, when the exact GDSCODE is unknown.

You can have the sqlcode or the gdscode, but not both. If you need the
gdscode, go for the gdscode, as each gdscode hangs off one and only one
sqlcode. (You can get the groupings from the download file in the User
Documentation category of the Knowledgebase section at the main Firebird
website).

Modified handler code (watch out for your flaky keyword syntax and
terminators!! this is PSQL, not Pascal)

set term ^^;

begin
for select......
...
...
WHEN any DO
begin
IF (GDSCODE IS NOT NULL AND GDSCODE > 0) THEN
insert into error_log (GDSERR, stamp)
values (GDSCODE, current_TIMESTAMP);
end /* ; */
end ^^

Reason for null test is that WHEN ANY is a catch-all and custom exceptions
don't have either a gdscode or an sqlcode.

You might also want to look at the idea of saving the gdscode into a local
variable and handing the internal exception on to a custom exception, so
that you can also log info about the record that caused the exception, e.g.
the primary key value. If you do this, be meticulous about re-initialising
your variable!!

./hb