Hello Helen

I accept that this is unexpected behaviour (to me) and consistent with
CommitAction - see below . .

> >
> >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.

I undertsand that - but the only call to gen_id( ,1) should be at Open.

>
> 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.

The point I was trying to make is that there should not be surpise and
unnecessary fetches. This example was contrived using a generator as a way
of proving the fetch took place ;-)

I don't agree that Close causes a fetch - and it didn't.
Open did cause a fetch as expected.
I didn't do a refresh but if I had would have expected it to cause a fetch.
Commit caused a fetch - this I don't think is right.

>
> 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.
>

That's not quite right - there is no automatic opening before calling
close/open.

> 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.
>

I understand this - sorry it did not make it clear that my example was very
contrived to demonstrate a point.

> 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.
>

I didn't realise that gen_id was available at connection level - that's
handy - thanks.

OK - I'm coming to view that this probably isnt a bug (whenever I stick my
neck out and make a bug accusation I get proved wrong ;-) ) its more like
unexpected behaviour. I noticed that the default value for TIBOQuery
CommitAction is caInvalidateCursor while for TIB_Query it is caClose. So
the behaviour is totally consistent with the CommitAction setting. I cant
easily check what effective CommitAction a TQuery exhibits but TIBOQuery
should be consistent with it.

Regards
Paul