Working With TSQLQuery/ja

From Free Pascal wiki
Jump to navigationJump to search

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

データベースのポータル

参照:

チュートリアル/練習となる記事:

各種データベース

Advantage - MySQL - MSSQL - Postgres - Interbase - Firebird - Oracle - ODBC - Paradox - SQLite - dBASE - MS Access - Zeos
日本語版メニュー
メインページ - Lazarus Documentation日本語版 - 翻訳ノート - 日本語障害情報


概要

TSQLQuery は、データベース (Firebird、MS SQL Server、Oracle などの SQL を使用する RDBMS) からのデータセットを具体化できるオブジェクトである。 TSQLQuery の SQL プロパティで SELECT SQL ステートメントを使用すると、どのデータがデータベースからデータセットに取得されるかを決定できる。 プログラム (またはユーザー) によってデータセットが変更されると、その変更をデータベースに送信し返すことができる。

TSQLQuery は、データを直接変更するために使用することもできる。SQL プロパティで目的の INSERT、UPDATE、DELETE などの SQL ステートメントを指定し、TSQLQuery の ExecSQL メソッドを呼び出すと、クエリ オブジェクトは何の結果も取得せずに SQL をデータベースに送信する。

FPC での使用とは別に、Lazarus は次のコンポーネントも提供する: TSQLQuery/ja tsqlquery.png

公式ドキュメント

TSQLQuery documentationを参照のこと。

多くの状況感知型ドキュメントが Lazarus で利用できるようになった。 残念ながら、TSQLQuery は Lazarus 1.0 ヘルプの索引には表示されない。 TSQLQuery のメソッドとプロパティにカーソルを置いたら、F1 キーを押して、そのコードが文書化されているかどうかを確認されたい。 例えば これはうまくいく: 

var
Q: TSQLQuery
...
  Q.Open; //<--- カーソルを Openの上において F1を押す

一般的に用いられるコントロール

TSQLQuery によって返されたデータセットは、TDBGrid/ja のインスタンスを使用して便利に表示できるが、個々のフィールドやセルのデータを編集するにはあまり適していない。または、この目的のために、TDBEdit/ja などのデータ対応、単一フィールド コントロールをフォームに配置し、その DataSource プロパティを使用されているデータ ソースに設定する必要がある。 DataField プロパティは、名前付きフィールド (「IDENTITY」など) または適切な文字列を返す式に設定する必要がある。

TDBNavigator/ja ツールバーの追加により、レコード間の移動や、編集するレコードの選択が非常に簡単になる。 ツールバーでレコードを選択するか、データ グリッド内でマウスを移動してレコードを選択すると、関連する行と列のデータが TDBEdit ボックスに表示され、「編集」ボタンをクリックすると、編集ボックスの内容を変更できる。「投稿」ボタンをクリックすると変更が確定し、「キャンセル」ボタンをクリックすると変更がキャンセルされる。

一般に、プロセスは次のとおりである:

  1. TSQLQuery/ja をフォーム/データモジュールに配置し、Database、Transaction、および SQL プロパティを設定する。
  2. TDataSource/ja コンポーネントを配置し、その DataSet プロパティを TSQLQuery インスタンスに設定する。
  3. TDBGrid/ja をフォームに配置し、その DataSource プロパティを TDataSource インスタンスに設定する。
  4. 必要に応じて、TDBNavigator インスタンスをフォームに配置し、その Datasource プロパティを TDatasource インスタンスに設定する。この後、ActiveプロパティをTrueに設定すると、クエリによって取得されたデータを確認できるようはずである。

(TSQLConnectionコンポーネントとTSQLTransactionコンポーネントの両方がアクティブである場合)

データを更新する

レコードを DELETE または変更できるようにする必要がある場合、データベース テーブルは次のいずれかを行う必要がある

  1. PRIMARY KEY 列が 1 つ含まれている。
  2. レコードを一意に決定する一連のフィールドがある。 通常、これらは一意のインデックスの一部である必要がある。これは必須ではないが、クエリが大幅に高速化される。

主フィールドがない場合、またはレコードを一意に決定するフィールドがない場合は、PRIMARY KEY フィールドを追加する必要がある。 これは、テーブル構造の設計時、作成時に行うことが望ましいが、後で追加することもできる。

たとえば、MySQL クライアントの次のコード例は、テーブルに一意のインデックスを追加する: 

