SQLdb Tutorial1: 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		This tutorial shows you how to program databases using practical example code based on the [[SQLdb Package]]. It is primarily targeted at newbies. If somebody is looking for basics about databases and SQL, he should read relevant books/documentation. 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.
4	4
5	5		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.
6	6
7	7		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.
8	8
9	9		From Swen: thanks to Joost and Michael. Without their help this tutorial probably never would have come about.
10	10
11	11		## Requirements
12	12
13	13		This tutorial is written for use with recent Lazarus versions (Laz 1.0); it should also work on older versions Lazarus 0.9.30.
14	14
15	15		Please see [[SQLdb Tutorial0]], which will walk you through making sure you have the right sample database set up.
16	16
17	17		## Basic example
18	18
19	19		### Project and components
20	20
21	21		First you should create a new Lazarus project.
22	22
23	23		To get access to our database we need one [[TIBConnection]], one [[TSQLTransaction]] and one [[TSQLQuery]] component from the '[[SQLdb tab]]' in the component palette:
24	24
25		-	~~<figure>~~
26		-	~~<img src="sqldbcomponents.png‎" title="sqldbcomponents.png‎" />~~
27		-	~~<figcaption>sqldbcomponents.png‎</figcaption>~~
28		-	~~</figure>~~
	25	+	![](/wiki-images/c/c5/Sqldbcomponents.png%E2%80%8E)
