Difference between revisions of "Working With TSQLQuery/ja"

From Free Pascal wiki
Jump to navigationJump to search
 
(25 intermediate revisions by the same user not shown)
Line 5: Line 5:
 
{{Japanese Menu}}
 
{{Japanese Menu}}
  
−
== 一般的な事柄 ==
 
−
TSQLQuery is an object that can embody a dataset coming from a database (RDBMS that uses SQL, such as Firebird, MS SQL Server, Oracle...). Using a SELECT SQL statement in the TSQLQuery's SQL property, you can determine what data is retrieved from the database into the dataset. When the dataset is changed by the program (or user), the changes can be submitted back to the database.
 
  
−
A TSQLQuery can also be used to directly modify data: if you specify the desired INSERT, UPDATE, DELETE etc SQL statement in the SQL property and call the ExecSQL method of the TSQLQuery, the query object will send the SQL to the database without retrieving any results.
+
== 概要 ==
 +
TSQLQuery は、データベース (Firebird、MS SQL Server、Oracle などの SQL を使用する RDBMS) からのデータセットを具体化できるオブジェクトである。 TSQLQuery の SQL プロパティで SELECT SQL ステートメントを使用すると、どのデータがデータベースからデータセットに取得されるかを決定できる。 プログラム (またはユーザー) によってデータセットが変更されると、その変更をデータベースに送信し返すことができる。
  
−
Apart from its use in FPC, Lazarus also provides a component: [[TSQLQuery]] [[image:tsqlquery.png]]  
+
TSQLQuery は、データを直接変更するために使用することもできる。SQL プロパティで目的の INSERT、UPDATE、DELETE などの SQL ステートメントを指定し、TSQLQuery の ExecSQL メソッドを呼び出すと、クエリ オブジェクトは何の結果も取得せずに SQL をデータベースに送信する。。
 +
 
 +
FPC での使用とは別に、Lazarus は次のコンポーネントも提供する: [[TSQLQuery/ja]] [[image:tsqlquery.png]]
  
 
== 公式ドキュメント ==
 
