Fetching and Populating Questions (FireDAC)

Go Up to FAQ (FireDAC)

This topic contains a list of questions and answers related to fetching records and populating datasets.

Q1: Does FireDAC provide fast forward-only, read-only access to the result dataset?

A: Set FDQuery.FetchOptions:

• CursorKind = ckDefault or ckForwardOnly
• Mode = fmOnDemand
• RowsetSize = 1000
• Unidirectional = True
• Optionally exclude fiMeta from Items, if you do not need to edit the dataset.

Q2: How can I append multiple result sets to a FDQuery or a FDMemTable?

A: The following approach with the client dataset to hold multiple result sets:

  begin loop
    // run SqlQuery with new params
    ...
    ClientDataset.AppendData(SqlQuery.Data)
    ...
  end loop

may be replaced with the single TFDQuery. For example:

  // initial open and fetch
  FDQuery1.Params[0].AsInteger := 1;
  FDQuery1.Open;

  // reexecute command, fetch rows again and append them
  // to the existing rows
  FDQuery1.Command.Close;
  FDQuery1.Params[0].AsInteger := 2;
  FDQuery1.Command.Open;
  FDQuery1.FetchAgain;
  FDQuery1.FetchAll;

  // reexecute again with different parameter value
  FDQuery1.Command.Close;
  FDQuery1.Params[0].AsInteger := 3;
  FDQuery1.Command.Open;
  FDQuery1.FetchAgain;
  FDQuery1.FetchAll;

Q3: How can I execute a query and append its result set to an existing dataset, without inserting these records into the database?

A: See the TFDDataSet.FetchAgain method description. Alternatively, you can execute a SQL command that will fetch the additional records into an existing dataset:

FDCommand1.CommandText := 'select ... from ... where id= :id';
FDCommand1.Open;
FDCommand1.Fetch(ADQuery1.Table);
FDCommand1.Close;

Q4: My query returns 800 records, but RecordCount returns 50. What is wrong?

A: By default, the RecordCount shows the number of records in the dataset record's cache. For more information about record counting modes, see the FetchOptions.RecordCountMode property.

50 is the default rowset size. This is the number of records FireDAC will fetch at a single request. So, right after opening, the dataset will have <= 50 records, that is what RecordCount is showing. When you navigate through the dataset, it will fetch additional rowsets and the number of records may grow.

To show the total number of records that the query returns, you may do one of the following:

• Perform ADQuery1.FetchAll. As a result, all records will be fetched and RecordCount will show their number;
• Set FetchOptions.RecordCountMode to cmTotal. Note that this may lead to performance degradation, as FireDAC will perform an additional SELECT COUNT(*) query for each SELECT query.