Using FireDac BatchMove with an Active Index on a Query

Question

I have a utility function to export a FireDac query to a csv file using the TFDBatchMove object.

My current dataset has an index, and I would like the data exported in index order when I call my ExportToCsv function. However, with .IndexName set I get the error: EFDException with message '[FireDAC][Comp][DS]-211. Cannot perform operation on unidirectional dataset [FDQuery1]'.

I've double checked fetch options and changing the CursorKind does not make a difference. I using a Static cursor.

I've traced into the FireDac code and the issue seems to happen when re-opening the query at the end of BatchMove.Execute. I have checked and my output file does contain all of the data.

TFDBatchMove.Execute calls Reader.Refresh which seems to close the Query. It then calls Reader.Close(False) which despite its name sets Dataset.Active := true attempting to re-open the query. The setting of Active to true is what throws the error.

Here is some example code where I call ExportToCsv without an error, set the index name and then call ExportToCsv a second time causing the error.

Is there a way around this? Why does FireDac close and re-open the query? Why can't the query be re-opened when an Index is active?

procedure TDemoData.ExportToCsvTest();
begin
  FDQuery1.Connection := MyDataAccess.Connection;
  FDQuery1.FetchOptions.CursorKind := ckStatic;

  FDQuery1.Sql.Text := 'Select PeriodPost, DebitAmt from Trans';
  FDQuery1.Open;

  with FDQuery1.Indexes.Add do
  begin
    Name := 'TestIndex';
    Fields := 'PeriodPost';
    Options := [];
    Active := true;
    Selected := false; // Start out with no current index
  end;

  // This export works fine.
  ExportToCsv(FDQuery1, 'c:\localdata\test1.csv');

  // After setting an Active IndexName there will be an exception:
  // EFDException with message '[FireDAC][Comp][DS]-211.
  // Cannot perform operation on unidirectional dataset [FDQuery1]'.
  FDQuery1.IndexName := 'TestIndex';
  ExportToCsv(FDQuery1, 'c:\localdata\test2.csv');
end;


procedure ExportToCsv(DataSet: TFDDataSet; FileName: string);
var
  TextWriter: TFDBatchMoveTextWriter;
  DataReader: TFDBatchMoveDataSetReader;
  BatchMove: TFDBatchMove;
begin

  DataReader := nil;
  TextWriter := nil;
  BatchMove := nil;

  try
    DataReader := TFDBatchMoveDataSetReader.Create(nil);
    TextWriter := TFDBatchMoveTextWriter.Create(nil);
    BatchMove := TFDBatchMove.Create(nil);

    DataReader.DataSet := DataSet;
    DataReader.Rewind := true;

    TextWriter.FileName := FileName;
    TextWriter.DataDef.WithFieldNames := true;
    TextWriter.DataDef.Separator := ',';
    BatchMove.Options := [poClearDestNoUndo, poCreateDest];

    BatchMove.Reader := DataReader;
    BatchMove.Writer := TextWriter;
    BatchMove.Execute;

  finally
    DataReader.Free;
    TextWriter.Free;
    BatchMove.Free;
  end;

end;

Answer 1

Personally, I don't follow why FireDAC reopens dataset object after batch move finishes. However, the problem in your case is caused by the Optimise option enabled. With this option enabled, the engine modifies dataset option set for speed which includes enabling Unidirectional option which, as you may guess now, is mutually exclusive with custom index specifications. So, we can actually narrow the problem into a code like this:

FDQuery1.FetchOptions.Unidirectional := True;
FDQuery1.IndexName := 'MyIndex';
FDQuery1.Close;
FDQuery1.Open;

And as I said, I don't know why FireDAC needs to reopen the source dataset which was only read, hence I would simply suggest turning the Optimise option off (but you can write your own reader):

DataReader.Optimise := False;

What I think should be improved in this component is (at least) backing up the original fetch option set with subsequent restoring when the batch move is finished and before the dataset is reopened.