29	26
30	27		[[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, [[TPQConnection]] for a PostgreSQL database. Discussion of setting up any database access libraries is out of scope for this tutorial; see e.g. [[Databases]] for that.
31	28
32	29		Click on the [[TIBConnection]] (or equivalent connection component) on your form, and in the Object Inspector, change the name to DBConnection. This will simplify the rest of the tutorial when using different databases. It's also generally a good idea to name your components for something useful in your program (e.g. MainframeDBConnection) so you know what it stands for.
33	30
34	31		The other two components, [[TSQLTransaction]] and [[TSQLQuery]], can be used for all databases that are supported by SQLdb.
35	32
36	33		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]]'.
37	34
38	35		Now we have all database components needed for the first example. You can enlarge the TDBGrid to have enough space to display all data.
39	36
40	37		### Link the components
41	38
42	39		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.
43	40
44	41		Change the Transaction property of DBConnection to 'SQLTransaction1'. This causes the Database property of SQLTransaction1 to automatically change to 'DBConnection'.
45	42
46	43		Then, change the Database property of SQLQuery1 to 'DBConnection'. Lazarus automatically adds the value for the 'Transaction' property.
47	44
48	45		Next, change the Dataset property of Datasource1 to 'SQLQuery1'.
49	46
50	47		Finally we change the Datasource property of DBGrid1 to 'Datasource1'.
51	48
52	49		We now end up with a connection that links its default transaction to a transaction component. The transaction component links its database property to the connection object. These two components are enough to connect and execute instructions, but not enough to show queries. For this, an SQLQuery component is used, which points to the database (and links to its default transaction). With the SQLQuery, we'll later on retrieve data and post it back to the database.
53	50
54	51		Finally, the datasource component, which is linked to the query component, is a sort of place holder. It keeps track of where in the query dataset we are and the GUI components are linked to that so they all show the same record.
55	52
56	53		If this is gibberish to you, don't despair: with just some more work, we'll be able to show our first data.
57	54
58	55		### Connecting to the database
59	56
60	57		How can we now show the data from our database on the screen?
61	58
62	59		First we need to tell DBConnection where the employee.fdb database is located. Locations of that db differ depending on operating system, it could be something like:
63	60
64	61		- .../examples/empbuild/ subdirectory of your Firebird installation on Linux
65	62		- C:\Program Files\Firebird\Firebird_2_5\examples\empbuild\EMPLOYEE.FDB on a Windows machine
66	63		- Using Firebird embedded, the file should be in your project directory
67	64
68	65		Again you have the choice: you can use the object inspector to assign the path or do it directly in your source code.
69	66
70	67		We choose to use the object inspector: set the DBConnection 'HostName' property to the Firebird server name or IP adddress. Use localhost if a Firebird server is running on your development machine; use a blank value if you use the embedded Firebird client. Change the DatabaseName property of DBConnection to the path to the employee.fdb file on the database server. If you use embedded Firebird, the path part should be empty and you should just specify the filename.
71	68
72	69		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. A possible way of doing this is shown in [[SQLdb Tutorial3]].
73	70
74	71		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' (of course, adjust if your database installation has a different username/password).
75	72
76	73		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 'Connected' to 'False').
77	74
78	75		#### PostgreSQL
79	76
80	77		The situation with PostgreSQL is very similar to that on Firebird. The database name does not have a path - you just specify a name part (e.g. 'employee'). PostgreSQL has no embedded mode, so you need to fill out the HostName property for the connection test to work.
81	78
82	79		#### SQLite
83	80
84	81		For SQLite, you can leave the 'HostName', 'UserName', and 'Password' properties empty. Set the 'DatabaseName' to the name of your SQLite file, e.g. employee.sqlite. Note: sqlite will create the database specified if it doesn't exist, so be careful here.
85	82
86	83		You should now have something like the following screenshot - todo: this screenshot is actually further along, we only have a button now: [framed\|center\|Form and components set up](/wiki-images/b/b2/SQLTut1ComponentsSetUp.png)
87	84
88	85		Change the property Options → dgDisplayMemoText of the DBGrid to '(True)'. Otherwise you will see (MEMO) in all the data fields.
89	86
90	87		### Choosing what data to show
91	88
92	89		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. Save our project and then open the project lazdatadesktop.lpi and compile it.
93	90
94	91		[framed\|center\|The DataDesktop in action](/wiki-images/d/da/DataDictonary1.png)
95	92
96	93		Back to our example.
97	94
98	95		We want to display all data from the table 'CUSTOMER'. The SQL instruction for that is:
99	96
100	97		``` sql
101	98		select * from CUSTOMER
102	99		```
103	100
104	101		We need to assign this command to the 'SQL' property of SQLQuery1. In the source code of our project this would look like:
105	102
106	103		``` pascal
107	104		SQLQuery1.SQL.Text := 'select * from CUSTOMER';
108	105		```
109	106
110	107		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.
111	108
112	109		Let's add a TButton from the 'Standard' tab on the form. When the user clicks on the button, data retrieval should start. Change its Caption property from "Button1" to Show data".
113	110
114	111		We will need some code for this. Double click on Button1. Lazarus then creates the skeleton of the necessary procedure. In our source code we should find the following lines:
115	112
116	113		``` pascal
117	114		procedure TForm1.Button1Click(Sender: TObject);
118	115		begin
119	116
120	117		end;
121	118		```
122	119
123	120		Between begin and end we must enter the instructions needed to display the data.... obviously that will be something to do with SQLQuery1..
124	121
125	122		The 'SQL' property of SQLQuery1 can only be changed, if SQLQuery1 is not active. That's why we close the component first:
126	123
127	124		``` pascal
128	125		SQLQuery1.Close;
129	126		```
130	127
131	128		Then we assign our SQL instruction to the 'SQL' property, overwriting any previous SQL commands:
132	129
133	130		``` pascal
134	131		SQLQuery1.SQL.Text := 'select * from CUSTOMER';
135	132		```
136	133
137	134		Now we need to establish the connection to the database, activate the transaction and open the query:
138	135
139	136		``` pascal
140	137		DBConnection.Connected := True;
141	138		SQLTransaction1.Active := True;
142	139		SQLQuery1.Open;
143	140		```
144	141
145	142		You can omit the first two instructions, because they are done automatically by the third instruction. If you compile the project at this point, you could already see the data from the 'CUSTOMER' table.
146	143
147	144		However, a serious application must make sure 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):
148	145
149	146		``` pascal
150	147		procedure TForm1.FormClose(Sender: TObject; var CloseAction: TCloseAction);
151	148		begin
152	149
153	150		end;
154	151		```
155	152
156	153		To close the connection we use the reverse order compared to our opening code:
157	154
158	155		``` pascal
159	156		SQLQuery1.Close;
160	157		SQLTransaction1.Active := False;
161	158		DBConnection.Connected := False;
162	159		```
163	160
164	161		If you set DBConnection.Connected to False, the Transaction and the Query is automatically closed and you can omit closing them manually.
165	162
166	163		## Summary
167	164
168	165		Up to now we have learned how to connect to a database using the SQLdb package and how to display the contents of a table on the screen. If you want to add more functionality such as editing, please continue with [[SQLdb Tutorial2]]
169	166
170	167		If you followed the previous steps, then your code should look like:
171	168
172	169		``` pascal
173	170		unit Unit1;
174	171
175	172		{$mode objfpc}{$H+}
176	173
177	174		interface
178	175
179	176		uses
180	177		Classes, SysUtils, IBConnection, sqldb, db, FileUtil, Forms, Controls,
181	178		Graphics, Dialogs, DBGrids, StdCtrls;
182	179
183	180		type
184	181
185	182		{ TForm1 }
186	183
187	184		TForm1 = class(TForm)
188	185		Button1: TButton;
189	186		Datasource1: TDatasource;
190	187		DBGrid1: TDBGrid;
191	188		DBConnection: TIBConnection;
192	189		SQLQuery1: TSQLQuery;
193	190		SQLTransaction1: TSQLTransaction;
194	191		procedure Button1Click(Sender: TObject);
195	192		procedure FormClose(Sender: TObject; var CloseAction: TCloseAction);
196	193		private
197	194		{ private declarations }
198	195		public
199	196		{ public declarations }
200	197		end;
201	198
202	199		var
203	200		Form1: TForm1;
204	201
205	202		implementation
206	203
207	204		{$R *.lfm}
208	205
209	206		{ TForm1 }
210	207
211	208		procedure TForm1.Button1Click(Sender: TObject);
212	209		begin
213	210		SQLQuery1.Close;
214	211		SQLQuery1.SQL.Text:= 'select * from CUSTOMER';
215	212		DBConnection.Connected:= True;
216	213		SQLTransaction1.Active:= True;
217	214		SQLQuery1.Open;
218	215		end;
219	216
220	217		procedure TForm1.FormClose(Sender: TObject; var CloseAction: TCloseAction);
221	218		begin
222	219		SQLQuery1.Close;
223	220		SQLTransaction1.Active:= False;
224	221		DBConnection.Connected:= False;
225	222		end;
226	223
227	224		end.
228	225		```
229	226
230	227		## See also
231	228
232	229		- [[SQLdb Tutorial0]]: Instructions for setting up sample tables/sample data for the tutorial series.
233	230		- [[SQLdb Tutorial2]]: Second part of the DB tutorial series, showing editing, inserting etc.
234	231		- [[SQLdb Tutorial3]]: Third part of the DB tutorial series, showing how to program for multiple databases and use a login form
235	232		- [[SQLdb Tutorial4]]: Fourth part of the DB tutorial series, showing how to use data modules
236	233		- [[Lazarus Database Overview]]: Information about the databases that Lazarus supports. Links to database-specific notes.
237	234		- [[SQLdb Package]]: information about the SQLdb package
238	235		- [[SQLdb Programming Reference]]: an overview of the interaction of the SQLdb database components
239	236		- [[SqlDBHowto]]: information about using the SQLdb package
240	237		- [[Working With TSQLQuery]]: information about TSQLQuery
241	238
242	239		---
243	240		From the Free Pascal wiki, page "SQLdb Tutorial1", as it stood on 2022-11-01 after a change by Kj14, 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