SQLdb Tutorial2: what changed

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

Old	New		Line
1	1		## Overview
2	2
3	3		If you have followed [[SQLdb Tutorial1]], you have a basic grid showing database information. While this application works, we can add some refinements on this.
4	4
5	5		### Dynamic database connection
6	6
7	7		Up to now, we used a fixed database server name, database location, username and password for simplicity. As mentioned, "real" applications normally let users specify their own username and password.
8	8
9	9		Let's change the form so we can specify them: add two [[TEdit\|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 against people looking over your shoulder.
10	10
11	11		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 a default value like masterkey, easy for testing on developer machines if security doesn't matter...
12	12
13	13		Cosmetically, adding some labels so people know what they're supposed to type is useful.
14	14
15	15		Also, to make it easier to connect to any employee 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.
16	16
17	17		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`
18	18
19	19		Labels to explain what users need to enter would help here, too.
20	20
21	21		For clarity, we're going to remove the connection info from our design time components: on the [[TSQLConnector]] component, remove all text from the UserName, Password, DatabaseName and HostName properties.
22	22
23	23		Now, finally, we need to tell our database connection component how to connect. This should normally only be necessary at the beginning of an application run. In our case the existing 'Button1' code is a good way to set up the connection.
24	24
25	25		Add code until you get:
26	26
27	27		``` pascal
28	28		procedure TForm1.Button1Click(Sender: TObject);
29	29		begin
30	30		SQLQuery1.Close;
31	31		//Connection settings for Firebird/Interbase database
32	32		//only needed when we have not yet connected:
33	33		if not DBConnection.Connected then
34	34		begin
35	35		DBConnection.HostName := ServerName.Text;
36	36		DBConnection.DatabaseName := DatabaseName.Text;
37	37		DBConnection.Username := UserName.Text;
38	38		DBConnection.Password := Password.Text;
39	39		// Now we've set up our connection, visually show that
40	40		// changes are not possibly any more
41	41		ServerName.ReadOnly:=true;
42	42		DatabaseName.ReadOnly:=true;
43	43		UserName.ReadOnly:=true;
44	44		Password.ReadOnly:=true;
45	45		end;
46	46		SQLQuery1.SQL.Text:= 'select * from CUSTOMER';
47	47		DBConnection.Connected:= True;
48	48		SQLTransaction1.Active:= True;
49	49		SQLQuery1.Open;
50	50		end;
51	51		```
52	52
53	53		Now run and test if you can connect.
54	54
55	55		#### SQLite, other databases
56	56
57	57		Adjust the Text property in the DatabaseName TEdit as needed; e.g. `employee.sqlite` for SQLite.
58	58
59	59		For sqlite, specifying HostName, Username and Password doesn't make sense, so you can omit these TEdits. Obviously, leave out/comment out assigning the corresponding values to DBConnection in the code above. For Firebird embedded, please hardcode the Username to SYSDBA; specifying this when sqlite is used won't hurt.
60	60
61	61		The code will look something like:
62	62
63	63		``` pascal
64	64		procedure TForm1.Button1Click(Sender: TObject);
65	65		begin
66	66		SQLQuery1.Close;
67	67		//Connection settings for embedded databases
68	68		//only needed when we have not yet connected:
69	69		if not DBConnection.Connected then
70	70		begin
71	71		DBConnection.DatabaseName := DatabaseName.Text;
72	72		DBConnection.UserName := 'SYSDBA'; //Firebird embedded needs this; doesn't harm if using SQLite
73	73		// Now we've set up our connection, visually show that
74	74		// changes are not possibly any more
75	75		DatabaseName.ReadOnly:=true;
76	76		end;
77	77		SQLQuery1.SQL.Text:= 'select * from CUSTOMER';
78	78		DBConnection.Connected:= True;
79	79		SQLTransaction1.Active:= True;
80	80		SQLQuery1.Open;
81	81		end;
82	82		```
83	83
84	84		### Filtering data
85	85
86	86		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:
87	87
88	88		``` SQL
89	89		select * from CUSTOMER where COUNTRY = 'USA'
90	90		```
91	91
92	92		... which would translate to something like this in our code:
93	93
94	94		``` pascal
95	95		SQLQuery1.SQL.Text := 'select * from CUSTOMER where COUNTRY = 'USA'';
96	96		```
97	97
98	98		There are two reasons why we will not use this instruction for our example application:
99	99
100	100		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:
101	101
102	102		``` pascal
103	103		SQLQuery1.SQL.Text := 'select * from CUSTOMER where COUNTRY = ''USA''';
104	104		```
105	105
106	106		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.
107	107
108	108		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:
109	109
110	110		``` pascal
111	111		SQLQuery1.SQL.Text := 'select * from CUSTOMER';
112	112		```
113	113
114	114		with:
115	115
116	116		``` pascal
117	117		SQLQuery1.SQL.Text:= 'select * from CUSTOMER where COUNTRY = :COUNTRY';
118	118		```
119	119
120	120		In FPC SQLDB, the SQL parameter is marked by the leading colon (other languages/environments use other conventions like ?). To allow the user to enter a value for the filter, we place a TEdit component on our form. Delete the value of its 'Text' property.
121	121
122	122		We can now take the text entered in the TEdit and fill the SQL COUNTRY parameter by using the 'Params' property of TSQLQuery. Add this below the previous statement:
123	123
124	124		``` pascal
125	125		SQLQuery1.Params.ParamByName('COUNTRY').AsString := Edit1.Text;
126	126		```
127	127
128	128		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.
129	129
130	130		We use .AsString to assign a string value to the parameter; there are equivalent property assignments for integer parameters, boolean parameters etc.
131	131
132	132		The code up to now forces us to use a filter. If a user specifies an empty value in the edit box, no record will be displayed. This is probably not what we want. Let's test for an empty value and build our query accordingly. We should end up with a procedure like this:
133	133
134	134		``` pascal
135	135		procedure TForm1.Button1Click(Sender: TObject);
136	136		begin
137	137		SQLQuery1.Close;
138	138		//Connection settings for Firebird/Interbase database
139	139		//only needed when we have not yet connected:
140	140		if not DBConnection.Connected then
141	141		begin
142	142		DBConnection.HostName := ServerName.Text;
143	143		DBConnection.DatabaseName := DatabaseName.Text;
144	144		DBConnection.Username := UserName.Text;
145	145		DBConnection.Password := Password.Text;
146	146		// Now we've set up our connection, visually show that
147	147		// changes are not possibly any more
148	148		ServerName.ReadOnly:=true;
149	149		DatabaseName.ReadOnly:=true;
150	150		UserName.ReadOnly:=true;
151	151		Password.ReadOnly:=true;
152	152		end;
153	153		// Show all records, or filter if user specified a filter criterium
154	154		if Edit1.Text='' then
155	155		SQLQuery1.SQL.Text := 'select * from CUSTOMER'
156	156		else
157	157		begin
158	158		SQLQuery1.SQL.Text := 'select * from CUSTOMER where COUNTRY = :COUNTRY';
159	159		SQLQuery1.Params.ParamByName('COUNTRY').AsString := Edit1.Text;
160	160		end;
161	161		DBConnection.Connected:= True;
162	162		SQLTransaction1.Active:= True;
163	163		SQLQuery1.Open;
164	164		end;
165	165		```
166	166
167	167		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.
168	168
169	169		### Error handling
170	170
171	171		The application should run, but sometimes problems can occur. Databases, even embedded databases can crash (e.g. when the database server crashes, the disk is full, or just due to a bug), leaving the application hanging.
172	172
173	173		Access to a database (any external process, really) should therefore always be integrated in a try ... except and/or try ... finally construct. This ensures that database errors are handled and the user isn't left out in the cold. A rudimentary routine for our example application could look like this:
174	174
175	175		``` pascal
176	176		begin
177	177		try
178	178		SQLQuery1.Close;
179	179		...
180	180		SQLQuery1.Open;
181	181		except
182	182		//We could use EDatabaseError which is a general database error, but we're dealing with Firebird/Interbase, so:
183	183		on E: EDatabaseError do
184	184		begin
185	185		MessageDlg('Error','A database error has occurred. Technical error message: ' + E.Message,mtError,[mbOK],0);
186	186		Edit1.Text:='';
187	187		end;
188	188		end;
189	189		end;
190	190		```
191	191
192	192		#### SQLite, PostgreSQL, other databases
193	193
194	194		You can either use the more generic EDatabaseError, or - if available - your own specialized databaseerror, if you need more details. E.g. SQLite and the PostgreSQL driver in FPC 2.6.1 and lower doesn't have a specialized E*DatabaseError; you'd have to use EDatabaseError. PostgreSQL on FPC trunk (development version) has EPQDatabaseError.
195	195
196	196		## Editing data using the grid
197	197
198	198		### Editing
199	199
200	200		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:
201	201
202	202		``` pascal
203	203		SQLQuery1.ApplyUpdates; //Pass user-generated changes back to database...
204	204		SQLTransaction1.Commit; //... and commit them using the transaction.
205	205		//SQLTransaction1.Active now is false
206	206		```
207	207
208	208		We want to make sure any edits (inserts, updates, deletes) are written to the database:
209	209
210	210		- when the users changes the filtering criteria and presses the button to query the database
211	211		- when the form is closed
212	212
213	213		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:
214	214
215	215		``` pascal
216	216		TForm1 = class(TForm)
217	217		Button1: TButton;
218	218		Datasource1: TDatasource;
219	219		DBGrid1: TDBGrid;
220	220		Edit1: TEdit;
221	221		DBConnection: TIBConnection;
222	222		SQLQuery1: TSQLQuery;
223	223		SQLTransaction1: TSQLTransaction;
224	224		***insert the empty line here**
225	225		procedure Button1click(Sender: TObject);
226	226		procedure Formclose(Sender: TObject; var Closeaction: Tcloseaction);
227	227		private
228	228		```
229	229
230	230		then type:
231	231
232	232		``` pascal
233	233		procedure SaveChanges;
234	234		```
235	235
236	236		press shift-ctrl-c (default combination) to let code completion automatically create the corresponding procedure body.
237	237
238	238		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:
239	239
240	240		``` pascal
241	241		procedure Tform1.SaveChanges;
242	242		// Saves edits done by user, if any.
243	243		begin
244	244		try
245	245		if SQLTransaction1.Active then
246	246		// Only if we are within a started transaction;
247	247		// otherwise you get "Operation cannot be performed on an inactive dataset"
248	248		begin
249	249		SQLQuery1.ApplyUpdates; //Pass user-generated changes back to database...
250	250		SQLTransaction1.Commit; //... and commit them using the transaction.
251	251		//SQLTransaction1.Active now is false
252	252		end;
253	253		except
254	254		on E: EDatabaseError do
255	255		begin
256	256		SQLTransaction1.Rollback;
257	257		MessageDlg('Error', 'A database error has occurred. Technical error message: ' +
258	258		E.Message, mtError, [mbOK], 0);
259	259		Edit1.Text := '';
260	260		end;
261	261		end;
262	262		end;
263	263		```
264	264
265	265		Now we need to call this procedure at the appropriate moments:
266	266
267	267		``` pascal
268	268		procedure Tform1.Button1click(Sender: TObject);
269	269		begin
270	270		SaveChanges; //Saves changes and commits transaction
271	271		try
272	272		SQLQuery1.Close;
273	273		....
274	274		```
275	275
276	276		and:
277	277
278	278		``` pascal
279	279		procedure Tform1.Formclose(Sender: TObject; var Closeaction: Tcloseaction);
280	280		begin
281	281		SaveChanges; //Saves changes and commits transaction
282	282		SQLQuery1.Close;
283	283		....
284	284		```
285	285
286	286		Now test and see if edits made in the dbgrid are saved to the database.
287	287
288	288		### Hiding primary key column
289	289
290	290		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, that there are gaps in the numbers, etc.
291	291
292	292		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.
293	293
294	294		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 needed in those circumstances for uniquely identifying the row/record in question.
295	295
296	296		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:
297	297
298	298		``` pascal
299	299		procedure Tform1.Button1click(Sender: TObject);
300	300		begin
301	301		...
302	302		SQLQuery1.Open;
303	303		// Hide the primary key column which is the first column in our queries.
304	304		// We can only do this once the DBGrid has created the columns
305	305		DBGrid1.Columns[0].Visible:=false;
306	306		```
307	307
308	308		Recompile, and check to see if the primary key column is really hidden.
309	309
310	310		#### SQLite, other databases
311	311
312	312		- 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.
313	313		- Sqlite: the example above works for SQLite as is because we're using an integer primary key. See the [documentation](http://www.sqlite.org/autoinc.html) for details.
314	314
315	315		### Inserting new data
316	316
317	317		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.
318	318
319	319		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.
320	320
321	321		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.
322	322
323	323		Change the code to something like:
324	324
325	325		``` pascal
326	326		SQLQuery1.Open;
327	327		{
328	328		Make sure we don't get problems with inserting blank (=NULL) CUST_NO values, e.g.:
329	329		Field CUST_NO is required, but not supplied
330	330		We need to tell Lazarus that, while CUST_NO is a primary key, it is not required
331	331		when inserting new records.
332	332		}
333	333		SQLQuery1.FieldByName('CUST_NO').Required:=false;
334	334		// Hide the primary key column which is the first column in our queries.
335	335		// We can only do this once the DBGrid has created the columns
336	336		DBGrid1.Columns[0].Visible:=false;
337	337		```
338	338
339	339		### Deleting data
340	340
341	341		You can let your users use the mouse to do this. You don't even need to code a single line for this functionality...
342	342
343	343		On the 'Data Controls' tab, select a TDBNavigator component and drop it on the form, above the grid.
344	344
345	345		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 the records. Also, when editing cells/fields, you can use the Cancel button to cancel your edits.
346	346
347		-	~~To allow users to delete the row they're in on the grid using the <span class="kbd">Delete</span> key, add LCLType (this contains definitions for key codes) to your uses clause:~~
	347	+	To allow users to delete the row they're in on the grid using the `Delete` key, add LCLType (this contains definitions for key codes) to your uses clause:
348	348
349	349		``` pascal
350	350		uses
351	351		Classes, SysUtils, sqldb, pqconnection, DB, FileUtil, Forms,
352	352		Controls, Graphics, Dialogs, DBGrids, StdCtrls, DbCtrls, LCLType;
353	353		```
354	354
355		-	... then handle the KeyUp event for the grid, which occurs when a key is released if in the grid. However, we do need to check that the user is not editing a field - as he'll probably use the <span class="kbd">Delete</span> key to delete letters rather than the record he's working on.
	355	+	... then handle the KeyUp event for the grid, which occurs when a key is released if in the grid. However, we do need to check that the user is not editing a field - as he'll probably use the `Delete` key to delete letters rather than the record he's working on.
356	356
357	357		Select the grid, then go to events and create an OnKeyUp event like this:
358	358
359	359		``` pascal
360	360		procedure TForm1.DBGrid1KeyUp(Sender: TObject; var Key: Word; Shift: TShiftState
361	361		);
362	362		begin
363	363		// Check for del key being hit and delete the current record in response
364	364		// as long as we're not editing data
365	365		if (key=VK_DELETE) and (not(DBGrid1.EditorMode)) then
366	366		begin
367	367		//... delete current record and apply updates to db:
368	368		SQLQuery1.Delete;
369	369		SQLQuery1.ApplyUpdates;
370	370		end;
371	371		end;
372	372		```
373	373
374		-	> Note: By default TDBGrid property Options / dgDisableDelete is set to false, this means a user can delete any record with the <span class="kbd">ctrl</span>-<span class="kbd">delete</span> key combo. You may not want this behaviour.
	374	+	> Note: By default TDBGrid property Options / dgDisableDelete is set to false, this means a user can delete any record with the `ctrl`-`delete` key combo. You may not want this behaviour.
375	375
376	376		## Summary
377	377
378	378		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:
379	379
380	380		``` pascal
381	381		unit sqldbtutorial1unit;
382	382
383	383		{$mode objfpc}{$H+}
384	384
385	385		interface
386	386
387	387		uses
388	388		Classes, SysUtils, sqldb, pqconnection, DB, FileUtil, Forms,
389	389		Controls, Graphics, Dialogs, DBGrids, StdCtrls, DbCtrls, LCLType;
390	390		type
391	391
392	392		{ TForm1 }
393	393
394	394		TForm1 = class(TForm)
395	395		Button1: TButton;
396	396		DatabaseName: TEdit;
397	397		Datasource1: TDatasource;
398	398		DBGrid1: TDBGrid;
399	399		Dbnavigator1: Tdbnavigator;
400	400		Edit1: TEdit;
401	401		Label2: Tlabel;
402	402		Label3: Tlabel;
403	403		Label4: Tlabel;
404	404		Label5: Tlabel;
405	405		Password: TEdit;
406	406		UserName: TEdit;
407	407		ServerName: TEdit;
408	408		DBConnection: TIBConnection;
409	409		Label1: TLabel;
410	410		SQLQuery1: TSQLQuery;
411	411		SQLTransaction1: TSQLTransaction;
412	412		procedure SaveChanges;
413	413		procedure Button1click(Sender: TObject);
414	414		procedure Formclose(Sender: TObject; var Closeaction: Tcloseaction);
415	415		private
416	416		{ private declarations }
417	417		public
418	418		{ public declarations }
419	419		end;
420	420
421	421		var
422	422		Form1: TForm1;
423	423
424	424		implementation
425	425
426	426		{$R *.lfm}
427	427
428	428		{ TForm1 }
429	429
430	430		procedure Tform1.Savechanges;
431	431		// Saves edits done by user, if any.
432	432		begin
433	433		try
434	434		if SQLTransaction1.Active then
435	435		// Only if we are within a started transaction
436	436		// otherwise you get "Operation cannot be performed on an inactive dataset"
437	437		begin
438	438		SQLQuery1.ApplyUpdates; //Pass user-generated changes back to database...
439	439		SQLTransaction1.Commit; //... and commit them using the transaction.
440	440		//SQLTransaction1.Active now is false
441	441		end;
442	442		except
443	443		on E: EDatabaseError do
444	444		begin
445	445		SQLTransaction1.Rollback;
446	446		MessageDlg('Error', 'A database error has occurred. Technical error message: ' +
447	447		E.Message, mtError, [mbOK], 0);
448	448		Edit1.Text := '';
449	449		end;
450	450		end;
451	451		end;
452	452
453	453		procedure TForm1.DBGrid1KeyUp(Sender: TObject; var Key: Word; Shift: TShiftState
454	454		);
455	455		begin
456	456		// Check for del key being hit and delete the current record in response
457	457		// as long as we're not editing data
458	458		if (key=VK_DELETE) and (not(DBGrid1.EditorMode)) then
459	459		begin
460	460		//... delete current record and apply updates to db:
461	461		SQLQuery1.Delete;
462	462		SQLQuery1.ApplyUpdates;
463	463		end;
464	464		end;
465	465
466	466		procedure Tform1.Button1click(Sender: TObject);
467	467		begin
468	468		SaveChanges; //Saves changes and commits transaction
469	469		try
470	470		SQLQuery1.Close;
471	471		//Connection settings for Firebird/Interbase database
472	472		//only needed when we have not yet connected:
473	473		if not DBConnection.Connected then
474	474		begin
475	475		DBConnection.HostName := ServerName.Text;
476	476		DBConnection.DatabaseName := DatabaseName.Text;
477	477		DBConnection.Username := UserName.Text;
478	478		DBConnection.Password := Password.Text;
479	479		// Now we've set up our connection, visually show that
480	480		// changes are not possibly any more
481	481		ServerName.ReadOnly:=true;
482	482		DatabaseName.ReadOnly:=true;
483	483		UserName.ReadOnly:=true;
484	484		Password.ReadOnly:=true;
485	485		end;
486	486		// Show all records, or filter if user specified a filter criterium
487	487		if Edit1.Text='' then
488	488		SQLQuery1.SQL.Text := 'select * from CUSTOMER'
489	489		else
490	490		begin
491	491		SQLQuery1.SQL.Text := 'select * from CUSTOMER where COUNTRY = :COUNTRY';
492	492		SQLQuery1.Params.ParamByName('COUNTRY').AsString := Edit1.Text;
493	493		end;
494	494		DBConnection.Connected := True;
495	495		SQLTransaction1.Active := True; //Starts a new transaction
496	496		SQLQuery1.Open;
497	497		{
498	498		Make sure we don't get problems with inserting blank (=NULL) CUST_NO values, i.e. error message:
499	499		"Field CUST_NO is required, but not supplied"
500	500		We need to tell Lazarus that, while CUST_NO is a primary key, it is not required
501	501		when inserting new records.
502	502		}
503	503		SQLQuery1.FieldByName('CUST_NO').Required:=false;
504	504		{
505	505		Hide the primary key column which is the first column in our queries.
506	506		We can only do this once the DBGrid has created the columns
507	507		}
508	508		DBGrid1.Columns[0].Visible:=false;
509	509		except
510	510		// EDatabaseError is a general error;
511	511		// you could also use one for your specific db, e.g.
512	512		// use EIBDatabaseError for Firebird/Interbase
513	513		on E: EDatabaseError do
514	514		begin
515	515		MessageDlg('Error', 'A database error has occurred. Technical error message: ' +
516	516		E.Message, mtError, [mbOK], 0);
517	517		Edit1.Text := '';
518	518		end;
519	519		end;
520	520		end;
521	521
522	522		procedure Tform1.Formclose(Sender: TObject; var Closeaction: Tcloseaction);
523	523		begin
524	524		SaveChanges; //Saves changes and commits transaction
525	525		SQLQuery1.Close;
526	526		SQLTransaction1.Active := False;
527	527		DBConnection.Connected := False;
528	528		end;
529	529
530	530		end.
531	531		```
532	532
533	533		## Embedded database without code changes
534	534
535	535		### Firebird on Windows
536	536
537	537		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.
538	538
539	539		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).
540	540
541	541		See [[Firebird embedded]] for more details.
542	542
543	543		September 2011: in recent development (SVN) versions of Free Pascal, FPC tries to first load `fbembed.dll`, so you need not rename `fbclient.dll` anymore for this to work.
544	544
545	545		### Firebird on Linux/macOS/Unix
546	546
547	547		There must be a way to get this to work on Linux/macOS. See [[Firebird]] for hints and links. Updates to the wiki are welcome.
548	548
549	549		### SQLite
550	550
551	551		SQLite certainly offers embedded functionality - it does not allow a client/server setup on the other hand. By following the tutorial above, you can see that switching between databases (e.g. SQLite and Firebird) is not so much work at all.
552	552
553	553		### Other databases
554	554
555	555		Your database might offer similar functionality. Updates of this wiki for other database systems are welcome.
556	556
557	557		## See also
558	558
559	559		- [[SQLdb Tutorial0]]: Instructions for setting up sample tables/sample data for the tutorial series.
560	560		- [[SQLdb Tutorial1]]: First part of the DB tutorial series, showing how to set up a grid that shows database data
561	561		- [[SQLdb Tutorial3]]: Third part of the DB tutorial series, showing how to program for multiple databases and use a login form
562	562		- [[SQLdb Tutorial4]]: Fourth part of the DB tutorial series, showing how to use data modules
563	563		- [[Lazarus Database Overview]]: Information about the databases that Lazarus supports. Links to database-specific notes.
564	564		- [[SQLdb Package]]: information about the SQLdb package
565	565		- [[SQLdb Programming Reference]]: an overview of the interaction of the SQLdb database components
566	566		- [[SqlDBHowto]]: information about using the SQLdb package
567	567		- [[Working With TSQLQuery]]: information about TSQLQuery
568	568
569	569		---
570	570		From the Free Pascal wiki, page "SQLdb Tutorial2", as it stood on 2024-03-29 after a change by Ariben, 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