Array DML (FireDAC)
Go Up to Working with Commands (FireDAC)
Contents
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:
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 |
|
|
|
| Sybase SQL Anywhere | Native | aeUpToFirstError | |
| Teradata Database | Native | aeOnErrorUndoAll |
Note:
- "Array DML Mode" description: see "Error Handling" chapter, Handling Errors (FireDAC)
- "Array DML Limit Symptoms" description: see "Troubleshooting" chapter, Debugging and Support (FireDAC)
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
- FireDAC TFDQuery Batch sample
- FireDAC TFDQuery Batch Error Handling sample
- FireDAC TFDQuery Array DML sample
- FireDAC IFDPhysCommand Async sample
- FireDAC IFDPhysCommand Batch sample