At 11:03 PM 29/12/2003 -0700, you wrote:

>Hi,
> I have a TIBOQuery with the following sql:
> select gen_id(lotidgen, 1) from rdb$database
>
>Am using the following components in an simple test application:
>
> IBOQuery1: TIBOQuery;
> IBOTransaction1: TIBOTransaction;
> IBOQuery1GEN_ID: TLargeintField;
> Button1: TButton;
> Label1: TLabel;
> IB_Connection1: TIB_Connection;
> IB_Session1: TIB_Session;
>
>I have a button event with the code:
>
>var
> num : int64;
>begin
>if IBOQuery1.active then
> IBOQuery1.active := false;
> IBOTransaction1.StartTransaction;
> try
> IBOQuery1.active := true;
> num := IBOQuery1GEN_ID.aslargeint;
> label1.caption := inttostr(num);
> except
> IBOTransaction1.Rollback;
> raise;
> end;
> IBOTransaction1.Commit;
>end;
>
>When I run the program, the generator is incremented by 3 each time I click
>the button. Why is this?
>If I use a TIBODatabase and use it to control the transaction, then the
>generator increments properly (ie, by one each time).

I suspect this is coincidence.

Every time you set this query active, the generator will fire. You can't
rollback a number-generation - once it happens, it is done "for
good". Generators fire independently of any transaction. Each time you
"open" that query, you will return the next number of the series - hence
the behaviour you are seeing. Each click of the button increments the
generator.

Don't use the IBOQuery you have shown us to get the new value of the
generator. It does exactly what you don't want. Use the GeneratorLinks
property of the query that needs the value, e.g. qryYourTable. The entry
should look something like this (no spaces in it):

idfield=lotidgen

>Should a TIB_Connection component not be used with the Tdataset compatible
>components?

You can use a TIB_Connection with the TDataset-compatible
components. IBODatabase can be more useful if you are converting a BDE
application, as it has some VCL compatibility properties that IB_Connection
doesn't have.

Incidentally, both TIB_Connection and TIBOQuery (through inheritance) have
the Gen_ID() function, that does what your query does. Look up its
arguments in the help text.

Another tip: if you have a Before Insert trigger like this on the table:

begin
new.idfield = gen_id(lotidgen, 1);
end

..then your trigger will fire when the insert is posted, regardless of what
you passed in the field. If you already got the next value via
GeneratorLinks or Gen_ID() or, indeed, your query (above) then the
generator will end up being multi-stepped.

Modify the trigger to

if (new.idfield is null) then
new.idfield = gen_id(lotidgen, 1);

Helen