Re: [Firebird-docs] Firebird 1.5 Release Notes - Error/Exception Handling Syntax

[Christian D. <chr...@da...>]

Dmitry,

>First of all, I'd like to say that IB6 documentation is a bit wrong about
>the PSQL exception handling :-)
>
>>   create procedure tst_when_in
>>     returns (state_txt varchar( 10))
>>     as
>>       begin
>>         delete from tst
>>                where id = 1;
>>         delete from tst
>>                where id = 2;
>>
>>         begin /* <<< when inside block */
>>           insert into tst (id, name)
>>                  values   (1, 'Name_1');
>>           insert into tst (id, name)
>>                  values   (2, 'Name_1');
>>           when any do
>>             state_txt = 'Error!';
>>         end   /* <<< when inside block */
>>       end
>
>WHEN is always inside a block. The above example has it inside an unnamed
>statement-level BEGIN..END one.

And how would you unite both inserts to react in a similar way
(commit/rollback) on errors - my primary question? Not this way, as I
demonstrated!

I don't want to discuss FB internals. They work fine. My concern is
the syntax in your example, and that's why I posted in firebird-docs.

>>   create procedure tst_when_out
>>     returns (state_txt varchar( 10))
>>     as
>>       begin
>>         delete from tst
>>                where id = 1;
>>         delete from tst
>>                where id = 2;
>>
>>         begin /* <<< when outside block */
>>           insert into tst (id, name)
>>                  values   (1, 'Name_1');
>>           insert into tst (id, name)
>>                  values   (2, 'Name_1');
>>         end   /* <<< when outside block */
>>           when any do
>>             state_txt = 'Error!';
>>       end
>
>WHEN is outside the previously mentioned block, but inside the
>procedure-level one. You cannot have a SP/trigger without BEGIN..END
>construct.

A truism. Again: The procedure level block doesn't concatenate
statements for commit/rollback atomicity. See result of tst_when_in.
There might not be any row after an error deriving from a statement
within the block. Right?

>> The resulting table entries:
>>   tst_when_in : 1 row  (id = 1, name = 'Name_1')
>>   tst_when_out: empty
>>
>> Both SPs generate a unique constraint error, but in the end the
>> content of the table differs. With your favourite (tst_when_in) the
>> result from the first insert remains in the table (not desired) while
>> only tied together by a begin...end (tst_when_out) both inserts act
>> simultaneously and the error initiated by the 2nd insert rolls back
>> the first one.
>
>If a BEGIN..END block doesn't have any matching error handlers and an
>exception occurs, then all its actions are undone. Otherwise only the
>problematic statement is undone and execution goes to the WHEN block. That
>how it works, regardless of how Borland documentation says it.

You're right, tst_when_in does have one error handler for both
inserts. An abortion of the SP wouldn't occur even on an error during
the first insert, but nevertheless the first insert isn't undone after
the forbidden 2nd insert!

>> So for atomicity the WHEN-clause has to follow the _single_ concerning
>> statement resp. begin...end block.
>>
>> I hope this explains my thoughts sufficiently.
>
>I just suggest you another vision of the same thing. It may be more
>difficult to understand, but that's how the things actually work. WHEN
>clause is a part of a BEGIN..END block.

Right again, just as everything is part of a greater unit...;-) But
regardless of this, as I showed above, the wrapping block definition
doesn't unite statements to react similar on errors.


To sum it up I think we have to distinguish between error handling and
the atomicity of a statement block.

For error handling (suppressing SP abortion and executing according
commands) a WHEN within the BEGIN...END block is O.K..

But this doesn't apply to commit/rollback-atomicity, which only
proceeds in the desired way with the single preceding statement
(block), whether this fits the common rules or not.


Thanks for your efforts,

Christian