配列 DML（FireDAC）

提供: RAD Studio
移動先: 案内、 検索

コマンドの操作（FireDAC） への移動

概要

配列 DML 実行手法では、パラメータの配列を持つ単一の DBMS コマンドを発行します。各コマンド パラメータには値の配列があり、その配列の長さはどのパラメータでも同じです。FireDAC では、配列内の行ごとにコマンドを 1 回実行するように DBMS に要求します。この手法により、DBMS とクライアントの間の通信量が減り、DBMS がコマンド実行を効率化できます。その結果、実行速度が向上します。

次の図でこの処理を示します。

FDPhysCmdBatch.png

FireDAC の用語では、"バッチ コマンド実行" と "配列 DML 実行" はよく同じ意味で使用されます。配列 DML は、ストアド プロシージャ呼び出しも含めて、ほとんどすべてのパラメータ化コマンドに使用できます。FireDAC では、ネイティブ DBMS API 機能を使って配列 DML を実装しています。また、DBMS API でサポートされていない場合は、配列 DML 実行をエミュレートします。

DBMS と配列 DML 機能の対応関係を以下の一覧表に示します。

DBMS 配列 DML の実装 配列 DML モード 配列 DML の限界の兆候
Advantage Database エミュレーション aeUpToFirstError
DataSnap サーバー エミュレーション aeUpToFirstError
IBM DB2 ネイティブ aeCollectAllErrors
Informix ネイティブ aeCollectAllErrors
InterBase XE3 以前 エミュレーション aeUpToFirstError
InterBase XE3 以降 ネイティブ（コマンド バッチ API） aeUpToFirstError
Firebird 2.1 以前 エミュレーション aeUpToFirstError
Firebird 2.1 以降 ネイティブ（EXECUTE BLOCK） aeOnErrorUndoAll "Too many contexts"（コンテキストが多すぎます）エラー
Microsoft SQL Server ネイティブ aeCollectAllErrors "アクセス違反が発生しました。" エラー
Microsoft Access データベース エミュレーション aeUpToFirstError
MySQL サーバー ネイティブ（VALUES 句が複数ある INSERT） aeOnErrorUndoAll
Oracle サーバー ネイティブ（OCI の配列 DML） aeUpToFirstError アプリケーションのハングアップ。明示的な限界 - 65K 個の配列項目
PostgreSQL 8.1 以前 エミュレーション aeUpToFirstError
PostgreSQL 8.1 以降 ネイティブ（VALUES 句が複数ある INSERT /MERGE） aeOnErrorUndoAll
SQLite データベース 3.7.11 以前 エミュレーション aeUpToFirstError
SQLite データベース 3.7.11 以降
  • Params.BindMode = pbByName の場合は、エミュレーション
  • Params.BindMode = pbByNumber の場合は、ネイティブ（VALUES 句が複数ある INSERT）
  • aeUpToFirstError
  • aeOnErrorUndoAll
Sybase SQL Anywhere ネイティブ aeUpToFirstError
Teradata Database ネイティブ aeOnErrorUndoAll
メモ:

コマンドの実行

アプリケーション コードでは、配列 DML の実行前に、パラメータ値配列をセットアップする必要があります。まず、Params.ArraySize に値を割り当てることで配列の長さをセットアップします。このプロパティ値を割り当てると、指定した配列長がすべてのパラメータの ArraySize プロパティに暗黙に割り当てられます。そのため、Params.ArraySize に値を割り当てる前に、Params コレクション プロパティには値が設定されている必要があります。次に、パラメータ配列に値を割り当てます。TADParam クラスには、AsXXX プロパティと似た一連の AsXXXs[AIndex: Integer] プロパティ、その他のプロパティ、第 1 パラメータとして配列インデックスを受け取るメソッドがあります。以下に例を示します。 

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、TFDStoredProc には Execute(ATimes: Integer = 0; AOffset: Integer = 0) メソッドがあります。このうち、ATimes は配列の長さを指定します。AOffset は配列内の先頭項目のインデックスです。そのため、コマンドは、AOffset 行目から始めて、(ATimes - AOffset) 回実行されます。ATimes は Params.ArraySize 以下でなければなりません。以下に例を示します。 

