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

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

Dmitry,

an example to show what I mean:

I create a table TST with PK and uniquwe constraint on NAME

  create table tst ( id   bigint not null,
                     name varchar(20));
  alter table tst add constraint tst_fk primary key (id);
  alter table tst add constraint tst_name_unq unique (name);

Then I run the following SPs against this table

  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

  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

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.

So for atomicity the WHEN-clause has to follow the _single_ concerning
statement resp. begin...end block.

I hope this explains my thoughts sufficiently.


Kind regards,

Christian