Difference between revisions of "SqlDBHowto/ja"

	データベースのポータル
	参照: Databases 概要; ライブラリ; フィールドの型; コントロール; FAQ; SqlDBHowto/ja; TSQLQuery の利用; メモリ上で動作するデータベースの作り方; チュートリアル/練習となる記事: 概要; 0 - データベースのセットアップ; 1 - 開始; 2 - 編集; 3 - クエリ; 4 - データモジュール; SQLdb Programming Reference/ja; 各種データベース; Advantage - MySQL - MSSQL - Postgres - Interbase - Firebird - Oracle - ODBC - Paradox - SQLite - dBASE - MS Access - Zeos

Latest revision as of 17:29, 1 April 2024

│ Deutsch (de) │ English (en) │ español (es) │ français (fr) │ 日本語 (ja) │ Nederlands (nl) │ polski (pl) │ 中文（中国大陆）‎ (zh_CN) │

日本語版メニュー
メインページ - Lazarus Documentation日本語版 - 翻訳ノート - 日本語障害情報

このテキストは「how-to」として作られている。数多くの質問に1つ、1つ答え、どのように様々なクラスを用いるのか説明したい。これらすべての質問は次から次へと沸き起こり、ある種のチュートリアルとなった。

このテキストがFree Pascal同様にLazarusでも用いられるように書くつもりだが、例はFree Pascal(即ち、コンソールアプリケーション)に向けてある。

どこで公式のドキュメンテーションが見つかるか

公式ドキュメンテーションはここを参照されたいSQLDBドキュメンテーション。

どのようにしてデータベースサーバに接続するか

SqlDBは直接データベースサーバには接続せず、用いられているデータベースサーバに対応するクライアントを用いる。SqlDBはクライアントライブラリにコマンドを送り、クライアントライブラリがデータベースサーバに接続しコマンドを送る。これはデータベースと接続を持つためにクライアントライブラリがインストールされていなければならないことを意味する。Windowsではクライアントライブラリは通常、.dll、Linux では.so、OS/Xでは.dylibである。

クライアントライブラリが適切にインストールされているときには、TSQLConnectionコンポーネントを用いてデータベースサーバに接続できる。様々なTSQLConnectionコンポーネントが異なるデータベースサーバに対して利用可能である(SQLdb_Packageを参照):

