# Lazarus Database Tutorial/zh CN

Deutsch (de) English (en) español (es) français (fr) Bahasa Indonesia (id) italiano (it) 日本語 (ja) Nederlands (nl) português (pt) русский (ru) 中文（中国大陆）‎ (zh_CN) 中文（台灣）‎ (zh_TW)

## 概述

Lazarus 支持多种数据库的‘开箱即用’，当然，开发者必须预先安装要使用的数据库系统。访问数据库，要么通过编写代码实现，要么通过可视化的拖拉控件到窗体来实现。数据感知（data-aware）控件将DataSource 属性指向TDataSource 控件,就能显示数据库字段内容. Datasource 控件将DataSets 属性连接到 database类组件（例如：TPSQLDatabase，TSQLiteDataSet）,就能显示表的内容. 数据感知组件位于“数据控制（Data Control）”标签页。数据源(Datasource)和数据库控件位于“数据操作（Data Access）”标签。

## Lazarus 和 MySQL

### 从FPC文本模式连接MySQL

（Get MySQL working for FPC in text mode）

（译注：略，请参见英文页面） See Sample Console Listing.

### 从Lazarus程序连接到MySQL

#### 界面部分

Project -> New Project -> Application


#### 代码部分

The responses from the database cannot now be written using the Pascal write or writeln statements: rather, the replies have to be converted into strings and displayed in the Memo box. Whereas the Pascal write and writeln statements are capable of performing a lot of type conversion 'on the fly', the use of a memo box for text output necessitates the explicit conversion of data types to the correct form of string, so Pchar variables have to be converted to strings using StrPas, and integers have to be converted with IntToStr. 来自数据库的响应现在还不能写，用Pascal写，或者写成（writeln）报表（statements）：因为（rather），结果必须被转换为字串并显示在Memo框内。但是Pascal写和writeln语句是……大量类型转换‘，用memo况来显示文本需要详细地转换数据类型，正确的表单字串，所以，Pchar变量必须转换为字串，用 StrPas，整数用IntToStr转换。

procedure ShowString (S : string);
(* display a string in a Memo box *)
begin
end;


procedure TtrymysqlForm1.ConnectButtonClick(Sender: TObject);
(* Connect to MySQL using user data from Text entry boxes on Main Form *)
var strg: string;
begin
dummy1 :=  trymysqlForm1.HostEdit.text+#0;
host := @dummy1[1];
dummy2 := trymysqlForm1.UserEdit.text+#0;
user := @dummy2[1] ;
dummy3 := trymysqlForm1.PasswdEdit.text+#0;
passwd := @dummy3[1] ;
alloc := mysql_init(PMYSQL(@qmysql));
sock :=  mysql_real_connect(alloc, host, user, passwd, database, 0, nil, 0);
if sock=Nil then
begin
strg :='Couldnt connect to MySQL.'; showstring (strg);
Strg :='Error was: '+ StrPas(mysql_error(@qmysql)); showstring (strg);
end
else
begin
trymysqlForm1.statusBar1.simpletext := 'Connected to MySQL';
strg := 'Now choosing database : ' + database; showstring (strg);
{$ifdef Unix} strg :='Mysql_port : '+ IntToStr(mysql_port); showstring (strg); strg :='Mysql_unix_port : ' + StrPas(mysql_unix_port); showstring (strg); {$endif}
Strg :='Host info       : ' + StrPas(mysql_get_host_info(sock));
showstring (strg);
Strg :='Server info     : ' + StrPas(mysql_stat(sock)); showstring (strg);
Strg :='Client info     : ' + Strpas(mysql_get_client_info);  showstring (strg);

trymysqlForm1.statusbar1.simpletext := 'Selecting Database ' + DataBase +'...';
if mysql_select_db(sock,DataBase) < 0 then
begin
strg :='Couldnt select database '+ Database; ShowString (strg);
Strg := mysql_error(sock); ShowString (strg);
end
end;
end;


The SendQuery event handler is again based on the FPC text-mode version, except that once again explicit type-conversion has to be done before strings are displayed in the box.

A difference from the text-mode FPC program is that if an error condition is detected, the program does not halt and MySQL is not closed; instead, control is returned to the main form and an opportunity is given to correct the entry before the command is re-submitted. The application finally exits (with closure of MySQL) when the Exit Button is clicked.

