At 12:04 AM 30/05/2007, you wrote:

>Hi
>
>IBO version 4.7.16
>
>I have an TIBOQuery with 'select * from test2'
>
>test2 is a procedure that return a number from a generator - -
>create procedure TEST2
>returns (i integer)
>as
>begin
> i=gen_id(inv_no,1);
> suspend;
>end
>
>the query is connected to a TIB_connection and a TIB_Transaction (no
>autocommit). I also have a TIB_Query (q) with default transaction just for
>reading the generator value and a TMemo for displaying these.
>
>I run the code
>
>procedure TForm1.Button1Click(Sender: TObject);
>begin
>memo1.lines.add('before '+intToStr(q.Gen_ID('inv_no',0)));
>ib_transaction1.StartTransaction;
>iboquery1.Close;
>iboquery1.Open;
>memo1.lines.add('after open '+intToStr(q.Gen_ID('inv_no',0)));
>ib_transaction1.Commit;
>memo1.lines.add('after commit '+intToStr(q.Gen_ID('inv_no',0)));
>end;
>
>I get the following results
>before 291974
>after open 291975
>after commit 291976
>
>commit causes the statement to execute again and increment the generator.
>
>Also the IB_Monitor shows execute statement occurring twice.
>
>If I change the TIBOQuery to a TIB_Query this doesnt happen.

Nevertheless, it's not a bug. Remember that generators are not under
transaction control: any call to GEN_ID() with a non-zero increment
argument is going to increment the generator. It will stay
incremented regardless of what happens to the transaction that
invoked the function.

This should not be a selectable procedure because every fetch request
from the client is going to increment the generator. With a
bi-directional dataset, which is absurd for *any* single-row result,
it doesn't take much client activity to cause a re-fetch. Each Close
and Open sequence (including Refresh) will re-execute the SP and
increment the generator.

On the client side, the difference between the iboquery and the
ib_query is that your iboquery is opening automatically (one
increment), then you are calling Close and Open __again__
(re-executing the SP and incrementing again), while the ib_query
waits to be told to open. But neither case is advisable. You are
tempting fate with two cumulative and unpredictable effects.

Make it an executable procedure (remove the SUSPEND) and use a
TIB_dsql to execute it. After the Execute call on the client, read
the Fields[] array to get the result into a variable, and immediately
commit the transaction.

However, with the client-side Gen_ID procedure available at
connection level, there is no need to have any statement object, at
design-time or at run-time, for the purpose of fetching a new value
from a generator. Your SP is risky at best, i.e., just because you
can do it doesn't mean you should.

Helen