Firedac Array DML

Home → Knowledgebase → Delphi → Firedac Array DML

Introduction

How often does it happen that we need to add or update large numbers of records in the same table. Consider imports where this happens a lot.

Firedac has a nice solution for this called Array DML. Basically, in a query you define your INSERT, UPDATE or DELETE statement with parameters once, then fill the parameters as an array and then run the query.

The array of parameters is sent to the database along with the query in one command. The performance differences from a more classical approach are significant. In Chapter 15 of the book “Delphi in Depth: Firedac”, Cary Jensen discusses Array DML and its technical details. In this blog, the description of how to apply it.

Code example

First define your query and parameters. Set the array size for parameters to the number of items you want to use.

  Qry.SQL.Text:='INSERT INTO TABLE_TEST (ID, DESCRIPTION) VALUES (:ID,:DESCRIPTION)';
  Qry.ParamByName('ID').DataType := ftInteger;
  Qry.ParamByName('DESCRIPTION').DataType := ftString;
  
  Qry.Params.ArraySize := Descriptions.Count;

 
Then loop through your data, fill the parameters and execute the query.

  var i := 0;
  for var Item in Descriptions do
  begin
    Qry.ParamByName('ID').AsIntegers[i] := Item.ID;
    Qry.ParamByName('DESCRIPTION').AsStrings[i] := Item.Description;

    Inc(i);
  end;
  
  Qry.Execute(Qry.Params.ArraySize);

Using Blobs

It took some effort to figure out how this worked with BLOB fields. No example of this could be found online and therefore the code snippet below. First define the parameter for a BLOB field.

Qry.ParamByName('BLOBFIELD').DataType:=ftStream;
Qry.ParamByName('BLOBFIELD').StreamMode:=smOpenWrite;

 
In this example a Delphi record is written to the database as a binary field. The following code should be added in the loop where the parameters are filled.

ABlobStream := TMemoryStream.Create;
try
  APointer := @ARecord;
  ABlobStream.Write(APointer^, SizeOf(ARecord));

  ABlobStream.Position := 0;
  Qry.ParamByName('BLOBFIELD').LoadFromStream(ABlobStream, ftBlob, i);
finally
  ABlobStream.Free;
end;