At 10:30 PM 23/11/2004 +0000, you wrote:

>I encountered a problem when iterating through a TIB_Query where I
>never reached EOF.
>
>I've distilled it down to the following:
>
>procedure TFormCustomerMaintenance.Button1Click(Sender: TObject);
>var
> Count: word;
>begin
> Count := 0;
> DMCM1.QBillto.First;
>
> while not DMCM1.QBillto.BufferEof do begin
> DMCM1.QBillto.BufferNext;
> inc( Count );
> if Count > 500 then
> Break;
>
> end;
> ShowMessage( inttostr( Count ) );
>
> Count := 0;
> DMCM1.QBillto.First;
>
> while not DMCM1.QBillto.Eof do begin
> DMCM1.QBillto.Next;
> inc( Count );
> if Count > 500 then
> Break;
>
> end;
> ShowMessage( inttostr( Count ) );
>end;
>
>The first count returns correctly with 96 using the BufferEOF.
>
>The second count "Breaks" and returns 501.
>
>Is EOF not functional in IB_Query?

The absolute EOF will be found once the dataset has fetched all of the rows
that match the SELECT specification. By default, the bi-directional
dataset (ib_query) fetches enough rows to fill the buffer - in this case,
96. It operates a "sliding window" to minimise the amount of redundant
data flowing across the wire; so, at the point where the first Break
occurred, it was looking at a window of 96 records somewhere in the
theoretical "whole dataset".

Firebird/IB don't support bi-directional (scrolling) result sets, so IBO
achieves this behaviour by holding three cursors on the theoretical set.
Calling First() tells the dataset to relocate its window on the absolute
first record in the theoretical set. If that record was previously fetched
(as it was, on Open) then the dataset "knows" about it, in its KeyFields
buffer. If it doesn't have the data in the buffer currently, it has to
repeat the SELECT and get it (and the other rows in the window).

The other side of the coin is that, if you have code that calls for EOF,
the dataset will eventually reach the absolute EOF. Once it does so, it
"knows about" every row in the set that was eligible the last time it
fetched a batch from the server.

If you have to do an operation like this on a set that is too big to fit
into the buffer, call FetchAll() before you begin, to ensure that the
dataset "knows about" all of the rows, including the one before EOF. As an
observation, this kind of operation isn't client-server-cool on large
sets. In Read Committed isolation, it isn't even safe to assume that the
row count will remain stable; and, in any isolation, it will be invalid as
soon as a commit is done.

Those old Paradox-style client-side loops in most cases, should be
dispatched to the Great Desktop in the Sky. If you have to iterate through
a set and do similar stuff to each record in the set, then there is an SQL
statement lurking there that you can execute on the server to do the same
thing in a one-off DML operation without the traffic and timing overheads
that the client loops bestow.

Helen