At 08:08 PM 30/05/2007, you wrote:

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

Wrong. You placed a selectable SP in a bi-directional dataset. The
Open method invokes the SP and begins fetching data from its output
buffer. The IBOQuery opens its dataset by default - as you are
apparently already aware, because you begin by calling its Close
method (with no test to determine whether or not it is active, by the way....)

> >
> > 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 ;-)

You get what you ask for. You can actually influence the default
behaviour of the IBOQuery at session level and stop it from opening
datasets automatically. (IB_Query doesn't have this
behaviour....sensibly). So the answer here is that, if you don't
like the TDataset query's behaviour, either modify it explicitly or
use the native IBO data access.

>I don't agree that Close causes a fetch - and it didn't.

It doesn't. It was the Open initially that invoked the SP and
incremented the generator. Your test returns the value that was
generated the first time the SP was called. Then you re-open the
dataset, i.e. invoke the SP again, and you get the next value from
the generator.

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

Close followed by Open is a refresh.

>Commit caused a fetch - this I don't think is right.

It's not, and it didn't. Your monitor reported two invocations,
which is consistent with the code snippet you provided.

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

Then what is the purpose of calling Close in your code?

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

Ah, yes. This is a revolving door. You shouldn't be using a
bidirectional dataset to invoke any query with a singleton result,
anyway...but doing so with this particular SP (which shouldn't be a
selectable SP for obvious reasons) just about ensures you're going to
see "unexpected behaviour". :-)

cheers,
Helen