Subject Re: never reach EOF
Author rick_roen
Thanks Helen,

In this case I'm just looping through the entire dataset and
printing a report for Customers, Billto accounts etc., so I need the
entire set.

I tried a FetchAll both before and after the DataSet.First, but it
is still the same result.

In this case there are only 96 rows in the Billto dataset so the
first loop with BufferEOF stops at 96, the next loop with EOF stops
at 501 after having repeated the last row many, many times in my
report that I am trying to print.

I don't understand why the regular EOF never seems to return TRUE?



--- In, Helen Borrie <helebor@t...> wrote:
> 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-
> dataset (ib_query) fetches enough rows to fill the buffer - in
this case,
> 96. It operates a "sliding window" to minimise the amount of
> data flowing across the wire; so, at the point where the first
> occurred, it was looking at a window of 96 records somewhere in
> 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
> 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
> 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
> 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
> 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
> 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
> that the client loops bestow.
> Helen