Difference between revisions of "SqlDBHowto/zh CN"

From Lazarus wiki
Jump to navigationJump to search
m (Fixed syntax highlighting)
 
(12 intermediate revisions by 3 users not shown)
Line 17: Line 17:
  
 
当客户端库已经正确安装,你便可以使用 TSQLConnection 组件连接到数据库服务器。各种 TSQLConnection 组件可连接到不同的数据库服务器。(查看 [[SQLdb_Package|SQLdb 包]]):
 
当客户端库已经正确安装,你便可以使用 TSQLConnection 组件连接到数据库服务器。各种 TSQLConnection 组件可连接到不同的数据库服务器。(查看 [[SQLdb_Package|SQLdb 包]]):
* Firebird/Interbase: TIBConnection (查看 [[firebird#Connection]])
+
* Firebird/Interbase: TIBConnection (查看 [[Firebird#Connection]])
 
* MS SQL Server: TMSSQLConnection (自 FPC 2.6.1, 查看 [[Lazarus_Database_Overview#Lazarus_and_MSSQL.2FSybase]])
 
* MS SQL Server: TMSSQLConnection (自 FPC 2.6.1, 查看 [[Lazarus_Database_Overview#Lazarus_and_MSSQL.2FSybase]])
 
* MySQL v4.0: TMySQL40Connection (查看 [[mysql#SQLDB]])
 
* MySQL v4.0: TMySQL40Connection (查看 [[mysql#SQLDB]])
Line 26: Line 26:
 
* MySQL v5.6: TMySQL56Connection (自 Lazarus 1.2.4/FPC 2.6.4, 查看 [[mysql#SQLDB]])
 
* MySQL v5.6: TMySQL56Connection (自 Lazarus 1.2.4/FPC 2.6.4, 查看 [[mysql#SQLDB]])
 
* ODBC: TODBCConnection (查看 [[ODBCConn#TODBCConnection]])
 
* ODBC: TODBCConnection (查看 [[ODBCConn#TODBCConnection]])
* Oracle: TOracleConnection (查看 [[oracle|Oracle]])
+
* Oracle: TOracleConnection (查看 [[Oracle]])
 
* PostgreSQL: TPQConnection (查看[[postgresql#SQLDB]])
 
* PostgreSQL: TPQConnection (查看[[postgresql#SQLDB]])
 
* Sqlite3: TSQLite3Connection (自 FPC 2.2.2, 查看[[SQLite#Built-in_SQLDB]])  
 
* Sqlite3: TSQLite3Connection (自 FPC 2.2.2, 查看[[SQLite#Built-in_SQLDB]])  
Line 42: Line 42:
 
设置好这些属性,使用 'open' 方法打开连接。如果连接失败,你可以使用 EDatabaseError 抛出异常。使用 'connected' 属性来确定是否建立与数据库服务器的连接。使用 'close' 方法来关闭与服务器的连接。
 
设置好这些属性,使用 'open' 方法打开连接。如果连接失败,你可以使用 EDatabaseError 抛出异常。使用 'connected' 属性来确定是否建立与数据库服务器的连接。使用 'close' 方法来关闭与服务器的连接。
  
<syntaxhighlight>
+
<syntaxhighlight lang=pascal>
 
Program ConnectDB
 
Program ConnectDB
 
   
 
   
Line 78: Line 78:
 
在 FPC 中,期间有些区别:
 
在 FPC 中,期间有些区别:
 
* SQL 语句返回的信息(数据集)。对于这一点,你需要使用 TSQLQuery 组件; 查看[[#如何读取表中数据]]。
 
* SQL 语句返回的信息(数据集)。对于这一点,你需要使用 TSQLQuery 组件; 查看[[#如何读取表中数据]]。
* 语句不返回信息,但在做其他事,例如更新数据,为此,你可以使用 TSQLConnection 的 'ExecuteDirect' 方法。(You can also use this if you get a dataset back but are not interested in the results, e.g. in a selectable stored procedure).
+
* 语句不返回信息,但在做其他事,例如更新数据,为此,你可以使用 TSQLConnection 的 'ExecuteDirect' 方法。You can also use this if you get a dataset back but are not interested in the results, e.g. in a selectable stored procedure.
  
  
Line 90: Line 90:
 
下面的示例将创建 'TBLNAMES' 表,包含字段有 'NAME' 和 'ID',并插入2条记录。不解释所使用的 SQL 语句。 SQL 语句相关用途和语法,请参阅数据库系统文档。程序中定义的 'CreateConnection',代码示例在[[#如何连接到数据库服务器]]上。
 
下面的示例将创建 'TBLNAMES' 表,包含字段有 'NAME' 和 'ID',并插入2条记录。不解释所使用的 SQL 语句。 SQL 语句相关用途和语法,请参阅数据库系统文档。程序中定义的 'CreateConnection',代码示例在[[#如何连接到数据库服务器]]上。
  
<syntaxhighlight>program CreateTable;
+
<syntaxhighlight lang=pascal>program CreateTable;
 
   
 
   
 
var  
 
var  
Line 125: Line 125:
 
== 如何读取表中数据 ==
 
== 如何读取表中数据 ==
  
Use the TSQLQuery component to read data from a table. A TSQLQuery component must be connected to a TSQLConnection component and a TSQLTransaction component to do its work. Setting the TSQLConnection and TSQLTransaction is discussed in [[#How to connect to a database server? ]] and [[#如何执行查询/创建表]].
+
使用 TSQLQuery 组件从表中读取数据。它必须连接到 TSQLConnection TSQLTransaction 组件才能正常工作。设置 TSQLConnection TSQLTransaction 讨论在 [[#如何连接到数据库服务器]][[#如何执行查询/创建表]]
  
When the TSQLConnection, TSQLTransaction and TSQLQuery are connected, then TSQLQuery needs to be further configured to work.  TSQLQuery has a 'SQL' property containing a TStrings object. The 'SQL' property contains a SQL statement that must be executed. If all data from a table <tt>tablename</tt> must be read, then set the 'SQL' property to:
+
当使用 TSQLConnection、 TSQLTransaction TSQLQuery 连接时,TSQLQuery 需要进行些配置,'SQL' 属性包含要执行的 SQL 语句。如只读取表,那么将其属性设置为:
<syntaxhighlight lang="sql">'SELECT * FROM tablename;'</syntaxhighlight>.
+
<syntaxhighlight lang="sql">'SELECT * FROM tablename;'</syntaxhighlight>
  
Use 'open' to read the table from the server and put the data in the TSQLQuery dataset. The data can be accessed through TSQLQuery until the query is closed using 'close'.
+
使用 'open' 从服务器中读取表并将数据放在 TSQLQuery 数据集中。可以通过 TSQLQuery 访问数据,直到使用 'close' 关闭查询。
  
TSQLQuery is a subclass of TDataset. TDataset has a 'Fields' collection that contains all columns of the table. The TDataset also keeps track of the current record. Use 'First', 'Next', 'Prior' and 'Last' to change the current record. 'Bof' returns 'True' if the first record is reached, and 'Eof' returns 'True' if the last record is reached. To read the value of a field in the current record, first find the right 'TField' object and then use 'AsString', 'AsInteger', etc.
+
TSQLQuery 是 TDataSet 的一个子类。TDataset 是 'Fields' 集合,包含表中的所有列。TDataset 可以定位当前记录,使用 'First''Next''Prior' 'Last' 分别改变到 '第一条''下一条''上一条''最后一条'记录。当在第一条记录时,Bof 为真;为最后一条记录时 Eof 为真。读取当前记录某字段的值,先找到合适的 'TField' 对象,之后使用 'AsString''AsInteger' 等等。
  
 
=== 示例:从表中读取数据 ===
 
=== 示例:从表中读取数据 ===
Below is an example that displays all values of the table as it was made in [[#如何执行查询/创建表]] above.
+
下面的示例将显示表中的所有数据,表来自 [[#如何执行查询/创建表]]
  
<syntaxhighlight>Program ShowData;
+
<syntaxhighlight lang=pascal>Program ShowData;
 
   
 
   
 
var  
 
var  
Line 175: Line 175:
 
end.</syntaxhighlight>
 
end.</syntaxhighlight>
  
(The code above of course is not quite finished, it misses 'try...finally' blocks. However, the above code intends to show the database code and thus the finishing touches are left out.)
+
(上面的代码不是很完善,因为忽略了 'try...finally' 块;旨在演示显示数据,因此'美化'工作被忽略。)
Please note that 'TSQLTransaction.StartTransaction' is not used. This is not necessary. When TSQLQuery is opened, the SQL statement is executed and if no transaction is available then a transaction is automatically started. The programmer does not need to start the transaction explicitly.
+
Please note that 'TSQLTransaction.StartTransaction' is not used. This is not necessary. When TSQLQuery is opened, the SQL statement is executed and if no transaction is available then a transaction is automatically started. The programmer does not need to start the transaction explicitly.(<strike>请注意,未使用 'TSQLTransaction.StartTransaction' 。似乎没有这个必要。当 TSQLQuery 被打开时,SQL语句被执行,如果没有事务可用,那么事务将自动启动。程序员并不需要明确的开始事务。</strike>)
 +
 
 +
 
 
The same applies for the connection maintained by TSQLConnection. The connection is opened as needed, the line 'Aconnection.Open' is not really required.
 
The same applies for the connection maintained by TSQLConnection. The connection is opened as needed, the line 'Aconnection.Open' is not really required.
 
If a TSQLTransaction is destroyed, an automatic 'rollback' will be executed. '''Possible changes to data contained in the transaction will be lost.'''  
 
If a TSQLTransaction is destroyed, an automatic 'rollback' will be executed. '''Possible changes to data contained in the transaction will be lost.'''  
 +
(<strike>这同样适用 TSQLConnection 连接。根据需要打开连接,Aconnection.Open 是否真的需要。如果 TSQLTransaction 被销毁,将会 'rollback'(回退)刚才执行的。 '''包含在事务中的数据,可能会丢失。'''</strike>)
  
 
=== 为什么 TSQLQuery.RecordCount 总返回 10 ===
 
=== 为什么 TSQLQuery.RecordCount 总返回 10 ===
  
To count the records in a datase, use '.RecordCount'. However, notice that '.RecordCount' shows the number of records that is already loaded from the server. For performance reasons, SqlDB does not read all records when opening TSQLQuery by default, only the first 10. Only when the eleventh record is accessed will the next set of 10 records be loaded, etc. Using '.Last', all records will be loaded.
+
使用 '.RecordCount' 统计数据集记录数。需要注意的是,'.RecordCount' 表示的是已经从服务器加载的记录数。出于性能考虑,SqlDB 默认情况下只读取 TSQLQuery 的前 10 条记录。只有当访问第 11 条记录时,下一组的 10 条记录才被加载。使用 '.Last',将载入所有记录。
  
When you want to know the real number of records on the server you can first call '.Last' and then call '.RecordCount'.
+
如果你想知道服务器真正的记录数,先调用 '.Last' 再调用 '.RecordCount'
  
An alternative is available. The number of records returned by the server is set by the '.PacketRecords' property. The default value is 10; if you make it -1 then all records will be loaded at once.
+
另一种方法更实用,服务器返回记录数是由 '.PacketRecords' 控制,默认值时 10,你可以设置为 -1,那么所有记录都将被加载。
  
 
In current stable FPC, '.RecordCount' does not take filters into account, i.e. it shows the unfiltered total.
 
In current stable FPC, '.RecordCount' does not take filters into account, i.e. it shows the unfiltered total.
 +
(<strike>当前稳定的 FPC,'.RecordCount' 不会考虑筛选器,既便它显示了过滤。</strike>)
  
If you need the exact number of records, it often is a better idea to directly query the number of records in a query using another SQL query, but you would have to do that in the same transaction, as other transactions may have changed the number of records in the meanwhile.
+
If you need the exact number of records, it often is a better idea to directly query the number of records in a query using another SQL query, but you would have to do that in the same transaction, as other transactions may have changed the number of records in the meanwhile.(<strike>如果你需要确切记录数量,在同一个事务中使用 SQL 查询记录数。否则,其他事务或许会改变记录数。</strike>)
  
 
=== Lazarus ===
 
=== Lazarus ===
  
Lazarus has various components to show data from a TDataset on a form. Instead of a While-loop and Writeln statements as used above, you can use the components to show the data in a table. Place the right TSQLConnection, TSQLTransaction and TSQLQuery components on a form, then connect them and set them properly. In addition you will need a TDatasource; set the 'TDatasource.Dataset' property to the TSQLQuery component you used. ('''Note''' do not set the 'TSQLQuery.Datasource' property to the TDatasource compnent you used. The 'TSQLQuery.Datasource' property is used only in master-detail tables - see [[MasterDetail]]) Subsequently you may put a TDBGrid onto the form and set the 'Datasource' property of the grid to the TDatasource component you added before.
+
Lazarus 有各种组件在窗体上显示 TDataset 数据。而不是使用 While-loop Writeln 语句在上面显示,你可以使用组件显示表中的数据。
  
To see if it all works, set the 'Connected' property of the TSQLConnection to 'True' in the Lazarus IDE. The IDE will try to connect to the database server immediately. If this works you can set the 'TSQLQuery.Active' property to 'True'. If everything is right, you will see - within the IDE - all data from the table immediately on the screen.
+
请正确在窗体上放置 TSQLConnection、TSQLTransaction 和 TSQLQuery 组件,此外还需要一个 TDatasource 组件,设置 'TDatasource.Dataset' 使用 TSQLQuery 组件。('''注意''',不要设置 'TSQLQuery.Datasource' 的属性为 TDatasource 组件。'TSQLQuery.Datasource'  使用[[MasterDetail |主从]]表)之后,你可以把 TDBGrid 的属性 'Datasource' 设置为 TDatasource 。看下能否工作,设置 TSQLConnection 的 'Connected' 属性为真,IDE 将尝试立刻连接到数据库服务器。如果正常,设置 'TSQLQuery.Active'属性为真。如果一切顺利,你将在屏幕上看到所有表数据。
 +
 
 +
 
 +
译者注:
 +
 
 +
在测试使用时,设置 'TSQLQuery.Active'属性 或 TSQLConnection 的 'Connected' 为真,(因为我使用的是 MySQL) 因此提示:
 +
Can not load default MySQL library libmysql.dll ......
 +
完全可以不用理会,你在程序中手动连接数据库服务器就行了。
  
 
== 如何修改表中数据 ==
 
== 如何修改表中数据 ==
  
To change the data in a record, the TDataset (from which TSQLQuery is derived) must be set to edit mode. To enter edit mode call the '.Edit', '.Insert' or '.Append' methods. Use the '.Edit' method to change the current record. Use '.Insert' to insert a new record before the current record. Use '.Append' to insert a new record at the end of the table. In edit mode you can change field values through the 'Fields' property. Use 'Post' to validate the new data, if the data is valid then the edit mode is left. If you move to another record - for example by using '.Next' - and the dataset is in edit mode, then first '.Post' is called. Use '.Cancel' to discard all changes you made since the last '.Post' call and leave the edit mode.
+
要更改记录中的数据,TDataset (由 TSQLQuery 派生)必须设置为编辑模式。进入编辑模式可以调用 '.Edit''.Insert' '.Append' 方法。使用 '.Edit' 方法用来修改当前记录。'.Insert' 方法,在当前记录前插入新记录。'.Append' 方法,在表尾插入新记录。在编辑模式中,你可以通过  'Fields' 属性更改字段值。使用 'Post' 以验证新的数据,如果数据有效将离开编辑模式。If you move to another record - for example by using '.Next' - and the dataset is in edit mode, then first '.Post' is called. (<strike>如果你移动到另一条记录 - 像通过使用  '.Next' - 而数据集处于编辑模式,那么 '.Post' 将被调用。</strike>)使用 '.Cancel' 放弃自上次 '.Post' 的所有更改并退出编辑模式。
  
<syntaxhighlight>Query.Edit;
+
<syntaxhighlight lang=pascal>Query.Edit;
 
Query.FieldByName('NAME').AsString := 'Edited name';
 
Query.FieldByName('NAME').AsString := 'Edited name';
 
Query.Post;</syntaxhighlight>
 
Query.Post;</syntaxhighlight>
  
The above is not the complete story yet. TSQLQuery is derived from TBufDataset which makes use of buffered updates. Buffered update means that after you called 'Post' the changes in the dataset are visible immediately, but they are not sent to the database server. What does happen is that the changes are maintained in a change log. When the '.ApplyUpdates' method is called, then all changes in the change log are sent to the database. Only then will database server know of the changes. The changes are sent to the server within a transaction of TSQLTransaction. Make sure to properly set the transaction before 'ApplyUpdates'. After applying the updates, a commit must be executed to save the changes on the database server.
+
上述还不完整。TSQLQuery is derived from TBufDataset which makes use of buffered updates. Buffered update means that after you called 'Post' the changes in the dataset are visible immediately,(<strike>TSQLQuery 派生自 TBufDataset 利用缓冲更新。缓冲更新意味着,调用  'Post' 更新数据集是即刻生效。</strike>)但它们还没被发送到数据库服务器。 What does happen is that the changes are maintained in a change log. (<strike>所发生的是,改变在更新日志中。</strike>)
 +
当调用 '.ApplyUpdates' 方法时,所做的更改都将发送到数据库。只有这样,数据库服务器才知道所做的更改。所做的更改发送到 TSQLTransaction 事务内部服务器。 Make sure to properly set the transaction before 'ApplyUpdates'. After applying the updates, a commit must be executed to save the changes on the database server. (<strike>在事务开始之前正确设置 'ApplyUpdates'。应用更新后,必须执行提交,将更改保存在数据库服务器上。</strike>)
  
The below is an example of changing the data in a table, sending the changes to the server and comitting the transaction.
+
下面是更改表中数据的示例,sending the changes to the server and comitting the transaction.(<strike>发送变更到服务器。</strike>)
  
<syntaxhighlight>Program EditData;
+
<syntaxhighlight lang=pascal>Program EditData;
 
   
 
   
 
var  
 
var  
Line 235: Line 247:
 
end.</syntaxhighlight>
 
end.</syntaxhighlight>
  
For a discussion of 'UpdateMode' continue reading.
+
探讨 'UpdateMode' 请继续阅读。
  
 
== 如何将SqlDB更改发送到服务器 ==
 
== 如何将SqlDB更改发送到服务器 ==
  
 
In the code example in [[#How to change data in a table?]], you will find the line
 
In the code example in [[#How to change data in a table?]], you will find the line
<syntaxhighlight>Query.UpdateMode := upWhereAll;</syntaxhighlight>
+
<syntaxhighlight lang=pascal>Query.UpdateMode := upWhereAll;</syntaxhighlight>
 
without explanation of what it does. The best way to find out what that line does is to leave it out. If you leave out the statement and the followed this howto precisely, then you will receive the following error message:
 
without explanation of what it does. The best way to find out what that line does is to leave it out. If you leave out the statement and the followed this howto precisely, then you will receive the following error message:
 
  No update query specified and failed to generate one. (No fields for inclusion in where statement found)
 
  No update query specified and failed to generate one. (No fields for inclusion in where statement found)
Line 262: Line 274:
 
== 如何使用 TSQLQuery 执行查询 ==
 
== 如何使用 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 <tt>INSERT</tt>, <tt>UPDATE</tt> and <tt>DELETE</tt> 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.
+
下一个语句返回数据集(查看 [[#如何读取表中数据]]) SQL中有不返回数据的语句。如 <tt>INSERT</tt>, <tt>UPDATE</tt> <tt>DELETE</tt> 语句没有返回数据。 这些语句可以使用 ''[[#如何执行查询/创建表|TSQLConnection.ExecuteDirect]]'',但 TSQLQuery 也可以使用。如果你不希望返回数据,则需要使用 ''TSQLQuery.ExecSQL'',而不是使用 ''TSQLQuery.Open''。正如前面提到的,''TSQLQuery.Open'' 打开 SQL 语句并返回数据集。
  
The following procedure creates a table and inserts two records using TSQLQuery.
+
下面的程序将创建一个表,并使用 TSQLQuery 插入两条记录。
  
<syntaxhighlight>procedure CreateTable;
+
<syntaxhighlight lang=pascal>procedure CreateTable;
 
    
 
    
 
var  
 
var  
Line 288: Line 300:
 
== 如何在查询中使用参数 ==
 
== 如何在查询中使用参数 ==
  
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.
+
[[#如何使用 TSQLQuery 执行查询]]中,相同的查询被使用两次,不过是插入的值不同,一个更好的方法是,在查询中使用参数。
  
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:
+
查询参数语法在各数据库系统中不同,但不同处由 TSQLQuery 处理,在冒号后面使用参数名称,以替换查询词。如:
<syntaxhighlight>Query.SQL.Text := 'insert into TBLNAMES (ID,NAME) values (:ID,:NAME);';</syntaxhighlight>
+
<syntaxhighlight lang=pascal>Query.SQL.Text := 'insert into TBLNAMES (ID,NAME) values (:ID,:NAME);';</syntaxhighlight>
  
This query will create two parameters: 'ID' and 'NAME'.
+
查询将创建2个参数,'ID' '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:
+
确定参数,查询被解析为''TSQLQuery.SQL''分配或修改。所有现有的参数都将被删除,并且新的参数将被添加到 'TSQLQuery.Params' 属性。赋值给参数,类似于赋值给数据集中的一个字段:
<syntaxhighlight>Query.Params.ParamByName('Name').AsString := 'Name1'</syntaxhighlight>;
+
<syntaxhighlight lang=pascal>Query.Params.ParamByName('Name').AsString := 'Name1'</syntaxhighlight>;
  
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.
+
You can't tell from the query what kind of data must be stored in the parameter. (<strike>你不能告诉查询什么类型的数据必须存储什么的参数。</strike>)通过分配使用 '.AsString',则该参数指定数据类型为 'ftString'。你可以直接通过设置  'DataType' 属性来确定数据类型。不正确的数据类型分配给参数,将在打开或执行查询时引发异常。查看 [[Database_field_type/zh_CN|数据库字段类型]] 的详细信息。
See [[Database field type]] for more information on data types.
 
  
 
=== Select 查询 ===
 
=== Select 查询 ===
An example of a select query with parameters would be to change something like this:
+
带查询参数会是这样:
<syntaxhighlight>
+
<syntaxhighlight lang=pascal>
 
   Query.SQL.Text := 'select ID,NAME from TBLNAMES where NAME = '''+Edit1.Text+''' ORDER BY NAME ';
 
   Query.SQL.Text := 'select ID,NAME from TBLNAMES where NAME = '''+Edit1.Text+''' ORDER BY NAME ';
 
</syntaxhighlight>
 
</syntaxhighlight>
to something like this:
+
 
<syntaxhighlight>
+
要像这样:
 +
<syntaxhighlight lang=pascal>
 
   Query.SQL.Text := 'select ID,NAME from TBLNAMES where NAME = :NAMEPARAM ORDER BY NAME ';
 
   Query.SQL.Text := 'select ID,NAME from TBLNAMES where NAME = :NAMEPARAM ORDER BY NAME ';
 
   Query.Params.ParamByName('NAMEPARAM').AsString := Edit1.Text;
 
   Query.Params.ParamByName('NAMEPARAM').AsString := Edit1.Text;
Line 312: Line 324:
  
 
=== 示例 ===
 
=== 示例 ===
The following example creates the same table as the previous example, but now parameters are used:
+
下面的示例用于创建相同的表,现在使用查询参数:
  
<syntaxhighlight>procedure CreateTableUsingParameters;
+
<syntaxhighlight lang=pascal>procedure CreateTableUsingParameters;
 
    
 
    
 
var  
 
var  
Line 339: Line 351:
  
 
Notice that this example requires more code than the example without the parameters. Then what is the use of using parameters? Speed is one of the reasons. The example with parameters is faster because the query is parsed only once. TSQLQuery only parses the query once, but also the database server parses the query only once. Most database systems support parameters. Whenever a query is used more than once with different values for the parameter each time, then the database server only parses the query and plans the query only once making execution considerably faster.
 
Notice that this example requires more code than the example without the parameters. Then what is the use of using parameters? Speed is one of the reasons. The example with parameters is faster because the query is parsed only once. TSQLQuery only parses the query once, but also the database server parses the query only once. Most database systems support parameters. Whenever a query is used more than once with different values for the parameter each time, then the database server only parses the query and plans the query only once making execution considerably faster.
 +
 
Use 'TSQLQuery.Prepare' to determine the moment the query is parsed and planned by the database server. Use 'TSQLQuery.UnPrepare' to make sure the query is parsed and planned every time by the data server.
 
Use 'TSQLQuery.Prepare' to determine the moment the query is parsed and planned by the database server. Use 'TSQLQuery.UnPrepare' to make sure the query is parsed and planned every time by the data server.
  
Another reason to use prepared statements is prevention of [http://zh.wikipedia.org/wiki/SQL_Injection SQL注入] (see also [[Secure programming]].
+
使用预处理语句的另一个原因是预防[http://zh.wikipedia.org/wiki/SQL_Injection SQL注入] (参见 [[Secure programming|安全编程]])。
  
Finally, in some cases it just simplifies coding.
+
最后,在某些情况下它只是简化了代码。
  
== Troubleshooting: TSQLConnection logging ==
+
== 故障排除: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.
 
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.
 
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.
+
# FPC (Lazarus)调试已启用的话,你可使用调试器来逐步分析数据库代码。
 +
# 如果你使用 ODBC (至少在 Windows 上),你可以在 ODBC 控制面板中启用tracelog 输出。
 +
# 大多数据库允许你监视从某个 IP 地址/连接 发送的所有语句。
  
  
If you use TSQLConnection logging, two things are required:
+
如果你使用 TSQLConnection 日志,有2件事需要注意:
# indicate which event types your TSQLConnection should log
+
# 哪些事件类型应该作为 TSQLConnection 日志
# point TSQLConnection at a function that receives the events and processes them (logs them to file, prints them to screen, etc.).
+
# point TSQLConnection at a function that receives the events and processes them (logs them to file, prints them to screen, etc.).(<strike>TSQLConnection 在函数中接收事件并处理它们(记录到文件或在屏幕上显示等等)</strike>)
That function must be of type TDBLogNotifyEvent (see sqldb.pp), so it needs this signature:
+
函数类型必须是 TDBLogNotifyEvent(见 sqldb.pp),所以它需要这样的签名:
<syntaxhighlight>
+
<syntaxhighlight lang=pascal>
 
TDBLogNotifyEvent = Procedure (Sender : TSQLConnection; EventType : TDBEventType; Const Msg : String) of object;
 
TDBLogNotifyEvent = Procedure (Sender : TSQLConnection; EventType : TDBEventType; Const Msg : String) of object;
 
</syntaxhighlight>
 
</syntaxhighlight>
  
 
=== FPC (或:手动方式) ===
 
=== FPC (或:手动方式) ===
A code snippet can illustrate this:
+
下面的代码可以说明这个问题:
<syntaxhighlight>
+
<syntaxhighlight lang=pascal>
 
uses
 
uses
 
...
 
...
TSQLConnection, //or a child object like TIBConnection, TMSSQLConnection
+
TSQLConnection, //或子对象,像 TIBConnection, TMSSQLConnection
 
...
 
...
 
var
 
var
 
type  
 
type  
   TMyApplication = class(TCustomApplication); //this is our application that uses the connection
+
   TMyApplication = class(TCustomApplication); // 这是我们的应用程序,使用连接
 
...
 
...
 
   private
 
   private
     // This example stores the logged events in this stringlist:
+
     // 这个例子事件都保存在这个 stringlist :
 
     FConnectionLog: TStringList;
 
     FConnectionLog: TStringList;
 
...
 
...
 
   protected
 
   protected
     // This procedure will receive the events that are logged by the connection:
+
     // 这个过程将接收连接记录事件:
 
     procedure GetLogEvent(Sender: TSQLConnection; EventType: TDBEventType; Const Msg : String);
 
     procedure GetLogEvent(Sender: TSQLConnection; EventType: TDBEventType; Const Msg : String);
 
...
 
...
 
   procedure TMyApplication.GetLogEvent(Sender: TSQLConnection;
 
   procedure TMyApplication.GetLogEvent(Sender: TSQLConnection;
 
     EventType: TDBEventType; const Msg: String);
 
     EventType: TDBEventType; const Msg: String);
   // The procedure is called by TSQLConnection and saves the received log messages
+
   // 这个过程用于为 TSQLConnection 保存收到的日志信息
   // in the FConnectionLog stringlist
+
   // FConnectionLog stringlist 类型
 
   var
 
   var
 
     Source: string;
 
     Source: string;
 
   begin
 
   begin
     // Nicely right aligned...
+
     // 正确的对齐...
 
     case EventType of
 
     case EventType of
 
       detCustom:  Source:='Custom:  ';
 
       detCustom:  Source:='Custom:  ';
Line 404: Line 418:
  
 
...
 
...
   // We do need to tell our TSQLConnection what to log:
+
   // 需要告诉我们 TSQLConnection 日志:
 
     FConnection.LogEvents:=LogAllEvents; //= [detCustom, detPrepare, detExecute, detFetch, detCommit, detRollBack]
 
     FConnection.LogEvents:=LogAllEvents; //= [detCustom, detPrepare, detExecute, detFetch, detCommit, detRollBack]
     // ... and to which procedure the connection should send the events:
+
     // ... 并且该程序应发送的事件
 
     FConnection.OnLog:=@Self.GetLogEvent;
 
     FConnection.OnLog:=@Self.GetLogEvent;
 
...
 
...
   // now we can use the connection and the FConnectionLog stringlist will fill with log messages.
+
   // 现在我们的 FConnectionLog 会存满了日志信息。
 
</syntaxhighlight>
 
</syntaxhighlight>
  
You can also use TSQLConnection's GlobalDBLogHook instead to log everything from multiple connections.
+
You can also use TSQLConnection's GlobalDBLogHook instead to log everything from multiple connections.(<strike>你还可以使用 TSQLConnection 的 GlobalDBLogHook,而不是从多个连接记录所有。</strike>)
  
=== Lazarus (或: 快捷方式) ===
+
=== 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.
+
最后,上述是 FPC 的处理方式;如果使用 Lazarus,更便捷的方式是指定 TSQLConnection OnLog 事件处理程序。
  
[[Category:Databases]]
 
  
 
== 参见 ==
 
== 参见 ==
 
* [[Working With TSQLQuery]]
 
* [[Working With TSQLQuery]]
 
[[Category:Databases]]
 
[[Category:zh]]
 

Latest revision as of 13:11, 27 February 2020

Deutsch (de) English (en) español (es) français (fr) 日本語 (ja) Nederlands (nl) polski (pl) 中文(中国大陆)‎ (zh_CN)

介绍

本页面翻译自 SqlDBHowto/nl,最初荷兰语领先,不过,现在两篇文章已经同步。

本文选择从'如何'做为开始。我想通过,一个问题一个答案的方式来解释如何使用不同类。像这样,一个接一个,形成教程以介绍如何使用它。

我会尽力阐明它,以便清楚的说明如何在 Lazarus 和 FreePascal 中使用。但这些例子是 FreePascal 中的(即控制台应用程序)。

在哪里可以找到官方文档

请参阅官方 SQLDB 文档

如何连接到数据库服务器

SqlDB 无法直接连接到数据库服务器,但可以使用数据库服务器客户端连接。SqlDB 发送命令到客户端;客户端连接到数据库并传输命令。这意味着电脑上必须安装数据库客户端才能与数据库连接。Windows 通常是 .dll文件,Linux 下是 .so文件,OS/X中为 .dylib的文件。

当客户端库已经正确安装,你便可以使用 TSQLConnection 组件连接到数据库服务器。各种 TSQLConnection 组件可连接到不同的数据库服务器。(查看 SQLdb 包):

MySQL留意 - 在某种程度上客户端版本之间的差异使得客户端和连接不能互换。如 MySQL 客户端版本为4.1,你必须使用 TMySQL41Connection 连接。它与 MySQL 服务器端无关;也许你可以使用 4.1 的客户端,连接到 MySQL 5.0 的服务器(请参见 MySQL文档 关于支持哪些组合)


虽然各数据库的细节不同,不过,一般只需要设置四个属性就可以连接到数据库服务器:

  • 服务器名或 IP 地址
  • 数据库名称
  • 用户名
  • 密码

设置好这些属性,使用 'open' 方法打开连接。如果连接失败,你可以使用 EDatabaseError 抛出异常。使用 'connected' 属性来确定是否建立与数据库服务器的连接。使用 'close' 方法来关闭与服务器的连接。

Program ConnectDB
 
var AConnection : TIBConnection;
 
Procedure CreateConnection;
begin
  AConnection := TIBConnection.Create(nil);
  AConnection.Hostname := 'localhost';
  AConnection.DatabaseName := '/opt/firebird/examples/employee.fdb';
  AConnection.UserName := 'sysdba';
  AConnection.Password := 'masterkey';
end;
 
begin
  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地址输入错误。如果消息提示客户端库无法找到,你需要检查客户端库是否安装正确。Often the error message states literally the name of the file looked for. (通常按错误消息上的名称查找。

如何执行查询/创建表

SqlDB - 见名知意 - 适用于使用 SQL 的数据库服务器。SQL代表 '结构化查询语言',它工作在关系型数据库上。事实上,每个数据库系统都有自己的方言,不过,大多 SQL 语句通用于所有数据库系统。

在 FPC 中,期间有些区别:

  • SQL 语句返回的信息(数据集)。对于这一点,你需要使用 TSQLQuery 组件; 查看#如何读取表中数据
  • 语句不返回信息,但在做其他事,例如更新数据,为此,你可以使用 TSQLConnection 的 'ExecuteDirect' 方法。You can also use this if you get a dataset back but are not interested in the results, e.g. in a selectable stored procedure.


Most database system execute SQL statements within a transaction. If you want changes made within a transaction available in other transactions, or have those changes available even after closing the transaction(!), then you have to 'commit' the transaction.


支持事务的 Sqldb 包含 TSQLTransaction 组件。由 Sqldb 执行的 SQL 语句必须在事务内执行,即使数据库系统不支持事务。此外,there are database systems that do support transaction for which TSQLConnection does not (yet) support transaction(使用 TSQLConnection 操作支持事务的数据库系统),即使这样,你也必须使用 TSQLTransaction 组件。

使用 TSQLConnection.ExecuteDirect 执行SQL语句你必须指定 'Transaction';反过来,使用 TSQLTransaction 你也必须指定使用哪个 TSQLConnection 组件。

下面的示例将创建 'TBLNAMES' 表,包含字段有 'NAME' 和 'ID',并插入2条记录。不解释所使用的 SQL 语句。 SQL 语句相关用途和语法,请参阅数据库系统文档。程序中定义的 'CreateConnection',代码示例在#如何连接到数据库服务器上。

program CreateTable;
 
var 
  AConnection : TSQLConnection;
  ATransaction : TSQLTransaction;
 
procedure CreateTransaction;
begin
  ATransaction := TSQLTransaction.Create;
  ATransaction.Database := AConnection;
end;
 
begin
  CreateConnection;
  CreateTransaction;
  AConnection.Transaction := ATransaction;
  AConnection.Open;
  ATransaction.StartTransaction;
  AConnection.ExecuteDirect('create table TBLNAMES (ID integer, NAME varchar(40));'); 
   
  // Some database-server types need a commit before you can use a newly created table. (Firebird)
  // With .Commit you also close the transaction
  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;
  AConnection.Free;
  ATransaction.Free;
end.

如何读取表中数据

使用 TSQLQuery 组件从表中读取数据。它必须连接到 TSQLConnection 和 TSQLTransaction 组件才能正常工作。设置 TSQLConnection 和 TSQLTransaction 讨论在 #如何连接到数据库服务器#如何执行查询/创建表

当使用 TSQLConnection、 TSQLTransaction 和 TSQLQuery 连接时,TSQLQuery 需要进行些配置,'SQL' 属性包含要执行的 SQL 语句。如只读取表,那么将其属性设置为:

'SELECT * FROM tablename;'

使用 'open' 从服务器中读取表并将数据放在 TSQLQuery 数据集中。可以通过 TSQLQuery 访问数据,直到使用 'close' 关闭查询。

TSQLQuery 是 TDataSet 的一个子类。TDataset 是 'Fields' 集合,包含表中的所有列。TDataset 可以定位当前记录,使用 'First'、 'Next'、 'Prior' 和 'Last' 分别改变到 '第一条'、'下一条'、'上一条'和'最后一条'记录。当在第一条记录时,Bof 为真;为最后一条记录时 Eof 为真。读取当前记录某字段的值,先找到合适的 'TField' 对象,之后使用 'AsString'、 'AsInteger' 等等。

示例:从表中读取数据

下面的示例将显示表中的所有数据,表来自 #如何执行查询/创建表

Program ShowData;
 
var 
  AConnection : TSQLConnection;
  ATransaction : TSQLTransaction;
 
function GetQuery : TSQLQuery;
  var AQuery : TSQLQuery;
begin
  AQuery := TSQLQuery.Create;
  AQuery.Database := FConnection;
  AQuery.Transaction := FTransaction;
  Result := AQuery;
end;
 
var 
  Query : TSQLQuery;
 
begin
  CreateConnection;
  CreateTransaction;
  Query := GetQuery;
  Query.SQL.Text := 'select * from tblNames';
  AConnection.Open;
  Query.Open;
  while not Query.Eof do
  begin
    Writeln('ID: ', Query.FieldByName('Name').AsInteger, 'Name: ' +
                                  Query.FieldByName('Name').AsString);
    Query.Next;
  end;
  Query.Close;
  AConnection.Close;
  Query.Free;
  ATransaction.Free;
  AConnection.Free;
end.

(上面的代码不是很完善,因为忽略了 'try...finally' 块;旨在演示显示数据,因此'美化'工作被忽略。) Please note that 'TSQLTransaction.StartTransaction' is not used. This is not necessary. When TSQLQuery is opened, the SQL statement is executed and if no transaction is available then a transaction is automatically started. The programmer does not need to start the transaction explicitly.(请注意,未使用 'TSQLTransaction.StartTransaction' 。似乎没有这个必要。当 TSQLQuery 被打开时,SQL语句被执行,如果没有事务可用,那么事务将自动启动。程序员并不需要明确的开始事务。)


The same applies for the connection maintained by TSQLConnection. The connection is opened as needed, the line 'Aconnection.Open' is not really required. If a TSQLTransaction is destroyed, an automatic 'rollback' will be executed. Possible changes to data contained in the transaction will be lost. (这同样适用 TSQLConnection 连接。根据需要打开连接,Aconnection.Open 是否真的需要。如果 TSQLTransaction 被销毁,将会 'rollback'(回退)刚才执行的。 包含在事务中的数据,可能会丢失。)

为什么 TSQLQuery.RecordCount 总返回 10

使用 '.RecordCount' 统计数据集记录数。需要注意的是,'.RecordCount' 表示的是已经从服务器加载的记录数。出于性能考虑,SqlDB 默认情况下只读取 TSQLQuery 的前 10 条记录。只有当访问第 11 条记录时,下一组的 10 条记录才被加载。使用 '.Last',将载入所有记录。

如果你想知道服务器真正的记录数,先调用 '.Last' 再调用 '.RecordCount'。

另一种方法更实用,服务器返回记录数是由 '.PacketRecords' 控制,默认值时 10,你可以设置为 -1,那么所有记录都将被加载。

In current stable FPC, '.RecordCount' does not take filters into account, i.e. it shows the unfiltered total. (当前稳定的 FPC,'.RecordCount' 不会考虑筛选器,既便它显示了过滤。)

If you need the exact number of records, it often is a better idea to directly query the number of records in a query using another SQL query, but you would have to do that in the same transaction, as other transactions may have changed the number of records in the meanwhile.(如果你需要确切记录数量,在同一个事务中使用 SQL 查询记录数。否则,其他事务或许会改变记录数。

Lazarus

Lazarus 有各种组件在窗体上显示 TDataset 数据。而不是使用 While-loop 或 Writeln 语句在上面显示,你可以使用组件显示表中的数据。

请正确在窗体上放置 TSQLConnection、TSQLTransaction 和 TSQLQuery 组件,此外还需要一个 TDatasource 组件,设置 'TDatasource.Dataset' 使用 TSQLQuery 组件。(注意,不要设置 'TSQLQuery.Datasource' 的属性为 TDatasource 组件。'TSQLQuery.Datasource' 使用主从表)之后,你可以把 TDBGrid 的属性 'Datasource' 设置为 TDatasource 。看下能否工作,设置 TSQLConnection 的 'Connected' 属性为真,IDE 将尝试立刻连接到数据库服务器。如果正常,设置 'TSQLQuery.Active'属性为真。如果一切顺利,你将在屏幕上看到所有表数据。


译者注:

在测试使用时,设置 'TSQLQuery.Active'属性 或 TSQLConnection 的 'Connected' 为真,(因为我使用的是 MySQL) 因此提示:

Can not load default MySQL library libmysql.dll ......

完全可以不用理会,你在程序中手动连接数据库服务器就行了。

如何修改表中数据

要更改记录中的数据,TDataset (由 TSQLQuery 派生)必须设置为编辑模式。进入编辑模式可以调用 '.Edit'、 '.Insert' 或 '.Append' 方法。使用 '.Edit' 方法用来修改当前记录。'.Insert' 方法,在当前记录前插入新记录。'.Append' 方法,在表尾插入新记录。在编辑模式中,你可以通过 'Fields' 属性更改字段值。使用 'Post' 以验证新的数据,如果数据有效将离开编辑模式。If you move to another record - for example by using '.Next' - and the dataset is in edit mode, then first '.Post' is called. (如果你移动到另一条记录 - 像通过使用 '.Next' - 而数据集处于编辑模式,那么 '.Post' 将被调用。)使用 '.Cancel' 放弃自上次 '.Post' 的所有更改并退出编辑模式。

Query.Edit;
Query.FieldByName('NAME').AsString := 'Edited name';
Query.Post;

上述还不完整。TSQLQuery is derived from TBufDataset which makes use of buffered updates. Buffered update means that after you called 'Post' the changes in the dataset are visible immediately,(TSQLQuery 派生自 TBufDataset 利用缓冲更新。缓冲更新意味着,调用 'Post' 更新数据集是即刻生效。)但它们还没被发送到数据库服务器。 What does happen is that the changes are maintained in a change log. (所发生的是,改变在更新日志中。) 当调用 '.ApplyUpdates' 方法时,所做的更改都将发送到数据库。只有这样,数据库服务器才知道所做的更改。所做的更改发送到 TSQLTransaction 事务内部服务器。 Make sure to properly set the transaction before 'ApplyUpdates'. After applying the updates, a commit must be executed to save the changes on the database server. (在事务开始之前正确设置 'ApplyUpdates'。应用更新后,必须执行提交,将更改保存在数据库服务器上。

下面是更改表中数据的示例,sending the changes to the server and comitting the transaction.(发送变更到服务器。

Program EditData;
 
var 
  AConnection : TSQLConnection;
  ATransaction : TSQLTransaction;
  Query : TSQLQuery;
 
begin
  CreateConnection;
  CreateTransaction;
  AConnection.Transaction := ATransaction;
  Query := GetQuery;
  Query.SQL.Text := 'select * from tblNames';
  Query.Open;
  Query.Edit;
  Query.FieldByName('NAME').AsString := 'Edited name';
  Query.Post;
  Query.UpdateMode := upWhereAll;
  Query.ApplyUpdates;
  AConnection.Transaction.Commit;
  Query.Free;
  ATransaction.Free;
  AConnection.Free;
end.

探讨 'UpdateMode' 请继续阅读。

如何将SqlDB更改发送到服务器

In the code example in #How to change data in a table?, you will find the line

Query.UpdateMode := upWhereAll;

without explanation of what it does. The best way to find out what that line does is to leave it out. If you leave out the statement and the followed this howto precisely, then you will receive the following error message:

No update query specified and failed to generate one. (No fields for inclusion in where statement found)

To understand what went wrong, you must understand how changes are sent to the database server. The only way to get data in a SQL server is by executing SQL queries. SQL has three types of queries for three different ways of manupulating a record. To create a new record, change or delete a record insert, update and delete statements are executed respectively. An update statement may be as follows:

update TBLNAMES set NAME='Edited name' where ID=1;

To send a change to the database server, Sqldb must assemble an update query. To assemble the query, three things are needed:

The name of the table
The table name is retrieved from parsing the select query, although this doesn't always work.
UPDATE or INSERT clause
These contain the fields that must be changed.
WHERE clause
This contains the fields that determine which records should be changed.

Every field (each TField in Fields) has a ProviderFlags property. Only fields with pfInUpdate in ProviderFlags will be used in the update or insert cluase of a query. By default all fields have pfInUpdate set in their ProviderFlags property.

Which fields are used in the WHERE clause depends on the UpdateMode property of the query and the ProviderFlags property of the fields. Fields with pfInkey in their ProviderFlags are always used in the WHERE clause. A field will have the pfInKey flag set automatically if the field is part of the primary key of the table and 'TSQLQuery.UsePrimaryKeyAsKey' returns 'True'.

The default value for UpdateMode of the query is upWhereKeyOnly. In this update mode only fields with pfInkey in their ProviderFlags property are used in the WHERE clause. If none of the fields have their pfInKey flag set, then no fields are available for the WHERE clause and the error message from the beginning of this section will be returned. You can solve the issue by:

  • Adding a primary key to the table and set TSQLQuery.UsePrimaryKeyAsKey to 'True', or
  • Setting the pfInkey flag for one or more fields in code.

The UpdateMode property knows two more possible values. 'upWhereAll' can be used to add all fields with the 'pfInWhere' flag set to the WHERE clause. By default all fields have this flag set. 'upWhereChanged' can be used to add only those fields that have the 'pfInWhere' flag set and that are changed in the current record.

如何使用 TSQLQuery 执行查询

下一个语句返回数据集(查看 #如何读取表中数据) SQL中有不返回数据的语句。如 INSERT, UPDATEDELETE 语句没有返回数据。 这些语句可以使用 TSQLConnection.ExecuteDirect,但 TSQLQuery 也可以使用。如果你不希望返回数据,则需要使用 TSQLQuery.ExecSQL,而不是使用 TSQLQuery.Open。正如前面提到的,TSQLQuery.Open 打开 SQL 语句并返回数据集。

下面的程序将创建一个表,并使用 TSQLQuery 插入两条记录。

procedure CreateTable;
  
var 
  Query : TSQLQuery;
  
begin
  Query := GetQuery;
  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;
  
  Query.Close;
  Query.Free;
end;

如何在查询中使用参数

#如何使用 TSQLQuery 执行查询中,相同的查询被使用两次,不过是插入的值不同,一个更好的方法是,在查询中使用参数。

查询参数语法在各数据库系统中不同,但不同处由 TSQLQuery 处理,在冒号后面使用参数名称,以替换查询词。如:

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

查询将创建2个参数,'ID' 和 'NAME'。 确定参数,查询被解析为TSQLQuery.SQL分配或修改。所有现有的参数都将被删除,并且新的参数将被添加到 'TSQLQuery.Params' 属性。赋值给参数,类似于赋值给数据集中的一个字段:

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

;

You can't tell from the query what kind of data must be stored in the parameter. (你不能告诉查询什么类型的数据必须存储什么的参数。)通过分配使用 '.AsString',则该参数指定数据类型为 'ftString'。你可以直接通过设置 'DataType' 属性来确定数据类型。不正确的数据类型分配给参数,将在打开或执行查询时引发异常。查看 数据库字段类型 的详细信息。

Select 查询

带查询参数会是这样:

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

要像这样:

  Query.SQL.Text := 'select ID,NAME from TBLNAMES where NAME = :NAMEPARAM ORDER BY NAME ';
  Query.Params.ParamByName('NAMEPARAM').AsString := Edit1.Text;

示例

下面的示例用于创建相同的表,现在使用查询参数:

procedure CreateTableUsingParameters;
  
var 
  Query : TSQLQuery;
  
begin
  Query := GetQuery;
  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.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.Close;
  Query.Free;
end;

Notice that this example requires more code than the example without the parameters. Then what is the use of using parameters? Speed is one of the reasons. The example with parameters is faster because the query is parsed only once. TSQLQuery only parses the query once, but also the database server parses the query only once. Most database systems support parameters. Whenever a query is used more than once with different values for the parameter each time, then the database server only parses the query and plans the query only once making execution considerably faster.

Use 'TSQLQuery.Prepare' to determine the moment the query is parsed and planned by the database server. Use 'TSQLQuery.UnPrepare' to make sure the query is parsed and planned every time by the data server.

使用预处理语句的另一个原因是预防SQL注入 (参见 安全编程)。

最后,在某些情况下它只是简化了代码。

故障排除: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.

不管怎么说,连接日志在程序设计时会对你有很大的帮助。

替代方案:

  1. FPC (和 Lazarus)调试已启用的话,你可使用调试器来逐步分析数据库代码。
  2. 如果你使用 ODBC (至少在 Windows 上),你可以在 ODBC 控制面板中启用tracelog 输出。
  3. 大多数据库允许你监视从某个 IP 地址/连接 发送的所有语句。


如果你使用 TSQLConnection 日志,有2件事需要注意:

  1. 哪些事件类型应该作为 TSQLConnection 日志
  2. point TSQLConnection at a function that receives the events and processes them (logs them to file, prints them to screen, etc.).(TSQLConnection 在函数中接收事件并处理它们(记录到文件或在屏幕上显示等等))

函数类型必须是 TDBLogNotifyEvent(见 sqldb.pp),所以它需要这样的签名:

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

FPC (或:手动方式)

下面的代码可以说明这个问题:

uses
...
TSQLConnection, //或子对象,像 TIBConnection, TMSSQLConnection
...
var
type 
  TMyApplication = class(TCustomApplication); // 这是我们的应用程序,使用连接
...
  private
    // 这个例子事件都保存在这个 stringlist 里:
    FConnectionLog: TStringList;
...
  protected
    // 这个过程将接收连接记录事件:
    procedure GetLogEvent(Sender: TSQLConnection; EventType: TDBEventType; Const Msg : String);
...
  procedure TMyApplication.GetLogEvent(Sender: TSQLConnection;
    EventType: TDBEventType; const Msg: String);
  // 这个过程用于为 TSQLConnection 保存收到的日志信息
  // FConnectionLog 是 stringlist 类型
  var
    Source: string;
  begin
    // 正确的对齐...
    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;

...
  // 需要告诉我们 TSQLConnection 日志:
    FConnection.LogEvents:=LogAllEvents; //= [detCustom, detPrepare, detExecute, detFetch, detCommit, detRollBack]
    // ... 并且该程序应发送的事件
    FConnection.OnLog:=@Self.GetLogEvent;
...
  // 现在我们的 FConnectionLog 会存满了日志信息。

You can also use TSQLConnection's GlobalDBLogHook instead to log everything from multiple connections.(你还可以使用 TSQLConnection 的 GlobalDBLogHook,而不是从多个连接记录所有。)

Lazarus (或:快捷方式)

最后,上述是 FPC 的处理方式;如果使用 Lazarus,更便捷的方式是指定 TSQLConnection 的 OnLog 事件处理程序。


参见