Subject RE: [firebird-support] Generators, revisited
Author Alan McDonald
> 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 adding
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 from
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 of
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 AM,
> the other after 1 PM.
> The journals show the same invoice number. I have never gotten a support
> call on the raise portion of the above try/except. These particular users
> 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 my
> backside.
> Kevin