alter table testrig 
add column autoid int 
primary key auto_increment;

このフィールドを追加しても害はなく、アプリケーションでフィールドを更新できるようになる。

キャッシュされた更新

TSQLQueryコンポーネントはすべての更新をキャッシュする。 つまり、更新はデータベースにすぐには送信されないが、ApplyUpdates メソッドが呼び出されるまでメモリ内に保持される。 その時点で、更新は SQL 更新ステートメントに変換され、データベースに適用される。 ApplyUpdates を呼び出さない場合、データベースはローカルの変更で更新されない。

プライマリキーフィールド

レコードを更新するとき、TSQLQuery は、レコードの更新に使用できる主キーを構成するフィールドを知る必要がある。 どのフィールドを更新する必要があるか: その情報に基づいて、SQL の UPDATE、INSERT、または DELETE コマンドを構築する。

SQL ステートメントの構築は、UsePrimaryKeyAsKey プロパティと ProviderFlags プロパティによって制御されます。

Providerflagsプロパティは、次の 3 つのフラグのセットである:

pfInkey
フィールドはプライマリキーの一部である。
pfInWhere
このフィールドは SQL ステートメントの WHERE 句で使用する必要がある。
pfInUpdate
更新または挿入には、このフィールドを含める必要がある。

デフォルトでは、 ProviderFlagsは pfInUpdate のみからなっている。

テーブルに主キーがある場合 (上記のとおり)、UsePrimaryKeyAsKey プロパティを True に設定するだけですべてが行われます。これにより、主キー フィールドに pfInKey フラグが設定される。

テーブルに主キー インデックスがないものの、レコードを一意に識別するために使用できるフィールドがいくつかある場合は、レコードを一意に決定するすべてのフィールドの ProviderFlags プロパティに pfInKey オプションを含めることができる。

UpdateMode プロパティは、どのフィールドが WHERE 句で正確に使用されるかを決定する:

upWhereKeyOnly
TSQLQueryが更新操作のためにWHERE句を構築する際に使用される。このオプションを指定すると、TSQLQueryは更新操作を行うための一意なレコードを特定するために、ProviderFlagsプロパティがpfInKeyフラグを持つすべてのフィールドの値を収集する。通常、これはUPDATEまたはDELETEステートメントでのみ必要である。
upWhereChanged
pfInKeyを持つフィールドだけでなく、ProviderFlagsプロパティにpfInWhereを持ち、かつ変更されたすべてのフィールドがWHERE句に含まれるようにする。
upWhereAll
pfInKeyを持つフィールドに加えて、ProviderFlagsプロパティにpfInWhereを持つすべてのフィールドもWHERE句に含まれるようにする。

更新をコントロールする

特定のフィールドを更新するように指定することができる: 前述の通り、ProviderOptionsプロパティにpfInUpdateを持つフィールドのみがSQL UPDATEまたはINSERTステートメントに含まれる。デフォルトでは、pfInUpdateは常にProviderOptionsプロパティに含まれる。

SQLをTSQLQueryの中でカスタマイズする

通常、TSQLQuery は、上で説明したプロパティに基づいて汎用 SQL ステートメントを使用する。 ただし、sqldb によって作成された汎用 SQL は、状況によっては正しくない可能性がある。 TSQLQuery を使用すると、データベースの状況に応じて最適に機能するように、さまざまなアクションに使用される SQL ステートメントをカスタマイズできる。 この目的のために、プロパティ SQL、InsertSQL、UpdateSQL、および DeleteSQL を使用する。

SQLをTSQLQueryの中でカスタマイズする

通常、TSQLQuery は、上で説明したプロパティに基づいて汎用 SQL ステートメントを使用する。 ただし、sqldb によって作成された汎用 SQL は、状況によっては正しくない可能性がある。 TSQLQuery を使用すると、データベースの状況に応じて最適に機能するように、さまざまなアクションに使用される SQL ステートメントをカスタマイズできる。 この目的のために、プロパティ SQL、InsertSQL、UpdateSQL、および DeleteSQL を使用する。

これらのすべてのプロパティは、複数行のSQLを受け入れるTStringList型のプロパティです。すべてのプロパティには、IDE内にプロパティエディタが付属してる。IDEでプロパティを選択し、省略ボタンをクリックしてエディタを開く。このエディタ（TSQLQuery metadata tool）では、テーブル情報などを参照することもできる。

