At 08:43 AM 23-04-02 +1200, Sandeep wrote:

>On 22 Apr 2002 at 16:09, Helen Borrie wrote:
>
> > At 01:02 PM 22-04-02 +1200, Sandeep wrote:
> > >Is it possible to store SQLCODE in a local variable?
> >
>
>Sorry about that, I meant SQLCODE in stor proc

IMO, error handling in SPs and triggers is 80% broken in ib 5.x, ib 6.x and
Firebird. It's a long time since I looked at 4.x but I'm sure you could do
this then (or its equivalent, given changes in casting rules):

create procedure testit(invar1 integer, invar2 integer)
returns (outvar integer)
as
declare variable result double precision;
begin
outvar = 2002;
result = cast (invar1/invar2 as double precision);
when any do
begin
outvar = cast(SQLCODE as integer);
exit;
end
end

However, even if this were never possible, the whole error-handling area
does not work as documented. In fact, none the versions cited above
recognises GDSCODE *at all*. Yet, for SPs, this is the only way to catch
data-related run-time errors. SQLCODE won't cut it.

I'm intending to post three bugs wrt to this area. I think possibly some
changes happened at some point in 5.x development that broke a lot of
things. For example, if you query RDB$EXCEPTIONS, you should (according to
the doc) see all of the engine's exception codes and messages, as well as
user-defined ones. But you will find this table contains nothing but your
UDE's.

However, all that said, you *can* create a user-defined exception to catch
potential exceptions *before* they happen, which is presumably what you
need to do. For example,

create exception blah 'Divide-by-zero error';
commit;
...
create procedure testit(invar1 integer, invar2 integer)
returns (outvar varchar(78))
as
declare variable result double precision;
begin
outvar = 'No div-by-zero error occurred';
if invar2 = 0 then
exception blah;
result = cast (invar1/invar2 as double precision);
when exception blah do
begin
outvar = select rdb$message from rdb$exceptions
where rdb$exception_name='blah';
exit;
end
end
That is, by trapping the exception yourself and handling it, you pre-empt
the db engine from encountering it at all. Any other errors will be
trapped as usual.

But - if the doc is correct - this should work too:

create procedure testit(invar1 integer, invar2 integer)
returns (outvar varchar(78))
as
declare variable result double precision;
begin
outvar = 'No div-by-zero error occurred';
result = cast (invar1/invar2 as double precision);
when gdscode 35544778 do
begin
outvar = 'Div-by-zero error occurred'
exit;
end
end

But the SP compiler doesn't recognise the gdscode keyword at all. The doc
says it should.

Still, this isn't particularly useful if all you want to do is return a
message to the client. The status array will return the gdscodes of errors
it traps and your client can handle those easily. You can also get at
UDE's through the status array.

You really only want this server-side handling if, for example, you want to
just swallow a particular error if it occurs - for example, when looping
through an external file structure with FOR SELECT...DO...SUSPEND, you just
want to ignore an invalid input record, maybe log it somewhere and move on
to the next record. In a data entry situation, it would be very unwise: a
hornet's nest.

regards,
Helen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________