Array DML (FireDAC)

From RAD Studio
Jump to: navigation, search

Go Up to Working with Commands (FireDAC)

General

The Array DML execution technique submits a single DBMS command with an array of parameters. Each command parameter has an array of values, and all parameters have arrays of the same length. Then FireDAC requests the DBMS to execute a command once for each row in arrays. This technique reduces the amount of communication between DBMS and client, enables DBMS to stream command execution, and speeds up execution time.

The following picture illustrates this process:

FDPhysCmdBatch.png

In FireDAC terms, "batch command execution" and "Array DML execution" are often used as synonyms. Array DML may be used for nearly all parameterized commands, including stored procedure calls. FireDAC implements Array DML using native DBMS API capabilities, or emulates Array DML execution if the DBMS API does not support it.

The following table lists DBMS and Array DML features:

DBMS Array DML Implementation Array DML Mode Array DML Limit Symptoms
Advantage Database Emulation aeUpToFirstError
DataSnap server Emulation aeUpToFirstError
IBM DB2 Native aeCollectAllErrors
Informix Native aeCollectAllErrors
InterBase v < XE3 Emulation aeUpToFirstError
InterBase v >= XE3 Native (Command Batch API) aeUpToFirstError
Firebird v < 2.1 Emulation aeUpToFirstError
Firebird v >= 2.1 Native (EXECUTE BLOCK) aeOnErrorUndoAll "Too many contexts" error
Microsoft SQL Server Native aeCollectAllErrors Possible "Access violation" error
Microsoft Access database Emulation aeUpToFirstError
MySQL Server Native (INSERT with multiple VALUES) aeOnErrorUndoAll
Oracle Server Native (OCI Array DML) aeUpToFirstError Application hangs up. Explicit limit - 65K of array items.
PostgreSQL v < 8.1 Emulation aeUpToFirstError
PostgreSQL v >= 8.1 Native (INSERT /MERGE with multiple VALUES) aeOnErrorUndoAll
SQLite database v < 3.7.11 Emulation aeUpToFirstError
SQLite database v >= 3.7.11
  • Emulation, when Params.BindMode = pbByName
  • Native (INSERT with multiple VALUES), when Params.BindMode = pbByNumber
  • aeUpToFirstError
  • aeOnErrorUndoAll
Sybase SQL Anywhere Native aeUpToFirstError
Teradata Database Native aeOnErrorUndoAll

Note:

Command execution

Before Array DML execution, the application code must setup parameter value arrays. First, setup array length by assigning value to Params.ArraySize. Assigning this property value, implicitly assigns specified array length to all parameters ArraySize property. So, Params collection must be not empty before assigning to Params.ArraySize. Second, assigns values to parameter arrays. TADParam class has a set of AsXXXs [AIndex: Integer] properties, similar to AsXXX properties and other properties and methods accepting as first parameter array index. For example:

FDQuery1.SQL.Text := 'insert into MyTab values (:p1, :p2, :p3)';
// here FDQuery1.Params collection is filled by 3 parameters
FDQuery1.Params.ArraySize := 100;
for i := 0 to 100-1 do begin
  FDQuery1.Params[0].AsIntegers[i] := i;
  FDQuery1.Params[1].AsStrings[i] := 'qwe';
  FDQuery1.Params[2].Clear(i);
end;

TFDCustomCommand, TFDQuery and TFDStoredProc have the Execute (ATimes: Integer = 0; AOffset: Integer = 0) method. Here, ATimes defines the length of the array. AOffset is index of first item in the array. So, the command will be executed (ATimes - AOffset) times, starting from AOffset row. ATimes must be equal or less Params.ArraySize. For example:

FDQuery1.Execute(100, 0);

After Array DML execution, the property RowsAffected has the number of successful executions, not the total number of affected rows by all executions. For example:

ShowMessage(IntToStr(FDQuery1.RowsAffected));

Error handling

TFDAdaptedDataSet, TFDQuery and TFDStoredProc have ability to trap errors using OnExecuteError event handlers. If the error handler is not assigned and an error happens, then Execute will raise an exception and RowsAffected will be updated.

If TFDAdaptedDataSet.OnExecuteError event handler is assigned, it will get original exception object, current times and offset, and may return AAction value, talking what to do next. The AError.Errors[...] contains one or more errors. AError.Errors[i].RowIndex is a failed row index. Note, OnExecuteError will be not called for the syntax errors or when ATimes = 1.

For example:

