Subject RE: [firebird-support] Generators, revisited
Author Kevin Stanton
This is a bit scary - the possibility of generators returning invalid
numbers - numbers already used. If generators truly worked outside the
transaction, the number should be incremented whenever called upon.

I got most of the code below from Ann and Helen - Ann regarding the actual
SQL statement and Helen for the proper retrieval using the IBO TIB_DSQL
component with proper transaction handling.

I'm going to have to go overboard on code now in my Delphi app to make sure
the numbers generated don't already exist in the system.

Thanks for your replies.


-----Original Message-----
From: Alan McDonald [mailto:alan@...]
Sent: Friday, September 10, 2004 5:11 PM
Subject: RE: [firebird-support] Generators, revisited

> Greetings,
> I'm getting into hot water here as invoice numbers are being duplicated.
> >From what I know, generators work "outside" the transaction
> thus, they can
> "never be duplicated". Such is the premise I'm working on, but
> somewhere/somehow, this is not true, or the statements below do
> not properly
> increment the generator. I'm sure it's me somehow.
> Does anyone see a flaw in the following function:
> (qryGetInvNo2 is an IBO TIB_DSQL component)

you do't call generators like this normally. Always increment them in a
standard way - don't fool around with manipulating the InvCnt this way. It
should always be 1 or whatever other increment you wish. Use 0 to find the
current value but never use this value in multi-user environments by
1 to it on the client side. Another connection may have already incemented
it and your value is invalid.
Use the server to increment the generator and just call the next value
the server instead.
If your issue is then keeping a consecutive run of invoice numbers with no
gaps then this is quite a different set of demands and will require a bit
research by you and maybe more question here on the subject. But using the
method you have here to get generator values is faulty.

> function TdmInvoice.GetInvoiceNos(const InvCnt: Integer; const Inventory
> Boolean): Integer;
> begin
> try
> trInvPost.StartTransaction;
> qryGetInvNo2.SQL.Clear;
> If (Pos('SENECA', Uppercase(CoName)) > 0) and (Inventory =
> True) then
> qryGetInvNo2.SQL.Add('select gen_id (gen_invoiceno_pi, '+
> IntToStr(InvCnt) + ') as NextInvNo from rdb$database')
> else
> qryGetInvNo2.SQL.Add('select gen_id (gen_invoiceno, '+
> IntToStr(InvCnt) + ') as NextInvNo from rdb$database');
> qryGetInvNo2.Active := True;
> Result := qryGetInvNo2.FieldByName('NextInvNo').AsInteger;
> trInvPost.Commit;
> except
> trInvPost.Rollback;
> raise;
> end;
> end;
> This is the first routine to run at time of posting the invoice batch.
> I have 2 invoice journals from 2 different users, same day, one in the
> the other after 1 PM.
> The journals show the same invoice number. I have never gotten a
> call on the raise portion of the above try/except. These particular
> are quite good on reporting errors.
> Environment:
> Firebird 1.03
> Delphi 6.2
> IBO Version 4.2.Gb
> Thanks very much in advance to anyone that can shed some light and save
> backside.
> Kevin

Yahoo! Groups Sponsor

Yahoo! Groups Links

a.. To visit your group on the web, go to:

b.. To unsubscribe from this group, send an email to:

c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.

[Non-text portions of this message have been removed]