SQL スクリプトの実行（FireDAC）

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

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

トピック

トピック 説明
SQL スクリプト制御コマンド TFDScript および FDExecutor ユーティリティでは、さまざまな SQL スクリプト実行制御コマンドをサポートしています。
カスタム コマンドの開発 TFDScript では、SQL スクリプト制御実行コマンド セットを拡張できます。

概要

SQL スクリプトは、別個の SQL コマンド、実行制御コマンド、ログ記録コマンドの集合です。 SQL スクリプトは、バックエンドの SQL オブジェクト作成/削除/アップグレード、初期データ読み込みなどのバックエンド保守タスクに役に立ちます。

多くの DBMS では、複数の SQL コマンドを単一の TFDQuery.ExecSQL 呼び出しでバッチとして実行できますが、制限があります。 以下は SQL スクリプトと SQL コマンド バッチの相違点です。

  • スクリプトでは、可能な限りの SQL コマンドを単一のスクリプトで使用できます。 コマンド バッチでは、DBMS によっては制限がある場合があります。 たとえば、Oracle の無名 PL/SQL ブロックには、DDL コマンドを含めることはできません。
  • スクリプトはいくつかのトランザクションに分割できます。 コマンド バッチは単一のトランザクションで実行しなければなりません。
  • スクリプトでは、SQL 以外のコマンドやカスタム コマンドを使用できます。 コマンド バッチには、DBMS で認識できるコマンドのみ含まれています。
  • スクリプトはサブスクリプトに分割できます。 コマンド バッチでは、ストアド プロシージャを別個のコード ブロックとして呼び出すことができます。
  • スクリプトの実行はクライアントで完全に制御されます。 コマンド バッチの実行は DBMS だけで完全に制御されます。
  • バッチの実行とは異なり、スクリプトの実行はログに記録できます。
  • バッチの実行とは異なり、スクリプトの場合は実行の進行状況がフィードバックされます。

TFDScript には、標準ユーティリティに勝る利点が多数あります。たとえば、FireDAC アプリケーションに完全に統合できることや、コマンド セットをカスタム スクリプト コマンドで拡張できることなどです。 TFDScript コンポーネントでは、以下を始めとするいくつかの業界標準 SQL スクリプト構文を認識します。

  • Oracle SQL*Plus
  • Microsoft ISQL/OSQL
  • MySQL mysql.exe/mysqldump.exe
  • Firebird/InterBase ISQL

たとえば、次の Firebird スクリプトではデータベースを作成しており、TFDScript を使ってこのスクリプトを実行できます。 

SET SQL DIALECT 3;
SET NAMES UTF8;
SET CLIENTLIB 'C:\fb25\bin\fbclient.dll';
CREATE DATABASE 'E:\Test2.ib'
  USER 'sysdba' PASSWORD 'masterkey'
  PAGE_SIZE 16384
  DEFAULT CHARACTER SET NONE;

SET TERM ^ ;

CREATE PROCEDURE MY_PROC RETURNS (aParam INTEGER) AS
BEGIN
  aParam = 10;
END^

スクリプトの実行

TFDScript では、SQLScriptFileName が指定された場合、そのプロパティで示されたファイルに記述されたスクリプトを実行できます。 そうでない場合は、SQLScripts コレクション プロパティ内のインデックス 0 のスクリプトから実行を開始できます。

メモ： あるいは、SQL スクリプトを記述したファイルを FDExecutor ユーティリティを使って実行することもできます。

たとえば、スクリプト ファイルを実行するには、次のようなコードを使用します。 

with FDScript1 do begin
  SQLScriptFileName := 'c:\create.sql';
  ValidateAll;
  ExecuteAll;
end;

メモリに格納されたスクリプトを実行するには、次のようなコードを使用します。 

with FDScript1 do begin
  SQLScripts.Clear;
  SQLScripts.Add;
  with SQLScripts[0].SQL do begin
    Add('INSERT INTO Brands VALUES (1, ''Audi'')');
    Add('INSERT INTO Brands VALUES (2, ''BMW'')');
  end;
  ValidateAll;
  ExecuteAll;
