Subject RE: [firebird-support] Get duplicate generator value? Am I doing wrong?
Author Helen Borrie
At 07:47 AM 1/04/2005 -0800, Kevin Stanton wrote:

>Hi Helen,
>I thought there was also a transaction issue as well. I was getting
>duplicates as well at one point using a TIB_Cursor and you recommended the
>qryGetOrderNos is TIB_DSQL
> with qryGetOrderNos do
> begin
> try
> trNextID.StartTransaction;
> SQL.Strings[0] := 'select gen_id ( gen_orderno, ' + IntToStr(OrderCnt)
>+ ' ) as EOrderNo from rdb$database';
> If NOT Prepared then Prepare;
> Execute;
> eo := FieldByName('EOrderNo').AsInteger;
> trNextID.Commit;
> except
> trNextID.Rollback;
> raise;
> end;
> end;

This doesn't look like my code! maybe a mod of someone else's,
though. This routine could certainly return duplicates - it would only
take the increment expression evaluating to '0' to cause that.

>This has always confused me because in the docs that I've read, the
>generator function works independent/outside of transactions thus always
>guaranteeing a unique value.

It does. But gen_id(MyGenerator, 0) is always going to return a duplicate
number. Using client code to mess around with generator values needs a
very even head. Checking that the increment value is > 0 would be essential.

>(I'm using FB 1.03, soon to be 1.5.x)
>Is using the IB_Connection a better, more bullet-proof way of retrieving
>generator values?

Not better or more bullet-proof, just more elegant and always consistent.
One line of code as compared with about 8. Code re-use is the key here.
Why keep a dog and do the barking yourself?

>Someone else posted a reply stating that once he moved to
>FB 1.5, the problem went away. Is this a possible bug w/ <= 1.3?

If so, it's the first I've heard of it. v.1.0.3 has been around a long
time. Many gazillions of generators have rolled out since then. I would bet
that something else they had to fix in the transition to v.1.5 also forced
them to fix whatever was causing that problem (revisiting their return
structure without a fresh call to gen_id() ?)