コード内では、例えばInsertSQL.TextやInsertSQL.Add()を使用して、SQLステートメントの行を設定したり追加したりします。1つのステートメントは複数行にまたがる場合があり、セミコロンで終わる。

また、4 つのプロパティはすべて、以下で説明するパラメータを受け入れる。

SQL - 基本的なSQLカスタマイゼーション

SQL プロパティは通常、データベースからデータをフェッチするために使用される。 このプロパティの汎用 SQL は SELECT * FROM fpdev です。fpdev はデータベースに設定されているテーブルである。

汎用 SQL ステートメントによって返されるデータセットは、かなり粗いものになる。 結果を TDBGrid で表示すると、レコードの順序がランダムに見えたり、列の順序が意図したものと異なったり、フィールド名は技術的には正しいものの、ユーザーフレンドリーではない可能性がある。 カスタマイズされた SQL を使用すると、これを改善できる。 id、UserName、InstEmail 列を持つ fpdev というテーブルの場合、次のようなことができる: 

SELECT id AS 'ID', UserName AS 'User', InstEmail AS 'e-mail' FROM fpdev ORDER BY id;

上記のクエリの結果として得られるデータセットは、クエリで指定されたフィールド名 (ID、ユーザー、および電子メール)、クエリで指定された列の順序を使用し、レコードは ID によって並べ替えられる。

InsertSQL、UpdateSQL、DeleteSQL - パラメータの基本的な使用

SQLQueryのSQLプロパティにSELECTクエリを割り当てると、SQLQueryはデータベースからデータを取得する方法を知っている。しかし、DBGridなどのデータバインドコントロールを使用する場合、SQLQueryはユーザーのアクションに基づいてデータベースから行を挿入、更新、削除する能力も必要となる。

開発を加速するために、SQLQueryは必要なSQLステートメントを推測しようとすることができる。SQLプロパティが存在し、かつParseSQLプロパティがtrueである場合（デフォルトではtrueである）、SQLQueryはSQLプロパティを解析することでこれらのステートメントを生成しようとする。SQLDBはこれらのステートメントをInsertSQL、UpdateSQL、およびDeleteSQLプロパティに格納する。

しかし、生成されたステートメントが機能しない場合（例えば、自動インクリメント/オートナンバープライマリキーを持つテーブルに挿入する場合）や、非常に遅い場合がある。必要に応じて、ステートメントを手動で割り当てることができる。

InsertSQL、UpdateSQL、DeleteSQLプロパティのステートメントは、データセット内のフィールドを表すパラメーターを受け入れる。 以下の規則が適用される:

  • パラメーター名は、データセットで使用されるフィールド名と完全に同じである必要がある。データセット内のフィールド名は、使用されるSELECTステートメントによってテーブル内の列名と異なる場合がある(上記を参照)。
  • SQLDBの他のクエリのパラメータと同様に、パラメータ名はコロンを前置して記述する必要がある。
  • 更新/削除ステートメントで使用する場合、データセットのフィールド名の前にOLD_(少なくともLazarus v. 1.0では厳密に大文字)を付けると、新しい値ではなく編集される前のレコードの値を取得できる。

テーブルfpdev 、id、UserName、InstEmail というカラムがあり、データセットには ID、User、e-mail（select ステートメントの例を参照）というフィールドがリンクしている場合、このような InsertSQL クエリを書くことができる: 

INSERT INTO fpdev(id, UserName, InstEmail) VALUES(:ID,:User,:e-mail);

このステートメントは、データセットの現在のレコードから ID、User、e-mail の値を取得し、それぞれ fpdev テーブルの対応するフィールドに挿入しする。

この例のステートメントは、実際には SQLDB 自体が自動生成するものとほぼ同じである。 与えられたステートメントは、id フィールドがユニークキー内の自動増分フィールドの場合、エラーが発生する可能性があります。さまざまなデータベースは、異なる方法でこの問題を解決している。例えば、次のような方法が MySQL で機能する。 異なるデータベースは、この問題を異なる方法で解決する。たとえば、次の方法は MySQL で機能する。 

INSERT INTO fpdev(id, UserName, InstEmail) VALUES(0,:User,:e-mail)
 ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID();

上記のステートメントは、新しいレコードを挿入しようとするが、id列には0（ゼロ）を使用する。 もしゼロが既にキーとして使用されている場合、重複が検出され、idは最後に挿入されたidを使用するように更新される。実際には、最後に使用されたidよりも1つ増えたidである。


