At 06:23 PM 10/08/2004 -0300, you wrote:

>Helen
>
>Thanks for your answer.
>I think there is no conceptual problem, I only post a small code to show
>the idea. (see at end for a "minimal" "real" executable example. Try to
>guess the resulkts, you may be surprised)

OK, then I agree to remain mystified until revelation.

>Despite my particular use, must be a need for GDSCODE and SQLCODE, for
>documentation in Firebird reflects that:
>
>BEGIN
> ...
> WHEN SQLCODE -802 DO
> EXCEPTION E_EXCEPTION_1;
> WHEN SQLCODE -803 DO
> EXCEPTION E_EXCEPTION_2;
> WHEN ANY DO
> EXECUTE PROCEDURE P_ANY_EXCEPTION(SQLCODE);
>END
>
>I can't imagine why the developers (Dmitry Yemanov) created GDS_CODE and
>SQL_CODE, but no something like "USER_CODE".

Huh? There is a context variable named GDSCODE and one named
SQLCODE. What's different in 1.5 is that you can capture the actual code
via the context variable, within the block context.

BEGIN
...
WHEN SQLCODE -802 DO
EXCEPTION E_EXCEPTION_1;
WHEN SQLCODE -803 DO
EXCEPTION E_EXCEPTION_2;
WHEN ANY DO
begin
if (SQLCODE IS NOT NULL) THEN
EXECUTE PROCEDURE P_ANY_EXCEPTION(SQLCODE);
END

You can also do stuff like:

e.g.

declare GCODE integer;
declare SCODE integer;

...
when GDSCODE foreign_key do
begin
gcode = GDSCODE;
..BLAH..
end

>(
> 5. If user-defined exception is thrown, both SQLCODE and GDSCODE
> variables contain zero, regardless of the exception handling
> block type.
>)
>
>What I DESEPERATE need is something like USERCODE, because when an user
>exception is raised the exception number can not be in any variable, and
>must be "WHENed" for each one.

OIC, you're after the ability to pick up the name or identifier of a
user-defined exception. True, there isn't any way to store that into a
variable. If you can make a case for it, why not raise it in
firebird-architect?

>So I will very happy to do a EXECUTE PROCEDURE
>P_ANY_EXCEPTION(SQLCODE,GDS_CODE, USER_CODE);
>or better EXECUTE PROCEDURE P_ANY_EXCEPTION(EXCEPTIONCODE); where
>EXCEPTIONCODE < 0 for user exceptions, or something like this.
>
>Now, I can't put the exception in a parameter for a SP, and I'm forced to
>do a very hard to mantain code
>
>WHEN EXCEPTION E_EXCEPTION_1 DO
> somethig(1)
>WHEN EXCEPTION E_EXCEPTION_2 DO
> somethig(2)
>...

Absent the ability to pick up a context variable, why not devise your own
system of local variables that map to your user-defined exceptions?

..
declare MyException varchar(31);

....
WHEN EXCEPTION E_EXCEPTION_1 DO
begin
MyException = 'E_EXCEPTION_1';
EXECUTE PROCEDURE SOMETHING(:MyException);
end
...

It's not as useful as a context variable but at least you have a way to
generalise the handler.

However, I reiterate that a handler must actually HANDLE the exception in
some way that corrects the error immediately and allows execution to
proceed successfully. In a trigger, an SQLCODE or GDSCODE exception
handler is usually unlikely to fix most problems, e.g. constraint
violations, so that the trigger action will ultimately fail, or simply
recurse until the recursions get deep enough to throw a fatal recursion
error. Error codes posted to a database table during the course of
execution, up to the point of ultimate failure, will be lost when the
operation fails.

/heLen