== 公式ドキュメント ==
−
See [http://www.freepascal.org/docs-html/fcl/sqldb/tsqlquery.html TSQLQuery documentation]
+
[http://www.freepascal.org/docs-html/fcl/sqldb/tsqlquery.html TSQLQuery documentation]を参照のこと。
  
−
A lot of context-sensitive documentation is now available in Lazarus. Unfortunately, TSQLQuery does not appear in the index of Lazarus 1.0 help. If you place your cursor on TSQLQuery methods and properties, try pressing F1 to see if that code is documented; e.g. this will work:
+
多くの状況感知型ドキュメントが Lazarus で利用できるようになった。 残念ながら、TSQLQuery は Lazarus 1.0 ヘルプの索引には表示されない。 TSQLQuery のメソッドとプロパティにカーソルを置いたら、F1 キーを押して、そのコードが文書化されているかどうかを確認されたい。 例えば これはうまくいく:
 
<syntaxhighlight lang=pascal>
 
<syntaxhighlight lang=pascal>
 
var
 
var
 
Q: TSQLQuery
 
Q: TSQLQuery
 
...
 
...
−
   Q.Open; //<--- place cursor on Open and press F1
+
   Q.Open; //<--- カーソルを Openの上において F1を押す
 
</syntaxhighlight>
 
</syntaxhighlight>
  
 
== 一般的に用いられるコントロール ==
 
== 一般的に用いられるコントロール ==
−
The dataset returned by TSQLQuery can conveniently be ''viewed'' with an instance of [[TDBGrid]], but it is not very suitable for ''editing'' the data in the individual fields and cells.
+
TSQLQuery によって返されたデータセットは、[[TDBGrid/ja]] のインスタンスを使用して便利に''表示''できるが、個々のフィールドやセルのデータを''編集''するにはあまり適していない。または、この目的のために、[[TDBEdit/ja]] などのデータ対応、単一フィールド コントロールをフォームに配置し、その DataSource プロパティを使用されているデータ ソースに設定する必要がある。
−
For this purpose you need to place some Data-Aware single-field controls such as [[TDBEdit]] on your form, and set their DataSource poperty to the data source being used. The DataField property should be set to a named field (eg 'IDENTITY') or to some expression that returns a suitable string.
+
DataField プロパティは、名前付きフィールド (「IDENTITY」など) または適切な文字列を返す式に設定する必要がある。
  
−
Addition of a [[TDBNavigator]] toolbar makes it very easy to navigate through the records, and to select records for editing. When a record is selected by the toolbar or by moving the mouse through the data grid, the data for the relevant row and column appear in the TDBEdit box and if the 'Edit' button is clicked, the contents in the Edit box can be modified. Clicking on the 'Post' button confirms the change, or clicking on the 'Cancel' button cancels the changes.
+
[[TDBNavigator/ja]] ツールバーの追加により、レコード間の移動や、編集するレコードの選択が非常に簡単になる。
 +
ツールバーでレコードを選択するか、データ グリッド内でマウスを移動してレコードを選択すると、関連する行と列のデータが TDBEdit ボックスに表示され、「編集」ボタンをクリックすると、編集ボックスの内容を変更できる。「投稿」ボタンをクリックすると変更が確定し、「キャンセル」ボタンをクリックすると変更がキャンセルされる。
  
−
In general, the process is as follows:
+
一般に、プロセスは次のとおりである:
−
# Drop a [[TSQLQuery]] on a form/datamodule, and set the ''Database'', ''Transaction'' and ''SQL'' properties.
+
# [[TSQLQuery/ja]] をフォーム/データモジュールに配置し、''Database''、''Transaction''、および ''SQL'' プロパティを設定する。
−
# Drop a [[TDataSource]] component, and set its ''DataSet'' property to the TSQLQuery instance.
+
# [[TDataSource/ja]] コンポーネントを配置し、その ''DataSet'' プロパティを TSQLQuery インスタンスに設定する。
−
# Drop a [[TDBGrid]] on the form and set its ''DataSource'' property to the TDataSource instance.
+
# [[TDBGrid/ja]] をフォームに配置し、その ''DataSource'' プロパティを TDataSource インスタンスに設定する。
−
# Optionally, drop a TDBNavigator instance on the form,  and set its ''Datasource'' property to the TDatasource instance.
+
# 必要に応じて、TDBNavigator インスタンスをフォームに配置し、その ''Datasource'' プロパティを TDatasource インスタンスに設定する。この後、''Active''プロパティを''True''に設定すると、クエリによって取得されたデータを確認できるようはずである。
−
After this, the ''Active'' property can be set to ''True'', and it should be possible to see the data retrieved by the query.
+
(''TSQLConnection''コンポーネントと''TSQLTransaction''コンポーネントの両方がアクティブである場合)
−
(provided both the ''TSQLConnection'' and ''TSQLTransaction'' components are active)
 
  
 
== データを更新する ==  
 
== データを更新する ==  
 +
レコードを DELETE または変更できるようにする必要がある場合、データベース テーブルは次のいずれかを行う必要がある
 +
# PRIMARY KEY 列が 1 つ含まれている。
 +
# レコードを一意に決定する一連のフィールドがある。 通常、これらは一意のインデックスの一部である必要がある。これは必須ではないが、クエリが大幅に高速化される。
  
−
If you need to be able to DELETE or otherwise modify records, your database table should either
+
主フィールドがない場合、またはレコードを一意に決定するフィールドがない場合は、PRIMARY KEY フィールドを追加する必要がある。
−
# contain one PRIMARY KEY column.
+
これは、テーブル構造の設計時、作成時に行うことが望ましいが、後で追加することもできる。
−
# have a set of fields that uniquely determine the record. Normally, they should be part of a unique index. This is not required, but will speed up the queries quite a lot
 
−
 
 
−
If there is no primary field, or no fields that uniquely determine your record, then a primary key field should be added.
 
−
This is done preferably when the table structure is designed, at CREATE time, but can be added at a later time.
 
  
−
For instance the following example code in your MySQL client will add a unique index to your table:
+
たとえば、MySQL クライアントの次のコード例は、テーブルに一意のインデックスを追加する:
 
<syntaxhighlight lang="sql">
 
<syntaxhighlight lang="sql">
 
alter table testrig  
 
alter table testrig  
Line 52: Line 52:
 
primary key auto_increment;
 
primary key auto_increment;
 
</syntaxhighlight>
 
</syntaxhighlight>
−
Adding this field will not hurt, and will allow your applications to update the field.
+
このフィールドを追加しても害はなく、アプリケーションでフィールドを更新できるようになる。
  
 
== キャッシュされた更新 ==
 
== キャッシュされた更新 ==
−
The ''TSQLQuery'' component caches all updates. That is, the updates are not sent immediately to the database, but are kept in memory till the ''ApplyUpdates'' method is called. At that point, the updates will be transformed to SQL update statements, and will be applied to the database. If you do not call ''ApplyUpdates'', the database will not be updated with the local changes.
+
''TSQLQuery''コンポーネントはすべての更新をキャッシュする。 つまり、更新はデータベースにすぐには送信されないが、''ApplyUpdates'' メソッドが呼び出されるまでメモリ内に保持される。 その時点で、更新は SQL 更新ステートメントに変換され、データベースに適用される。 ''ApplyUpdates'' を呼び出さない場合、データベースはローカルの変更で更新されない。
  
 
== プライマリキーフィールド ==
 
== プライマリキーフィールド ==
  
−
When updating records, ''TSQLQuery'' needs to know which fields comprise the primary key that can be used to update the record,
+
レコードを更新するとき、''TSQLQuery'' は、レコードの更新に使用できる主キーを構成するフィールドを知る必要がある。
−
and which fields should be updated: based on that information, it constructs an SQL UPDATE, INSERT or DELETE command.
+
どのフィールドを更新する必要があるか: その情報に基づいて、SQL の UPDATE、INSERT、または DELETE コマンドを構築する。
  
−
The construction of the SQL statement is controlled by the ''UsePrimaryKeyAsKey'' property and the ''ProviderFlags'' properties.
+
SQL ステートメントの構築は、''UsePrimaryKeyAsKey'' プロパティと ''ProviderFlags'' プロパティによって制御されます。
  
−
The ''Providerflags'' property is a set of 3 flags:
+
''Providerflags''プロパティは、次の 3 つのフラグのセットである:
−
; pfInkey: The field is part of the primary key
+
; pfInkey: フィールドはプライマリキーの一部である。
−
; pfInWhere: The field should be used in the WHERE clause of SQL statements.
+
; pfInWhere: このフィールドは SQL ステートメントの WHERE 句で使用する必要がある。
−
; pfInUpdate: Updates or inserts should include this field.
+
; pfInUpdate: 更新または挿入には、このフィールドを含める必要がある。
  
−
By default, ''ProviderFlags'' consists of ''pfInUpdate'' only.
+
デフォルトでは、 ''ProviderFlags''は ''pfInUpdate'' のみからなっている。
  
−
If your table has a primary key (as described above) then you only need to set the ''UsePrimaryKeyAsKey'' property to ''True'' and
+
テーブルに主キーがある場合 (上記のとおり)、''UsePrimaryKeyAsKey'' プロパティを ''True'' に設定するだけですべてが行われます。これにより、主キー フィールドに ''pfInKey'' フラグが設定される。
−
everything will be done for you. This will set the ''pfInKey'' flag for the primary key fields.
 
  
−
If the table doesn't have a primary key index, but does have some fields that can be used to uniquely identify the record, then
+
テーブルに主キー インデックスがないものの、レコードを一意に識別するために使用できるフィールドがいくつかある場合は、レコードを一意に決定するすべてのフィールドの ''ProviderFlags'' プロパティに ''pfInKey'' オプションを含めることができる。
−
you can include the ''pfInKey'' option in the ''ProviderFlags'' property all the fields that uniquely determine the record.
 
  
−
The ''UpdateMode'' property will then determine which fields exactly will be used in the WHERE clause:
+
''UpdateMode'' プロパティは、どのフィールドが WHERE 句で正確に使用されるかを決定する:
  
−
; upWhereKeyOnly: When ''TSQLQuery'' needs to construct a WHERE clause for the update, it will collect all fields that have the ''pfInKey'' flag in their ''ProviderFlags'' property set, and will use the values to construct a WHERE clause which uniquely determines the record to update -- normally this is only needed for an UPDATE or DELETE statement.
+
; upWhereKeyOnly: TSQLQueryが更新操作のためにWHERE句を構築する際に使用される。このオプションを指定すると、TSQLQueryは更新操作を行うための一意なレコードを特定するために、ProviderFlagsプロパティがpfInKeyフラグを持つすべてのフィールドの値を収集する。通常、これはUPDATEまたはDELETEステートメントでのみ必要である。
 +
;upWhereChanged: pfInKeyを持つフィールドだけでなく、ProviderFlagsプロパティにpfInWhereを持ち、かつ変更されたすべてのフィールドがWHERE句に含まれるようにする。
 +
; upWhereAll: pfInKeyを持つフィールドに加えて、ProviderFlagsプロパティにpfInWhereを持つすべてのフィールドもWHERE句に含まれるようにする。
  
−
; upWhereChanged: In addition to the fields that have ''pfInKey'' in the ''ProviderFlags'' property, all fields that have ''pfInWhere'' in their ''ProviderFlags'' and that have changed, will also be included in the WHERE clause.
+
== 更新をコントロールする ==
 +
特定のフィールドを更新するように指定することができる: 前述の通り、''ProviderOptions''プロパティに''pfInUpdate''を持つフィールドのみがSQL UPDATEまたはINSERTステートメントに含まれる。デフォルトでは、''pfInUpdate''は常に''ProviderOptions''プロパティに含まれる。
  
−
; upWhereAll: In addition to the fields that have ''pfInKey'' in the ''ProviderFlags'' property, all fields that have ''pfInWhere'' in their ''ProviderFlags'', will also be included in the WHERE clause.
+
== SQLをTSQLQueryの中でカスタマイズする ==
−
 
+
通常、TSQLQuery は、上で説明したプロパティに基づいて汎用 SQL ステートメントを使用する。 ただし、sqldb によって作成された汎用 SQL は、状況によっては正しくない可能性がある。 TSQLQuery を使用すると、データベースの状況に応じて最適に機能するように、さまざまなアクションに使用される SQL ステートメントをカスタマイズできる。 この目的のために、プロパティ ''SQL''、''InsertSQL''、''UpdateSQL''、および ''DeleteSQL'' を使用する。
−
== 更新をコントロールする ==
 
−
It is possible to specify which fields should be updated: As mentioned above: Only fields that have ''pfInUpdate'' in their ''ProviderOptions'' property will be included in the SQL UPDATE or INSERT statements. By default, ''pfInUpdate'' is always included in the ''ProviderOptions'' property.
 
  
 
== SQLをTSQLQueryの中でカスタマイズする ==
 
== SQLをTSQLQueryの中でカスタマイズする ==
−
Normally TSQLQuery will use generic SQL statements based on properties as discussed above. However, the generic SQL created by sqldb may not be correct for your situation. TSQLQuery allows you to customize SQL statements used for the various actions, to work best in your situation with your database. For this purpose you use the properties ''SQL'', ''InsertSQL'', ''UpdateSQL'' and ''DeleteSQL''.
+
通常、TSQLQuery は、上で説明したプロパティに基づいて汎用 SQL ステートメントを使用する。 ただし、sqldb によって作成された汎用 SQL は、状況によっては正しくない可能性がある。 TSQLQuery を使用すると、データベースの状況に応じて最適に機能するように、さまざまなアクションに使用される SQL ステートメントをカスタマイズできる。 この目的のために、プロパティ ''SQL''、''InsertSQL''、''UpdateSQL''、および ''DeleteSQL'' を使用する。
  
−
All these properties are of type TStringList, a list of strings, that accepts multiple lines of SQL. All four come with a property editor in the IDE. In the IDE, select the property and open the editor by clicking the ellipsis button. In this editor ([[Database_metadata#Lazarus_TSQLQuery_metadata_tool|TSQLQuery metadata tool]]), you may also look up table information etc.
+
これらのすべてのプロパティは、複数行のSQLを受け入れるTStringList型のプロパティです。すべてのプロパティには、IDE内にプロパティエディタが付属してる。IDEでプロパティを選択し、省略ボタンをクリックしてエディタを開く。このエディタ（[[Database_metadata#Lazarus_TSQLQuery_metadata_tool|TSQLQuery metadata tool]]）では、テーブル情報などを参照することもできる。
  
−
In code, use for example <tt>InsertSQL.Text</tt> or <tt>InsertSQL.Add()</tt> to set or add lines of SQL statements. One statement may span several lines and ends with a semicolon.
+
コード内では、例えば<tt>InsertSQL.Text</tt>や<tt>InsertSQL.Add()</tt>を使用して、SQLステートメントの行を設定したり追加したりします。1つのステートメントは複数行にまたがる場合があり、セミコロンで終わる。
  
−
Also, all four properties accept parameters explained further below.
+
また、4 つのプロパティはすべて、以下で説明するパラメータを受け入れる。
  
 
===SQL - 基本的なSQLカスタマイゼーション===
 
===SQL - 基本的なSQLカスタマイゼーション===
−
The SQL property is normally used to fetch the data from the database. The generic SQL for this property is <tt>SELECT * FROM fpdev</tt> where <tt>fpdev</tt> is the table as set in the database.
+
SQL プロパティは通常、データベースからデータをフェッチするために使用される。 このプロパティの汎用 SQL は <tt>SELECT * FROM fpdev</tt> です。<tt>fpdev</tt> はデータベースに設定されているテーブルである。
  
−
The dataset returned by the generic SQL statement will be kind of rough. If you show the result in a TDBGrid, the order of the records may seem random, the order of the columns may not be what you want and the field names may be technically correct but not user friendly. Using customized SQL you can improve this.
+
汎用 SQL ステートメントによって返されるデータセットは、かなり粗いものになる。 結果を TDBGrid で表示すると、レコードの順序がランダムに見えたり、列の順序が意図したものと異なったり、フィールド名は技術的には正しいものの、ユーザーフレンドリーではない可能性がある。 カスタマイズされた SQL を使用すると、これを改善できる。
−
For a table called fpdev with columns id, UserName and InstEmail, you could do something like this:
+
id、UserName、InstEmail 列を持つ fpdev というテーブルの場合、次のようなことができる:
−
<syntaxhighlight lang="sql">SELECT id AS 'ID', UserName AS 'User', InstEmail AS 'e-mail' FROM fpdev ORDER BY id;</syntaxhighlight>
+
<syntaxhighlight lang="sql">SELECT id AS 'ID', UserName AS 'User', InstEmail AS 'e-mail' FROM fpdev ORDER BY id;</syntaxhighlight>上記のクエリの結果として得られるデータセットは、クエリで指定されたフィールド名 (ID、ユーザー、および電子メール)、クエリで指定された列の順序を使用し、レコードは ID によって並べ替えられる。
  
−
The dataset that results from the above query uses the field names as given in the query (ID, User and e-mail), the column order as given in the query and the records are sorted by their id.
+
=== InsertSQL、UpdateSQL、DeleteSQL - パラメータの基本的な使用===
 +
SQLQueryの'''SQL'''プロパティにSELECTクエリを割り当てると、SQLQueryはデータベースからデータを取得する方法を知っている。しかし、DBGridなどのデータバインドコントロールを使用する場合、SQLQueryはユーザーのアクションに基づいてデータベースから行を挿入、更新、削除する能力も必要となる。
  
−
=== InsertSQL、UpdateSQL、DeleteSQL - パラメータの基本的な使用===
+
開発を加速するために、SQLQueryは必要なSQLステートメントを推測しようとすることができる。'''SQL'''プロパティが存在し、かつ'''ParseSQL'''プロパティがtrueである場合（デフォルトではtrueである）、SQLQueryは''SQL''プロパティを解析することでこれらのステートメントを生成しようとする。SQLDBはこれらのステートメントを'''InsertSQL'''、'''UpdateSQL'''、および'''DeleteSQL'''プロパティに格納する。
−
When you assign a SELECT query to an SQLQuery's '''SQL''' property the SQLQuery knows how to get data from the database.
 
−
However, when using databound controls such as a DBGrid, SQLQuery will also need to be able to insert, update and delete rows from the database based on the user's actions.
 
  
−
In order to speed development, SQLQuery can try and deduce the required SQL statements. If the SQL property exists and the '''ParseSQL''' property is true (which it is by default), SQLQuery will try to generate these statements by parsing the '''SQL''' property.
+
しかし、生成されたステートメントが機能しない場合（例えば、自動インクリメント/オートナンバープライマリキーを持つテーブルに挿入する場合）や、非常に遅い場合がある。必要に応じて、ステートメントを手動で割り当てることができる。
−
SQLDB stores these statements in the '''InsertSQL''', '''UpdateSQL''' and '''DeleteSQL''' properties.
 
  
−
However, sometimes the generated statements will not work (e.g. when inserting in tables with auto-increment/autonumber primary keys) or will be very slow. If needed, you can manually assign the statements.
+
''InsertSQL''、''UpdateSQL''、''DeleteSQL''プロパティのステートメントは、データセット内のフィールドを表すパラメーターを受け入れる。
 +
以下の規則が適用される:
 +
* パラメーター名は、データセットで使用されるフィールド名と完全に同じである必要がある。データセット内のフィールド名は、使用されるSELECTステートメントによってテーブル内の列名と異なる場合がある(上記を参照)。
 +
* SQLDBの他のクエリのパラメータと同様に、パラメータ名はコロンを前置して記述する必要がある。
 +
* 更新/削除ステートメントで使用する場合、データセットのフィールド名の前に'''OLD_'''(少なくともLazarus v. 1.0では'''厳密に大文字''')を付けると、新しい値ではなく編集される前のレコードの値を取得できる。
  
−
The statements in the ''InsertSQL'', ''UpdateSQL'' and ''DeleteSQL'' properties accept parameters that represent fields in the dataset. The following rules apply:
+
テーブルfpdev 、id、UserName、InstEmail というカラムがあり、データセットには ID、User、e-mail（select ステートメントの例を参照）というフィールドがリンクしている場合、このような '''InsertSQL''' クエリを書くことができる:
−
* Parameter names must be exactly the same as the field names used in the dataset. The field names in the dataset may be different from the column names in the table, depending on the used select statement (see above).
 
−
* Just as parameters in other SQLDB queries, parameter names must be written preceded by a colon.
 
−
* For use in update/delete statements, precede the dataset field name with '''OLD_''' ('''strictly uppercase''', at least in Lazarus v. 1.0) to get the value of the record before it was edited instead of the new value.
 
  
−
If you have a table called fpdev and columns id, UserName and InstEmail, linked to a dataset with fields ID, User and e-mail (see example in select statement), you could write this '''InsertSQL''' query:
 
 
<syntaxhighlight lang="sql">INSERT INTO fpdev(id, UserName, InstEmail) VALUES(:ID,:User,:e-mail);</syntaxhighlight>
 
<syntaxhighlight lang="sql">INSERT INTO fpdev(id, UserName, InstEmail) VALUES(:ID,:User,:e-mail);</syntaxhighlight>
−
This statement will insert the values of <tt>ID</tt>, <tt>User</tt> and <tt>e-mail</tt> from the current record of the dataset into the respective fields of table <tt>fpdev</tt>.
+
このステートメントは、データセットの現在のレコードから <tt>ID</tt>、<tt>User</tt>、<tt>e-mail</tt> の値を取得し、それぞれ <tt>fpdev</tt> テーブルの対応するフィールドに挿入しする。
  
−
This example statement is actually more or less what SQLDB itself would autogenerate. The given statement may result in errors when the <tt>id</tt> field is an auto-increment field in a unique key. Different databases solve this problem in different ways. For example, the following works for MySQL.
+
この例のステートメントは、実際には SQLDB 自体が自動生成するものとほぼ同じである。 与えられたステートメントは、<tt>id</tt> フィールドがユニークキー内の自動増分フィールドの場合、エラーが発生する可能性があります。さまざまなデータベースは、異なる方法でこの問題を解決している。例えば、次のような方法が MySQL で機能する。 異なるデータベースは、この問題を異なる方法で解決する。たとえば、次の方法は MySQL で機能する。
 
<syntaxhighlight lang="sql">INSERT INTO fpdev(id, UserName, InstEmail) VALUES(0,:User,:e-mail)
 
<syntaxhighlight lang="sql">INSERT INTO fpdev(id, UserName, InstEmail) VALUES(0,:User,:e-mail)
 
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID();</syntaxhighlight>
 
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID();</syntaxhighlight>
−
The above statement tries to insert a new record using 0 (zero) for the <tt>id</tt> column. If zero is already used as a key, then a duplicate is detected and the <tt>id</tt> is updated to use the last inserted id. Well, actually an id one increment higher than the last one used.
 
  
−
For Firebird, if you emulate autoincrement keys [http://www.firebirdfaq.org/faq29/] something like this should work:
+
上記のステートメントは、新しいレコードを挿入しようとするが、<tt>id</tt>列には0（ゼロ）を使用する。 もしゼロが既にキーとして使用されている場合、重複が検出され、<tt>id</tt>は最後に挿入されたidを使用するように更新される。実際には、最後に使用されたidよりも1つ増えたidである。
 +
 
 +
 
 +
その文は、0を<tt>id</tt>列の値として新しいレコードを挿入しようとする。もし0がすでにキーとして使用されている場合、重複が検出され、<tt>id</tt>は最後に挿入されたidを使用するように更新される。実際には、最後に使用されたidよりも1つ大きいidが使用される。
 +
Firebirdの場合、自動増分キーをエミュレートする場合[http://www.firebirdfaq.org/faq29/]、次のようなものが機能するはずである:
 
<syntaxhighlight lang="sql">INSERT INTO fpdev(UserName, InstEmail) VALUES(:User,:e-mail);(</syntaxhighlight>
 
<syntaxhighlight lang="sql">INSERT INTO fpdev(UserName, InstEmail) VALUES(:User,:e-mail);(</syntaxhighlight>
−
The statement inserts everything except the primary key and lets the Firebird before insert trigger use a generator/sequence to insert an <tt>id</tt> value for you.
+
このステートメントは、主キー以外のすべてを挿入し、Firebirdのbefore insertトリガーがジェネレータ/シーケンスを使用して<tt>id</tt>値を挿入する。
−
 
+
<tt>INSERT</tt>ステートメントでは、選択されたレコードの現在のフィールド値を使用したい場合がある。<tt>UPDATE</tt>ステートメントでは、編集前のフィールド値をWHERE句で使用する必要がある。前述のように、編集前のフィールド値は、フィールド名の前にOLD_が付いた形で書かれなければならない(Lazarus v. 1.0では、厳密に大文字である必要がある)。たとえば、次のクエリ:
−
For an <tt>INSERT</tt> statement you may want to use the current field values of the selected record. For <tt>UPDATE</tt> statements, you will want to use the field values as they were before editing in the <tt>WHERE</tt> clause. As mentioned before, the field values before editing must be written as the field name precede by '''OLD_''' ('''strictly uppercase''', at least in Lazarus v. 1.0). For example, this query:
 
 
<syntaxhighlight lang="sql">UPDATE fpdev SET UserName=:User, InstEmail=:e-mail WHERE UserName=:OLD_User;</syntaxhighlight>
 
<syntaxhighlight lang="sql">UPDATE fpdev SET UserName=:User, InstEmail=:e-mail WHERE UserName=:OLD_User;</syntaxhighlight>
−
The above statement updates the <tt>UserName</tt> and <tt>InstEmail</tt> columns of all records where <tt>User</tt> equals the old <tt>User</tt> value.
+
上記のステートメントは、<tt>User</tt>が古い<tt>User</tt>値と等しいすべてのレコードの<tt>UserName</tt>および<tt>InstEmail</tt>列を更新する。
  
−
We leave it as an exercise to the reader to use the current field values and the old field values in DELETE statements.
+
DELETEステートメントで現在のフィールド値と古いフィールド値を使用する方法は、読者に宿題として残しておく。
  
−
See also the official documentation:
+
以下の公式ドキュメントも参照のこと:
 
* [http://www.freepascal.org/docs-html/fcl/sqldb/tsqlquery.insertsql.html InsertSQL documentation]
 
* [http://www.freepascal.org/docs-html/fcl/sqldb/tsqlquery.insertsql.html InsertSQL documentation]
 
* [http://www.freepascal.org/docs-html/fcl/sqldb/tsqlquery.updatesql.html UpdateSQL documentation]
 
* [http://www.freepascal.org/docs-html/fcl/sqldb/tsqlquery.updatesql.html UpdateSQL documentation]
Line 145: Line 144:
  
 
===TSQLQuery.SQLにおけるパラメータ===
 
===TSQLQuery.SQLにおけるパラメータ===
−
In most situations, the SQL property of TSQLQuery will contain the select statement which in most situations doesn't need parameters. However, it can contain them. This allows a very easy and powerful way to filter your records.
+
ほとんどの状況では、TSQLQueryのSQLプロパティには、通常パラメータが必要ない select ステートメントが含まれる。ただし、それらを含めることもできる。これにより、レコードをフィルタリングする非常に簡単で強力な方法が提供される。
  
−
Parameters have the following advantages:
+
パラメータの利点は次のとおりである:
−
* no need to format your data as SQL text, date etc arguments (i.e. no need to remember how to format a date for MySQL, which might differ from the Firebird implementation; no need to escape text data like ''O'Malley's "SQL Horror"''
+
* データをSQLテキスト、日付などの引数としてフォーマットする必要がないため、例えばMySQL用に日付をフォーマットする方法を覚える必要がない。これは、Firebirdの実装と異なる可能性がある。また、テキストデータをエスケープする必要もない。例えば、''O'Malley's "SQL Horror"''のようなテキストデータをエスケープする必要がない。
−
* possibly increased performance
+
* パフォーマンスの向上の可能性
−
* protection against SQL injection attacks
+
* SQLインジェクション攻撃からの保護
  
 +
パラメータの使用は、データベースのパフォーマンスを向上させることができる。ほとんどのデータベースはプリペアドステートメントをサポートしており、これはステートメントが準備され、データベースにキャッシュされることを意味する。プリペアドステートメントは複数回使用でき、使用するたびにステートメントの解析やクエリの計画が必要なく、パラメータのみが変更される。
 +
同じステートメントが大量の回数使用される場合（パラメータの値のみが異なる場合）、プリペアドステートメントはパフォーマンスを大幅に向上させることができる。また、パラメータの使用により、SQLインジェクション攻撃を緩和することができる。
  
−
The use of parameters may help performance of the database. Most databases support prepared statements, which means that the statement is prepared and cached in the database. A prepared statement can be used more than once and doesn't require parsing and query planning every time it is used, only the parameters are changed each time it is used.
+
''InsertSQL''、''UpdateSQL''、および ''DeleteSQL'' プロパティには、現在のフィールド値と古いフィールド値のための事前定義されたパラメータがある。しかし、''SQL'' プロパティにはありません。''Params'' プロパティで独自のパラメータを作成できる。
−
In situations where the same statement is used a large number of times (where only the values of the parameters differ), prepared statements can help performance considerably.
 
−
Additionally, SQL injection attacks can be mitigated by use of parameters.
 
−
 
 
−
The ''InsertSQL'', ''UpdateSQL'' and ''DeleteSQL'' properties have predefined parameters for current and old field values. However, the ''SQL'' property does not. You can create your own parameters in the ''Params'' property.
 
  
 
====Selectクエリの例====
 
====Selectクエリの例====
−
This example shows how to select data using parameters. It also demonstrates using aliases (... AS ...) in SQL.
+
この例は、パラメータを使用してデータを選択する方法を示している。また、SQL内でのエイリアス(... AS ...)の使用も示している。
 
<syntaxhighlight lang=pascal>
 
<syntaxhighlight lang=pascal>
 
  sql_temp.sql.text := 'SELECT id AS ''ID'', UserName AS ''User'', InstEmail AS ''e-mail'' FROM fpdev WHERE InstEmail=:emailsearch;'
 
  sql_temp.sql.text := 'SELECT id AS ''ID'', UserName AS ''User'', InstEmail AS ''e-mail'' FROM fpdev WHERE InstEmail=:emailsearch;'
 
  ...
 
  ...
−
  //This will create a parameter called emailsearch.
+
  //これは、emailsearchというパラメータを生成する。
  
−
  //If we want to, we can explicitly set what kind of parameter it is... which might only be necessary if FPC guesses wrong:
+
  // もし必要なら、パラメータがどのような種類のパラメータかを明示的に設定することもできる...
−
  //sql_temp.params.parambyname('emailsearch').datatype:=ftWideString
+
// これは、FPCが誤って推測した場合にのみ必要になるかもしれない:
 +
  // sql_temp.params.parambyname('emailsearch').datatype:=ftWideString
 
   
 
   
−
  //We can now fill in the parameter value:
+
  //これでパラメータ値を入れることができる:
 
  sql_temp.params.parambyname('emailsearch').asstring := 'mvancanneyt@freepascal.org';
 
  sql_temp.params.parambyname('emailsearch').asstring := 'mvancanneyt@freepascal.org';
 
  ...
 
  ...
−
  //Then use your regular way to retrieve data,
+
  //そして、いつもの通りデータを引き出す
−
  //optionally change the parameter value & run it again
+
  //場合によっては、パラメータ値を変え、再び実行する。
 
</syntaxhighlight>
 
</syntaxhighlight>
  
 
====Insertクエリの例====
 
====Insertクエリの例====
−
This example shows how to insert a new record into the table using parameters:
+
この例は、パラメータを使用してテーブルに新しいレコードを挿入する方法を示している:
 
<syntaxhighlight lang=pascal>
 
<syntaxhighlight lang=pascal>
 
  sql_temp.sql.text := 'insert into PRODUCTS (ITEMNR,DESCRIPTION) values (:OURITEMNR,:OURDESCRIPTION)'
 
  sql_temp.sql.text := 'insert into PRODUCTS (ITEMNR,DESCRIPTION) values (:OURITEMNR,:OURDESCRIPTION)'
Line 184: Line 182:
 
  sql_temp.Params.ParamByName('OURDESCRIPTION').AsString := 'description';
 
  sql_temp.Params.ParamByName('OURDESCRIPTION').AsString := 'description';
 
  sql_temp.ExecSQL;
 
  sql_temp.ExecSQL;
−
  SQLTransaction1.Commit; //or possibly CommitRetaining, depending on how your application is set up
+
  SQLTransaction1.Commit; //もしくは、CommitRetainingアプリケーションの設定による
 
</syntaxhighlight>
 
</syntaxhighlight>
  
−
Another way of doing this is something like:
+
別の方法でこれをするには、以下のように:
 
<syntaxhighlight lang=pascal>
 
<syntaxhighlight lang=pascal>
 
  tsqlquery1.appendrecord(['XXXX', 'description'])  
 
  tsqlquery1.appendrecord(['XXXX', 'description'])  
−
  tsqltransaction1.commit; //or commitretaining
+
  tsqltransaction1.commit; //もしくは CommitRetaining
 
</syntaxhighlight>
 
</syntaxhighlight>
  
 
===フォーマット関数を伴なうクエリ===
 
===フォーマット関数を伴なうクエリ===
−
Using parameterized queries is the preferred approach, but in some situations the format function can be an alternative. (see warning below). For example, parameters can't be used when you execute statements with the connection's ExecuteDirect procedure (''of course, you can just as well use a query to run the SQL statement in question''). Then this can come in handy:
+
パラメータ化されたクエリの使用が好ましいアプローチだが、一部の状況では format 関数が代替手段となりうる(下記の警告を参照)。たとえば、接続の ExecuteDirect プロシージャでステートメントを実行する場合は、パラメータを使用することができない('' もちろん、問題のある SQL ステートメントを実行するためにクエリを使用することもできる '')。その場合、format 関数が役立つ:
  
 
<syntaxhighlight lang=pascal>
 
<syntaxhighlight lang=pascal>
Line 209: Line 207:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
−
The values of the variables can change and the query values will change with them, just as with parameterized queries.
+
変数の値が変更されると、クエリの値もそれに応じて変更される。これは、パラメータ化されたクエリと同様である。
  
−
The parameter %d is used for integers, %s for strings; etc. See the documentation on the Format function for details.
+
パラメータ%dは整数、%sは文字列などに使用される。詳細については、Format 関数のドキュメントを参照のこと。
  
−
{{Warning|Be aware that you may run into issues with text containing ' and dates using this technique!}}
+
{{Warning|このテクニックでは、'を含むテキストや日付に関する問題に遭遇する可能性があることに注意すること!}}
  
 
== 独自のSQLを実行しメタデータを得る ==
 
== 独自のSQLを実行しメタデータを得る ==
−
If you want to just check some SQL statements, troubleshoot, or get metadata (e.g. list of tables) from the database, you can do so from within the IDE.
+
一部の SQL ステートメントのチェック、トラブルシューティング、またはデータベースからのメタデータ (テーブルのリストなど) の取得だけを行いたい場合は、IDE 内から行うことができる。
−
In your program, with your T*Connection, transaction, query object etc set up at design-time, go into the SQL property for the query object, then click the ... button.
+
プログラムでは、設計時に T*Connection、トランザクション、クエリ オブジェクトなどを設定し、クエリ オブジェクトの SQL プロパティに移動し、[...] ボタンをクリックする。
  
−
You'll see a window with SQL code, and you can run some statements like
+
SQL コードを含むウィンドウが表示され、次のようなステートメントを実行できる。
 
<syntaxhighlight lang="sql">
 
<syntaxhighlight lang="sql">
 
SELECT * FROM EMPLOYEE
 
SELECT * FROM EMPLOYEE
Line 227: Line 225:
 
[[Image:runsqlstatements.png]]
 
[[Image:runsqlstatements.png]]
  
−
You can also get metadata: table names, column names etc (if the sqldb connector supports it but most of them do nowadays):
+
テーブル名、列名などのメタデータも取得できる (sqldb コネクタがそれをサポートしているが、最近ではほとんどのコネクタがサポートしている場合):
  
 
[[Image:sqlquerymetadata.png]]
 
[[Image:sqlquerymetadata.png]]
  
−
(See also: [[Database metadata#Lazarus TSQLQuery metadata tool]])
+
([[Database metadata#Lazarus TSQLQuery metadata tool]]も参照のこと)
  
 
== 問題解決 ==
 
== 問題解決 ==
−
=== Logging ===
+
=== ロギング ===
−
See here: [[SqlDBHowto#Troubleshooting: TSQLConnection logging]] for more detail.
+
詳細はここを参照のこと: [[SqlDBHowto/ja#問題解決法: TSQLConnection ログ]]
−
=== Poor performance ===
+
 
−
* Make sure your database queries are optimized (use proper indexes etc). Use your database tools (e.g. providing query plans) for this.
+
=== 芳しくない性能 ===
−
* See [[#Out of memory errors]] below for possible performance improvements when moving forward through an SQLQuery.
+
* データベースクエリを最適化し、適切なインデックスなどを使用する。クエリプランを提供するなど、データベースツールを使用すること。
 +
* SQLQueryに進むときに、可能なパフォーマンス向上策については、以下の[[#エラー: メモリが足りない]]下を参照のこと。
  
 
=== エラーメッセージ ===
 
=== エラーメッセージ ===
 
=== エラー: メモリが足りない ===
 
=== エラー: メモリが足りない ===
−
TSQLQuery is a descendant of BufDataset, a dataset that buffers the data it receives into memory. If you retrieve a lot of records (e.g. when looping through them for an export), your heap memory may become full (with records you already looped through) and you will get out of memory errors.
+
TSQLQuery は BufDataset の派生クラスであり、受信したデータをメモリにバッファリングするデータセットである。多くのレコードを取得する場合(例えば、エクスポート用にそれらをループで処理する場合)、ヒープメモリがいっぱいになり(すでに処理したレコードで)、メモリ不足のエラーが発生する可能性がある。
  
−
Although this situation has improved in the FPC development version, a workaround is to tell bufdataset to discard the records you have already read by setting the Unidirectional property to true before opening the query:
+
この状況は FPC 開発版で改善されているが、回避策として、クエリを開く前に Unidirectional プロパティを true に設定して、既に読み取ったレコードを破棄するように bufdataset に指示することができる。
 
<syntaxhighlight lang=pascal>
 
<syntaxhighlight lang=pascal>
 
MySQLQuery.UniDirectional:=True;
 
MySQLQuery.UniDirectional:=True;
 
</syntaxhighlight>
 
</syntaxhighlight>
  
−
This may also improve performance.
+
これでパフォーマンスも改善するかもしれない。
  
 
=== データセットは読み取り専用 ===
 
=== データセットは読み取り専用 ===
−
This may happen if you specify a query that you know is updatable but FPC doesn't.
+
これは、FPCが更新可能であるとは認識していないが、実際には更新可能なクエリを指定した場合に発生する可能性がある。
  
 
例:
 
例:
Line 258: Line 257:
 
select p.dob, p.surname, p.sex from people p;
 
select p.dob, p.surname, p.sex from people p;
 
</syntaxhighlight>
 
</syntaxhighlight>
−
The SQL parser in FPC is quite simplistic and when it finds a comma or space in the FROM part it considers multiple tables are involved and sets the dataset to read only. To its defense, aliasing tables is usually not done when only one table is involved.
+
FPCのSQLパーサーはかなり単純であり、FROM句にコンマやスペースがあると、複数のテーブルが関与していると見なしてデータセットを読み取り専用に設定する。その弁護として、通常、1つのテーブルのみが関与している場合、テーブルにエイリアスを付けることは行われない
−
Solution: rewrite the query or specify your own <code>InsertSQL</code>, <code>UpdateSQL</code> and <code>DeleteSQL</code>.
+
解決策：クエリを書き直すか、独自の<code>InsertSQL</code>、<code>UpdateSQL</code>、<code>DeleteSQL</code>を指定する。

Latest revision as of 15:14, 2 April 2024

│ 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 "http://wiki.freepascal.org/index.php?title=Working_With_TSQLQuery/ja&oldid=159591"