| Databases portal |
|
References:
Tutorials/practical articles:
Databases Advantage - MySQL - MSSQL - Postgres - Interbase - Firebird - Oracle - ODBC - Paradox - SQLite - dBASE - MS Access - Zeos |
本文介绍了Lazarus和数据库。下表给出了 Lazarus 支持的数据库。
请只安装必要的数据库组件,如数据库需用到客户端库则应先安装客户端库。否则由于缺少文件,Lazarus 可能会无法启动。因为数据库组件无法卸载,一旦有问题 Lazarus 就得重装。
支持的数据库
| 数据库 | 软件包名 | 是否需要客户端库 | 是否需要服务器端 | 支持的版本 | 支持的平台 |
|---|---|---|---|---|---|
| Advantage | TAdsDataSet | 是 | 否 | 10.1 以上 | i386: Linux、Win32 |
| DBase | DBFLaz | 否 | 否 | III+, IV, VII | 所有 |
| TurboPower FlashFiler | FlashFiler | 否 | 否 | - | Win 32、(win64?) |
| In memory | memds | 否 | 否 | - | 所有 |
| In memory | bufdataset | 否 | 否 | - | 所有 |
| Firebird | SQLdb | 是 | 依赖于1 | 1 - 2.5 | i386: Linux、Win32 |
| (Visual) FoxPro | DBFLaz | 否 | 否 | 2.0, 2.5, 3.0 (不完全) | 所有 |
| Interbase | SQLdb | 是 | 是 | 4 - 6 | i386: Linux、Win32 |
| Microsoft SQL Server | SQLdb | 是 | 是 | 6- | FPC 2.6.2+. Linux、macOS、Win32、可能 *BSD、可能 Solaris2 |
| MySQL | SQLdb | 是 | 是 | 3.0 - 5.5 | i386: Linux、Win32 |
| ODBC | SQLdb | 是 | 有依赖条件 | 3.x 3 | i386: Linux、Win32 |
| Oracle | SQLdb | 是 | 是 | - | - |
| Paradox | TParadoxDataSet | 否 | 否 | 最高至7级表(以上?) | 所有 |
| Paradox | TParadox | 是 | 否 | Win32 | |
| PostgreSQL | SQLdb | 是 | 是 | 6.6 - 8 | i386: Linux、Win32 |
| Sybase Adaptive Server Enterprise (ASE) | SQLdb | 是 | 是 | 任何 | Linux、macOS、Win32、可能 *BSD, 可能Solaris2) |
| SQLite | SQLdb | 是 | 否 | sqlite3 | 所有 |
| SQLite | SQLite(3)Laz | 是 | 否 | sqlite2、sqlite3 | 所有 |
| Text 文件 | sdf | 否 | 否 | - | 所有 |
| ZMSQL | zmsql | 否 | 否 | ? | ? |
注1: 可在 Windows 和 Linux(可能也包括 macOS)中使用嵌入版的 Firebird,或者连到运行于 Windows/Unix/macOS/FreeBSD/其他 Firebird 支持平台中的 Firebird 服务器。
注2: 这些连接库用 FreeTDS 作为驱动程序,FreeTDS 的文档指出,至少应该可在这些平台下编译。Windows x86/x64 版本可在此下载:ftp://ftp.freepascal.org/fpc/contrib/windows/dblib_x32.zip 和 ftp://ftp.freepascal.org/fpc/contrib/windows/dblib_x64.zip
注3: 这里是指 ODBC 标准的版本,而非驱动程序或驱动程序管理器的版本号。大多数 DBMS 都带有 ODBC 3.x 驱动程序。
绑定数据库客户端
如果连接数据库需要客户端库,那就必须安装一下。不仅要在编程的计算机上安装,还要在所有运行应用程序的计算机上安装。请注意,某些数据库(特别是 MySQL)要求,编译时绑定的库版本必须与安装运行时的相同。在数据库开发方网站上,可以找到客户端库的安装方法(在 *nix 系统上是 .so 文件,在 Windows 上是 .dll 文件)。有关绑定的代码文件位于 fpc-sources 的 packages/base 目录中。内容基本就是一些客户端 API 的调用,比如 mysql_connect_database,每种数据库的调用各不相同。当然可以用这些代码文件编写数据库应用,但通常会增加很多工作量,且更容易出错,相比之下,使用 Lazarus 的 DB 组件会更为方便。
这些绑定软件包大多用硬编码链接客户端库。这意味着,如果在编译时包含了某个单元,但没有合适的客户端库,那么整个应用程序将无法链接。也就是说,如果计算机中没有安装 MySQL 客户端,程序中又使用了 mysql4.pp 单元,那么将无法生成可执行文件。虽然在安装了 MySQL 客户端库的计算机上编译成功,但在没有对应 MySQL 客户端库的其他机器上该程序仍然无法启动。换句话说:这些数据库要在开发机器上安装客户端库,并且还要将这些客户端库与应用程序一起安装。
为了避免这类问题,有些包能够动态链接到客户端库。在调用客户端库之前,必须先进行“初始化”。如果计算机上没有安装数据库客户端,初始化就会失败。如果客户端库连接就绪,那么之后需要执行“释放”操作。
Datasets
Dataset 和 Database 的区别
许多数据库编程新人无法区分数据集和数据库。现在数据库有很多种,比如 Firebird、Interbase、Oracle、Sybase、Microsoft Access 等。
最简单的概念就是,将 Lazarus 数据集(TDataset 的子类,如 TSQLQuery 等)视为部分数据库查询记录的副本。假设用以下 SQL 语句开启查询:
SQLQuery1.SQL.Text:= 'select * from table1 where id <=100';
打开 SQLQuery1,数据库 table1 中 id 小于等于 100 的记录将复制到 SQLQuery1 中。
通过 TDatasource(其又与 TDataset 连接),数据集中的数据可用数据库控件(如 TDBGrid、TDBEdit 等)进行可视化展示。
数据集中的数据可通过 insert、edit、delete、post 等命令(后续会讲解)进行修改,但这些操作都是基于数据集进行的,而不是直接在数据库表上完成。需要多做一步才能让改动持久性地存入数据库,比如调用 TDataSet.ApplyUpdates 或执行插入、更新 SQL 语句(与 TDataset.insert 或 TDataset.update 不同)。
通过设置数据库的事务属性,可在数据集关闭时将改动自动保存(或丢弃)。
数据集简介
在 Lazarus(或 Free Pascal)中,数据库的使用底层都基于 TDataSet 类展开。在应用程序中,TDataSet 类代表一张表或一个查询。然而,与很多其他基础类一样,TDataset 类不会直接用到,而会采用其某个子类。这些子类有很多,提供了对各种数据库的访问,例如本地 dBase 或文本文件,或者远程数据库如 PostgreSQL、Firebird、MySQL等。有些子类直接链接到数据库表,而另一些则使用其他组件或库来执行链接。
Dataset 的子类是非可视化组件,通常属于自由组件库(FCL)而不是 Lazarus 组件库(LCL)。
数据集既可通过纯代码方式使用,也可与可视化控件一起使用。典型的 Lazarus 数据库应用通常会同时使用这两种方式。不论哪种方式,第一步都是创建 TDataset 子类,进行初始化以连接到所需的表或查询,并执行 open 操作。可在运行时用代码完成,也可通过在窗体上放置组件并在设计时设置属性来完成。不同的 TDataset 子类在这方面的细节差异很大,因此请参阅数据库中的各个手册,了解操作步骤。
数据集打开后,会创建一些字段组件,每个字段(或打开的表、查询的列)都对应一个字段组件。每个字段组件都是 TField 的子类,根据字段的数据类型(如 TStringField)对应某个子类。
通过代码使用数据集对象
关于以编程方式访问数据集对象的详细说明,请参阅数据集和字段组件的用法,不过以下给出了非常简单的描述:
- 用 TDataset 的子类打开表或查询,过滤出需查看的行,并在各行之间移动。
- 用 TField 的子类:
- 访问字段的定义信息
- 访问当前行的字段值(通过 AsString、AsInteger 之类的属性)
- 用以下方式访问 TDataset 子类的多个字段:
- Fields 属性,如 Fields[0] 是第一个字段
- FieldByName 方法,如 FieldByName('AGE') 返回数据库字段名为“AGE”的字段对象
字段类型的清单参见 Database_field_type。
Using the visual (data-aware) controls
To use databases in a simple, "RAD" style Lazarus application, you usually configure the dataset descendant at design time and the use the data-aware controls. To do this:
- Add the dataset descendant for the database of your choice, together with any supporting components, to your form, and open it (Set the ' Active' property to true )
- Add a TDataSource component (from the Data Access tab) to the form, and "link" it to the dataset (set the DataSet property)
- Add data-aware controls from the Data Controls tab to the form, and link each one to the DataSource (not dataset) component
- Most controls link to a single field, so you also need to set the Field for each tab.
See #Data Controls below for more details on the controls
Dataset State
Datasets can be in a number of states. While there are quite a few (look up TDataSetState in the source), the main ones to be aware of initally are
| State | Function |
|---|---|
| dsInactive | The dataset is closed |
| dsBrowse | The user can browse through the dataset, looking at values |
| dsEdit | The user can edit values on the current row.Values are not saved until a post is performed. |
| dsInsert | A new row has been added, and the user can set the values.The record is not saved until a post is performed |
The other states are fairly transitory, and are usually handled "automatically". They are used internally and in more complicated code. If your database only views data, and you open the dataset at design time, you can largely ignore the state, as it will mostly be dsBrowse. However, most applications will want to change the data at some stage. If you are using data-aware controls, they will handle a lot of this automatically. If you change the text in a TDBEdit control, for example, it will put the dataset into dsEdit state - unless you are already in dsEdit or dsInsert. If you "scroll" to a different record while the current state is dsEdit or dsInsert, that record will be "posted" and the dataset revert to dsBrowse. However, if you are accessing the dataset from code, you will often have to change the state in code as well. The TDBNavigator control (see below) allows the user to change the state explicitly.
Dataset UpdateStatus
UpdateStatus determines the current state of the record buffer, if updates have not yet been applied to the database.
Example how to detect if ApplyUpdates will Insert, Update or Delete data:
procedure QueryAfterPost(DataSet: TDataSet);
begin
case DataSet.UpdateStatus of
usUnmodified : ShowMessage('Unmodified');
usModified : ShowMessage('Modified');
usInserted : ShowMessage('Inserted');
usDeleted : ShowMessage('Deleted');
end;
end;
Value Explanation
- usUnmodified: Record is unmodified
- usModified: Record exists in the database but is locally modified
- usInserted: Record does not yet exist in the database, but is locally inserted
- usDeleted: Record exists in the database, but is locally deleted
Post and Cancel
If you have edited or inserted a record, the new values are held in a buffer.
- Calling the dataset cancel method removes the new record (insert) or reverts the values to their previous values (edit).
- Calling the dataset post method saves the values (edit) or record (insert). In some dataset descendants, they will be written to the database immediately, while in others they will be stored in a list of updates until a further call is made to save all changes to the database. Finally, even when they are written to the database, you may still have to call a "commit" method to make the database write them permanently. All of this also varies considerably with the dataset descendant, so look up the details for the one you are using.
Inserting a new record
To insert a new record into a TDataSet descendent, one should use the method Insert. After that one can set the field values and then finally call Post to commit the new record, as the example below shows.
The example also shows how to insert BLOB data from a file - you can also use LoadFromStream to load the data from a stream.
MyDataset.Insert;
MyDataset.Fields[0].AsInteger := 4; //an integer field
MyDataset.Fields[1].AsString := 'First Name'; //a string field
(MyDataset.Fields[2] as TBlobField).LoadFromFile('SomeBlobfile.bin'); //blob field
MyDataset.Post;
How to quickly jump to 1 particular record in the table
After selecting all records of the table
If you use SELECT * FROM to select all records of the table and then desires to quickly jump between them, you will have to build an index and search on it. It is more efficient to select only the record that you want.
Selecting only the desired record
One fast solution to jump to a particular record is to select only it, for example doing:
var
MyDataset: TSQLQuery;
begin
//...
MyDataset.FieldDefs.Add('SessionId', ftLargeint);
MyDataset.FieldDefs.Add('GameEvent', ftLargeint);
MyDataset.FieldDefs.Add('TableId', ftInteger);
MyDataset.FieldDefs.Add('LoggedIn', ftBoolean);
MyDataset.FieldDefs.Add('PlayerId', ftInteger);
MyDataset.Active := False;
{ Non-parameterized format; may run into issues with text containing ' and dates
SQLText := Format('select * from "GameSession" WHERE "SessionId"=%d', [ASessionId]);
}
// Solution: parameterized query:
// Actually, if this is done in a loop, you only need to set the SQL.Text once,
// and only change the parameter value
MyDataset.SQL.Text := 'select * from "GameSession" WHERE "SessionID"=:SessionID');
MyDataSet.ParamByName('SessionID').AsLargeInt := ASessionID;
try
MyDataset.Active := True;
except
//...
end;
You can then read information using something like this:
lPlayerId := MyDataset.Fields[4].AsInteger;
Filtering
You can filter your dataset to restrict the records to a subset you want (e.g. all surnames starting with Smith).
- Using .Filter:
- TDbf, TBufDataset and descendants (including TSQLQuery) use the TDBF filtering parser; see Lazarus Tdbf Tutorial#Expressions for details on using this.
- TMemDataset does not support .Filter
- Using a callback/event procedure with OnFilter where you can program your own filter function
Locate/lookup
Although more often used in non-SQL datasets (e.g. TParadoxDataSet, TDbf) you can jump between records using locate/lookup.
Using TSQLQuery
For more information about TSQLQuery see Working With TSQLQuery
Exporting
FPC/Lazarus contains functionality to let you export datasets to various formats; see e.g.
- fpXMLXSDExport
- fpdbfexport
- the other components on the Data Export tab
Of course, you could also do it manually (see e.g. FPSpreadsheet#Converting_a_database_to_a_spreadsheet for export to Excel format using fpspreadsheet)
Data Controls
To use any of these controls, add the control to a form and set at least the datasource property. Other key properties will be noted.
Datasource Control
This control keeps track of which record the linked controls currently are on. The datasource contorl must be linked to a dataset (e.g. a TSQLQuery).
Single Field Controls
These controls all attach to a single field. As well as datasource, set the field name. Controls include:
- DBText control Displays a text field (readonly, no border)
- DBEdit control Displays / edits a text field as an edit box
- DBMemo control Displays / edits a text field in a multi-line edit box
- DBImage control Displays a picture stored in a database as a BLOB. Note: by default, Lazarus stores a header with the image type before the image data in the database BLOB field. This is different from Delphi. However, you can make TDBImage Delphi compatible: see Lazarus For Delphi Users#TDBImage
- DBListBox control and DBComboBox Control Allow the user to insert values into a database field from the list of values in the Items property of the controls
- DBLookupListBox control and DBLookupComboBox control, see also TDBLookupComboBox Allow the user to insert values into a database field by displaying the contents of a lookup field in another table. Though these controls store their results in a single field, you need another field for the lookup values. Note: at lest for DBLookupComboBox, there is a bug with FPC 2.6.0 that requires the listfield to be present in the datasource as well, you can bypass it by declaring a calculated field with the same name as the listfield in the datasource's dataset that does nothing.
- DBCheckBox control Displays / edits a boolean field by checking/clearing a check box
- DBRadioGroup control Displays the items as in a normal radio group, reading/setting the field value from a matching values list
- DBCalendar control Displays / edits a date field using a calendar panel
- DBGroupBox control
DBGrid control
This control can show a number of fields in a row/column layout - in fact by default it shows them all. However, you can put entries into the columns collection to restrict it to specific fields and to set the widths and titles of each column.
Apart from the mentioned documentation, some more detail can be found here: Grids Reference Page#TCustomDBGrid
Navigator Control
This control gives the user some direct control over the dataset. It allows the user to:
- Move to the next or previous record, or to the start or end of the records
- Add a new record (equivalent to a dataset.insert method call)
- Put the dataset into edit mode
- Delete a record
- Post or Cancel current changes
- Refresh the data (useful in multiuser database applications)
Key Properties:
- VisibleButtons: Lets you control what the user can do. For example, if deletes are not allowed, hide the delete button. If you have a DBGrid attached to the same dataset, you may decide you do not need the next and prior buttons.
- Width: If you do not show all buttons, you may want to set the width to (height*number_of_visible_buttons)
Running FPC database tests
Free Pascal database components include a fpcunit-based test framework, dbtestframework, that can be used to verify functionality. See the directory source\packages\fcl-db\tests\ in your FPC source tree. Included is a test framework that can be run on various database components, as well as some other tests (e.g. test of database export).
To run the test framework on a certain database:
1. Save source\packages\fcl-db\tests\database.ini.txt as source\packages\fcl-db\tests\database.ini
2. Modify source\packages\fcl-db\tests\database.ini to choose which database type you will use.
Example for Interbase/Firebird:
[Database]
type=interbase
3. In the same file, customize settings for your database. E.g. if you chose interbase before:
[interbase]
connector=sql
connectorparams=interbase
; Database name/path (note: database needs to exist already)
; You can use aliases (see aliases.conf in your Firebird documentation)
name=testdb
user=sysdba
password=masterkey
; your hostname may very well differ:
; Leave blank if you want to use an embedded Firebird database
hostname=192.168.0.42
4. Compile and run source\packages\fcl-db\tests\dbtestframework.pas (You can also use Lazarus to compile and run the GUI version, dbtestframework_gui) If you are using an embedded database on Windows (e.g. Firebird embedded or sqlite), copy the required DLL files to the directory first. The output will be in XML format (or displayed on your screen if you use dbtestframework_gui).
Please see source\packages\fcl-db\tests\README.txt for more details.
Database packages contained in Lazarus
sqldblaz.lpk
This package provides access to different databases. These include:
- Interbase/Firebird
- Microsoft SQL Server (except on Lazarus/FPC x64 for Windows)
- MySQL
- Oracle (except on Lazarus/FPC x64 for Windows)
- PostgreSQL (except on Lazarus/FPC x64 for Windows)
- SQLite (with support for the Spatialite extension)
- Sybase ASE (Adaptive Server Enterprise - not to be confused with Sybase ASA) (except on Lazarus/FPC x64 for Windows)
- any database that has an ODBC driver.
The components (TSQLQuery, TSQLTransaction, TIBConnection, TODBCConnection, TOracleConnection, TMSSQLConnection, TMySQL40Connection, TMySQL41Connection, TMySQL50Connection, TPQConnection, TSybaseConnection) are on the 'SQLdb' tab in the component palette.
dbflaz.lpk
This package provides access to dBase and FoxPro databases. You can get more information in the Lazarus Tdbf Tutorial. The TDbf component is on the Data Access tab in the component palette.
sqlitelaz.lpk
This package provides access to SQLite databases. You can get more information in the Lazarus Database Overview.
sdflaz.lpk
The component TSdfDataSet can be found on the Data Access tab in the component palette.
lazreport.lpk
The homepage of the report generator is http://lazreport.sourceforge.net/. More informationen (et al. an additional link) can be found here. LazReport depends on the Printer4Lazarus package. With revision 11950 LazReport was included in the Lazarus SVN repository.
lazdbexport.lpk
See lazdbexport.
External packages / libraries
Zeos DataBase Objects
These components provide access to different databases. You can find more information here. This wiki also contains a Zeos tutorial.
Pascal Data Objects
There is now an alternative.
Support:
- MySQL 4.1 and 5.0
- sqlite-2 and sqlite-3
- pgsql-8.1
- interbase-5, interbase-6, firebird-1.0, firebird-1.5, firebird-1.5E, firebird-2.0, firebird-2.0E
- mssql (Microsoft library) and sybase (FreeTDS library)
- oracle
like prepared statements, binding, and stored procedures are supported by database API called Pascal Data Objects, which is inspired by PHP Data Objects. All the code and documentation necessary to use this new API is available on Sourceforge:
TPSQL
These components provide access via TCP/IP to PostgreSQL databases. You can find more information on this page.
FIBL
These components provide access to Interbase and Firebird databases. The homepage is http://sourceforge.net/projects/fibl.
IBX
IBX For Lazarus are components to access Firebird databases: see IBX
FBLib Firebird Library
FBLib is an open Source Library No Data Aware for direct access to Firebird Relational Database from Borland Delphi/Kylix, Free Pascal and Lazarus.
Current Features include:
- Direct Access to Firebird 1.0.x, 1.5.x and 2.x Classic or SuperServer
- Multiplatform [Win32,Gnu/Linux,FreeBSD)
- Automatic select client library 'fbclient' or 'gds32'
- Query with params
- Support SQL Dialect 1/3
- LGPL License agreement
- Extract Metadata
- Simple Script Parser
- Only 100-150 KB added into final EXE
- Support BLOB Fields
- Export Data to HTML SQL Script
- Service manager (backup,restore,gfix...)
- Events Alerter
You can download documentation on FBLib's website.
Unified Interbase
UIB provides access to Interbase, Firebird and YAFFIL databases. The homepage is www.progdigy.com. A svn repository is available under https://uib.svn.sourceforge.net/svnroot/uib .
TechInsite Object Persistence Framework (tiOPF)
More information about tiOPF can be found on this page.
Advantage TDataSet Descendant
The Advantage TDataSet Descedant provides a means of connecting to (and opening tables with) the Advantage Database Server. Advantage is a flexible, administration-free embedded database that provides Client/Server as well as Peer-to-peer access to Clipper, FoxPro and Visual FoxPro 9 DBF file formats, as well as a proprietary file format that provides a migration path allowing the use of newer features.
Key Features:
- Royalty-free peer-to-peer database access with migration path to Client/Server
- Multi-Platform (Clients supported on Windows and Linux, Server supported on Windows, Linux, and NetWare)
- Supports Both navigational and relational SQL database access
- Full-text search engine
- Table, Index, Memo, and communication encryption
- Compatible with native TDataset components
- Online Backup
- Server supports Replication
For more information, see the Advantage Database Server website.
ZMSQL, sql-enhanced in-memory database
For more information, see the ZMSQL wiki page
ZMSQL is an open source, TBufDataset descendant SQL enhanced in-memory database for Free Pascal (FPC), operating with semicolon-separated values flat text tables. Completely written in Pascal, it has no dependencies on external libraries. It uses JanSQL engine for SQL implementation.
It offers:
- Loading from and saving to flat text tables
- Use of SQL to query the data
- Copy data and schema from other datasets
- Option to predefine fielddefs or create it on-the fly
- Master/detail filtering
- Referential integrity
- Parameterized queries
The download contains the source code, some demo applications illustrating the features of the component as well as a readme.