procedure TtrymysqlForm1.QueryButtonClick(Sender: TObject);
var
dumquery, strg: string;
begin
dumquery := TrymysqlForm1.CommandEdit.text;
dumquery := dumquery+#0;
query := @dumquery[1];
trymysqlForm1.statusbar1.simpletext := 'Executing query : '+ dumQuery +'...';
strg := 'Executing query : ' + dumQuery; showstring (strg);
if (mysql_query(sock,Query) < 0) then
begin
Strg :='Query failed '+ StrPas(mysql_error(sock)); showstring (strg);
end
else
begin
recbuf := mysql_store_result(sock);
if RecBuf=Nil then
begin
Strg :='Query returned nil result.'; showstring (strg);
end
else
begin
strg :='Number of records returned  : ' + IntToStr(mysql_num_rows (recbuf));
Showstring (strg);
Strg :='Number of fields per record : ' + IntToStr(mysql_num_fields(recbuf));
showstring (strg);
rowbuf := mysql_fetch_row(recbuf);
while (rowbuf <>nil) do
begin
Strg :='(Id: '+ rowbuf[0]+', Name: ' + rowbuf[1]+ ', Email : ' +
rowbuf[2] +')';
showstring (strg);
rowbuf := mysql_fetch_row(recbuf);
end;
end;
end;
end;


• 下载 MYSQL 示例源代码

### 使用TMySQL50Connection控件连接

procedure TfrmMain.btnMYSQLClick(Sender: TObject);
begin
try
//conn_mysql5是TMySQL50Connection
conn_mysql5.HostName := '127.0.0.1';//主机
conn_mysql5.CharSet := 'utf8';//如果在MYSQL中建表设置了UTF8，此处也设置，中文显示是正常的
conn_mysql5.DatabaseName := 'test'; //MYSQL的数据库
conn_mysql5.Open;
except
on E: Exception do
begin
ShowMessage('连接出错：' + E.Message);
Exit;
end;
end;
//打开一个表
SQLQuery1.Close;
SQLQuery1.SQL.Text := 'select * from t1';
try
SQLQuery1.Open;
except
on E: Exception do
begin
ShowMessage('打开表出错：' + E.Message);
Exit;
end;
end;
end;



## Lazarus 和 PostgreSQL

After correct install, follow these steps:

• Place a PQConnection from the SQLdb tab
• Place a SQLQuery from the SQLdb tab
• Place a SQLTransaction from the SQLdb tab
• Place a DataSource from the DataAccess tab
• Place a DBGrid from the DataControls tab
• In the PQConnection fill in:
• transaction property with the respective SQLTransaction object
• Database name
• HostName
• Check that the SQLTransaction was automatically changed to point to the PQConnection
• In the SQLQuery fill in:
• transaction property with the respective object
• database property with respective object
• SQL (something like 'select * from anytable')
• In the DataSource object fill in the DataSet property with the SQLQuery object
• In the DBGrid fill in the datasource as the DataSource Object

Turn everything to connected and active and the DBGrid should be filled in design time. TDBText and TDBEdit seem to work but (for me) they only _show_ _data_.

To change contents in the database, I called the DB Engine direct with the following code:

 try
sql:= 'UPDATE table SET setting=1';
PQDataBase.Connected:=True;
PQDataBase.ExecuteDirect('Begin Work;');
PQDataBase.ExecuteDirect(sql);
PQDataBase.ExecuteDirect('Commit Work;');
PQDataBase.Connected:=False;
except
on E : EDatabaseError do
MemoLog.Append('DB ERROR:'+sql+chr(13)+chr(10)+E.ClassName+chr(13)+chr(10)+E.Message);
on E : Exception do
MemoLog.Append('ERROR:'+sql+chr(13)+chr(10)+E.ClassName+chr(13)+chr(10)+E.Message);
end;


• 备注:
• Tested on windows, Lazarus 0.9.12 + PgSQL 8.3.1
• Some tests in linux, Lazarus 0.9.12 and PgSQL 8.0.x

• Instalation and errors:
• In the tested version of Lazarus .12, fields of type "text" and "numeric" have bugs
• I used with no problems char fixed size, int and float8
• Sometimes restarting Lazarus solves stupid errors...
• After some errors, the transactions remain active and should be deactivated mannually
• Changes made in Lazarus are of course not visible until transaction commited
• The integrated debugger seems buggy (at least in windows) - sometimes running outside of the IDE may help to find errors
• In linux certain error messages are printed in the console -- run your program in the command line, sometimes there is some extra useful debugging info
• Error: "Can not load Postgresql client. Is it installed (libpq.so) ?"
• Add the path to seach libpq* from the PostgreSQL installation.
• In linux add the path to the libpq.so file to the libraries section in your /etc/fpc.cfg file. For example : -Fl/usr/local/pgsql/lib
• In windows, add these libs anywhere in the Path environment variable or project dir
• I windows, I copied all the DLLs in my C:\Program Files\PostgreSQL\8.1\bin dir to another dir in the PATH
• Or add this postgres\bin dir to the path

