FireDAC BatchMove and Calculated Fields

If you need to “pump” records from one data source to an other, then TFDBatchmove is for you.

Using FireDAC’s BatchMove component allows you to easily map the fields of one query to an other, regardless if they belong to the same database/connection or two totally different databases. You can actually even copy records from text files to “real” databases and vice versa.

Just build a BatchMove chain like this:

SourceData and DataTarget are regular TFDQueries, Reader and Writer are TFDBatchmoveDatasetReader/Writer components, that just connect the queries with the TFDBatchmove component in the middle.

The actual magic is done by adding field mappings, from source to target:

In this case source and target fieldnames are mostly the same, which wouldn’t even require adding mappings. There are a couple of calculated fields in the source query too. They are used to format/modify the data, so that it would match the format requirements in the destiantion query.

These fields are calculated in QOPCQuery.OnCalcFields like this:

procedure TDMMain.QOPCSourceDataCalcFields(DataSet: TDataSet);
  if QOPCSourceDataoSchnittgruppe.IsNull then
    DataSet.FieldByName('FANr').AsString := ''
    DataSet.FieldByName('FANr').AsString := GenFANr(QOPCSourceDataoSchnittgruppe.AsInteger); 

The calculated fields work fine, but when calling BatchMove.execute it will raise an exception. The reason is simple:

FireDAC’s internal logic depends on TDataSet.FieldDefs, but calclulated fields won’t show up there. You have to use “InternalCalc” instead of “Calculated”, to push your calculated fields to its datataset’s FieldDefs collection.

No Comments

Post a Comment