Working With TSQLQuery/zh CN

From Lazarus wiki
Jump to navigationJump to search

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

概述

TSQLQuery 对象用来操作数据库 (RDBMS，即关系数据库管理系统 使用 SQL，像 Firebird,、MS SQL Server、Oracle...)；设置 TSQLQuery 的 SQL 属性为 SELECT 语句，这将用于从数据库中检索数据集。随着数据被用户（程序）修改，结果将被提交到数据库中。

TSQLQuery 也可以直接修改数据：设置 TSQLQuery 的 SQL 属性为所需要的，像 INSERT, UPDATE, DELETE 等等SQL语句，并调用 ExecSQL 方法，这将发送查询到数据库服务器，它不会有检索结果。

除了使用 FPC， Lazarus 还提供了些组件：

sqldbcomponents.png

官方文档

查看 TSQLQuery 文档

Lazarus 有很多上下文相关的文档。不幸的是，TSQLQuery 不会出现在 Lazarus 1.0 的帮助索引中。如果将光标放在 TSQLQuery 方法或属性后，试着按 F1，看看有没有相关帮助。将这样： 

var
Q: TSQLQuery
...
  Q.Open; //<--- 将光标放在这里按 F1 键

常用控件

由 TSQLQuery 返回的数据集，可以方便的在TDBGrid实例中 查看，但它不适合 编辑 字段和单元格中的数据。为此，你需要把一些数据感知组件，如 TDBEdit 控件放置到窗体上。并设置其 DataSource 属性为正在使用的数据源。DataField 属性设置为一个字段名（如 'IDENTITY')，或者是表达式返回一个合适的字符串。

添加 TDBNavigator 工具栏可以很方便的浏览数据，并选择记录进行编辑。通过工具栏或在数据网络中移动鼠标来选择记录。对于选择的数据，将出现在 TDBEdit 框中，如果点击 'Edit' ，在编辑框中的内容可以修改。点击 'Post' 将确认更改，点击 'Cancel' 将取消更改。

一般来说，流程如下：

  1. 拖放 TSQLQuery 组件到窗体上，并设置 Database, Transaction 和 SQL 属性。
  2. 拖放 TDatasource 组件，并设置其 Dataset属性为 TSQLQuery 实例。
  3. 拖放 TDBGrid 组件，并设置其 Datasource属性为 TDatasource 实例。
  4. 或者，在窗体中放置 TDBNavigator 实例，并设置 Datasource 属性为 TDatasource 实例。

在此之后，Active 属性可以被设置为 True，则可以通过查询检索到数据。

（使 TSQLConnection 和 TSQLTransaction 组件处于活动状态）

更新数据

如果需要删除或修改记录，数据库需要：

  1. 包含一个主键列。
  2. 有一组确定该记录唯一的字段。通常情况下，他们应该是索引的一部分，这不是必须，但会加快查询速度

如果没有主键字段，则应该添加一个。当表创建时，以添加主键，这样做最好；也可以，以后添加。

例如，下面的示例代码，将在表中添加一个唯一索引： 

alter table testrig 
add column autoid int 
primary key auto_increment;

添加此字段没坏处，这将允许你的应用程序更新该字段。

缓存更新

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, and which fields should be updated: based on that information, it constructs an SQL UPDATE, INSERT or DELETE command.

The construction of the SQL statement is controlled by the UsePrimaryKeyAsKey property and the ProviderFlags properties.

The Providerflags property is a set of 3 flags:

pfInkey
The field is part of the primary key
pfInWhere
The field should be used in the WHERE clause of SQL statements.
pfInUpdate
Updates or inserts should include this field.

By default, ProviderFlags consists of pfInUpdate only.

If your table has a primary key (as described above) then you only need to set the UsePrimaryKeyAsKey property to True and 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 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:

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.
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.
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.

控制更新

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.

TSQLQuery 自定义 SQL

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.

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, you may also look up table information etc; see Database_metadata#Lazarus_TSQLQuery_metadata_tool

In code, use for example InsertSQL.Text or InsertSQL.Add() to set or add lines of SQL statements. One statement may span several lines and ends with a semicolon.

Also, all four properties accept parameters explained further below.

TSQLQuery.SQL: SQL 自定义

The SQL property is normally used to fetch the data from the database. The generic SQL for this property is SELECT * FROM fpdev where fpdev is the table as set in the database.

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. For a table called fpdev with columns id, UserName and InstEmail, you could do something like this: 

SELECT id AS 'ID', UserName AS 'User', InstEmail AS 'e-mail' FROM fpdev ORDER BY 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.

TSQLQuery.InsertSQL, TSQLQuery.UpdateSQL 和 TSQLQuery.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.

The statements in the InsertSQL, UpdateSQL and DeleteSQL properties accept parameters that represent fields in the dataset. The following rules apply:

  • 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: 

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

This statement will insert the values of ID, User and e-mail from the current record of the dataset into the respective fields of table fpdev.

This example statement is actually more or less what SQLDB itself would autogenerate. The given statement may result in errors when the id 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. 

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

The above statement tries to insert a new record using 0 (zero) for the id column. If zero is already used as a key, then a duplicate is detected and the id 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 [1] something like this should work: 

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

The statement inserts everything except the primary key and lets the Firebird before insert trigger use a generator/sequence to insert an id value for you.

For an INSERT statement you may want to use the current field values of the selected record. For UPDATE statements, you will want to use the field values as they were before editing in the WHERE 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: 

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

The above statement updates the UserName and InstEmail columns of all records where User equals the old User value.

We leave it as an exercise to the reader to use the current field values and the old field values in DELETE statements.

See also the official documentation:

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.

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"
  • possibly increased performance
  • protection against SQL injection attacks


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. 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 查询示例

This example shows how to select data using parameters. It also demonstrates using aliases (... AS ...) in SQL. 

 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.

 //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
 
 //We can now fill in the parameter value:
 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

Insert 查询示例

This example shows how to insert a new record into the table using parameters: 

 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; //or possibly CommitRetaining, depending on how your application is set up

Another way of doing this is something like: 

 tsqlquery1.appendrecord(['XXXX', 'description']) 
 tsqltransaction1.commit; //or commitretaining

格式化查询

An alternative to parameterized queries can be to use the format function (see warning below).

Here is how to use program variables in a query at runtime. The values of the variables can change and the query values will change with them, just as with parameterized queries.

We can use for example the text from two TEdits to build an insert query. Whatever is in the text of the TEdits, the query will use for execution: 

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

The parameter %d is used for integers, %s for strings and there are also other such parameters available for floats etc that you can check up on.

Warning-icon.png

Warning: Be aware that you may run into issues with text containing ' and dates using this technique!

运行 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. 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.

You'll see a window with SQL code, and you can run some statements like 

SELECT * FROM EMPLOYEE

by pressing the play icon:

runsqlstatements.png

You can also get metadata: table names, column names etc (if the sqldb connector supports it but most of them do nowadays):

sqlquerymetadata.png

(See also: Database metadata#Lazarus TSQLQuery metadata tool)

故障排除

日志记录

See here: SqlDBHowto#Troubleshooting: TSQLConnection logging for more detail.

表现不佳

  • 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.

错误消息

内存不足错误

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.

A solution is to tell bufdataset to discard the records you have already read by setting the Unidirectional property to true before opening the query: 

MySQLQuery.UniDirectional:=True;

This may also improve performance.

数据集是只读模式

This may happen if you specify a query that you know is updatable but FPC doesn't.

Example: 

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

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. Solution: rewrite the query or specify your own InsertSQL, UpdateSQL and DeleteSQL.

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