Walking through records in a Dataset


TIB_Cursor
If you are dealing with a large dataset it would probably be a good idea to use the TIB_Cursor component. I have this on the IBO Advanced tab. It is used in a loop a bit differently than the IB_Query is.

  with myCursor do
  begin
    First;
    while not Eof do
    begin
      RecordValue := FieldByName( 'MYCOL' ).AsString;
      Next;
    end;
  end;

One thing I'll often do with the TIB_Cursor is assign an AfterFetchRow and an AfterFetchEof events and then put whatever actions I want to take place in them.

For example, if I have a stringlist I want to populate I could do something like this:

  MyCursor.BeginBusy( true );
  try
    MyStrings.BeginUpdate;
    try
      MyCursor.Close;
      MyCursor.Open;
      // If you set AutoFetchAll to true then this line isn't necessary.
      MyCursor.FetchAll;
    finally
      if MyCursor.FetchingAborted then
      MyStrings.Clear;
      MyStrings.EndUpdate;
      MyCursor.Close;
    end;
  finally
    MyCursor.EndBusy;
  end;

  procedure TADataModule.MyCursorAfterFetchRow( ... )
  begin
    MyStrings.Add( MyCursor.FieldByName( 'MYCOL' ).AsString );
  end;

What you accomplish by doing this is you allow the user to cancel out of the query processing and abort. When calling the FetchAll method IBO will do what is called FetchCallbacks that allow a dialog to appear showing a count of the rows fetched and providing a button to cancel processing the query.

This is ideal for doing large exports. It is also possible to override the default callback behavior and plug in your own functionality. You might want to make a button visible on a form that would allow the user to cancel the query instead of rely on the dialog appearing...

TIB_Query
If you decide to use a query then you do this:

  with myQuery do
  begin
    Open; // This line is necessary with TIB_Query.
    First;
    while not Eof do
    begin
      RecordValue := FieldByName( 'MYCOL' ).AsString;
      Next;
    end;
  end;

The reason for the difference is that with a unidirectional dataset the First method has to close and reopen the cursor and perform a fetch to know that it is on the first record of the dataset. So, if you Open the cursor and then call First it will be closed and reopened. This would be inefficient.

The reason you can't just call First with the IB_Query is because First does not implicitly open a buffered query. It is possible to insert records into a closed query and then you can call the navigational methods to scroll in a closed (yet populated with records) dataset.

The TIBOQuery (TDataset based) query behaves exactly like TQuery so its different from the TIB_Cursor and TIB_Query both.

One thing that is often handy is to walk the buffer of a TIB_Query without affecting the current record position. This is accomplished by using the BufferXXXX methods and properties.

  with myQuery do
  begin
    Open; // This line is necessary with TIB_Query.
    BufferFirst;
    while not BufferEof do
    begin
      RecordValue := BufferFieldByName( 'MYCOL' ).AsString;
      BufferNext;
    end;
  end;

There are a good number of things that you can accomplish by using the buffer pointer instead of the current record pointer. This is a unique capability of native IBO datasets so all that you do here will not be portable to other data access layers.

Other BufferXXXX properties and methods of interest might include:

   BufferRowCount - Tells the number of records fetched into the buffer.
   BufferHasEOF - Tells if all of the records have been fetched into the dataset.
   BufferHasBOF - Same as above except this hasn't been fully implemented yet.
   BufferBookmark - Allows the bookmark to be set or retrieved from the current buffer row.
   BufferRowNum - Sets or gets the row number of the current buffer row.
   BufferActive - Tells when the dataset actually has records in it regardless it the dataset is opened or not.