SQLdb Tutorial1/zh CN: what changed

Revision 1 to 2. Removed lines have a minus sign; added lines have a plus sign.

Old	New		Line
1	1		## 介绍(Introduction)
2	2
3	3		编撰目的: 通过实例演示[[SQLDB包]]的使用。
4	4
5	5		阅读对象: 数据库编程的初学者/新手。如果对文中涉及的数据库和SQL的基础知识不太清楚，请阅读相应的书籍。
6	6
7	7		使用的数据库： Firebird和她自带的示例数据库employee.fdb。不用Firebird也可以使用其它数据库，但需要作些调整，文中相应位置有补充说明。
8	8
9	9		文章太长？：虽然本教程看起来很长，但笔墨大多用于解释要求你键入相关内容的原因。在文章末尾你可以看到，创建一个能够正常运行的应用程序，你实际上编写的代码量不是很大。对于许多有经验的开发者而言，你可以略读（就是忽略已经熟知的细节而快速翻阅各操作步骤的主要内容），并能迅速理解下一步将要做的事情。还嫌累赘的话，读到基本示例一节结尾处即可停止阅读，而且你已经创建了一个能够工作的程序。
10	10
11	11		译文来源： Swen编撰了[[SQLdb Tutorial1/de\|德语版]]，BigChimp做了英译，但对内容做了扩展，特别是基本示例后的部分。 lioncn根据英文版翻译为[[SQLdb Tutorial1/zh CN\|简体中文]]（翻译ing,请耐心等待）。德语用户请注意：Swen希望保留德语版的原样。如果你想修改德语版，你可以将Swen的版本重新命名并在此基础上重新编撰一个新的德语版。
12	12
13	13		致谢：没有Joost 和 Michael 的帮助本教程可能永远不会出现。
14	14
15	15		（原文：This tutorial intends to show the usage of the [[SQLdb Package]] on the basis of practical examples. It is primarily targeted at newbies. If somebody is looking for basics about databases and SQL, he should read the corresponding books. For this tutorial I use Firebird with the example database employee.fdb. Other databases can also be used; some adjustments will need to be made which are mentioned in the text.
16	16
17	17		While this tutorial may seem long, it mostly is just a lot of text that explains why you should type what you type. As you can see at the end, the amount of actual code you will need for a working application is not that great. More experienced developers will hopefully be able to glance through the instructions and quickly understand what's going on. Also, you can stop at the end of the Basic example chapter and have a working program.
18	18
19	19		This tutorial is based on a [[SQLdb Tutorial1/de\|German tutorial]] by Swen, but it is extended, especially after the Basic example. Swen wants the German version to remain as-is. If this is a problem, we can rename this version and base a new German translation on that.
20	20
21	21		Thanks to Joost and Michael. Without their help this tutorial probably never would have come about.）
22	22
23	23		## 系统需求(Requirements)
24	24
25	25		Lazarus：为减少示范实例出错的可能，尽量使用最新版的Lazarus (要求随带的FPC 不低于2.2.2)。如果找不到SQLdb包，现在就安装(Package -\> Install/Uninstall packages ... -\> SQLDBLaz 1.0.1)
26	26
27	27		Firebird数据库：安装不低于2.0版的[Firebird](http://sourceforge.net/project/showfiles.php?group_id=9028)。示范实例假定你使用标准安装而未作任何修改(比如管理员用户名为 SYSDBA，密码为 masterkey)，而且安装了示例数据库 employee.fdb 。
28	28
29	29		其他数据库：也可使用MySQL, PostgreSQL, Oracle, SQLite 或其他的使用 ODBC 桥接的数据库：要求数据库/表结构必须根据所使用的数据库而作相应修改（见下文），并使用继承自“TSQLConnector”的对应数据库组件（也如下所示）进行连接。如果所使用的数据库对于事务处理等方面与Firebird有显著差异，请在有关条文的做说明以方便后来者。
30	30
31	31		（原文：If possible you should use a recent Lazarus version (with FPC 2.2.2 or newer) for this tutorial. If the SQLdb package isn't already installed, you should do it now (Package -\> Install/Uninstall packages ... -\> SQLDBLaz 1.0.1).
32	32
33	33		Furthermore you need [Firebird](http://sourceforge.net/project/showfiles.php?group_id=9028) (if possible version 2.0 or newer). The examples assume that the standard settings (e.g user name SYSDBA and password masterkey) were not changed, and that you have the employee sample database installed.）
34	34
35	35		You can use another database (e.g. MySQL, PostgreSQL, Oracle, SQLite or another database using ODBC): you'd have to have the proper database /table structure (see below), and use the relevant TSQLConnector descendant (also indicated below). If your database has significant differences regarding transactions etc. please make a note in the relevant section.）
36	36
37	37		### Win平台下使用Firebird数据库必备的库文件(Firebird database libraries on Windows)
38	38
39	39		Firebird客户端DLL：在win平台的开发机上必须安装Firebird客户端DLL。安装位置可以是win的系统目录（如\windows\system32），但最好是安装在开发项目的目录和可执行文件的输出目录中，这样做的好处是，一来是在集成开发环境中开发设计时就能看到数据，二来是能够正确无误地运行编译过的程序。
40	40
41	41		安装Firebird客户端DLL的最容易的方法是：[下载安装Firebird 2.5的嵌入版](http://www.firebirdsql.org/en/firebird-2-5/)
42	42
43	43		方法如下：
44	44
45	45		展开下载的压缩包，将下列文件复制到开发项目目录： \<bash\> fbembed.dll firebird.msg ib_util.dll icudt30.dll icuin30.dll icuuc30.dll Microsoft.VC80.CRT.manifest msvcp80.dll msvcr80.dll \</bash\> Firebird分为两种结构：嵌入式和C/S结构。它们所使用的客户端DLL是通用的。所以再将嵌入式使用的fbembed.dll 改名为C/S结构使用的 fbclient.dll即可，或者将fbembed.dll复制为新文件fbclient.dll，就完成了Firebird客户端DLL的安装。
46	46
47	47		最后，编译项目－－就算项目为空也编译－－会自动创建输出目录，复制DLL到输出目录。
48	48
49	49		（原文：Finally, on Windows you will need to have the Firebird client DLLs present, optionally in your system directory, but preferably in your project directory (for design time support in the IDE) and in the output directory where the executable is (to run the compiled program). One easy way to get the client DLLs is: download Firebird Embedded 2.5 from [1](http://www.firebirdsql.org/en/firebird-2-5/) Extract these files to your application directory: \<bash\> fbembed.dll firebird.msg ib_util.dll icudt30.dll icuin30.dll icuuc30.dll Microsoft.VC80.CRT.manifest msvcp80.dll msvcr80.dll \</bash\> Rename fbembed.dll to fbclient.dll (the name for a regular, client-server Firebird client). The embedded Firebird DLL can also act as a regular Firebird client.
50	50
51	51		Finally, compile your project (even if it empty) once to create the output directory, and copy the dlls into that directory.）
52	52
53	53		### 其他平台下使用Firebird数据库必备的库文件(Firebird database libraries on other systems)
54	54
55	55		在Linux/OSX下，也需要安装Firebird客户端共享库文件。在linux下，使用对应发行版的程序安装方法来安装Firebird客户端包，如Debian中执行 \<bash\> aptitude install libfbclient2 \</bash\> 来完成安装。
56	56
57	57		（原文：On Linux/OSX, you will also need the Firebird client shared libraries. On Linux you can use your distribution's method of getting programs to get the Firebird client packages, e.g. on Debian: \<bash\> aptitude install libfbclient2 \</bash\> ）
58	58
59	59		### 未安装employee.fdb数据库?(No employee.fdb installed?)
60	60
61	61		如果没有安装示例数据库 employee.fdb，或者使用其它数据库，请使用如下SQL语句生成最小版本的数据表CUSTOMER，文中后面会利用到。
62	62
63	63		``` sql
64	64		/* 创建数据表： */
65	65		CREATE TABLE CUSTOMER
66	66		(
67	67		CUST_NO CUSTNO NOT NULL,
68	68		CUSTOMER VARCHAR(25) NOT NULL,
69	69		CITY VARCHAR(25),
70	70		COUNTRY VARCHAR(15),
71	71		CONSTRAINT INTEG_60 PRIMARY KEY (CUST_NO)
72	72		);
73	73
74	74		/* 录入几条数据： */
75	75		INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2001', 'Michael Design', 'San Diego', 'USA');
76	76		INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2002', 'VC Technologies', 'Dallas', 'USA');
77	77		INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2003', 'Klämpfl, Van Canneyt and Co.', 'Boston', 'USA');
78	78		INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2004', 'Felipe Bank', 'Manchester', 'England');
79	79		INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2005', 'Joost Systems, LTD.', 'Central Hong Kong', 'Hong Kong');
80	80		INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2006', 'Van der Voort International', 'Ottawa', 'Canada');
81	81		INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2007', 'Mrs. Mauvais', 'Pebble Beach', 'USA');
82	82		INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2008', 'Asinine Vacation Rentals', 'Lihue', 'USA');
83	83		INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2009', 'Fax', 'Turtle Island', 'Fiji');
84	84		INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2010', 'FPC Corporation', 'Tokyo', 'Japan');
85	85		INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2011', 'Dynamic Intelligence Corp', 'Zurich', 'Switzerland');
86	86		INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2012', '3D-Pad Corp.', 'Paris', 'France');
87	87		INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2013', 'Swen Export, Ltd.', 'Milan', 'Italy');
88	88		INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2014', 'Graeme Consulting', 'Brussels', 'Belgium');
89	89		INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2015', 'Klenin Inc.', 'Den Haag', 'Netherlands');
90	90		```
91	91
92	92		(原文：If you don't have the employee sample database installed or are using a different database, here is a minimal version of the table we'll be using:
93	93
94	94		``` sql
95	95		CREATE TABLE CUSTOMER
96	96		(
97	97		CUST_NO CUSTNO NOT NULL,
98	98		CUSTOMER VARCHAR(25) NOT NULL,
99	99		CITY VARCHAR(25),
100	100		COUNTRY VARCHAR(15),
101	101		CONSTRAINT INTEG_60 PRIMARY KEY (CUST_NO)
102	102		);
103	103		```
104	104
105	105		Some data so you can at least show something:
106	106
107	107		``` sql
108	108		INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2001', 'Michael Design', 'San Diego', 'USA');
109	109		INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2002', 'VC Technologies', 'Dallas', 'USA');
110	110		INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2003', 'Klämpfl, Van Canneyt and Co.', 'Boston', 'USA');
111	111		INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2004', 'Felipe Bank', 'Manchester', 'England');
112	112		INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2005', 'Joost Systems, LTD.', 'Central Hong Kong', 'Hong Kong');
113	113		INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2006', 'Van der Voort International', 'Ottawa', 'Canada');
114	114		INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2007', 'Mrs. Mauvais', 'Pebble Beach', 'USA');
115	115		INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2008', 'Asinine Vacation Rentals', 'Lihue', 'USA');
116	116		INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2009', 'Fax', 'Turtle Island', 'Fiji');
117	117		INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2010', 'FPC Corporation', 'Tokyo', 'Japan');
118	118		INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2011', 'Dynamic Intelligence Corp', 'Zurich', 'Switzerland');
119	119		INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2012', '3D-Pad Corp.', 'Paris', 'France');
120	120		INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2013', 'Swen Export, Ltd.', 'Milan', 'Italy');
121	121		INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2014', 'Graeme Consulting', 'Brussels', 'Belgium');
122	122		INSERT INTO CUSTOMER (CUST_NO, CUSTOMER, CITY, COUNTRY) VALUES ('2015', 'Klenin Inc.', 'Den Haag', 'Netherlands');
123	123		```
124	124
125	125		）
126	126
127	127		## 基本示例(Basic example)
128	128
129	129		### 项目与所需组件(Project and components)
130	130
131	131		首先，创建一个新Lazarus项目。
132	132
133	133		在组件面板的SQLdb控件页拖拉一个TIBConnection, 一个 TSQLTransaction 和一个 [[Working With TSQLQuery\|TSQLQuery]] 控件到窗体。
134	134
135	135		TIBConnection：是 Interbase/Firebird 专用连接组件，使用其它数据库，就必须换用 'SQLDB' 页的相应的其它数据库连接组件，如使用 TSQLite3Connection 连接 SQLite 数据库。关于设置其它数据库连接的方法不在本文讨论范围，感兴趣的可参见 [[Databases]] 详细讨论。
136	136
137	137		TSQLTransaction 和 TSQLQuery： SQLdb 连接所支持的所有数据库都会利用到这两个组件。
138	138
139	139		显示数据：利用 'Data Controls' 页的 TDBGrid 组件。先添加 'Data Access' 页内一个 TDatasource 控件后才能将 TDBGrid 连接到数据库。例中使用 'Standard' 页的 TButton 来触发这个动作。
140	140
141	141		现在已经添加好了首个示例中必须的所有组件。加大 TDBGrid 大小以有足够空间显示表中所有数据。
142	142
143	143		（原文：First you should create a new Lazarus project.
144	144
145	145		To get access to our database we need one TIBConnection, one TSQLTransaction and one [[Working With TSQLQuery\|TSQLQuery]] component from the 'SQLdb' tab in the component palette. TIBConnection is an Interbase/Firebird specific connection component. If you are using a different database, substitute the proper component from the 'SQLDB' tab, e.g. a TSQLite3Connection for an SQLite database. Discussion of setting up any database access libraries is out of scope for this tutorial; see e.g. [[Databases]] for that.
146	146
147	147		The other two components, TSQLTransaction and TSQLQuery, can be used for all databases that are supported by SQLdb.
148	148
149	149		To display the data, we use a TDBGrid component, which can be found on the 'Data Controls' tab. To connect this component to the database components we need a TDatasource component from the 'Data Access' tab. To trigger the actions we use a TButton from the 'Standard' tab.
150	150
151	151		Now we have all components needed for the first example. You can enlarge the TDBGrid to have enough space to display all data.）
152	152
153	153		### 组件连接关系(Link the components)
154	154
155	155		连接组件：如果使用对象检查器是非常简单的，也可以使用代码来实现。
156	156
157	157		1、设置 IBConnection1 的 'Transaction' 属性指向 'SQLTransaction1'，这会使得 SQLTransaction1 的 'Database' 属性自动变更为 'IBConnection1'；
158	158
159	159		2、设置 SQLQuery1 的 'Database' 属性指向 'IBConnection1'，Lazarus 会自动添加 'Transaction' 属性值；
160	160
161	161		3、设置 Datasource1 的 'Dataset' 属性指向 'SQLQuery1'；
162	162
163	163		4、设置 DBGrid1 的 'Datasource1' 属性指向 'Datasource'。
164	164
165	165		（原文：Next we need to connect our components. A very simple way is to use the object inspector, but you can also do this in your source code. Change the 'Transaction' property of IBConnection1 to 'SQLTransaction1'. This causes the 'Database' property of SQLTransaction1 to automatically change to 'IBConnection1'. In the next step you should change the 'Database' property of SQLQuery1 to 'IBConnection1'. Lazarus automatically adds the value for the 'Transaction' property. Now we change the 'Dataset' property of Datasource1 to 'SQLQuery1'. Finally we change the 'Datasource' property of DBGrid1 to 'Datasource1'.）
166	166
167	167		### 连接到数据库(Connecting to the database)
168	168
169	169		How can we now show the data from our database on the screen? First we need to tell IBConnection1 where the employee.fdb database is located (usually in the .../examples/empbuild/ subdirectory of your Firebird installation). Again you have the choice, if you want to use the object inspector to assign the path or if you want to do it directly in your source code.
170	170
171	171		We choose to use the object inspector: set the IBConnection1 'HostName' property to the Firebird server name or IP adddress; use localhost if Firebird is running on your development machine. Change the 'DatabaseName' property of IBConnection1 to the path to the employee.fdb file on the database server(e.g. C:\Program Files\Firebird\Firebird_2_0\examples\empbuild\EMPLOYEE.FDB on a Windows machine).
172	172
173	173		Before the database server grants access to the data, it will check authorisation via username and password. A serious database application will ask the user for both values, when the application is started, and send these to the server when connecting.
174	174
175	175		However, for now, to simplify matters, we use the object inspector again to hard code these. Change the 'UserName' property to 'SYSDBA' and 'Password' to 'masterkey'.
176	176
177	177		Now check if all settings so far are correct: set the 'Connected' property to 'True'. If the database path isn't correct or if username or password are wrong, you will get an error message. If the connection was successful, you should cut it now (set it to 'False').
178	178
179	179		You should now have something like the following screenshot: [framed\|center\|Form and components set up](/wiki-images/b/b2/SQLTut1ComponentsSetUp.png)
180	180
181	181		### 选择欲显示的数据(Choosing what data to show)
182	182
183	183		Although the connection was successful, no data was displayed. The reason is simple. We haven't told the database server which data to return: the employee.fdb database contains several tables, and we haven't told Firebird the table we want to see. If you don't know the structure of a database, you can use tools like [FlameRobin](http://www.flamerobin.org/index.php), to display the contents. Lazarus also provides such a tool - the DataDesktop. You can find it in the /tools/lazdatadesktop/ subdirectory of Lazarus. Open the project lazdatadesktop.lpi and compile it (FPC 2.3.x is currently required) (of course you have saved our example project before).
184	184
185	185		[framed\|center\|The DataDesktop in action](/wiki-images/d/da/DataDictonary1.png)
186	186
187	187		Back to our example. We want to display all data from the table 'CUSTOMER'. The SQL instruction for that is:
188	188
189		-	~~`select * from CUSTOMER`~~
	189	+	```text
	190	+	select * from CUSTOMER
	191	+	```
190	192
191	193		We need to assign this command to the 'SQL' property of SQLQuery1. In the source code of our project this would look like:
192	194
193		-	~~`SQLQuery1.SQL.Text := 'select * from CUSTOMER';`~~
	195	+	``` pascal
	196	+	SQLQuery1.SQL.Text := 'select * from CUSTOMER';
	197	+	```
194	198
195	199		The SQL instruction must be enclosed by single quotes. You also have the ability to assign the content of another component (e.g. Edit1.Text). This is not always a good idea; see [[Secure programming]] (a more advanced text) for details on SQL injection.
196	200
197	201		When the user clicks on the button on our form, data retrieval should start. Let's start coding this. Double click on Button1. Lazarus then creates the skeleton of the necessary procedure. In our source code we should find the following lines:
198	202
199	203		``` pascal
200	204		procedure TForm1.Button1Click(Sender: TObject);
201	205		begin
202	206
203	207		end;
204	208		```
205	209
206	210		Between begin and end we must enter the instructions needed to display the data. The 'SQL' property of SQLQuery1 can only be changed, if SQLQuery1 is not active. That's why we close the component first:
207	211
208	212		``` pascal
209	213		SQLQuery1.Close;
210	214		```
211	215
212	216		Then we assign our SQL instruction to the 'SQL' property, overwriting any previous SQL commands:
213	217
214	218		``` pascal
215	219		SQLQuery1.SQL.Text := 'select * from CUSTOMER';
216	220		```
217	221
218	222		Now we need to establish the connection to the database, activate the transaction and open the query:
219	223
220	224		``` pascal
221	225		IBConnection1.Connected := True;
222	226		SQLTransaction1.Active := True;
223	227		SQLQuery1.Open;
224	228		```
225	229
226	230		You can omit the first two instructions, because they are done automatically by the third instruction (but this will not happen in the contrary case, when you close the connection). If you compile the project at this point, you could already see the data from the 'CUSTOMER' table.
227	231
228	232		However, a serious application must take care that all open database connections are properly closed when not needed anymore. Otherwise the secondary effects would not be foreseeable. So, we use the OnClose event of our form (create it with a double click in the object inspector):
229	233
230	234		``` pascal
231	235		procedure TForm1.FormClose(Sender: TObject; var CloseAction: TCloseAction);
232	236		begin
233	237
234	238		end;
235	239		```
236	240
237	241		To close the connection we use the reverse order compared to our opening code:
238	242
239	243		``` pascal
240	244		SQLQuery1.Close;
241	245		SQLTransaction1.Active := False;
242	246		IBConnection1.Connected := False;
243	247		```
244	248
245	249		### 小结(Summary)
246	250
247	251		到目前为止，你已经学会使用 SQLdb 包连接到Firebird数据库，还有如何在屏幕上显示数据表的内容。
248	252
249	253		遵循前面的步骤，目前代码应该如下所示：
250	254
251	255		（原文：Up to now we have learned, how to connect to a Firebird database using the SQLdb package and how to display the contents of a table on the screen.
252	256
253	257		If you followed the previous steps, then your code should look like:）
254	258
255	259		``` pascal
256	260		unit Unit1;
257	261
258	262		{$mode objfpc}{$H+}
259	263
260	264		interface
261	265
262	266		uses
263	267		Classes, SysUtils, IBConnection, sqldb, db, FileUtil, Forms, Controls,
264	268		Graphics, Dialogs, DBGrids, StdCtrls;
265	269
266	270		type
267	271
268	272		{ TForm1 }
269	273
270	274		TForm1 = class(TForm)
271	275		Button1: TButton;
272	276		Datasource1: TDatasource;
273	277		DBGrid1: TDBGrid;
274	278		IBConnection1: TIBConnection;
275	279		SQLQuery1: TSQLQuery;
276	280		SQLTransaction1: TSQLTransaction;
277	281		procedure Button1Click(Sender: TObject);
278	282		procedure FormClose(Sender: TObject; var CloseAction: TCloseAction);
279	283		private
280	284		{ private declarations }
281	285		public
282	286		{ public declarations }
283	287		end;
284	288
285	289		var
286	290		Form1: TForm1;
287	291
288	292		implementation
289	293
290	294		{$R *.lfm}
291	295
292	296		{ TForm1 }
293	297
294	298		procedure TForm1.Button1Click(Sender: TObject);
295	299		begin
296	300		SQLQuery1.Close;
297	301		SQLQuery1.SQL.Clear;
298	302		SQLQuery1.SQL.Text:= 'select * from CUSTOMER';
299	303		IBConnection1.Connected:= True;
300	304		SQLTransaction1.Active:= True;
301	305		SQLQuery1.Open;
302	306		end;
303	307
304	308		procedure TForm1.FormClose(Sender: TObject; var CloseAction: TCloseAction);
305	309		begin
306	310		SQLQuery1.Close;
307	311		SQLTransaction1.Active:= False;
308	312		IBConnection1.Connected:= False;
309	313		end;
310	314
311	315		end.
312	316		```
313	317
314	318		## 加强版的基本示例(Enhancement of the basic example)
315	319
316	320		While the application works, we can add some refinements.
317	321
318	322		### 动态数据库连接(Dynamic database connection)
319	323
320	324		Up to now, we used a fixed database server name, database location, username and password for simplicity. As mentioned, "real" applications often let users specify their own username and password.
321	325
322	326		Let's change the form so we can specify them: add two TEdits from the standard menu. Set their name properties to Username and Password. Set the Password's PasswordChar property to * (the asterisk) for some security. If you want to make it easier (and less secure, of course) to connect, you can set the UserName Text property to a valid database user, such as SYSDBA. You could even set the Password Text property to masterkey, easy for testing on developer machines if security doesn't matter... Cosmetically, adding some labels so people know what they're supposed to type is useful.
323	327
324	328		Also, to make it easier to connect to any 'employee.fdb' sample database on any Firebird/Interbase server, we add two textboxes for server name and database path. Add another two TEdits, and name them ServerName and DatabaseName. If you want, you can set the 'Text' property to default sensible values for your situation, e.g. localhost and C:\Program Files\Firebird\Firebird_2_5\examples\empbuild\EMPLOYEE.FDB Labels to explain what users need to enter would help here, too.
325	329
326	330		For clarity, we're going to remove the connection info from our designtime components: on the IBConnection1 component, remove all text from the UserName, Password, DatabaseName and HostName properties.
327	331
328	332		Now, finally, we need to tell our database connection component how to connect. This should normally only be necessary only at the beginning of an application run. In our case the existing 'Button1' code is a good way to set up the connection: Add code until you get:
329	333
330	334		``` pascal
331	335		procedure TForm1.Button1Click(Sender: TObject);
332	336		begin
333	337		SQLQuery1.Close;
334	338		//Connection settings for Firebird/Interbase database
335	339		//only needed when we have not yet connected:
336	340		if IBConnection1.Connected = false then
337	341		begin
338	342		IBConnection1.HostName := ServerName.Text;
339	343		IBConnection1.DatabaseName := DatabaseName.Text;
340	344		IBConnection1.Username := UserName.Text;
341	345		IBConnection1.Password := Password.Text;
342	346		// Now we've set up our connection, visually show that
343	347		// changes are not possibly any more
344	348		ServerName.ReadOnly:=true;
345	349		DatabaseName.ReadOnly:=true;
346	350		UserName.ReadOnly:=true;
347	351		Password.ReadOnly:=true;
348	352		end;
349	353		SQLQuery1.SQL.Text:= 'select * from CUSTOMER';
350	354		IBConnection1.Connected:= True;
351	355		SQLTransaction1.Active:= True;
352	356		SQLQuery1.Open;
353	357		end;
354	358		```
355	359
356	360		Now run and test if you can connect.
357	361
358	362		- Other databases: obviously, where it says IBConnection1, use your specific connection component. Also, you might need to set other properties on your components, so you might need more textboxes, or different contents.
359	363
360	364		### 过滤数据(Filtering data)
361	365
362	366		Often, tables contain a huge amount of data that the user doesn't want to see (and that might take a long time to query from the database and travel over the network). Let's assume, that only the customers from the USA should be displayed. Therefore the SQL instruction in 'SQLQuery1' would look like:
363	367
364	368		``` sql
365	369		select * from CUSTOMER where COUNTRY = 'USA'
366	370		```
367	371
368	372		... which would translate to something like:
369	373
370	374		``` pascal
371	375		SQLQuery1.SQL.Text := 'select * from CUSTOMER where COUNTRY = 'USA'';
372	376		```
373	377
374	378		There are two reasons why we will not use this instruction for our example application:
375	379
376	380		First there is a problem with the usage of the single quote. The compiler would interpret the quote before USA as a closing quote (the first quote is before the select from...) and so the SQL instruction would become invalid. Solution: double the inside quotes:
377	381
378	382		``` pascal
379	383		SQLQuery1.SQL.Text := 'select * from CUSTOMER where COUNTRY = ''USA''';
380	384		```
381	385
382	386		The second, more important reason is the fact, that we probably don't know what constraints the user will want to filter on. We don't want to limit the flexibility of the user.
383	387
384	388		To get this flexibility, first we change our SQL query statement and replace 'USA' by a placeholder (a parameter in SQL speak): change the Button1click procedure and replace
385	389
386	390		``` pascal
387	391		SQLQuery1.SQL.Text := 'select * from CUSTOMER';
388	392		```
389	393
390	394		with
391	395
392	396		``` pascal
393	397		SQLQuery1.SQL.Text:= 'select * from CUSTOMER where COUNTRY = :COUNTRY';
394	398		```
395	399
396	400		The SQL parameter is marked by the leading colon. To allow the user to enter a value for the filter, we place a TEdit component ('Standard' tab in the component palette) on our form. Delete the value of its 'Text' property. We can now fill the SQL parameter with the text, which was entered in TEdit by using the 'Params' property of TSQLQuery. Add this below the previous statement:
397	401
398	402		``` pascal
399	403		SQLQuery1.Params.ParamByName('COUNTRY').AsString := Edit1.Text;
400	404		```
401	405
402	406		The parameter can be specified by its position or name. Using the name should improve the readability of the source code, and obviously helps if you insert more parameters in the middle of existing parameters.
403	407
404	408		Overall the procedure should now look like:
405	409
406	410		``` pascal
407	411		procedure TForm1.Button1Click(Sender: TObject);
408	412		begin
409	413		SQLQuery1.Close;
410	414		//Connection settings for Firebird/Interbase database
411	415		//only needed when we have not yet connected:
412	416		if IBConnection1.Connected = false then
413	417		begin
414	418		IBConnection1.HostName := ServerName.Text;
415	419		IBConnection1.DatabaseName := DatabaseName.Text;
416	420		IBConnection1.Username := UserName.Text;
417	421		IBConnection1.Password := Password.Text;
418	422		// Now we've set up our connection, visually show that
419	423		// changes are not possibly any more
420	424		ServerName.ReadOnly:=true;
421	425		DatabaseName.ReadOnly:=true;
422	426		UserName.ReadOnly:=true;
423	427		Password.ReadOnly:=true;
424	428		end;
425	429		SQLQuery1.SQL.Text:= 'select * from CUSTOMER where COUNTRY = :COUNTRY';
426	430		SQLQuery1.Params.ParamByName('COUNTRY').AsString := Edit1.Text;
427	431		IBConnection1.Connected:= True;
428	432		SQLTransaction1.Active:= True;
429	433		SQLQuery1.Open;
430	434		end;
431	435		```
432	436
433	437		Now you can play around a bit with filtering using Edit1. If you enter a country that's not present in the database, an empty grid is shown.
434	438
435	439		### 错误处理(Error handling)
436	440
437	441		The application should run, but sometimes, serious problems can occur. Because client and server are usually physically separated, it's often not clear at first sight why a problem occurred. Was the server shut down or has somebody unplugged a network connection? Databases, even embedded databases can crash (e.g. when the disk is full, or just due to a bug), leaving the application hanging.
438	442
439	443		Access to a database should therefore always be integrated in a try ... except and/or try ... finally loop. Only that way is it ensured, that database errors can be handled and the user isn't left out in the rain. A rudimental routine for our example application could look as follows:
440	444
441	445		``` pascal
442	446		begin
443	447		try
444	448		SQLQuery1.Close;
445	449		...
446	450		SQLQuery1.Open;
447	451		except
448	452		//EDatabaseError is a general error, but we're dealing with Firebird/Interbase, so:
449	453		on E: EIBDatabaseError do
450	454		begin
451	455		MessageDlg('Error','A database error has occurred. Technical error message: ' + E.Message,mtError,[mbOK],0);
452	456		Edit1.Text:='';
453	457		end;
454	458		end;
455	459		end;
456	460		```
457	461
458	462		- Other database users: if you don't use Firebird/Interbase, you can either use the more generic EDatabaseError, or your own specialized databaseerror, if you need more details.
459	463
460	464		## 使用表格编辑数据(Editing data using the grid
461	465
462	466		### 编辑(Editing)
463	467
464	468		Up to now, if you tried to edit data in the grid, the changes would not be saved. This is because the SQLQuery1 is not instructed to send the changes to the database transaction at the right moment. We need to fix this, and then commit the transaction in the database, so all changes get written. For this, you would use code like this:
465	469
466	470		``` pascal
467	471		SQLQuery1.ApplyUpdates; //Pass user-generated changes back to database...
468	472		SQLTransaction1.Commit; //... and commit them using the transaction.
469	473		//SQLTransaction1.Active now is false
470	474		```
471	475
472	476		We want to make sure any edits (inserts, updates, deletes) are written to the database:
473	477
474	478		- when the users changes the filtering criteria and presses the button to query the database
475	479		- when the form is closed
476	480
477	481		It makes sense to make a separate procedure for this that is called in those two instances. Go to the code, and add an empty line here:
478	482
479	483		``` pascal
480	484		TForm1 = class(TForm)
481	485		Button1: TButton;
482	486		Datasource1: TDatasource;
483	487		DBGrid1: TDBGrid;
484	488		Edit1: TEdit;
485	489		IBConnection1: TIBConnection;
486	490		SQLQuery1: TSQLQuery;
487	491		SQLTransaction1: TSQLTransaction;
488	492		***insert the empty line here**
489	493		procedure Button1click(Sender: TObject);
490	494		procedure Formclose(Sender: TObject; var Closeaction: Tcloseaction);
491	495		private
492	496		```
493	497
494	498		then type
495	499
496	500		``` pascal
497	501		procedure SaveChanges;
498	502		```
499	503
500	504		press shift-ctrl-c (default combination) to let code completion automatically create the corresponding procedure body.
501	505
502	506		We need to add error handling and check that the transaction is active - remember, this code also gets called when pressing the button the first time, when the transaction is not active yet. We get:
503	507
504	508		``` pascal
505	509		procedure Tform1.Savechanges;
506	510		// Saves edits done by user, if any.
507	511		begin
508	512		try
509	513		if SQLTransaction1.Active=true then
510	514		// Only if we are within a started transaction
511	515		// otherwise you get "Operation cannot be performed on an inactive dataset"
512	516		begin
513	517		SQLQuery1.ApplyUpdates; //Pass user-generated changes back to database...
514	518		SQLTransaction1.Commit; //... and commit them using the transaction.
515	519		//SQLTransaction1.Active now is false
516	520		end;
517	521		except
518	522		on E: EIBDatabaseError do
519	523		begin
520	524		MessageDlg('Error', 'A database error has occurred. Technical error message: ' +
521	525		E.Message, mtError, [mbOK], 0);
522	526		Edit1.Text := '';
523	527		end;
524	528		end;
525	529		end;
526	530		```
527	531
528	532		Now we need to call this procedure at the appropriate moments:
529	533
530	534		``` pascal
531	535		procedure Tform1.Button1click(Sender: TObject);
532	536		begin
533	537		SaveChanges; //Saves changes and commits transaction
534	538		try
535	539		SQLQuery1.Close;
536	540		....
537	541		```
538	542
539	543		and
540	544
541	545		``` pascal
542	546		procedure Tform1.Formclose(Sender: TObject; var Closeaction: Tcloseaction);
543	547		begin
544	548		SaveChanges; //Saves changes and commits transaction
545	549		SQLQuery1.Close;
546	550		....
547	551		```
548	552
549	553		Now test and see if edits made in the dbgrid are saved to the database.
550	554
551	555		### 隐藏主键列(Hiding primary key column)
552	556
553	557		Often, you don't want your users to see autonumber/generated primary keys as they are only meant to maintain referential integrity. If users do see them, they might want to try the edit the numbers, get upset that the numbers change, there are gaps in the numbers, etc.
554	558
555	559		In our example, CUST_NO is the primary key, with content auto-generated by Firebird using triggers and a sequence/generator. This means that you can insert a new record without specifying the CUST_NO; Firebird will create one automatically.
556	560
557	561		We could simply change our SQLQuery1.SQL.Text property to not include CUST_NO, but this would lead to problems when editing data - a primary key is handy in those circumstances for uniquely identifying the row/record in question.
558	562
559	563		Therefore, let's use a trick to query for all columns/fields in the table, but keep the grid from showing the first field, CUST_NO: in the Button1Click procedure, add code so it looks like:
560	564
561	565		``` pascal
562	566		procedure Tform1.Button1click(Sender: TObject);
563	567		begin
564	568		...
565	569		SQLQuery1.Open;
566	570		// Hide the primary key column which is the first column in our queries.
567	571		// We can only do this once the DBGrid has created the columns
568	572		DBGrid1.Columns[0].Visible:=false;
569	573		```
570	574
571	575		Recompile, and check to see if the primary key column is really hidden.
572	576
573	577		- Other databases: a lot of other databases use an 'autonumber' or 'autoinc' type of field to provide auto-generated field content. Try changing your table definition and see if it works.
574	578
575	579		### 插入新数据(Inserting new data)
576	580
577	581		If you insert new rows/records without any CUST_NO information you may have noticed that you get an error message: `Field CUST_NO is required, but not supplied`. This also happens if you hid the CUST_NO column, as in the previous section.
578	582
579	583		The reason: Lazarus thinks that CUST_NO is required. That's not so strange, because it is a primary key and the underlying table definition in the database does say it is required.
580	584
581	585		If we can instruct Lazarus that this field is not actually required, we can pass empty values (=NULL values) to the database. Fortunately, a query's field object has a Required property that does exactly that.
582	586
583	587		Change the code to something like:
584	588
585	589		``` pascal
586	590		SQLQuery1.Open;
587	591		{
588	592		Make sure we don't get problems with inserting blank (=NULL) CUST_NO values, e.g.:
589	593		Field CUST_NO is required, but not supplied
590	594		We need to tell Lazarus that, while CUST_NO is a primary key, it is not required
591	595		when inserting new records.
592	596		}
593	597		SQLQuery1.FieldByName('CUST_NO').Required:=false;
594	598		// Hide the primary key column which is the first column in our queries.
595	599		// We can only do this once the DBGrid has created the columns
596	600		DBGrid1.Columns[0].Visible:=false;
597	601		```
598	602
599	603		### 删除数据(Deleting data)
600	604
601	605		Up to now, I haven't found a way to delete records/rows in the grid. While there might be a way to do this using keyboard combinations, you can make it easier for your users by letting them use the mouse to do this. You don't even need to code a single line for this functionality...
602	606
603	607		On the 'Data Controls' tab, select a TDBNavigator component and drop it on the form, above the grid.
604	608
605	609		To indicate what the navigator should be linked to, set its DataSource property to your existing datasource ('DataSource1') using the Object Inspector. Now you can use the button on the DBNavigator to delete records, but also insert them, and move around. Also, when editing cells/fields, you can use the Cancel button to cancel your edits.
606	610
607	611		### 小结(Summary)
608	612
609	613		如果按上文的步骤照做，你已经学会从数据库取回数据，过滤数据，在表格中编辑和删除数据。
610	614
611	615		目前的代码应类似如下所示：
612	616
613	617		（原文：If you followed along up to now, you can retrieve data from the database, filter it, and edit and delete data in the grid. Your code should look something like this:）
614	618
615	619		``` pascal
616	620		unit sqldbtutorial1unit;
617	621
618	622		{$mode objfpc}{$H+}
619	623
620	624		interface
621	625
622	626		uses
623	627		Classes, SysUtils, IBConnection, sqldb, DB, FileUtil, Forms, Controls,
624	628		Graphics, Dialogs, DBGrids, StdCtrls, DbCtrls;
625	629
626	630		type
627	631
628	632		{ TForm1 }
629	633
630	634		TForm1 = class(TForm)
631	635		Button1: TButton;
632	636		DatabaseName: TEdit;
633	637		Datasource1: TDatasource;
634	638		DBGrid1: TDBGrid;
635	639		Dbnavigator1: Tdbnavigator;
636	640		Edit1: TEdit;
637	641		Label2: Tlabel;
638	642		Label3: Tlabel;
639	643		Label4: Tlabel;
640	644		Label5: Tlabel;
641	645		Password: TEdit;
642	646		UserName: TEdit;
643	647		ServerName: TEdit;
644	648		IBConnection1: TIBConnection;
645	649		Label1: TLabel;
646	650		SQLQuery1: TSQLQuery;
647	651		SQLTransaction1: TSQLTransaction;
648	652		procedure SaveChanges;
649	653		procedure Button1click(Sender: TObject);
650	654		procedure Formclose(Sender: TObject; var Closeaction: Tcloseaction);
651	655		private
652	656		{ private declarations }
653	657		public
654	658		{ public declarations }
655	659		end;
656	660
657	661		var
658	662		Form1: TForm1;
659	663
660	664		implementation
661	665
662	666		{$R *.lfm}
663	667
664	668		{ TForm1 }
665	669
666	670		procedure Tform1.Savechanges;
667	671		// Saves edits done by user, if any.
668	672		begin
669	673		try
670	674		if SQLTransaction1.Active=true then
671	675		// Only if we are within a started transaction
672	676		// otherwise you get "Operation cannot be performed on an inactive dataset"
673	677		begin
674	678		SQLQuery1.ApplyUpdates; //Pass user-generated changes back to database...
675	679		SQLTransaction1.Commit; //... and commit them using the transaction.
676	680		//SQLTransaction1.Active now is false
677	681		end;
678	682		except
679	683		on E: EIBDatabaseError do
680	684		begin
681	685		MessageDlg('Error', 'A database error has occurred. Technical error message: ' +
682	686		E.Message, mtError, [mbOK], 0);
683	687		Edit1.Text := '';
684	688		end;
685	689		end;
686	690		end;
687	691
688	692		procedure Tform1.Button1click(Sender: TObject);
689	693		begin
690	694		SaveChanges; //Saves changes and commits transaction
691	695		try
692	696		SQLQuery1.Close;
693	697		//Connection settings for Firebird/Interbase database
694	698		//only needed when we have not yet connected:
695	699		if IBConnection1.Connected = false then
696	700		begin
697	701		IBConnection1.HostName := ServerName.Text;
698	702		IBConnection1.DatabaseName := DatabaseName.Text;
699	703		IBConnection1.Username := UserName.Text;
700	704		IBConnection1.Password := Password.Text;
701	705		// Now we've set up our connection, visually show that
702	706		// changes are not possibly any more
703	707		ServerName.ReadOnly:=true;
704	708		DatabaseName.ReadOnly:=true;
705	709		UserName.ReadOnly:=true;
706	710		Password.ReadOnly:=true;
707	711		end;
708	712		SQLQuery1.SQL.Text := 'select * from CUSTOMER where COUNTRY = :COUNTRY';
709	713		SQLQuery1.Params.ParamByName('COUNTRY').AsString := Edit1.Text;
710	714		IBConnection1.Connected := True;
711	715		SQLTransaction1.Active := True; //Starts a new transaction
712	716		SQLQuery1.Open;
713	717		{
714	718		Make sure we don't get problems with inserting blank (=NULL) CUST_NO values, i.e. error message:
715	719		"Field CUST_NO is required, but not supplied"
716	720		We need to tell Lazarus that, while CUST_NO is a primary key, it is not required
717	721		when inserting new records.
718	722		}
719	723		SQLQuery1.FieldByName('CUST_NO').Required:=false;
720	724		{
721	725		Hide the primary key column which is the first column in our queries.
722	726		We can only do this once the DBGrid has created the columns
723	727		}
724	728		DBGrid1.Columns[0].Visible:=false;
725	729		except
726	730		//EDatabaseError is a general error, but we're dealing with Firebird/Interbase, so:
727	731		on E: EIBDatabaseError do
728	732		begin
729	733		MessageDlg('Error', 'A database error has occurred. Technical error message: ' +
730	734		E.Message, mtError, [mbOK], 0);
731	735		Edit1.Text := '';
732	736		end;
733	737		end;
734	738		end;
735	739
736	740		procedure Tform1.Formclose(Sender: TObject; var Closeaction: Tcloseaction);
737	741		begin
738	742		SaveChanges; //Saves changes and commits transaction
739	743		SQLQuery1.Close;
740	744		SQLTransaction1.Active := False;
741	745		IBConnection1.Connected := False;
742	746		end;
743	747
744	748		end.
745	749		```
746	750
747	751		## 无需改变代码就能在程序中嵌入数据库(Embedded database without code changes)
748	752
749	753		### Win平台下的Firebird(Firebird on Windows)
750	754
751	755		Win平台Firebird用户的奖赏：如你按本文照做（哪怕你仅仅完成了基本示例），只要将嵌入式Firebird的`fbembed.dll` 改名为C/S结构Firebird所用的 `fbclient.dll`，Lazarus程序就可以连接C/S结构Firebird（一般是安装在另一台机器上或者是你的开发机上）。
752	756
753	757		此外，您也可以复制 `employee.fdb`数据库到你的应用程序目录，再运行应用程序，既不需安装数据库服务器，也不需要设置“服务器名称”编辑框，就能使用嵌入式Firebird来直接连接到数据库文件。
754	758
755	759		这为一类用户带来了极大的方便：想给终端用户部署数据库应用程序，但又不想自找麻烦地去安装服务器，免除了烦琐的部署步骤：如检查是否已经安装了服务器，数据库版本是否是正确，防火墙是否开启等。
756	760
757	761		2011年9月：在近期的 FreePascal的 SVN 开发版本，FPC 尝试首先加载`fbembed.dll`，所以你不需要再为此而重命名为`fbclient.dll`。
758	762
759	763		（原文：A bonus for Firebird users on Windows: if you have been following this tutorial (even if you only did the basic example), you renamed the `fbembed.dll` embedded Firebird library to `fbclient.dll`. With this, Lazarus could connect to regular Firebird servers (either on another machine or on your local machine). However, you can also copy the `employee.fdb` database to your application directory, run the application, clear the Server name TEdit and use Firebird embedded to directly connect to the database file, without any servers set up.
760	764
761	765		This is great if you want to deploy database applications to end users, but don't want the hassle of installing servers (checking if a server is already installed, if it's the right version, having users check firewalls, etc).
762	766
763	767		September 2011: in recent development (SVN) versions of FreePascal, FPC tries to first load `fbembed.dll`, so you need not rename `fbclient.dll` anymore for this to work.）
764	768
765	769		### Linux/OSX/Unix平台下的Firebird(Firebird on Linux/OSX/Unix)
766	770
767	771		Linux/OSX 平台下应该也能够找到方法来进行嵌入式部署。参看 [[Firebird]] 技巧提示。欢迎修改、完善本节内容。
768	772
769	773		（原文：There must be a way to get this to work on Linux/OSX. See [[Firebird]] for hints and links. Updates to the wiki are welcome.）
770	774
771	775		### 其他数据库(Other databases)
772	776
773	777		你所使用的数据库可能提供同样的功能。 Sqlite 数据库如同它的名字所示，理所当然的可以进行嵌入式部署。欢迎针对你所使用的数据库来修改、完善本节内容。
774	778
775	779		（原文：Your database might offer similar functionality. Sqlite certainly does as it is meant for embedded deployment. Updates of this wiki for other database systems are welcome.）
776	780
777	781		## 参见(See also)
778	782
779	783		- [[Firebird]]: another Firebird tutorial/how to.
780	784		- [[SQLdb Package]]: information about the SQLdb package
781	785		- [[SQLdb Programming Reference]]: an overview of the interaction of the SQLdb database components
782	786		- [[SqlDBHowto]]: informationen about the usage of the SQLdb package
783	787		- [[Working With TSQLQuery]]: information about TSQLQuery
784	788
785	789		---
786	790		From the Free Pascal wiki, page "SQLdb Tutorial1/zh CN", as it stood on 2020-02-26 after a change by Trev, out of the wiki's dump of 2024-05-26. Copyright stays with the author who wrote it, according to the old wiki's [[Licensing]] page. If you have concerns about this material, [message the admin](/wiki-contact).

Back to history