At 08:03 AM 16/06/2007, Adrian Wreyford wrote:

>I have a table ANIMAL that I populate from ANIMALLIST, that contains 820
>animals.
>I have a Query IB_QueryANIMAL with a number of animals ordered by age from
>oldest to youngest, and assigned animal id from 1 to 820 consecutively.
>
>The SQL for the Query is:
>
>Select * from ANIMAL
>ORDER BY ANIMALID
>
>(PS I know * is not great, but just for the example).
>
>In the application, I do the following:
>
>On form.open
>
>IB_QueryAnimal.Active := True;
>
>IB_QueryAnimalList.Active := True;
>
>I then run a script "Script1" with the following code:
>
>DELETE FROM ANIMAL;
>
>COMMIT WORK;
>
>Script1.execute.
>
>Now the animal table is empty, and using code I fill it with an ordered set
>from animallist, from the oldest to the youngest animal, and assign an id
>from 1 to 820 (ie 820 animals in ANIMALLIST), as I step through animallist.
>
>Now ANIMAL contains the animals numbered by animalID 1 = Oldest, and 820 =
>Youngest.
>
>I then set n := IB_QueryAnimal.Recordcount;
>
>IT RETURNS 821!!!!, and not 820?????
>
>I then Call:
>
>IB_QueryAnimal.First;
>
>But find animal 820, and not animal 1 as I would expect. (Note it is ordered
>on animalID, and I would expect 1 to be the first record.)
>
>If I do everything as above, but insert the following code before setting
>n := IB_QueryAnimal.Recordcount;
>Ie.
>
>IB_QueryAnimal.Close;
>IB_QueryAnimal.Open;
>
>And then
>
>n := IB_QueryAnimal.Recordcount; now returns 820! And this is correct.
>
>and then
>
>IB_QueryAnimal.First;
>
>I get animalID = 1; and this is also correct .. but why should I close -
>open for this to work corectly?
>
>Could anybody spare the time to explain this to me, as I'm baffled. I
>allways thought a Query honours its SQL throughout.
>
>Has the script got something to do with this?

On the client side you have output sets - the results of
queries. These sets live in a series of buffers, including one that
contains all the key field values. You haven't said anything about
your keylinks (from which IBO forms this buffer) so it would be
impossible to give you an answer that explains in detail what's going
on with those sets.

On the server side you have tables: the actual stored data. Your
script operates on the tables. The server doesn't know anything
about your client-side sets or IBO's buffers.

On the client side you have the original buffers. These will have
been kept up-to-date with any operations you performed on the
sets. Anything you do at the client will work on the buffers in the
state they were in last time you did a client-side operation and
reflect that state, e.g., inserts, deletes, key changes, etc.

Until you refresh the output (close and open the set), the buffers
don't have anything else to go on.

I suspect that you either don't have any Keylinks defined, or you
have KeyLinksAutoDefine set on an unkeyed set. In that case, IBO
will have the db_keys (rdb$db_key) as the keys. If you delete all
the records on the server side and then fill the table again, the
db_keys will have changed but, until you refresh the set, they will
be wrong in the buffer.

As for RecordCount, all it can ever be is either a) the result of a
select count(*) over the defined set (which isn't done by default,
because it is costly and unreliable), or b) a row-by-row count of
records fetched from the server's output buffer. It becomes
"accurate" (for the set only) once the last eligible record has been
fetched. In a multi-user environment, RecordCount can never be
considered accurate; it's nothing but a snapshot of one of these two
things, that has a strong propensity to go out of date.

IBO starts maintaining the RecordCount number itself, incrementing
and decrementing according to positioned inserts and deletes that are
done on the client buffers and further fetches that are being done as
the buffer pointers head towards the last record. Once the last
record is fetched, IBO's counter should be more or less
accurate....but there's no way it could be, if you go off and do
stuff to the table outside the context of the set.

If this is a serious question then I hope your experiences with this
design at least illustrate why it is not a dependable approach to
getting a reliable set into multiple clients. Abandon this table
idea and instead write a selectable stored procedure to produce the
ordered set. This ensures that you will always be able to get an
up-to-date snapshot without destroying any data.

Helen