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工作于多种已知数据库。

Lazarus支持几种数据库的‘开箱即用’，当然，开发者必须为每种安装适当的包。你能够访问数据库，通过写代码，或者向一个表单（form）里面拖拉组件。数据感知（data-aware）组件能描绘字段，并且连接到数据库组件（例如：TPSQLDatabase，TSQLiteDataSet）通过设置‘数据集’（DataSet）属性。数据感知组件位于（辣子的界面）“Data Controls”标签（数据控制）。数据源和数据库控制位于“Data Access”标签（数据操作）。

Lazarus 和 MySQL

在Lin或Win下使MySQL工作

跟随小麦用户手册的介绍，确认小麦的守护进程（服务）运行可靠，并且所有潜在用户（包括：root、mysql、你自己还有任何需要的人）拥有他们需要的权限，来自多个主机或许（包括‘localhost’，本地主机名，任何其他主机在你的网络上）足够安全。最好包括root在内的所有账户都有密码。用手册中的例子测试数据库系统的动作，并且检查所有用户真的能够可靠存取。

从FPC文本模式连接MySQL

（Get MySQL working for FPC in text mode）

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

从Lazarus程序连接到MySQL

该教程显示如何连接辣子到小麦数据库，并且执行一个简单的查询，仅使用基本的辣子组件；这里没有用到数据感知组件，但能更好得阐述连接数据库接口的原理。

界面部分

Create a new project in Lazarus:

Project -> New Project -> Application

一个自动生成的表单将出现。

扩大表单至填充半个屏幕，然后重命名表单的标题为‘TryMySQL’（译注：随便起个名字，和下面一致就行）。

From the Standard Component tab place three Edit Boxes on the upper left side of the Form, and immediately above each box place a label. Change the names and captions to 'Host' (and HostLLabel,HostEdit), 'UserName' (and UserLabel, UserEdit) and 'Password' (with PasswdLabel and PasswdEdit). Alternatively you could use LabelledEdit components from the Additional tab.

Select the Passwd Edit box and find the PasswordChar property: change this to * or some other character, so that when you type in a password the characters do not appear on your screen but are echoed by a series of *s. Make sure that the Text property of each edit box is blank.

Now place another Edit box and label at the top of the right side of your form. Change the label to 'Enter SQL Command' and name it CommandEdit.

Place three Buttons on the form: two on the left under the Edit boxes, and one on the right under the command box.

Label the buttons on the left 'Connect to Database' (ConnectButton)and 'Exit' (ExitButton) and the one on the right 'Send Query' (QueryButton).

Place a large Memo Box labelled and named 'Results' (ResultMemo) on the lower right, to fill most of the available space. Find its ScrollBars property and select ssAutoBoth so that scroll bars appear automatically if text fills the space. Make the WordWrap property True.

Place a Status Bar (from the Common Controls tab) at the bottom of the Form, and make its SimpleText property 'TryMySQL'.

到这里可以看到一张表单的截图：Mysql Example Screenshot（译注：早说嘛，想那么久，最好能直接显示，是ccr的图）

代码部分

现在，我们需要写一些事件处理。

The three Edit boxes on the left are for entry of hostname, username and password. When these have been entered satisfactorily, the Connect Button is clicked. The OnCLick event handler for this button is based on part of the text-mode FPC program above.

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.

Strings are displayed in the Memo box using

procedure ShowString (S : string);
(* display a string in a Memo box *)
begin
       trymysqlForm1.ResultsMemo.Lines.Add (S)
end;

The ConnectButton event handler thus becomes:

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 Text Box on the right allows entry of a SQL statement, without a terminal semicolon; when you are satisfied with its content or syntax, the SendQuery button is pressed, and the query is processed, with results being written in the ResultsMemo box.

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.

The code for SendQuery follows:

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;

Save your Project, and press Run -> Run

下载 MYSQL 源代码

A full listing of the program is available here Sample Source Code

Lazarus 和 PostgreSQL

This is a very short tutorial to get Lazarus 0.9.12 or later to connect to a PostgreSQL database, local or remote, using TPQConnection.

（略，建议另开新页）

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:

Advantages:

• 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

Disadvantages

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

• Direct Access to Firebird 1.0.x 1.5.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.

Lazarus 和 dBase

Tony Maro： You might also want to visit the beginnings of the TDbf Tutorial page （译注：略，建议另开新页）

Related Links

• Creating Databases Applications with Lazarus using the DB-unit
  
• Creating a database application using MySQL
  
• Creating a database application using the Zeos components
• Pascal Data Objects is a database API that worked for both FPC and Delphi and utilises native MySQL libraries for version 4.1 and 5.0 and Firebird SQL 1.5, and 2.0. It's inspired by PHP's PDO class.

Contributors and Changes

This page has been converted from the epikwiki version.