Sign in Join
Wiki copytemporary

Working With TSQLQuery/ja: what changed

Revision 0 to 1. Removed lines have a minus sign; added lines have a plus sign.

Title: Working With TSQLQuery/ja

OldNewLine
1+## 概要
2+
3+TSQLQuery は、データベース (Firebird、MS SQL Server、Oracle などの SQL を使用する RDBMS) からのデータセットを具体化できるオブジェクトである。 TSQLQuery の SQL プロパティで SELECT SQL ステートメントを使用すると、どのデータがデータベースからデータセットに取得されるかを決定できる。 プログラム (またはユーザー) によってデータセットが変更されると、その変更をデータベースに送信し返すことができる。
4+
5+TSQLQuery は、データを直接変更するために使用することもできる。SQL プロパティで目的の INSERT、UPDATE、DELETE などの SQL ステートメントを指定し、TSQLQuery の ExecSQL メソッドを呼び出すと、クエリ オブジェクトは何の結果も取得せずに SQL をデータベースに送信する。。
6+
7+FPC での使用とは別に、Lazarus は次のコンポーネントも提供する: [[TSQLQuery/ja]] ![](/wiki-images/b/bb/Tsqlquery.png)
8+
9+## 公式ドキュメント
10+
11+[TSQLQuery documentation](http://www.freepascal.org/docs-html/fcl/sqldb/tsqlquery.html)を参照のこと。
12+
13+多くの状況感知型ドキュメントが Lazarus で利用できるようになった。 残念ながら、TSQLQuery は Lazarus 1.0 ヘルプの索引には表示されない。 TSQLQuery のメソッドとプロパティにカーソルを置いたら、F1 キーを押して、そのコードが文書化されているかどうかを確認されたい。 例えば これはうまくいく:
14+
15+``` pascal
16+var
17+Q: TSQLQuery
18+...
19+ Q.Open; //<--- カーソルを Openの上において F1を押す
20+```
21+
22+## 一般的に用いられるコントロール
23+
24+TSQLQuery によって返されたデータセットは、[[TDBGrid/ja]] のインスタンスを使用して便利に*表示*できるが、個々のフィールドやセルのデータを*編集*するにはあまり適していない。または、この目的のために、[[TDBEdit/ja]] などのデータ対応、単一フィールド コントロールをフォームに配置し、その DataSource プロパティを使用されているデータ ソースに設定する必要がある。 DataField プロパティは、名前付きフィールド (「IDENTITY」など) または適切な文字列を返す式に設定する必要がある。
25+
26+[[TDBNavigator/ja]] ツールバーの追加により、レコード間の移動や、編集するレコードの選択が非常に簡単になる。 ツールバーでレコードを選択するか、データ グリッド内でマウスを移動してレコードを選択すると、関連する行と列のデータが TDBEdit ボックスに表示され、「編集」ボタンをクリックすると、編集ボックスの内容を変更できる。「投稿」ボタンをクリックすると変更が確定し、「キャンセル」ボタンをクリックすると変更がキャンセルされる。
27+
28+一般に、プロセスは次のとおりである:
29+
30+1. [[TSQLQuery/ja]] をフォーム/データモジュールに配置し、*Database*、*Transaction*、および *SQL* プロパティを設定する。
31+2. [[TDataSource/ja]] コンポーネントを配置し、その *DataSet* プロパティを TSQLQuery インスタンスに設定する。
32+3. [[TDBGrid/ja]] をフォームに配置し、その *DataSource* プロパティを TDataSource インスタンスに設定する。
33+4. 必要に応じて、TDBNavigator インスタンスをフォームに配置し、その *Datasource* プロパティを TDatasource インスタンスに設定する。この後、*Active*プロパティを*True*に設定すると、クエリによって取得されたデータを確認できるようはずである。
34+
35+(*TSQLConnection*コンポーネントと*TSQLTransaction*コンポーネントの両方がアクティブである場合)
36+
37+## データを更新する
38+
39+レコードを DELETE または変更できるようにする必要がある場合、データベース テーブルは次のいずれかを行う必要がある
40+
41+1. PRIMARY KEY 列が 1 つ含まれている。
42+2. レコードを一意に決定する一連のフィールドがある。 通常、これらは一意のインデックスの一部である必要がある。これは必須ではないが、クエリが大幅に高速化される。
43+
44+主フィールドがない場合、またはレコードを一意に決定するフィールドがない場合は、PRIMARY KEY フィールドを追加する必要がある。 これは、テーブル構造の設計時、作成時に行うことが望ましいが、後で追加することもできる。
45+
46+たとえば、MySQL クライアントの次のコード例は、テーブルに一意のインデックスを追加する:
47+
48+``` sql
49+alter table testrig
50+add column autoid int
51+primary key auto_increment;
52+```
53+
54+このフィールドを追加しても害はなく、アプリケーションでフィールドを更新できるようになる。
55+
56+## キャッシュされた更新
57+
58+*TSQLQuery*コンポーネントはすべての更新をキャッシュする。 つまり、更新はデータベースにすぐには送信されないが、*ApplyUpdates* メソッドが呼び出されるまでメモリ内に保持される。 その時点で、更新は SQL 更新ステートメントに変換され、データベースに適用される。 *ApplyUpdates* を呼び出さない場合、データベースはローカルの変更で更新されない。
59+
60+## プライマリキーフィールド
61+
62+レコードを更新するとき、*TSQLQuery* は、レコードの更新に使用できる主キーを構成するフィールドを知る必要がある。 どのフィールドを更新する必要があるか: その情報に基づいて、SQL の UPDATE、INSERT、または DELETE コマンドを構築する。
63+
64+SQL ステートメントの構築は、*UsePrimaryKeyAsKey* プロパティと *ProviderFlags* プロパティによって制御されます。
65+
66+*Providerflags*プロパティは、次の 3 つのフラグのセットである:
67+
68+pfInkey
69+フィールドはプライマリキーの一部である。
70+
71+pfInWhere
72+このフィールドは SQL ステートメントの WHERE 句で使用する必要がある。
73+
74+pfInUpdate
75+更新または挿入には、このフィールドを含める必要がある。
76+
77+デフォルトでは、 *ProviderFlags*は *pfInUpdate* のみからなっている。
78+
79+テーブルに主キーがある場合 (上記のとおり)、*UsePrimaryKeyAsKey* プロパティを *True* に設定するだけですべてが行われます。これにより、主キー フィールドに *pfInKey* フラグが設定される。
80+
81+テーブルに主キー インデックスがないものの、レコードを一意に識別するために使用できるフィールドがいくつかある場合は、レコードを一意に決定するすべてのフィールドの *ProviderFlags* プロパティに *pfInKey* オプションを含めることができる。
82+
83+*UpdateMode* プロパティは、どのフィールドが WHERE 句で正確に使用されるかを決定する:
84+
85+upWhereKeyOnly
86+TSQLQueryが更新操作のためにWHERE句を構築する際に使用される。このオプションを指定すると、TSQLQueryは更新操作を行うための一意なレコードを特定するために、ProviderFlagsプロパティがpfInKeyフラグを持つすべてのフィールドの値を収集する。通常、これはUPDATEまたはDELETEステートメントでのみ必要である。
87+
88+upWhereChanged
89+pfInKeyを持つフィールドだけでなく、ProviderFlagsプロパティにpfInWhereを持ち、かつ変更されたすべてのフィールドがWHERE句に含まれるようにする。
90+
91+upWhereAll
92+pfInKeyを持つフィールドに加えて、ProviderFlagsプロパティにpfInWhereを持つすべてのフィールドもWHERE句に含まれるようにする。
93+
94+## 更新をコントロールする
95+
96+特定のフィールドを更新するように指定することができる: 前述の通り、*ProviderOptions*プロパティに*pfInUpdate*を持つフィールドのみがSQL UPDATEまたはINSERTステートメントに含まれる。デフォルトでは、*pfInUpdate*は常に*ProviderOptions*プロパティに含まれる。
97+
98+## SQLをTSQLQueryの中でカスタマイズする
99+
100+通常、TSQLQuery は、上で説明したプロパティに基づいて汎用 SQL ステートメントを使用する。 ただし、sqldb によって作成された汎用 SQL は、状況によっては正しくない可能性がある。 TSQLQuery を使用すると、データベースの状況に応じて最適に機能するように、さまざまなアクションに使用される SQL ステートメントをカスタマイズできる。 この目的のために、プロパティ *SQL*、*InsertSQL*、*UpdateSQL*、および *DeleteSQL* を使用する。
101+
102+## SQLをTSQLQueryの中でカスタマイズする
103+
104+通常、TSQLQuery は、上で説明したプロパティに基づいて汎用 SQL ステートメントを使用する。 ただし、sqldb によって作成された汎用 SQL は、状況によっては正しくない可能性がある。 TSQLQuery を使用すると、データベースの状況に応じて最適に機能するように、さまざまなアクションに使用される SQL ステートメントをカスタマイズできる。 この目的のために、プロパティ *SQL*、*InsertSQL*、*UpdateSQL*、および *DeleteSQL* を使用する。
105+
106+これらのすべてのプロパティは、複数行のSQLを受け入れるTStringList型のプロパティです。すべてのプロパティには、IDE内にプロパティエディタが付属してる。IDEでプロパティを選択し、省略ボタンをクリックしてエディタを開く。このエディタ（[[Database metadata|TSQLQuery metadata tool]]）では、テーブル情報などを参照することもできる。
107+
108+コード内では、例えば`InsertSQL.Text`や`InsertSQL.Add()`を使用して、SQLステートメントの行を設定したり追加したりします。1つのステートメントは複数行にまたがる場合があり、セミコロンで終わる。
109+
110+また、4 つのプロパティはすべて、以下で説明するパラメータを受け入れる。
111+
112+### SQL - 基本的なSQLカスタマイゼーション
113+
114+SQL プロパティは通常、データベースからデータをフェッチするために使用される。 このプロパティの汎用 SQL は `SELECT * FROM fpdev` です。`fpdev` はデータベースに設定されているテーブルである。
115+
116+汎用 SQL ステートメントによって返されるデータセットは、かなり粗いものになる。 結果を TDBGrid で表示すると、レコードの順序がランダムに見えたり、列の順序が意図したものと異なったり、フィールド名は技術的には正しいものの、ユーザーフレンドリーではない可能性がある。 カスタマイズされた SQL を使用すると、これを改善できる。 id、UserName、InstEmail 列を持つ fpdev というテーブルの場合、次のようなことができる:
117+
118+``` sql
119+SELECT id AS 'ID', UserName AS 'User', InstEmail AS 'e-mail' FROM fpdev ORDER BY id;
120+```
121+
122+上記のクエリの結果として得られるデータセットは、クエリで指定されたフィールド名 (ID、ユーザー、および電子メール)、クエリで指定された列の順序を使用し、レコードは ID によって並べ替えられる。
123+
124+### InsertSQL、UpdateSQL、DeleteSQL - パラメータの基本的な使用
125+
126+SQLQueryの**SQL**プロパティにSELECTクエリを割り当てると、SQLQueryはデータベースからデータを取得する方法を知っている。しかし、DBGridなどのデータバインドコントロールを使用する場合、SQLQueryはユーザーのアクションに基づいてデータベースから行を挿入、更新、削除する能力も必要となる。
127+
128+開発を加速するために、SQLQueryは必要なSQLステートメントを推測しようとすることができる。**SQL**プロパティが存在し、かつ**ParseSQL**プロパティがtrueである場合（デフォルトではtrueである）、SQLQueryは*SQL*プロパティを解析することでこれらのステートメントを生成しようとする。SQLDBはこれらのステートメントを**InsertSQL**、**UpdateSQL**、および**DeleteSQL**プロパティに格納する。
129+
130+しかし、生成されたステートメントが機能しない場合（例えば、自動インクリメント/オートナンバープライマリキーを持つテーブルに挿入する場合）や、非常に遅い場合がある。必要に応じて、ステートメントを手動で割り当てることができる。
131+
132+*InsertSQL*、*UpdateSQL*、*DeleteSQL*プロパティのステートメントは、データセット内のフィールドを表すパラメーターを受け入れる。 以下の規則が適用される:
133+
134+- パラメーター名は、データセットで使用されるフィールド名と完全に同じである必要がある。データセット内のフィールド名は、使用されるSELECTステートメントによってテーブル内の列名と異なる場合がある(上記を参照)。
135+- SQLDBの他のクエリのパラメータと同様に、パラメータ名はコロンを前置して記述する必要がある。
136+- 更新/削除ステートメントで使用する場合、データセットのフィールド名の前に**OLD_**(少なくともLazarus v. 1.0では**厳密に大文字**)を付けると、新しい値ではなく編集される前のレコードの値を取得できる。
137+
138+テーブルfpdev 、id、UserName、InstEmail というカラムがあり、データセットには ID、User、e-mail（select ステートメントの例を参照）というフィールドがリンクしている場合、このような **InsertSQL** クエリを書くことができる:
139+
140+``` sql
141+INSERT INTO fpdev(id, UserName, InstEmail) VALUES(:ID,:User,:e-mail);
142+```
143+
144+このステートメントは、データセットの現在のレコードから `ID`、`User`、`e-mail` の値を取得し、それぞれ `fpdev` テーブルの対応するフィールドに挿入しする。
145+
146+この例のステートメントは、実際には SQLDB 自体が自動生成するものとほぼ同じである。 与えられたステートメントは、`id` フィールドがユニークキー内の自動増分フィールドの場合、エラーが発生する可能性があります。さまざまなデータベースは、異なる方法でこの問題を解決している。例えば、次のような方法が MySQL で機能する。 異なるデータベースは、この問題を異なる方法で解決する。たとえば、次の方法は MySQL で機能する。
147+
148+``` sql
149+INSERT INTO fpdev(id, UserName, InstEmail) VALUES(0,:User,:e-mail)
150+ ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID();
151+```
152+
153+上記のステートメントは、新しいレコードを挿入しようとするが、`id`列には0（ゼロ）を使用する。 もしゼロが既にキーとして使用されている場合、重複が検出され、`id`は最後に挿入されたidを使用するように更新される。実際には、最後に使用されたidよりも1つ増えたidである。
154+
155+その文は、0を`id`列の値として新しいレコードを挿入しようとする。もし0がすでにキーとして使用されている場合、重複が検出され、`id`は最後に挿入されたidを使用するように更新される。実際には、最後に使用されたidよりも1つ大きいidが使用される。 Firebirdの場合、自動増分キーをエミュレートする場合[1](http://www.firebirdfaq.org/faq29/)、次のようなものが機能するはずである:
156+
157+``` sql
158+INSERT INTO fpdev(UserName, InstEmail) VALUES(:User,:e-mail);(
159+```
160+
161+このステートメントは、主キー以外のすべてを挿入し、Firebirdのbefore insertトリガーがジェネレータ/シーケンスを使用して`id`値を挿入する。 `INSERT`ステートメントでは、選択されたレコードの現在のフィールド値を使用したい場合がある。`UPDATE`ステートメントでは、編集前のフィールド値をWHERE句で使用する必要がある。前述のように、編集前のフィールド値は、フィールド名の前にOLD_が付いた形で書かれなければならない(Lazarus v. 1.0では、厳密に大文字である必要がある)。たとえば、次のクエリ:
162+
163+``` sql
164+UPDATE fpdev SET UserName=:User, InstEmail=:e-mail WHERE UserName=:OLD_User;
165+```
166+
167+上記のステートメントは、`User`が古い`User`値と等しいすべてのレコードの`UserName`および`InstEmail`列を更新する。
168+
169+DELETEステートメントで現在のフィールド値と古いフィールド値を使用する方法は、読者に宿題として残しておく。
170+
171+以下の公式ドキュメントも参照のこと:
172+
173+- [InsertSQL documentation](http://www.freepascal.org/docs-html/fcl/sqldb/tsqlquery.insertsql.html)
174+- [UpdateSQL documentation](http://www.freepascal.org/docs-html/fcl/sqldb/tsqlquery.updatesql.html)
175+- [DeleteSQL documentation](http://www.freepascal.org/docs-html/fcl/sqldb/tsqlquery.deletesql.html)
176+
177+### TSQLQuery.SQLにおけるパラメータ
178+
179+ほとんどの状況では、TSQLQueryのSQLプロパティには、通常パラメータが必要ない select ステートメントが含まれる。ただし、それらを含めることもできる。これにより、レコードをフィルタリングする非常に簡単で強力な方法が提供される。
180+
181+パラメータの利点は次のとおりである:
182+
183+- データをSQLテキスト、日付などの引数としてフォーマットする必要がないため、例えばMySQL用に日付をフォーマットする方法を覚える必要がない。これは、Firebirdの実装と異なる可能性がある。また、テキストデータをエスケープする必要もない。例えば、*O'Malley's "SQL Horror"*のようなテキストデータをエスケープする必要がない。
184+- パフォーマンスの向上の可能性
185+- SQLインジェクション攻撃からの保護
186+
187+パラメータの使用は、データベースのパフォーマンスを向上させることができる。ほとんどのデータベースはプリペアドステートメントをサポートしており、これはステートメントが準備され、データベースにキャッシュされることを意味する。プリペアドステートメントは複数回使用でき、使用するたびにステートメントの解析やクエリの計画が必要なく、パラメータのみが変更される。 同じステートメントが大量の回数使用される場合（パラメータの値のみが異なる場合）、プリペアドステートメントはパフォーマンスを大幅に向上させることができる。また、パラメータの使用により、SQLインジェクション攻撃を緩和することができる。
188+
189+*InsertSQL*、*UpdateSQL*、および *DeleteSQL* プロパティには、現在のフィールド値と古いフィールド値のための事前定義されたパラメータがある。しかし、*SQL* プロパティにはありません。*Params* プロパティで独自のパラメータを作成できる。
190+
191+#### Selectクエリの例
192+
193+この例は、パラメータを使用してデータを選択する方法を示している。また、SQL内でのエイリアス(... AS ...)の使用も示している。
194+
195+``` pascal
196+ sql_temp.sql.text := 'SELECT id AS ''ID'', UserName AS ''User'', InstEmail AS ''e-mail'' FROM fpdev WHERE InstEmail=:emailsearch;'
197+ ...
198+ //これは、emailsearchというパラメータを生成する。
199+
200+ // もし必要なら、パラメータがどのような種類のパラメータかを明示的に設定することもできる...
201+ // これは、FPCが誤って推測した場合にのみ必要になるかもしれない:
202+ // sql_temp.params.parambyname('emailsearch').datatype:=ftWideString
203+
204+ //これでパラメータ値を入れることができる:
205+ sql_temp.params.parambyname('emailsearch').asstring := 'mvancanneyt@freepascal.org';
206+ ...
207+ //そして、いつもの通りデータを引き出す
208+ //場合によっては、パラメータ値を変え、再び実行する。
209+```
210+
211+#### Insertクエリの例
212+
213+この例は、パラメータを使用してテーブルに新しいレコードを挿入する方法を示している:
214+
215+``` pascal
216+ sql_temp.sql.text := 'insert into PRODUCTS (ITEMNR,DESCRIPTION) values (:OURITEMNR,:OURDESCRIPTION)'
217+ ...
218+ sql_temp.Params.ParamByName('OURITEMNR').AsString := 'XXXX';
219+ sql_temp.Params.ParamByName('OURDESCRIPTION').AsString := 'description';
220+ sql_temp.ExecSQL;
221+ SQLTransaction1.Commit; //もしくは、CommitRetainingアプリケーションの設定による
222+```
223+
224+別の方法でこれをするには、以下のように:
225+
226+``` pascal
227+ tsqlquery1.appendrecord(['XXXX', 'description'])
228+ tsqltransaction1.commit; //もしくは CommitRetaining
229+```
230+
231+### フォーマット関数を伴なうクエリ
232+
233+パラメータ化されたクエリの使用が好ましいアプローチだが、一部の状況では format 関数が代替手段となりうる(下記の警告を参照)。たとえば、接続の ExecuteDirect プロシージャでステートメントを実行する場合は、パラメータを使用することができない(*もちろん、問題のある SQL ステートメントを実行するためにクエリを使用することもできる*)。その場合、format 関数が役立つ:
234+
235+``` pascal
236+procedure InsertRecord
237+var
238+ aSQLText: string;
239+ aSQLCommand: string;
240+begin
241+ aSQLText:= 'INSERT INTO products(item_no, description) VALUES(%d, %s)';
242+ aSQLCommand:= Format(aSQLText, [strtoint(Edit1.Text), Edit2.Text]);
243+ aConnection.ExecuteDirect(aSQLCommand);
244+ aTransaction.Commit;
245+end;
246+```
247+
248+変数の値が変更されると、クエリの値もそれに応じて変更される。これは、パラメータ化されたクエリと同様である。
249+
250+パラメータ%dは整数、%sは文字列などに使用される。詳細については、Format 関数のドキュメントを参照のこと。
251+
252+> **Warning:** このテクニックでは、'を含むテキストや日付に関する問題に遭遇する可能性があることに注意すること!
253+
254+## 独自のSQLを実行しメタデータを得る
255+
256+一部の SQL ステートメントのチェック、トラブルシューティング、またはデータベースからのメタデータ (テーブルのリストなど) の取得だけを行いたい場合は、IDE 内から行うことができる。 プログラムでは、設計時に T*Connection、トランザクション、クエリ オブジェクトなどを設定し、クエリ オブジェクトの SQL プロパティに移動し、\[...\] ボタンをクリックする。
257+
258+SQL コードを含むウィンドウが表示され、次のようなステートメントを実行できる。
259+
260+``` sql
261+SELECT * FROM EMPLOYEE
262+```
263+
264+by pressing the play icon:
265+
266+![](/wiki-images/f/f2/Runsqlstatements.png)
267+
268+テーブル名、列名などのメタデータも取得できる (sqldb コネクタがそれをサポートしているが、最近ではほとんどのコネクタがサポートしている場合):
269+
270+![](/wiki-images/c/c9/Sqlquerymetadata.png)
271+
272+([[Database metadata|Database metadata#Lazarus TSQLQuery metadata toolも]]参照のこと)
273+
274+## 問題解決
275+
276+### ロギング
277+
278+詳細はここを参照のこと: [[SqlDBHowto/ja|SqlDBHowto/ja#問題解決法: TSQLConnection ログ]]
279+
280+### 芳しくない性能
281+
282+- データベースクエリを最適化し、適切なインデックスなどを使用する。クエリプランを提供するなど、データベースツールを使用すること。
283+- SQLQueryに進むときに、可能なパフォーマンス向上策については、以下の#エラー: メモリが足りない下を参照のこと。
284+
285+### エラーメッセージ
286+
287+### エラー: メモリが足りない
288+
289+TSQLQuery は BufDataset の派生クラスであり、受信したデータをメモリにバッファリングするデータセットである。多くのレコードを取得する場合(例えば、エクスポート用にそれらをループで処理する場合)、ヒープメモリがいっぱいになり(すでに処理したレコードで)、メモリ不足のエラーが発生する可能性がある。
290+
291+この状況は FPC 開発版で改善されているが、回避策として、クエリを開く前に Unidirectional プロパティを true に設定して、既に読み取ったレコードを破棄するように bufdataset に指示することができる。
292+
293+``` pascal
294+MySQLQuery.UniDirectional:=True;
295+```
296+
297+これでパフォーマンスも改善するかもしれない。
298+
299+### データセットは読み取り専用
300+
301+これは、FPCが更新可能であるとは認識していないが、実際には更新可能なクエリを指定した場合に発生する可能性がある。
302+
303+例:
304+
305+``` sql
306+select p.dob, p.surname, p.sex from people p;
307+```
308+
309+FPCのSQLパーサーはかなり単純であり、FROM句にコンマやスペースがあると、複数のテーブルが関与していると見なしてデータセットを読み取り専用に設定する。その弁護として、通常、1つのテーブルのみが関与している場合、テーブルにエイリアスを付けることは行われない 解決策：クエリを書き直すか、独自の`InsertSQL`、`UpdateSQL`、`DeleteSQL`を指定する。
310+
311+---
312+*From the Free Pascal wiki, page "Working With TSQLQuery/ja", as it stood on 2024-04-02 after a change by Ariben, out of the wiki's dump of 2024-05-26. Copyright stays with the author who wrote it, according to the old wiki's [[Licensing]] page. If you have concerns about this material, [message the admin](/wiki-contact).*

Back to history