procedure TForm1.FDQuery1ExecuteError(ASender: TObject; ATimes,
  AOffset: Integer; AError: EFDDBEngineException; var AAction: TFDErrorAction);
begin
  if AError.Errors[0].Kind = ekUKViolated then
    AAction := eaSkip
  else
    AAction := eaFail;
end;

The exact behavior depends on a DBMS and its corresponding Array DML mode:


Array DML Mode Description
aeOnErrorUndoAll Execution stops on the first error. All successfully applied array items will be undone. Then FireDAC switches to one-by-one execute mode and re-executes the full array. This is similar to aeUpToFirstError. See aeUpToFirstError below.
aeUpToFirstError Execution stops on the first error. All successfully applied array items will be saved. DBMS returns the index of the first failed array item. RowsAffected = number of successfully applied array items. Collection of errors in AError.Errors[...] contains one or more errors referring to a single failed row. AError.Errors[i].RowIndex is the failed row index.
aeCollectAllErrors All array items are executed. All successfully applied array items will be saved. DBMS returns one-by-one the index of each failed array item. RowsAffected = number of successfully applied array items. Collection of errors in AError.Errors[...] contains one error for each failed row. AError.Errors[i].RowIndex is a failed row index.

Note: Setting ResourceOptions.ArrayDMLSize to 1 implicitly sets array execution mode to aeUpToFirstError. To get currently connected DBMS Array DML mode, use:

if FDConnection1.ConnectionMetaDataIntf.ArrayExecMode = aeOnErrorUndoAll then
  ....


Troubleshooting

It is important to properly setup parameters, including setting property Size for the string parameters. For example, FireDAC in case of Oracle will allocate 4000 bytes for each ftString / ftWideString parameter, when Size is not explicitly specified. So, for 10,000 of values will be allocated 40 Mb buffer. If there are many parameters, then application can eat all the system memory.

Most DBMS have implicit limit for the Array DML size. It depends on the DBMS client library buffer size or the maximum allowed network packet. When a limit is reached, use ResourceOptions.ArrayDMLSize option to transparently split large Array DML into few lesser slices.


Example 1

Array DML with IFDPhysCommand:

var
  oCmd: IFDPhysCommand;
……
  with oCmd do begin
    CommandText := 'insert into Customers (ID, Name) values (:ID, :Name)';
    // Set up parameter types
    Params[0].DataType := ftInteger;
    Params[1].DataType := ftString;
    Params[1].Size := 40;
    // Set up parameters' array size
    Params.ArraySize := 10000;
    // Set parameter values
    for i := 0 to 10000 - 1 do begin
      Params[0].AsIntegers[i] := i;
      Params[1].AsStrings[i] := 'Somebody ' + IntToStr(i);
    end;
    // Execute batch
    Execute(10000, 0);
  end;


Example 2

Array DML with TFDQuery and error handling:

procedure TForm1.FDQuery1ExecuteError(ASender: TObject; ATimes,
  AOffset: Integer; AException: EFDDBEngineException; var AAction: TFDErrorAction);
begin
  case AException.Errors[0].Kind of
  ekPKViolated:
    begin
      // fix ID to be unique
      FDQuery1.Params[0].AsIntegers[AException.Errors[0].RowIndex] := AException.Errors[0].RowIndex;
      AAction := eaRetry;
    end;
  ekFKViolated:
    // if Region with RegionID is not found, then just skip row
    AAction := eaSkip;
  else
    AAction := eaFail;
  end;
end;

procedure TForm1.Button1Click(ASender: TObject);
begin
  with FDQuery1 do begin
    SQL.Text := 'insert into Customers (ID, RegionID, Name, Note) values (:ID, :RegionID, :Name, :Note)';
    // Set up parameter types
    Params[0].DataType := ftInteger;
    Params[1].DataType := ftInteger;
    Params[2].DataType := ftString;
    Params[2].Size := 40;
    Params[3].DataSize := ftMemo;
    // Set up parameters' array size
    Params.ArraySize := 10000;
    // Set parameter values
    for i := 0 to 10000 - 1 do begin
      if i mod 100 = 0 then
        // force PK violation
        Params[0].AsIntegers[i] := i - 1
      else
        Params[0].AsIntegers[i] := i;
      Params[1].AsIntegers[i] := GetRegionIdForCustomer(i);
      Params[2].AsStrings[i] := 'Somebody ' + IntToStr(i);
      Params[3].Clear(i);
    end;
    // Execute batch
    Execute(10000, 0);
  end;
end;

See Also

Samples