その文は、0をid列の値として新しいレコードを挿入しようとする。もし0がすでにキーとして使用されている場合、重複が検出され、idは最後に挿入されたidを使用するように更新される。実際には、最後に使用されたidよりも1つ大きいidが使用される。 Firebirdの場合、自動増分キーをエミュレートする場合[1]、次のようなものが機能するはずである: 

INSERT INTO fpdev(UserName, InstEmail) VALUES(:User,:e-mail);(

このステートメントは、主キー以外のすべてを挿入し、Firebirdのbefore insertトリガーがジェネレータ/シーケンスを使用してid値を挿入する。 INSERTステートメントでは、選択されたレコードの現在のフィールド値を使用したい場合がある。UPDATEステートメントでは、編集前のフィールド値をWHERE句で使用する必要がある。前述のように、編集前のフィールド値は、フィールド名の前にOLD_が付いた形で書かれなければならない(Lazarus v. 1.0では、厳密に大文字である必要がある)。たとえば、次のクエリ: 

UPDATE fpdev SET UserName=:User, InstEmail=:e-mail WHERE UserName=:OLD_User;

上記のステートメントは、Userが古いUser値と等しいすべてのレコードのUserNameおよびInstEmail列を更新する。

DELETEステートメントで現在のフィールド値と古いフィールド値を使用する方法は、読者に宿題として残しておく。

以下の公式ドキュメントも参照のこと:

TSQLQuery.SQLにおけるパラメータ

ほとんどの状況では、TSQLQueryのSQLプロパティには、通常パラメータが必要ない select ステートメントが含まれる。ただし、それらを含めることもできる。これにより、レコードをフィルタリングする非常に簡単で強力な方法が提供される。

パラメータの利点は次のとおりである:

  • データをSQLテキスト、日付などの引数としてフォーマットする必要がないため、例えばMySQL用に日付をフォーマットする方法を覚える必要がない。これは、Firebirdの実装と異なる可能性がある。また、テキストデータをエスケープする必要もない。例えば、O'Malley's "SQL Horror"のようなテキストデータをエスケープする必要がない。
  • パフォーマンスの向上の可能性
  • SQLインジェクション攻撃からの保護

パラメータの使用は、データベースのパフォーマンスを向上させることができる。ほとんどのデータベースはプリペアドステートメントをサポートしており、これはステートメントが準備され、データベースにキャッシュされることを意味する。プリペアドステートメントは複数回使用でき、使用するたびにステートメントの解析やクエリの計画が必要なく、パラメータのみが変更される。 同じステートメントが大量の回数使用される場合（パラメータの値のみが異なる場合）、プリペアドステートメントはパフォーマンスを大幅に向上させることができる。また、パラメータの使用により、SQLインジェクション攻撃を緩和することができる。

InsertSQL、UpdateSQL、および DeleteSQL プロパティには、現在のフィールド値と古いフィールド値のための事前定義されたパラメータがある。しかし、SQL プロパティにはありません。Params プロパティで独自のパラメータを作成できる。

Selectクエリの例

この例は、パラメータを使用してデータを選択する方法を示している。また、SQL内でのエイリアス(... AS ...)の使用も示している。 

 sql_temp.sql.text := 'SELECT id AS ''ID'', UserName AS ''User'', InstEmail AS ''e-mail'' FROM fpdev WHERE InstEmail=:emailsearch;'
 ...
 //これは、emailsearchというパラメータを生成する。

 // もし必要なら、パラメータがどのような種類のパラメータかを明示的に設定することもできる...
 // これは、FPCが誤って推測した場合にのみ必要になるかもしれない:
 // sql_temp.params.parambyname('emailsearch').datatype:=ftWideString
 
 //これでパラメータ値を入れることができる:
 sql_temp.params.parambyname('emailsearch').asstring := 'mvancanneyt@freepascal.org';
 ...
 //そして、いつもの通りデータを引き出す
 //場合によっては、パラメータ値を変え、再び実行する。

Insertクエリの例

この例は、パラメータを使用してテーブルに新しいレコードを挿入する方法を示している: 

 sql_temp.sql.text := 'insert into PRODUCTS (ITEMNR,DESCRIPTION) values (:OURITEMNR,:OURDESCRIPTION)'
 ...
 sql_temp.Params.ParamByName('OURITEMNR').AsString := 'XXXX';
 sql_temp.Params.ParamByName('OURDESCRIPTION').AsString := 'description';
 sql_temp.ExecSQL;
 SQLTransaction1.Commit; //もしくは、CommitRetainingアプリケーションの設定による

別の方法でこれをするには、以下のように: 

 tsqlquery1.appendrecord(['XXXX', 'description']) 
 tsqltransaction1.commit; //もしくは CommitRetaining

フォーマット関数を伴なうクエリ

パラメータ化されたクエリの使用が好ましいアプローチだが、一部の状況では format 関数が代替手段となりうる(下記の警告を参照)。たとえば、接続の ExecuteDirect プロシージャでステートメントを実行する場合は、パラメータを使用することができない( もちろん、問題のある SQL ステートメントを実行するためにクエリを使用することもできる )。その場合、format 関数が役立つ: 

procedure InsertRecord
var
  aSQLText: string;
  aSQLCommand: string;
begin
  aSQLText:= 'INSERT INTO products(item_no, description) VALUES(%d, %s)';
  aSQLCommand:= Format(aSQLText, [strtoint(Edit1.Text), Edit2.Text]);
  aConnection.ExecuteDirect(aSQLCommand);
  aTransaction.Commit;
end;

変数の値が変更されると、クエリの値もそれに応じて変更される。これは、パラメータ化されたクエリと同様である。

パラメータ%dは整数、%sは文字列などに使用される。詳細については、Format 関数のドキュメントを参照のこと。

Warning-icon.png

Warning: このテクニックでは、'を含むテキストや日付に関する問題に遭遇する可能性があることに注意すること!

独自のSQLを実行しメタデータを得る

一部の SQL ステートメントのチェック、トラブルシューティング、またはデータベースからのメタデータ (テーブルのリストなど) の取得だけを行いたい場合は、IDE 内から行うことができる。 プログラムでは、設計時に T*Connection、トランザクション、クエリ オブジェクトなどを設定し、クエリ オブジェクトの SQL プロパティに移動し、[...] ボタンをクリックする。

SQL コードを含むウィンドウが表示され、次のようなステートメントを実行できる。 

SELECT * FROM EMPLOYEE

by pressing the play icon:

runsqlstatements.png

テーブル名、列名などのメタデータも取得できる (sqldb コネクタがそれをサポートしているが、最近ではほとんどのコネクタがサポートしている場合):

sqlquerymetadata.png

(Database metadata#Lazarus TSQLQuery metadata toolも参照のこと)

問題解決

ロギング

詳細はここを参照のこと: SqlDBHowto/ja#問題解決法: TSQLConnection ログ

芳しくない性能

  • データベースクエリを最適化し、適切なインデックスなどを使用する。クエリプランを提供するなど、データベースツールを使用すること。
  • SQLQueryに進むときに、可能なパフォーマンス向上策については、以下の#エラー: メモリが足りない下を参照のこと。

エラーメッセージ

エラー: メモリが足りない

TSQLQuery は BufDataset の派生クラスであり、受信したデータをメモリにバッファリングするデータセットである。多くのレコードを取得する場合(例えば、エクスポート用にそれらをループで処理する場合)、ヒープメモリがいっぱいになり(すでに処理したレコードで)、メモリ不足のエラーが発生する可能性がある。

この状況は FPC 開発版で改善されているが、回避策として、クエリを開く前に Unidirectional プロパティを true に設定して、既に読み取ったレコードを破棄するように bufdataset に指示することができる。 

MySQLQuery.UniDirectional:=True;

これでパフォーマンスも改善するかもしれない。

データセットは読み取り専用

これは、FPCが更新可能であるとは認識していないが、実際には更新可能なクエリを指定した場合に発生する可能性がある。

例: 

select p.dob, p.surname, p.sex from people p;

FPCのSQLパーサーはかなり単純であり、FROM句にコンマやスペースがあると、複数のテーブルが関与していると見なしてデータセットを読み取り専用に設定する。その弁護として、通常、1つのテーブルのみが関与している場合、テーブルにエイリアスを付けることは行われない 解決策：クエリを書き直すか、独自のInsertSQL、UpdateSQL、DeleteSQLを指定する。

Retrieved from "https://wiki.freepascal.org/index.php?title=Working_With_TSQLQuery/ja&oldid=160003"