FDQuery1.Execute(100, 0);

配列 DML の実行後、プロパティ RowsAffected には、すべての実行で影響を受けた行の総数ではなく、正常実行の回数が格納されています。以下に例を示します。 

ShowMessage(IntToStr(FDQuery1.RowsAffected));

エラー処理

TFDAdaptedDataSet、TFDQuery、TFDStoredProc では、OnExecuteError イベント ハンドラを使ってエラーをトラップできます。エラー ハンドラが割り当てられていない状態でエラーが発生した場合は、Execute で例外が発生し、RowsAffected が更新されます。

TFDAdaptedDataSet.OnExecuteError イベント ハンドラが割り当てられている場合は、このハンドラが元の例外オブジェクト、現在の回数、オフセットを受け取って、AAction 値を返し、次に実行するアクションを指示します。AError.Errors[...] には、エラーが 1 つ以上含まれています。AError.Errors[i].RowIndex は、エラーが発生した行のインデックスです。なお、OnExecuteError は、構文エラーや ATimes = 1 の場合には呼び出されません。

以下に例を示します。 

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;

詳しい動作は、以下のように、DBMS とそれに対応する配列 DML モードによって異なります。


配列 DML モード 説明
aeOnErrorUndoAll 実行は最初のエラーで停止します。正常に適用された配列項目はすべて取り消されます。その後、FireDAC は段階的実行モードに切り替わり、配列全体を再実行します。これは aeUpToFirstError と似ています。以下の aeUpToFirstError を参照してください。
aeUpToFirstError 実行は最初のエラーで停止します。正常に適用された配列項目はすべて保存されます。DBMS は、最初にエラーが発生した配列項目のインデックスを返します。RowsAffected は、正常に適用された配列項目の数です。エラー コレクション AError.Errors[...] にはエラーが 1 つ以上格納されており、それぞれは、実行に失敗した単一の行を参照しています。AError.Errors[i].RowIndex は、エラーが発生した行のインデックスです。
aeCollectAllErrors すべての配列項目が実行されます。正常に適用された配列項目はすべて保存されます。DBMS は、エラーが発生した各配列項目のインデックスを 1 つずつ返します。RowsAffected は、正常に適用された配列項目の数です。エラー コレクション AError.Errors[...] には、実行に失敗した行ごとにエラーが 1 つ格納されています。AError.Errors[i].RowIndex は、エラーが発生した行のインデックスです。
メモ: ResourceOptions.ArrayDMLSize を 1 に設定すると、配列実行モードが暗黙に aeUpToFirstError に設定されます。 現在接続している DBMS の配列 DML モードを取得するには、次のようなコードを使用します。 
if FDConnection1.ConnectionMetaDataIntf.ArrayExecMode = aeOnErrorUndoAll then
  ....


トラブルシューティング

文字列パラメータのプロパティ Size の設定など、パラメータを適切にセットアップすることが大切です。たとえば、Oracle の使用時に Size が明示的に指定されていない場合、FireDAC では ftString/ftWideString パラメータごとに 4,000 バイトを割り当てます。そのため、値が 10,000 個ある場合は、40 MB のバッファが割り当てられます。パラメータが多い場合、アプリケーションでシステム メモリが全部消費されるおそれがあります。

ほとんどの DBMS には、配列 DML のサイズについて暗黙の上限があります。 それは、DBMS クライアント ライブラリのバッファ サイズやネットワーク パケット サイズの最大許容値によって異なります。 上限に達したら、ResourceOptions.ArrayDMLSize オプションを使用して、大きい配列 DML をより小さいいくつかのスライスに透過的に分割します。


例 1

IFDPhysCommand を使用した場合の配列 DML： 

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;


例 2

TFDQuery とエラー処理を使用した場合の配列 DML： 

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;

関連項目

サンプル

「https://docwiki.embarcadero.com/RADStudio/Athens/j/index.php?title=配列_DML（FireDAC）&oldid=250764」から取得