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

│ 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 (訳注:いつから?)
MySQL v8.0: TMySQL80Connection (訳注:いつから?)
ODBC: TODBCConnection (ODBCConn#TODBCConnectionを参照)
Oracle: TOracleConnection (Oracleを参照)
PostgreSQL: TPQConnection (postgresql#SQLDBを参照)
Sqlite3: TSQLite3Connection (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 はデータベースサーバに変更を送るのか

#How to change data in a table?のコード例で以下が、何をしているのか説明なしに記載されている。

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」フラグセットを持つフィールド および 現在のレコードで、変更されたフィールドに用いることができる。

エラーの取り扱い方

Run time errors are unavoidable, disks may fill up, necessary libraries or helper apps may not be available, things go wrong and we need to allow for that. The FPC detects and handles run time errors quite well. It usually gives you a concise and reasonable explanation of what went wrong. However, you will want to monitor and handle errors yourself for a number of reasons -

You probably don't want the programme to teminate at the first sign of trouble.
If we do keep going, lets make sure any memory allocated in the problem area is recovered, we don't want any memory leaks.
If we are going to go under however, lets give the user a context sensitive explanation.

The following bit of code is based on the above examples but this time it DOES check for errors in critical places. Key is the try...finally...end and try...except...end blocks. You can test it by doing things like uninstalling SQLite3, putting a dummy file in place of the test_dbase database and so on.

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;          // This will also open Connect
        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;              // Its possibly silly worrying about freeing
    Trans.free;                 // if we are going to call halt() but its
    Connect.Free;               // a demo, alright ?
    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;   // give EInOutError if (eg) SQLite not installed
            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 を用いてクエリを実行するのか

Next to statements that return a dataset (see #How to read data from a table?) SQL has statements that do not return data. For example INSERT, UPDATE and DELETE statements do not return data. These statements can be executed using TSQLConnection.ExecuteDirect, but TSQLQuery can also be used. If you do not expect return data use TSQLQuery.ExecSQL instead of TSQLQuery.Open. As mentioned earlier, use TSQLQuery.Open to open the dataset returned by the SQL statement.

The following procedure creates a table and inserts two records using TSQLQuery.

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 #How to execute a query using TSQLQuery? the same query is used twice, only the values to be inserted differ. A better way to do this is by using parameters in the query.

The syntax of parameters in queries is different per database system, but the differences are handled by TSQLQuery. Replace the values in the query with a colon followed by the name of the parameter you want to use. For example:

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

This query will create two parameters: 'ID' and 'NAME'. To determine the parameters, the query is parsed at the moment the text of TSQLQuery.SQL is assigned or changed. All existing parameters will be removed and the new parameters will be added to the 'TSQLQuery.Params' property. Assigning a value to a parameter is similar to assigning a value to a field in the dataset:

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

You can't tell from the query what kind of data must be stored in the parameter. The data type of the parameter is determined at the moment a value is first assigned to the parameter. By assigning a value using '.AsString', the parameter is assigned the data type 'ftString'. You can determine the data type directly by setting the 'DataType' property. If an incorrect datatype is assigned to the parameter, then problems will occur during opening or executing the query. See Database field type for more information on data types.

Select クエリ

An example of a select query with parameters would be to change something like this:

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

to something like this:

  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 ログ

You can let a TSQLConnection log what it is doing. This can be handy to see what your Lazarus program sends to the database exactly, to debug the database components themselves and perhaps to optimize your queries. NB: if you use prepared statements/parametrized queries (see section above), the parameters are often sent in binary by the TSQLConnection descendent (e.g. TIBConnection), so you can't just copy/paste the logged SQL into a database query tool. Regardless, connection logging can give a lot of insight in what your program is doing.

Alternatives are:

you can use the debugger to step through the database code if you have built FPC (and Lazarus) with debugging enabled.
if you use ODBC drivers (at least on Windows) you could enable tracelog output in the ODBC control panel.
many databases allow you to monitor all statements sent to it from a certain IP address/connection.

If you use TSQLConnection logging, two things are required:

indicate which event types your TSQLConnection should log
point TSQLConnection at a function that receives the events and processes them (logs them to file, prints them to screen, etc.).

That function must be of type TDBLogNotifyEvent (see sqldb.pp), so it needs this signature:

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

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

A code snippet can illustrate this:

uses
...
TSQLConnection, //or a child object like TIBConnection, TMSSQLConnection
...
var
type 
  TMyApplication = class(TCustomApplication); //this is our application that uses the connection
...
  private
    // This example stores the logged events in this stringlist:
    FConnectionLog: TStringList;
...
  protected
    // This procedure will receive the events that are logged by the connection:
    procedure GetLogEvent(Sender: TSQLConnection; EventType: TDBEventType; Const Msg : String);
...
  procedure TMyApplication.GetLogEvent(Sender: TSQLConnection;
    EventType: TDBEventType; const Msg: String);
  // The procedure is called by TSQLConnection and saves the received log messages
  // in the FConnectionLog stringlist
  var
    Source: string;
  begin
    // Nicely right aligned...
    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;

...
  // We do need to tell our TSQLConnection what to log:
    FConnection.LogEvents:=LogAllEvents; //= [detCustom, detPrepare, detExecute, detFetch, detCommit, detRollBack]
    // ... and to which procedure the connection should send the events:
    FConnection.OnLog:=@Self.GetLogEvent;
...
  // now we can use the connection and the FConnectionLog stringlist will fill with log messages.

You can also use TSQLConnection's GlobalDBLogHook instead to log everything from multiple connections.

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

Finally, the description above is the FPC way of doing things as indicated in the introduction; if using Lazarus, a quicker way is to assign an event handler to the TSQLConnection's OnLog event.

SqlDBHowto/ja

Contents