配列 DML（FireDAC）
コマンドの操作（FireDAC） への移動
概要
配列 DML 実行手法では、パラメータの配列を持つ単一の DBMS コマンドを発行します。各コマンド パラメータには値の配列があり、その配列の長さはどのパラメータでも同じです。FireDAC では、配列内の行ごとにコマンドを 1 回実行するように DBMS に要求します。この手法により、DBMS とクライアントの間の通信量が減り、DBMS がコマンド実行を効率化できます。その結果、実行速度が向上します。
次の図でこの処理を示します。
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 以降 |
|
|
|
| Sybase SQL Anywhere | ネイティブ | aeUpToFirstError | |
| Teradata Database | ネイティブ | aeOnErrorUndoAll |
- "配列 DML モード" については、「エラーの処理（FireDAC）」を参照してください。
- "配列 DML の限界の兆候" については、「デバッグとサポート（FireDAC）」を参照してください。
コマンドの実行
アプリケーション コードでは、配列 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 は、エラーが発生した行のインデックスです。 |
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;