end;

さらに、SQL スクリプトを簡単に実行できる方法が他にもいくつかあります。 スクリプトの実行に関するその他多数の側面を、ScriptOptions を使って Delphi コードから制御することができます。あるいは、対応するスクリプト制御コマンドを使って制御することもできます。

また、スクリプトでは、@ <スクリプト>、@@ <スクリプト>、START <スクリプト>、INPUT <スクリプト> のいずれかのコマンドを使って、サブルーチンなどの他のスクリプトを呼び出すこともできます。 その場合、<スクリプト> は SQLScripts コレクション プロパティ内の項目の名前か外部ファイル名のどちらかです。 たとえば、以下の "root" スクリプトでは "first" および "second" サブスクリプトを実行しています。 

with FDScript1.SQLScripts do begin
  Clear;
  with Add do begin
    Name := 'root';
    SQL.Add('@first');  // explicitly call 'first' script
    SQL.Add('@second'); // explicitly call 'second' script
  end;
  with Add do begin
    Name := 'first';
    SQL.Add('create table t1 (...);');
    SQL.Add('create table t2 (...);');
  end;
  with Add do begin
    Name := 'second';
    SQL.Add('create procedure p1 (...);');
    SQL.Add('create procedure p2 (...);');
  end;
end;
FDScript1.ValidateAll;
FDScript1.ExecuteAll;

SQL スクリプトの実行は、ExecuteAll メソッドを使って、サブスクリプトを含めて全部一括で行うこともできますし、ExecuteStep メソッドを使って段階的に行うこともできます。 この最後の方法は GUI アプリケーションに有効で、アドホック クエリを実行できます。 次のコマンドは、スクリプトの Position 位置から抽出されて実行されます。 スクリプトの実行を中止するには、AbortJob メソッドを呼び出します。

コマンドの区切り

各 SQL コマンドはコマンド区切り記号で終わる必要があります。 この区切り記号のデフォルト値は ";" で、Microsoft SQL Server の場合は "GO" です。 制御コマンドは、コマンド区切り記号で終わる必要はありません。 区切り記号の変更は、CommandSeparator オプションを使って Delphiコードから行うこともできますし、SET CMDSEP <区切り記号> コマンドまたは DELIMiter <区切り記号> コマンドを使って SQL スクリプトから行うこともできます。 たとえば、SQL Server の場合は、次のようなコードを使用します。 

INSERT INTO Brands VALUES (1, 'Audi')
GO
INSERT INTO Brands VALUES (2, 'BMW')
GO

Oracle の場合は、次のようなコードになります。 

INSERT INTO Brands VALUES (1, 'Audi');
INSERT INTO Brands VALUES (2, 'BMW');

カスタム区切り記号を使用する場合は、次のようにします。 

SET CMDSEP #
INSERT INTO Brands VALUES (1, 'Audi')#
INSERT INTO Brands VALUES (2, 'BMW')#

スクリプトに DB プログラミング言語のコマンドやブロックが含まれている場合は、以下のように、さらに考慮しなければならないことがあります。

DBMS SQL コマンド 説明
Firebird
  • CREATE FUNCTION
  • EXECUTE BLOCK

"/" で終わる必要があります。あるいは、";" 以外の区切り記号を設定する必要があります。
Oracle
  • CREATE PROCEDURE / FUNCTION / PACKAGE / など
  • BEGIN <コード ブロック> END
 "/" で終わる必要があります。あるいは、";" 以外の区切り記号を設定する必要があります。
PostgreSQL
  • CREATE FUNCTION
  • DO <コード ブロック>
 対処は不要です。

たとえば、Firebird の場合は、次のようなコードを使用します。 

SET CMDSEP #;
EXECUTE BLOCK ... #
SET CMDSEP ;#
INSERT INTO Brands VALUES (3, 'Mercedes');

上記の対処を行わない場合、アプリケーションで次のようなエラーが発生するおそれがあります。 

[FireDAC][IB] Unexpected end of command line 3

パラメータを使用する場合