Firebird/Interbase: TIBConnection
MS SQL Server: TMSSQLConnection (FPC 2.6.1から利用可能)
MySQL v4.0: TMySQL40Connection
MySQL v4.1: TMySQL41Connection
MySQL v5.0: TMySQL50Connection
MySQL v5.1: TMySQL51Connection (FPC version 2.5.1から利用可能)
MySQL v5.5: TMySQL55Connection (Lazarus 1.0.8/FPC version 2.6.2から利用可能)
MySQL v5.6: TMySQL56Connection (Lazarus 1.2.4/FPC version 2.6.4から利用可能)
MySQL v5.7: TMySQL57Connection (訳注:いつから?)
ODBC: TODBCConnection (ODBCConn#TODBCConnectionを参照)
Oracle: TOracleConnection (Oracleを参照)
PostgreSQL: TPQConnection (postgresql#SQLDBを参照)
Sqlite3: TSQLite3Connection/ja (FPC version 2.2.2から利用可能、SQLite#Built-in_SQLDBを参照)
Sybase ASE: TSybaseConnection (FPC 2.6.1から利用可能、LazarusとMSSQL/Sybase)

MySQLに対する注意 - クライアントとコネクションが相互交換できないほどクライアントバージョン間に多くの相違がある。もしクライアントライブラリバージョン 4.1がインストールされているとすると、TMySQL41Connectionを用いなければならない。これはMySQLサーバには関連しておらずMySQL 4.1クライアントライブラリを用いて、おそらくMySQL 5.0サーバに接続できる(どの組み合わせがサポートされているかはMySQLドキュメンテーションを参照されたい)。

さまざまなデータベースで細部は異なるが、一般的にデータベースサーバに接続するために4つのプロパティを設定しなければならない:

サーバの名前とIPアドレス
データベースの名前
ユーザー名
パスワード

これらのプロパティが設定されると、サーバに「open」メソッドを作ることができる。もし接続に失敗するとEDatabaseError例外が生じる。データベースサーバに接続が確立しているかどうか確かめるために、「connected」プロパティを用いること。

Program ConnectDB;

{$mode objfpc}{$H+}

uses
  IBConnection;

function CreateConnection: TIBConnection;
begin
  result := TIBConnection.Create(nil);
  result.Hostname := 'localhost';
  result.DatabaseName := '/opt/firebird/examples/employee.fdb';
  result.UserName := 'sysdba';
  result.Password := 'masterkey';
end;
 
var   
  AConnection : TIBConnection;

begin
  AConnection := CreateConnection;
  AConnection.Open;
  if Aconnection.Connected then
    writeln('Successful connect!')
  else
    writeln('This is not possible, because if the connection failed, ' +
            'an exception should be raised, so this code would not ' +
            'be executed');
  AConnection.Close;
  AConnection.Free;
end.

もし、エラーが発生したら、注意してエラーメッセージを読むこと。データベースサーバが稼働していない、ユーザー名もしくはパスワードが誤っている、データベース名またはIPアドレスが誤って入力されたかもしれない。もしエラーメッセージがクライアントライブラリが見つからないと言っているなら、クライアントライブラリが正しくインストールされているか確かめること。しばしばエラーメッセージは探しているファイル名を文字通り言う。

どのようにして直接クエリを実行/テーブルを作るのか

SqlDB - 文字通り名前がすべてを体現している - はSQLを用いて唯一、データベースサーバとやり取りをする。SQL は「Structured Query Language」を意味する。 SQLはリレーショナルデータベースとやり取りすることを可能にするために開発された言語である。実際にはすべてのデータベースシステムはそれぞれ方言を持っているが、SQLステートメントの多くはすべてのデータベースシステムで共通している。

FPCでは、以下の違いがある:

情報(データセット)を返すSQLステートメント。このため、TSQLQueryコンポーネントを用いなければならない; #How to read data from a table?を参照のこと。
情報を返さずに、変わって何かを行うステートメント、例えば、データを更新する。このため、またTSQLConnectionの「ExecuteDirect」メソッドを使うかもしれない(データセットを引き戻すためにこれを使うこともできるが、結果に興味はない、例えばセレクタブルストアドプロシージャ)。

大半のデータベースシステムはトランザクションでSQLステートメントを実行する。もし、他のトランザクションの中で利用できるトランザクションの中で行われる変更を望む、もしくはトランザクションが閉じた後でそれらの変更する(!)ならば、トランザクションを「commit」しなければならない。

トランザクションをサポートするために、SqlDBはTSQLTransactionコンポーネントを含んでいる。SqlDBによって実行されるSQLステートメントはデータベースシステムが、トランザクションをサポートしていなくとも、常にトランザクションの最中に行われる必要がある。同様に、TSQLTransactionがいまだにサポートしていないトランザクションをサポートしているデータベースシステムも存在する。その時ですらTSQLTransactionコンポーネントを使わなければならない。

SQLステートメントを実行するためにTSQLConnection.ExecuteDirectを使うには、どの「Transaction」が使われなければならないかを明示しなければならない。逆に、TSQLTransactionを使うためには、どのTSQLTransactionコンポーネントが使われるかを明示しなければならない。

以下の例は「NAME」と「ID」フィールドを持つ「TBLNAMES」を生成し2つのレコードを挿入する。今回はSQLiteを用いる。使われるSQLステートメントは説明されない。SQLステートメント、その使用と文法に関しては、データベースシステムのドキュメンテーションを参照されたい。この例ではいかなるエラーの捕捉も試みないことに注意すること、それは悪いことである! 例外を調べていただきたい。

program CreateTable;
{$mode objfpc} {$ifdef mswindows}{$apptype console}{$endif}
uses
  sqldb, sqlite3conn; 
 
var 
  AConnection : TSQLite3Connection;
  ATransaction : TSQLTransaction;
 
begin
  AConnection := TSQLite3Connection.Create(nil);
  AConnection.DatabaseName := 'test_dbase';
 
  ATransaction := TSQLTransaction.Create(AConnection);
  AConnection.Transaction := ATransaction;
  AConnection.Open;
  ATransaction.StartTransaction;
  AConnection.ExecuteDirect('create table TBLNAMES (ID integer, NAME varchar(40));'); 
 
  ATransaction.Commit;
 
  ATransaction.StartTransaction;
  AConnection.ExecuteDirect('insert into TBLNAMES (ID,NAME) values (1,''Name1'');'); 
  AConnection.ExecuteDirect('insert into TBLNAMES (ID,NAME) values (2,''Name2'');'); 
  ATransaction.Commit; 
  AConnection.Close;
  ATransaction.Free;	
  AConnection.Free;
end.

どのようにしてテーブルからデータを取得するのか

テーブルからデータを読み込むときにはTSQLQueryを用いること。TSQLQueryコンポーネントはその仕事をするために、TSQLConnectionコンポーネントとTSQLTransactionコンポーネントに接続されなければない。TSQLConnectionとTSQLTransactionの設定は#How to connect to a database server? と#How to execute direct queries/make a table?で議論する。

TSQLConnectionの時、TSQLTransactionとTSQLQueryが接続される、そしてTSQLQueryは動作するためにさらに設定される。TSQLQueryはTStringsオブジェクトを含む「SQL」プロパティを持つ。「SQL」プロパティは実行されるべきSQLステートメントを含んでいる。もしテーブルtablenameのすべてのデータを読まなければならないときには、「SQL」プロパティを以下のように設定する:

'SELECT * FROM tablename;'

サーバからテーブルを読み込み、TSQLQueryデータセットにデータを読み込むには「open」を使うこと。データは「close」を用いてクエリが閉じられるまでTSQLQueryを通してアクセスできる。

TSQLQueryはTDatasetのサブクラスである。TDatasetはテーブルのすべてのカラムを含む「Field」コレクションを持っている。TDatasetはまた、現在のレコードを追跡する。現在のレコードを変更するために「First」、「Next」、「Prior」、「Last」を用いること。もし最初のレコードが届いたら「Bof」が「True」となり、もし最後のレコードに達したら、「Eof」が「True」になる。現在のレコードの値を読むためには初めに正しい「TField」オブジェクトを見つけ、そして「AsString」、「AsInteger」を用いること。

例: テーブルからデータを取得する

以下は上のCreateTable例で作られたテーブルのすべての値を表示する例である。test_databaseファイルをShowDataワーキングディレクトリにコピーすること。エラーチェックを行っていないことに再び注意すること!

Program ShowData;
 {$mode objfpc} {$ifdef mswindows}{$apptype console}{$endif}
uses
  DB, Sysutils, sqldb, sqlite3conn;

var
  AConnection  : TSQLConnection;
  ATransaction : TSQLTransaction;
  Query        : TSQLQuery;
 
begin
  AConnection := TSQLite3Connection.Create(nil);
  ATransaction := TSQLTransaction.Create(AConnection);
  AConnection.Transaction := ATransaction;
  AConnection.DatabaseName := 'test_dbase';
  Query := TSQLQuery.Create(nil);
  Query.SQL.Text := 'select * from tblNames';
  Query.Database := AConnection;
  Query.Open;
  while not Query.Eof do
  begin
    Writeln('ID: ', Query.FieldByName('ID').AsInteger, 'Name: ' +
                                  Query.FieldByName('Name').AsString);
    Query.Next;
  end;
  Query.Close;
  AConnection.Close;
  Query.Free;
  ATransaction.Free;
  AConnection.Free;
end.

もちろん、上記のコードは未完成で、「try...finally」ブロックを欠く。しかし、上のコードはデータベースコードを示す意図のもので、そのため仕上げの一筆は省いてある。「TSQLTransaction.StartTransaction」が使われてないことに注意してほしい。これは不要である。TSQLQueryが開かれるとき、SQLステートメントが実行され、もしトランザクションが利用できないときにはトランザクションが自動的に始まる。プログラマは明示的にトランザクションを開始させる必要はない。同様のことがTSQLConnectionによって維持されている接続にも適用される。接続は必要に応じて開かれ、「Aconnection.Open」は全く必要ない。もし、TSQLTransactionが破棄されると、自動的「rollback」が実行される。トランザクションに含まれるデータに対する、ありえる変更は失われるだろう。

なぜ TSQLQuery.RecordCount はいつも10を返すのか

データセットのレコードを数えるためには、「.RecordCount」を用いること。しかし、「.RecordCount」はサーバからすでに送り出されたレコードの数を示している。性能上の理由により、SqlDBはデフォルトではTSQLQueryを開いたときすべてのレコードを読まず、最初の10を読む。11番目のレコードを読むときのみに次の10レコードがアクセスされる、など。「.Last」を用いると、すべてのレコードが読み込まれる。サーバにあるレコードの真の数を知りたいときは、初めに「.Last」、そして「.RecordCount」を呼ぶことができる。また別のやり方もある。サーバによって返されるレコードの数は「.PacketRecords」プロパティにセットされる。そのデフォルト値は10、これを-1に設定すると、一度にすべてのレコードが読み込まれる。

現在の安定版のFPCでは、.RecordCountはフィルタを考慮に入れていない。即ち、それはフィルタをかけられない総和となる。

もしレコードの正確な数を必要としているのであれば、ほかのSQLクエリをクエリの中で用いてレコードの数を直接問い合わせるのがしばしばより良い考えであるが、その間に他のトランザクションがレコードの数を変えたかもしれないので、同じトランザクションの中でそれをしなければならないだろう。

Lazarus

Lazarusはフォーム上にTDatasetからのデータを示すために様々なコンポーネントを持っている。正しいTSQLConnection、TSQLTransaction、TSQLQueryコンポーネントをフォームに置き、そしてそれらを適切に接続、設定すること。加えて、TDatasourceが必要となり、「TDatasource.Dataset」プロパティを、用いるTSQLQueryコンポーネントに設定すること。 (注意「TSQLQuery.Datasource」プロパティを、用いるTDatasourceコンポーネントに設定しないこと。「TSQLQuery.Datasource」プロパティはmaster-detailテーブルのみに使われる - MasterDetail参照)。結果としてTDGridをフォームに配置して、前に加えたTDatasourceをグリッドのプロパティに設定するることになる。

これらすべてが機能していることを見るために、TSQLConnectionの「Connected」プロパティをLazarus IDEで「True」に設定すること。IDEはデータベースサーバへ直ちに接続を試みるだろう。もし、これが動いたら、「TSQLQuery.Active」プロパティを「True」に設定できる。もしすべてが正しいなら、IDEの中に、直ちにテーブルからのすべてのデータがみられるだろう。

どのようにしてテーブルのデータを変更するのか

レコード内のデータを変更するための一般的なプロセスは、TSQLQuery を取得して変更するレコードを検索し、そこで変更を加えてデータベースにプッシュし直すことである。TDataSet (TSQLQuery の派生元) は編集モードに設定する必要がある。編集モードに入るには、「.Edit」、「.Insert」、または「.Append」メソッドを呼び出す。現在のレコードを変更するには、「.Edit」メソッドを使用する。現在のレコードを変更するには、「.Edit」メソッドを使用する。現在のレコードの前に新しいレコードを挿入するには、「.Insert」を使用する。テーブルの最後に新しいレコードを挿入するには、「.Append」を使用する。編集モードでは、「フィールド」プロパティを通じてフィールド値を変更できる。「Post」を使用して新しいデータを検証する。データが有効な場合は、編集モードが終了する。たとえば「.Next」を使用して別のレコードに移動し、データセットが編集モードの場合、最初に「.Post」が呼び出される。「.Cancel」を使用すると、最後の「.Post」呼び出し以降に加えたすべての変更が破棄され、編集モードが終了します。

Query.Edit;
Query.FieldByName('NAME').AsString := 'Edited name';
Query.Post;

上記はまだ完全な話ではない。 TSQLQuery は、バッファされた更新を利用する TBufDataset から派生している。バッファ更新とは、「Post」を呼び出した後、データセット内の変更がすぐに表示されるが、データベースサーバーには送信されないことを意味する。実際に起こるのは、変更が変更ログに保持されることである。「.ApplyUpdates」メソッドが呼び出されると、変更ログ内のすべての変更がデータベースに送信される。そうして初めて、データベースサーバーは変更を認識する。変更は、TSQLTransaction のトランザクション内でサーバーに送信される。「ApplyUpdates」の前にトランザクションを正しく設定すること。更新を適用した後、コミットを実行して変更をデータベースサーバーに保存する必要がある。

以下は、テーブル内のデータを変更し、変更をサーバーに送信し、トランザクションをコミットする例である。繰り返すが、エラーチェックがない。これは非常に芳しくない!

Program EditData;
{$mode objfpc} {$ifdef mswindows}{$apptype console}{$endif}
uses
    db, sqldb, sqlite3conn;
var 
  AConnection : TSQLConnection;
  ATransaction : TSQLTransaction;
  Query : TSQLQuery;
 
begin
  AConnection := TSQLite3Connection.Create(nil);
  ATransaction := TSQLTransaction.Create(AConnection);
  AConnection.Transaction := ATransaction;
  AConnection.DatabaseName := 'test_dbase';
  Query := TSQLQuery.Create(nil);
  Query.DataBase := AConnection;
  Query.SQL.Text := 'select * from tblNames where ID = 2';
  Query.Open;
  Query.Edit;
  Query.FieldByName('NAME').AsString := 'Name Number 2';
  Query.Post;
  Query.UpdateMode := upWhereAll;         // dbで定義されている
  Query.ApplyUpdates;
  ATransaction.Commit;
  Query.Free;
  ATransaction.Free;
  AConnection.Free;
end.

実際の作業は、変更するレコードを識別する SQL ステートメント「select * from tblNames where ID = 2」から始まる。「where ID = 2」ビットを省略すると、TSQLQuery は明らかに ID (および他の整数フィールド?) を 1 に設定する。そのため、ID=1 の行のみで動作する。「UpdateMode」については、引き続き読み進められたい。

どのようにして SqlDB はデータベースサーバに変更を送るのか

#どのようにしてテーブルのデータを変更するのかのコード例で以下が、何をしているのか説明なしに記載されている。

Query.UpdateMode := upWhereAll;

その行が何をしているのかを知る最良の方法は、その行を省略することだ。このステートメントを省略し、この Howto に正確に従った場合、次のエラーメッセージが表示される:

No update query specified and failed to generate one. (No fields for inclusion in where statement found)

何が問題なのかを理解するには、変更がどのようにデータベースサーバーに送信されるかを理解する必要がある。SQL サーバー内のデータを取得する唯一の方法は、SQL クエリを実行することだ。SQL には、レコードを操作する 3 つの異なる方法に対応する 3 種類のクエリがある。新しいレコードの作成、レコードの変更または削除には、それぞれ、insert、update、delete ステートメントが実行される。アップデートステートメントは以下のようなものだろう:

update TBLNAMES set NAME='Edited name' where ID=1;

変更をデータベースサーバーに送信するには、Sqldb は更新クエリを組み立てる必要がある。クエリを組み立てるには、以下の3つが必要である:

テーブルの名前: テーブル名は選択クエリの解析から取得されるが、これは常に機能するとは限らない。
UPDATE もしくは INSERT 句: これらには、変更する必要があるフィールドが含まれている。
WHERE 句: これには、どのレコードを変更する必要があるかを決定するフィールドが含まれる。

すべてのフィールド(Fieldの各TField) には ProviderFlags プロパティがある。ProviderFlags に pfInUpdate があるフィールドのみが、クエリの更新または挿入条件で使用される。デフォルトではすべてのフィールドの ProviderFlags プロパティに pfInUpdate が設定されている。WHERE 句でどのフィールドが使用されるかは、クエリの UpdateMode プロパティとフィールドの ProviderFlags プロパティによって異なる。「ProviderFlags」に「pfInkey」が含まれるフィールドは、常に WHERE 句で使用されます。フィールドがテーブルの主キーの一部であり、「TSQLQuery.UsePrimaryKeyAsKey」が「True」を返す場合、フィールドには自動的に「pfInKey」フラグが設定されます。クエリの UpdateMode のデフォルト値は upWhereKeyOnly である。この更新モードでは、ProviderFlags プロパティに pfInkey を持つフィールドのみが WHERE 句で使用される。どのフィールドにも pfInKey フラグが設定されていない場合、WHERE 句に使用できるフィールドはなく、このセクションの冒頭のエラーメッセージが返される。

この問題は以下で解決される:

テーブルにプライマリキーを加え、TSQLQuery.UsePrimaryKeyAsKey を「True」に設定するか、
コードの中でpfInkeyフラグを1つ、またはそれ以上のフィールドに設定する。

UpdateMode プロパティは、さらに 2 つの可能な値を認識します。「upWhereAll」を使用すると、「pfInWhere」フラグが WHERE 句に設定されているすべてのフィールドを追加できる。UpdateModeプロパティは2つの可能な値を知っている。デフォルトでは、すべてのフィールドにこのフラグが設定されている。「upWhereChanged」は「pfInWhere」フラグセットを持つフィールド および 現在のレコードで、変更されたフィールドに用いることができる。

エラーの取り扱い方

実行時エラーは避けられず、ディスクがいっぱいになる、必要なライブラリやヘルパーアプリケーションが利用できない、等の問題が発生する可能性があるため、それを許容する必要がある。 FPC は実行時エラーを非常に適切に検出して処理する。通常、何が問題だったのかについて簡潔かつ合理的な説明が得られるが、多くの理由でエラーを監視し、取り扱うことを望むだろう -

おそらく、問題の最初の兆候が現れた時点でプログラムが終了することは望ましくないだろう。
続行する場合は、問題領域に割り当てられたメモリが回復されることを確認すること。メモリリークは発生しないようにする。
ただし、さらに詳しく説明する場合は、状況に応じた説明をユーザーに提供しよう。

次のコードは上記の例に基づいているが、今回は重要な場所でのエラーをチェックをする。鍵はtry...finally...end と try...except...end ブロックである。SQLite3をアンインストールして、test_dbaseのあったところにダミーファイルを置くなどして、これをテストすることができる。

program DemoDBaseWithErrors;
{$mode objfpc} {$ifdef mswindows}{$apptype console}{$endif}
uses
  DB, Sysutils, sqldb, sqlite3conn;
var
    Connect : TSQLite3Connection;
    Trans : TSQLTransaction;


procedure WriteTable (Command : string);
begin
	Connect.ExecuteDirect(Command);
    	Trans.Commit;
end;

procedure ReadTable ();
var
   Query : TSQLQuery;
   Count : smallint;
begin
    Count := 0;
    try
        Query := TSQLQuery.Create(nil);
        Query.DataBase := Connect;
        Query.SQL.Text:= 'select * from tblNames';
        Query.Open;          // これは接続を開始する
        while not Query.EOF do begin
            writeln('ID: ', Query.FieldByName('ID').AsInteger, '  Name: ' +
                              Query.FieldByName('Name').AsString);
            Query.Next;
            Count := Count + 1;
        end;
    finally
        Query.Close;
        Query.Free;
    end;
    writeln('Found a total of ' + InttoStr(Count) + ' lines.');
end;

procedure FatalError(ClassName, Message, Suggestion : string);
begin
    writeln(ClassName);
    writeln(Message);
    writeln(Suggestion);
    Connect.Close;              // 解放することを心配するのはばかげているかもしれない。
    Trans.free;                 // halt()を呼ぶつもりだが
    Connect.Free;               // これはデモだから、いいか ?
    halt();
end;

begin
    Connect := TSQLite3Connection.Create(nil);
    Trans := TSQLTransaction.Create(Connect);
	Connect.Transaction := Trans;
    Connect.DatabaseName := 'test_dbase';
    try
        if not fileexists(Connect.DatabaseName) then begin
            Connect.Open;   // もし例えばSQLiteがインストールされていないときにEInOutErrorをあげる
            Trans.StartTransaction;
            WriteTable('create table TBLNAMES (ID integer Primary Key, NAME varchar(40));');
            Trans.Commit;
        end;
        Connect.open;
        Trans.StartTransaction;
        WriteTable('insert into TBLNAMES (NAME) values (''AName1'');');
        WriteTable('insert into TBLNAMES (NAME) values (''AName2'');');
    except
        on E : EDatabaseError do
            FatalError(E.ClassName, E.Message, 'Does the file contain the correct database ?');
        on E : EInOutError do
            FatalError(E.ClassName, E.Message, 'Have you installed SQLite (and dev package)?');
        on E : Exception do
            FatalError(E.ClassName, E.Message, 'Something really really bad happened.');
     end;
    ReadTable();
    Connect.Close;
    Trans.Free;
    Connect.Free;
end.

どのようにしてTSQLQuery を用いてクエリを実行するのか

次にデータセット(#どのようにしてテーブルからデータを取得するのか参照)を返すステートメントである。SQLはデータを返さないステートメントを持っている。例えば、INSERT、UPDATE、DELETEステートメントはデータを返さない。これらはTSQLConnection.ExecuteDirectを用いることで実行できるが、TSQLQueryもまた用いることができる。もし、TSQLQuery.Open の代わりに TSQLQuery.ExecSQL を使って、データが変えることを期待しないなら、先に述べたように、SQLステートメントによりデータセットを開くために、TSQLQuery.Open を用いること。

以下は TSQLQuery を用いてテーブルを作り、2つのレコードを作るプロシージャである。

procedure CreateTable;
  
var 
  Query : TSQLQuery;
  
begin
  Query := TSQLQuery.Create(nil);
  try
    Query.Database := AConnection;

    Query.SQL.Text := 'create table TBLNAMES (ID integer, NAME varchar(40));';
    Query.ExecSQL;
 
    Query.SQL.Text := 'insert into TBLNAMES (ID,NAME) values (1,''Name1'');';
    Query.ExecSQL;
  
    Query.SQL.Text := 'insert into TBLNAMES (ID,NAME) values (2,''Name2'');';
    Query.ExecSQL;
  finally
    Query.Free;
  end;
end;

どのようにしてクエリの中でパラメータを用いるのか

In the code example of #どのようにしてTSQLQuery を用いてクエリを実行するのかのコード例の中で同じクエリが2回使われており、挿入される値が異なるだけである。これを行うより良い方法は、クエリの中でパラメータを用いることである。

クエリ内のパラメータの構文はデータベースシステムごとに異なるが、その違いは TSQLQuery によって処理される。

クエリ内の値をコロンに置き換え、その後に使用するパラメータの名前を続ける。例えば:

Query.SQL.Text := 'insert into TBLNAMES (ID,NAME) values (:ID,:NAME);';

このクエリは2つのパラメータ: 「ID」と「NAME」を作る。

パラメーターを決定するために、TSQLQuery.SQL のテキストが割り当てられるか変更される時点でクエリが解析される。既存のパラメータはすべて削除され、新しいパラメータが「TSQLQuery.Params」プロパティに追加される。パラメーターに値を割り当てることは、データセット内のフィールドに値を割り当てることと似ている:

Query.Params.ParamByName('Name').AsString := 'Name1';

クエリからは、どのような種類のデータをパラメータに保存する必要があるかを知ることはできない。パラメータのデータ型は、パラメータに値が最初に割り当てられた時点で決定される。「.AsString」を使用して値を割り当てると、パラメータにはデータ型「ftString」が割り当てられる。'DataType' プロパティを設定することで、データ型を直接決定できる。不正なデータ型がパラメータに割り当てられている場合、クエリを開いたり実行したりするときに問題が発生する。データ型についてのさらなる情報は Database field type を参照されたい。

Select クエリ

パラメーターを使用した選択クエリの例は、次のように変更することになる:

  Query.SQL.Text := 'select ID,NAME from TBLNAMES where NAME = '''+Edit1.Text+''' ORDER BY NAME ';

をこのように:

  Query.SQL.Text := 'select ID,NAME from TBLNAMES where NAME = :NAMEPARAM ORDER BY NAME ';
  Query.Params.ParamByName('NAMEPARAM').AsString := Edit1.Text;

実装例

以下の例は1つ前の例と同じテーブルを生成するが、パラメータが用いられている:

procedure CreateTableUsingParameters;
  
var 
  Query : TSQLQuery;
  
begin
  Query := TSQLQuery.Create(nil);
  try
    Query.Database := AConnection;

    Query.SQL.Text := 'create table TBLNAMES (ID integer, NAME varchar(40));';
    Query.ExecSQL;

    Query.SQL.Text := 'insert into TBLNAMES (ID,NAME) values (:ID,:NAME);';
    Query.Prepare;
  
    Query.Params.ParamByName('ID').AsInteger := 1;
    Query.Params.ParamByName('NAME').AsString := 'Name1';
    Query.ExecSQL;
  
    Query.Params.ParamByName('ID').AsInteger := 2;
    Query.Params.ParamByName('NAME').AsString := 'Name2';
    Query.ExecSQL;

    //Query.UnPrepare; // これを呼ぶ必要はない; Query.Closeによって呼ばれるはずである
    Query.Close;
  finally
    Query.Free;
  end;
end;

この例はパラメータを用いないコードよりもコード量が多くなることに気を付けること。ではパラメータの用途は何であろうか?

速度が1つの理由である。このパラメータを用いた例はより実行が速い、何故なら(.Prepareステートメントもしくは最初の実行で)データベースサーバはクエリを1回のみパースするからである。

またプリペアードステートメントを用いる別の理由は、SQL-インジェクションを防ぐことになる (Secure programmingも参照のこと)。

最後に、ある例ではコーディングを簡素化するためである。

問題解決法: TSQLConnection ログ

TSQLConnection の動作をログに記録させることができる。これは、Lazarus プログラムがデータベースに送信する内容を正確に確認したり、データベースコンポーネント自体をデバッグしたり、クエリを最適化したりする場合に便利である。注意: 準備されたステートメント/パラメータ化されたクエリ (上記のセクションを参照) を使用する場合、パラメータは多くの場合、TSQLConnection の派生 (TIBConnection など) によってバイナリで送信されるため、ログに記録された SQL をコピーしてデータベースクエリツールに貼り付けるだけではすまない。いずれにせよ、接続ログにより、プログラムの動作について多くの洞察が得られる。

これに代わるものとして:

デバッグを有効にして FPC (および Lazarus) をビルドした場合は、デバッガーを使用してデータベースコードをステップ実行できます。
ODBC ドライバーを使用している場合 (少なくとも Windows では)、ODBC コントロールパネルでトレースログ出力を有効にすることができる。
多くのデータベースでは、特定の IP アドレス/接続から送信されるすべてのステートメントを監視できる。

TSQLConnection ログを使用する場合は、2 つのことが必要である:

TSQLConnection がログに記録する必要があるイベントタイプを指定する。
イベントを受信して処理する関数に TSQLConnection を指定する (イベントをファイルに記録したり、画面に出力したりするなど)。

この関数は TDBLogNotifyEvent 型である必要があるため (sqldb.pp を参照)、次の署名が必要である:

TDBLogNotifyEvent = Procedure (Sender : TSQLConnection; EventType : TDBEventType; Const Msg : String) of object;

FPC (もしくは: 手動的方法)

コードスニペットでこれを説明できる:

uses
...
TSQLConnection, //もしくはTIBConnection、TMSSQLConnection といった派生オブジェクト
...
var
type 
  TMyApplication = class(TCustomApplication); //これが接続を用いるアプリケーション
...
  private
    // この例はこの文字列リストにログを格納する:
    FConnectionLog: TStringList;
...
  protected
    // このプロシージャは接続によるログのイベントを受け取るだろう:
    procedure GetLogEvent(Sender: TSQLConnection; EventType: TDBEventType; Const Msg : String);
...
  procedure TMyApplication.GetLogEvent(Sender: TSQLConnection;
    EventType: TDBEventType; const Msg: String);
  // TSQLConnectionによってプロシージャが呼ばれ、FConnectionLogに
  // 受け取ったメッセージログが保存される
  var
    Source: string;
  begin
    // 見た目よく右揃え
    case EventType of
      detCustom:   Source:='Custom:  ';
      detPrepare:  Source:='Prepare: ';
      detExecute:  Source:='Execute: ';
      detFetch:    Source:='Fetch:   ';
      detCommit:   Source:='Commit:  ';
      detRollBack: Source:='Rollback:';
      else Source:='Unknown event. Please fix program code.';
    end;
    FConnectionLog.Add(Source + ' ' + Msg);
  end;

...
  // 何をログに残したいか TSQLConnection に知らせる必要がある:
    FConnection.LogEvents:=LogAllEvents; //= [detCustom, detPrepare, detExecute, detFetch, detCommit, detRollBack]
    // ... そして、どのプロシージャに向けて、接続がイベントを送るべきか:
    FConnection.OnLog:=@Self.GetLogEvent;
...
  // これで接続を使用できるようになり、FConnectionLog 文字列リストにログ メッセージが表示される

代わりに TSQLConnection の GlobalDBLogHook を使用して、複数の接続からすべてをログに記録することもできる。

Lazarus (もしくは: 速い方法)

最後に、上記の説明は、冒頭で示した FPC の方法である。 Lazarus を使用している場合、より簡単な方法は、TSQLConnection の OnLog イベントにイベントハンドラーを割り当てることである。

以下も参照のこと

Working With TSQLQuery