Array DML (FireDAC)

Nach oben zu Arbeiten mit Anweisungen (FireDAC)

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	Emulation, wenn "Params.BindMode = pbByName" Nativ (INSERT mit mehreren VALUES), wenn "Params.BindMode = pbByNumber"	aeUpToFirstError aeOnErrorUndoAll
Sybase SQL Anywhere	Nativ	aeUpToFirstError
Teradata Database	Nativ	aeOnErrorUndoAll

Hinweis:

• 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.

Hinweis: Durch Setzen von ResourceOptions.ArrayDMLSize auf 1 wird der Array-Ausführungsmodus implizit auf aeUpToFirstError gesetzt. Um den Array DML-Modus des aktuell verbundenen DBMS zu ermitteln, verwenden Sie:

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

• Leistung der Array DML
• Anweisungsstapel

Beispiele

• FireDAC TFDQuery Batch (Beispiel)
• FireDAC TFDQuery Batch Error Handling (Beispiel)
• FireDAC TFDQuery Array DML (Beispiel)