SQL スクリプトでは以下のものを参照できます。

  • TFDScript.Params コレクションに含まれているパラメータ。 スクリプトでパラメータを定義するには、<code>VARiable <名前><型>=<値></code> コマンドを使用します。 コードでパラメータを定義するには、スクリプトの実行前に、TFDScript.Params にパラメータを追加します。
  • TFDScript.Macros コレクションに含まれているマクロ。 スクリプトでマクロを定義するには、<code>DEFine <名前><型>=<値></code> コマンドを使用します。 コードでマクロを定義するには、スクリプトの実行前に、TFDScript.Macros にマクロを追加します。 マクロ処理を有効にするには、MacroExpand を True（デフォルト）に設定、または、SET DEFINE ON や SET SCAN ON を実行します。
  • &<引数番号> の構文で表された引数。 引数は、TFDScript.ExecuteFile メソッドまたは ExecuteScript メソッドの引数として、あるいは @ または @@ コマンドの一部として、TFDScript.Arguments プロパティで指定することができます。

たとえば、パラメータを定義して使用するには、次のようなコードを使用します。 

with FDScript1.SQLScripts[0].SQL do begin
  Add('VARIABLE name CHAR IN = ''aaa''');
  Add('VARIABLE id NUMBER INOUT');
  Add('INSERT INTO master (name) VALUES (:name) RETURNING id {INTO :id};');
  Add('INSERT INTO detail (fk_id, name) VALUES (:id, ''bbb'');');
end;

マクロを定義して参照するには、次のようなコードを使用します。 

DEF tab=Brands
INSERT INTO !tab VALUES (1, 'Audi');
INSERT INTO !tab VALUES (2, 'BMW');

引数を定義して使用するには、次のようなコードを使用します。 

FDScript1.Arguments.Add('Brands');
...
with FDScript1.SQLScripts[0].SQL do begin
  Add('INSERT INTO &1 VALUES (1, ''Audi'')');
  Add('INSERT INTO &1 VALUES (2, ''BMW'')');
end;

フィードバックの取得

実行ログを作成するには、スプール処理の有効化を、Delphi コードからなら、オプション SpoolOutput および SpoolFileName を使用して、SQL スクリプトからなら、<code>SPOol <名前></code> または <code>OUTput <名前></code> コマンドを使用して、行うことができます。 スプール出力の内容は、EchoCommands、FeedbackCommands、AutoPrintParams、FeedbackScript、IgnoreError、Timing、ColumnHeadings、PageSize、ServerOutput の各オプションで制御されます。

TFDGUIxScriptDialog コンポーネントすると、ダイアログを介したスクリプト実行エンジンとエンド ユーザーのコミュニケーションを可能にすることができます。 エンド ユーザーの TFDScript とやり取りするには、OnConsoleGet、OnConsolePut、OnGetText、OnPause などのイベントを使用します。 このダイアログは、これらのイベントの標準的な実装を提供します。 実行の進行状況を表示するには、実行されるすべてのスクリプトの合計長が TFDScript に知られている必要があります。 それには、スクリプトの実行を開始する前に ValidateAll メソッドを呼び出します。


非互換性の解消

TFDScript と元のスクリプト ユーティリティとの非互換性として知られているものは、以下のとおりです。

  • Firebird ISQL は非自動コミット モードで動作します。 TFDScript/TFDConnection については、デフォルトで自動コミット モードがオンになっています。 互換性を高めるには、スクリプトの実行前に FDConnection.TxOptions.AutoCommit を False に設定します。 あるいは、SET AUTOCOMMIT OFF スクリプト コマンドを実行します。
  • Microsoft ISQL では PRINT コマンドの結果を出力します。 TFDScript/TFDConnection は、デフォルトではそうなっていません。 PRINT の出力を有効にするには、ResourceOptions.ServerOutput を True に設定するか、SET SERVEROUTPUT ON スクリプト コマンドを実行します。
「https://docwiki.embarcadero.com/RADStudio/Rio/j/index.php?title=SQL_スクリプトの実行（FireDAC）&oldid=206149」から取得