## Lazarus and SQLite

by Luiz Américo

Visit the sqlite4fpc homepage to find the API reference and more tutorials.

### Introduction

TSqliteDataset and TSqlite3Dataset are TDataset descendants that access, respectively, 2.8.x and 3.x.x sqlite databases. Below is a list of the principal advantages and disadvantages:

• Flexible: programmers can choose to use or not to use the SQL language, allowing them to work with simple table layouts or any complex layout that SQL/sqlite allows
• Automatic database update: no need to update the database manually with SQL statements, a single method take cares of it
• Fast: it caches the data in memory, making browsing in the dataset fast
• No server installation/configuration: just ship together with sqlite dynamic library

• Requires external file (sqlite library)

### Requirements

• For sqlite2 databases:
• fpc 2.0.0
• Lazarus 0.9.10
• sqlite runtime library 2.8.15 or above (get from www.sqlite.org)
• For sqlite3 databases:
• fpc 2.0.2
• Lazarus 0.9.11 (svn revision 8443 or above)
• sqlite runtime library 3.2.1 or above (get from www.sqlite.org)

Before initiating a lazarus projects, ensure that:

• the sqlite library is on the system PATH or in the executable directory
• under Linux, put cmem as the first unit in uses clause of the main program

### How To Use (Basic Usage)

Install the package found at /components/sqlite directory (see instructions here)

At design time set the following properties:

• FileName: path of the sqlite file [required]
• TableName: name of the table used in the sql statement [required]
• Sql: a SQL select statement [optional]

Creating a Table (Dataset)

Double click in the component icon or use the 'Create Table' item of the popup menu that appears when clicking the right mouse button. A simple self-explaining table editor will be show.

 Here is all field types supported by TSqliteDataset and TSqlite3Dataset:

Integer
AutoInc
String
Memo
Bool
Float
Word
DateTime
Date
Time
LargeInt
Currency



Retrieving the data

After creating the table or with a previously created Table, open the dataset with Open method. If the SQL property was not set then all records from all fields will be retrieved, the same if you set the SQL to:

 SQL:='Select * from TABLENAME';


Applying changes to the underlying datafile

To use the ApplyUpdates function, the dataset must contain at least one field that fills the requirements for a Primary Key (values must be UNIQUE and not NULL)

It's possible to do that in two ways:

• Set PrimaryKey property to the name of a Primary Key field
• Add an AutoInc field (This is easier since the TSqliteDataSet automatically handles it as a Primary Key)

If one of the two conditions is set then just call

 ApplyUpdates;


PS1: If both conditions are set, the field corresponding to PrimaryKey is used to apply the updates.

PS2: Setting PrimaryKey to a field that is not a Primary Key will lead to loss of data if ApplyUpdates is called, so ensure that the chosen field contains not Null and Unique values before using it.

### Remarks

• Although it has been tested with 10000 records and worked fine, TSqliteDataset keeps all the data in memory, so remember to retrieve only the necessary data (principally with Memo Fields).
• The same datafile (Filename property) can host several tables/datasets
• Several datasets (different combinations of fields) can be created using the same table simultaneously
• It's possible to filter the data using WHERE statements in the sql, closing and reopening the dataset (or calling RefetchData method). But in this case, the order and number of fields must remain the same
• It's also possible to use complex SQL statements using aliases, joins, views in multiple tables (remember that they must reside in the same datafile), but in this case ApplyUpdates won't work. If someone wants to use complex queries and to apply the updates to the datafile, mail me and I will give some hints how to do that
• Setting filename to a sqlite2.x datafile not created by TSqliteDataset and opening it is allowed but some fields won't have the correct field type detected. These will be treated as string fields.

Generic examples can be found at fpc/fcl/db/sqlite CVS directory

Luiz Américo pascalive(at)bol(dot)com(dot)br

## Lazarus and MSSQL

It is working with Zeoslib (latest cvs), see the links on bottom of page.

## Lazarus and Interbase / Firebird

See Install Packages. On this page is a first small example en explanation about how to connect to an IB or FB server.

Also work with the latest Zeoslib (from cvs).

### FBLib Firebird Library

[1] is an open Source Library No Data Aware for direct access to Firebird Relational Database from Borland Delphi / Kylix, Freepascal and Lazarus.

Current Features include:

• Multiplatform [Win32,Gnu/Linux,FreeBSD)
• Automatic select client library 'fbclient' or 'gds32'
• Query with params
• Support SQL Dialect 1/3
• 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...)