SQLdb Package/zh CN

From Lazarus wiki
Jump to: navigation, search

English (en) español (es) français (fr) 中文(中国大陆)‎ (zh_CN)

组件(Components)

SQLdb 包提供下列组件:

(原文:The SQLdb package provides the following components:)

TSQLQuery

查询组件,继承自 TDataset, 功能是将提交的 SQL 查询执行后的结果数据集作为一张表中来使用。但也可以用来执行不返回任何数据集的SQL查询。

(原文:This is a descendant of TDataset, and provides the data as a table from the SQL query that you submit. But can also be used to execute SQL-queries that don't return any data.)

TSQLTransaction

事务组件,封装了发生在数据库服务器上的事务。最重要的一点是,它使用 commit 或 rollback 方法来完成或取消数据库的事务。

(原文:This encapsulates the transaction on the database server. I am not going to say much about it as I haven't quite worked out the details myself! The important bit is that it has the commit and rollback methods to finish or cancel your transaction on the database.)

TXXXConnection

连接组件,XXX代表你所连接的数据库。每种连接组件都要配合 SQLQuery 组件和 SqlTransaction 组件一同使用,负责将 SQLQuery 发出的“标准”请求,转换成数据库请求,转换要符合所使用的特定数据库的专用语法格式。

实际包括的组件有:

  • TIBConnection (Borland Interbase / Firebird)
  • TODBCConnection (连接到有 ODBC 驱动程序的任何数据库...)
  • TOracleConnection (Oracle)
  • TMySQL40Connection (MySQL - 多个版本)
  • TMySQL41Connection
  • TMySQL50Connection
  • TMySQL51Connection (从 FPC 2.5.1起可用)
  • TPQConnection (PostgreSQL)
  • TSQLite3Connection (从 FPC 2.2.2起可用)

(原文:where XXX is the flavour of the database you are connecting to. Each one of these components takes the "standard" requests of the SQLQuery and SQLTransaction components and translates them into database requests, allowing for the idiosyncrasies of the specific database you are using.

The actual components are:

  • TIBConnection (Borland Interbase / Firebird)
  • TODBCConnection (An ODBC connection to a database that the PC has the driver for ...)
  • TOracleConnection (Oracle)
  • TMySQL40Connection (MySQL - various versions)
  • TMySQL41Connection
  • TMySQL50Connection
  • TMySQL51Connection (available since FPC version 2.5.1)
  • TPQConnection (PostgreSQL)
  • TSQLite3Connection (available since FPC version 2.2.2))

使用 SQLdb 包(Using the SQLdb Package)

使用 SQLdb 包时,必须三种组件同时搭配使用:TSQLQuery、TSQLTransaction 和一种连接组件。

快速入门教程:

  • 选择组件面板的 SQLdb 页;
  • 添加一个连接到所使用的数据库的 T___Connection 组件到表单, 根据使用数据库的不同设置相应的连接属性,确保设置"connected" 属性为真;
  • 添加一个 TSQLTransaction 组件。 再次选择连接组件,设置 Transaction 属性为刚才新建的 transaction 控件。这会导致 transaction 控件的 database 属性自动指向 T___Connection 控件,仔细检查确保实现了这一点。设置 TSQLTransaction 属性 active 为 true;
  • 添加一个 SQLQuery 组件,设置 Database 和 Transaction 属性分别指向上述两个组件.
  • 设置 SQLQuery 的 SQL 属性。第一次测试时,可以简单设置为 "select * from <表名>"。
  • 设置 SQLQuery 的 active 属性为 true,如果可以,则执行了查询,已经有可用的数据集了。

从下一步开始,对任何数据库添加控件的步骤、方法都是相同的,简单回顾如下:

  • 添加一个 TDataSource 组件(在Data Access页)并设置 DataSet 属性指向 SQLQuery 组件;
  • 从 “Data Controls” 页添加一些数据敏感控件。比如添加一个 TDBGrid 组件,并设置其 DataSource 属性为刚才添加的 DataSource 组件,在表格中应该显示查询到的数据。

当然,这仅仅是开始。但在表格中显示一些数据通常是确认数据库连接能够正常工作的第一步。更详细的解释可参见Working With TSQLQuery

(原文:To use the SQLdb package, you need a TSQLQuery component, a TSQLTransaction component, and one of the connection components.

Here is a quick tutorial:

  • Go to the SQLdb tab in Component Palette.
  • Add a T___Connection component (suitable for the type of database you are using) to a form, and fill in the appropriate properties to connect to your database. These will vary depending on the actual database. Make sure you can set the "connected" property to true.
  • Add a TSQLTransaction component. Return to the Connection component, and set its Transaction Property to the new transaction. This should also set the transaction's database property - check to make sure. You should be able to set active to true.
  • Add an SQLQuery component and set its Database and Transaction properties to the components you just added.
  • Set the SQLQuery "SQL" property. For a first test, just do a "select * from <tablename>.
  • Set active to true. If you can, you have opened your query, and the data is available.

From here on, adding controls is the same as for any database, but just to recap:

  • Add a TDatasource component (from the Data Access tab) and set its dataset property to the SQLQuery component
  • Add some controls from the Data Controls tab. For a quick check, add a TDBGrid component, and set its datasource property to the Datasource component you just added. You should see the data from the query you entered.

OF course, this is just the beginning, but getting some data in a grid is usually the first step to ensure the database connection is working. More detailed explanations will be found at Working With TSQLQuery.)

Notes

More details will be found in SQLdb Programming Reference

The pages related to the SQLdb package were initially written from explorations done using TPQConnection, using a PostgreSQL 8.1 database on Win XP O/S. They are expected to be true for the other databases and OSes as well. If you can confirm this, please leave a note here.

  • It seems to work similarly on MySQL 5.0.45 on Win XP. TSQLTransaction property 'active' cannot however be set to true, but the live data from the database table are visible in the TDbGrid component already during design of the form and the program compiles.
  • It works on Firebird 2.5 with Lazarus running on Windows Vista. (August 2011)

There are some notes and documentation started: http://z505.com/cgi-bin/powtils/docs/1.6/idx.cgi?file=fcldbnotes

An interface view of the classes is availble: http://z505.com/cgi-bin/powtils/docs/1.6/idx.cgi?file=index-4&unit=sqldb