Array DML (FireDAC)
Nach oben zu Arbeiten mit Anweisungen (FireDAC)
Inhaltsverzeichnis
Allgemeine Informationen
Die Array DML-Ausführungstechnik übergibt eine einzelne DBMS-Anweisung mit einem Array von Parametern. Jeder Anweisungsparameter hat ein Array mit Werten, und alle Parameter haben Arrays in derselben Länge. FireDAC fordert dann das DBMS auf, eine Anweisung einmal für jede Array-Zeile auszuführen. Diese Technik reduziert die Kommunikationsmenge zwischen dem DBMS und dem Client, ermöglicht dem DBMS die Anweisungsausführung in einen Stream zu stellen und beschleunigt die Ausführung.
Die folgende Abbildung zeigt diesen Prozess:
In der FireDAC-Terminologie werden "Ausführung von Stapelanweisungen" und "Ausführung der Array DML" häufig als Synonyme verwendet. Die Array DML kann für fast alle parametrisierten Anweisungen, einschließlich Aufrufen von gespeicherten Prozeduren, verwendet werden. FireDAC implementiert die Array DML mit nativen DBMS-API-Funktionen oder emuliert die Array DML-Ausführung, wenn die DBMS-API sie nicht unterstützt.
Die folgende Tabelle enthält die DBMSs und die Array DML-Features:
DBMS | Array DML-Implementierung | Array DML-Modus | Symptome der Array DML-Grenzen |
---|---|---|---|
Advantage Database | Emulation | aeUpToFirstError | |
DataSnap-Server | Emulation | aeUpToFirstError | |
IBM DB2 | Nativ | aeCollectAllErrors | |
Informix | Nativ | aeCollectAllErrors | |
InterBase v < XE3 | Emulation | aeUpToFirstError | |
InterBase v >= XE3 | Nativ (Anweisungsstapel-API) | aeUpToFirstError | |
Firebird v < 2.1 | Emulation | aeUpToFirstError | |
Firebird v >= 2.1 | Nativ (EXECUTE BLOCK) | aeOnErrorUndoAll | Fehler "Too many contexts" (Zu viele Kontexte) |
Microsoft SQL Server | Nativ | aeCollectAllErrors | Möglicher Fehler "Access violation" (Zugriffsverletzung) |
Microsoft Access-Datenbank | Emulation | aeUpToFirstError | |
MySQL Server | Nativ (INSERT mit mehreren VALUES) | aeOnErrorUndoAll | |
Oracle Server | Nativ (OCI Array DML) | aeUpToFirstError | Anwendung hängt. Explizite Grenze – 65 K an Array-Elementen. |
PostgreSQL v < 8.1 | Emulation | aeUpToFirstError | |
PostgreSQL v >= 8.1 | Nativ (INSERT/MERGE mit mehreren VALUES) | aeOnErrorUndoAll | |
SQLite-Datenbank v < 3.7.11 | Emulation | aeUpToFirstError | |
SQLite-Datenbank v >= 3.7.11 |
|
|
|
Sybase SQL Anywhere | Nativ | aeUpToFirstError | |
Teradata Database | Nativ | aeOnErrorUndoAll |
- Eine Beschreibung des "Array DML-Modus" finden Sie im Kapitel "Fehlerbehandlung", Fehlerbehandlung (FireDAC).
- Eine Beschreibung der "Symptome der Array DML-Grenzen" finden Sie im Kapitel "Fehlerbehebung", Debuggen und Support (FireDAC).
Anweisungsausführung
Vor der Array DML-Ausführung müssen im Anwendungscode die Arrays für die Parameterwerte eingerichtet werden. Richten Sie zuerst die Array-Länge durch Zuweisen eines Wertes zu Params.ArraySize ein. Beim Zuweisen dieses Eigenschaftswertes wird implizit die angegebene Array-Länge den ArraySize-Eigenschaften aller Parameter zugewiesen. Daher darf die Params-Sammlung vor der Zuweisung von Params.ArraySize nicht leer sein. Weisen Sie zweitens den Parameter-Arrays Werte zu. Die Klasse TADParam verfügt über eine Menge von "AsXXXs[AIndex: Integer]"-Eigenschaften, die mit den AsXXX-Eigenschaften vergleichbar sind, und über andere Eigenschaften und Methoden, die einen Array-Index als ersten Parameter akzeptieren. Zum Beispiel:
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 und TFDStoredProc verfügen über die Methode Execute (ATimes: Integer = 0; AOffset: Integer = 0)". Hier wird die Länge des Arrays von ATimes festgelegt. AOffset ist der Index des ersten Elements in dem Array. Die Anweisung wird also ab der Zeile AOffset (ATimes - AOffset) Mal ausgeführt. ATimes muss gleich oder kleiner als Params.ArraySize sein. Zum Beispiel:
FDQuery1.Execute(100, 0);
Nach der Array DML-Ausführung enthält die Eigenschaft RowsAffected die Anzahl der erfolgreichen Ausführungen, nicht die Gesamtanzahl der betroffenen Zeilen in allen Ausführungen. Zum Beispiel:
ShowMessage(IntToStr(FDQuery1.RowsAffected));
Fehlerbehandlung
TFDAdaptedDataSet, TFDQuery und TFDStoredProc können mit den OnExecuteError-Ereignisbehandlungsroutinen Fehler abfangen. Wenn die Ereignisbehandlungsroutine nicht zugewiesen ist und ein Fehler auftritt, dann löst Execute eine Exception aus, und RowsAffected wird aktualisiert.
Wenn die Ereignisbehandlungsroutine TFDAdaptedDataSet.OnExecuteError zugewiesen ist, erhält sie das Original-Exception-Objekt, die aktuelle Anzahl und den Offset und kann einen AAction-Wert zurückgeben, der die nächste Aktion angibt. AError.Errors[...] enthält einen oder mehrere Fehler. AError.Errors[i].RowIndex ist ein fehlgeschlagener Zeilenindex. OnExecuteError wird nicht für Syntaxfehler oder bei "ATimes = 1" aufgerufen.
Zum Beispiel:
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;
Das genaue Verhalten ist vom DBMS und dem zugehörigen Array DML-Modus abhängig:
Array DML-Modus | Beschreibung |
---|---|
aeOnErrorUndoAll | Die Ausführung wird beim ersten Fehler angehalten. Alle erfolgreich übernommenen Array-Elemente werden rückgängig gemacht. FireDAC wechselt dann in den Einzelausführungsmodus und führt das gesamte Array erneut aus. Dies entspricht aeUpToFirstError. Siehe aeUpToFirstError weiter unten. |
aeUpToFirstError | Die Ausführung wird beim ersten Fehler angehalten. Alle erfolgreich übernommenen Array-Elemente werden gespeichert. Das DBMS gibt den Index des ersten fehlgeschlagenen Array-Elements zurück. RowsAffected = Anzahl der erfolgreich übernommenen Array-Elemente. Die Fehlersammlung in AError.Errors[...] enthält einen oder mehrere Fehler, die sich auf eine einzelne fehlgeschlagene Zeile beziehen. AError.Errors[i].RowIndex ist der fehlgeschlagene Zeilenindex. |
aeCollectAllErrors | Alle Array-Elemente werden ausgeführt. Alle erfolgreich übernommenen Array-Elemente werden gespeichert. Das DBMS gibt den Index jedes fehlgeschlagenen Array-Elements zurück. RowsAffected = Anzahl der erfolgreich übernommenen Array-Elemente. Die Fehlersammlung in AError.Errors[...] enthält für jede fehlgeschlagene Zeile einen Fehler. AError.Errors[i].RowIndex ist ein fehlgeschlagener Zeilenindex. |
if FDConnection1.ConnectionMetaDataIntf.ArrayExecMode = aeOnErrorUndoAll then
....
Fehlerbehebung
Es ist wichtig, dass Parameter korrekt konfiguriert werden, einschließlich dem Festlegen der Eigenschaft Size für die String-Parameter. FireDAC weist beispielsweise für Oracle 4.000 Byte für jeden ftString/ftWideString-Parameter zu, wenn Size nicht explizit angegeben ist. Für 10.000 Werte werden dann 40 MB Puffer zugewiesen. Wenn viele Parameter vorhanden sind, kann die Anwendung den gesamten Systemspeicher belegen.
Die meisten DBMSs haben eine implizite Begrenzung für die Array DML-Größe. Dies ist von der Puffergröße der DBMS-Client-Bibliothek oder dem maximal zulässigen Netzwerkpaket abhängig. Wenn eine Begrenzung erreicht ist, können Sie mit der Option ResourceOptions.ArrayDMLSize große Array DML transparent in kleinere Teile aufteilen.
Beispiel 1
Array DML mit 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;
Beispiel 2
Array DML mit TFDQuery und Fehlerbehandlung:
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].DataType := 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;
Siehe auch
Beispiele
- FireDAC TFDQuery Batch (Beispiel)
- FireDAC TFDQuery Batch Error Handling (Beispiel)
- FireDAC TFDQuery Array DML (Beispiel)