SQLdb Tutorial3: what changed

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

Title: SQLdb Tutorial3

New		Line
1	+	## Overview
2	+
3	+	In this tutorial, you will learn how to
4	+
5	+	- make your application suitable for multiple databases, including using a login form
6	+	- get database data into normal controls (instead of database controls)
7	+	- get data out of the controls and back into the database
8	+	- execute parametrized queries.
9	+
10	+	## Multi database support
11	+
12	+	Using any database and a login form, you can support multiple different database servers/embedded libraries that SQLDB supports.
13	+
14	+	Advantages:
15	+
16	+	- the user/programmer can dynamically use any sqldb t*connection, so he can choose between dbs
17	+
18	+	Disadvantages:
19	+
20	+	- More complicated SQL will likely need to still be adapted. Each database has its own dialect; it is of course possible to call db-specific SQL, this can grow into a maintenance problem.
21	+	- You can't use Tconnection specific properties (like TIBConnection.Dialect* to set Firebird dialect).
22	+
23	+	To use multi-database support, instead of your specific TConnection such as TIBConnection, use TSQLConnector* (not TSQLConnection), which dynamically (when the program is running) chooses the specific TConnection to use based on its ConnectorType* property:
24	+
25	+	``` pascal
26	+	uses
27	+	...
28	+	var
29	+	Conn: TSQLConnector;
30	+	begin
31	+	Conn:=TSQLConnector.Create(nil);
32	+	try
33	+	// ...actual connector type is determined by this property.
34	+	// Make sure the ChosenConfig.DBType string matches
35	+	// the connectortype (e.g. see the string in the
36	+	// T*ConnectionDef.TypeName for that connector .
37	+	Conn.ConnectorType:=ChosenConfig.DBType;
38	+	// the rest is as usual:
39	+	Conn.HostName:='DBSERVER';
40	+	Conn.DatabaseName:='bigdatabase.fdb';
41	+	Conn.UserName:='SYSDBA';
42	+	Conn.Password:='masterkey';
43	+	try
44	+	Conn.Open;
45	+	```
46	+
47	+	## Login form
48	+
49	+	As mentioned in [[SQLdb Tutorial1]], a user should login to the database using a form (or perhaps a configuration file that is securely stored), not via hardcoded credentials in the application. Besides security considerations, having to recompile the application whenever the database server information changes is not a very good idea.
50	+
51	+	In dbconfiggui.pas, we will set up a login form that pulls in default values from an ini file, if it exists. This allows you to set up a default connection with some details (database server, database name) filled out for e.g. enterprise deployments. In the form, the user can add/edit his username/password, and test the connection before going further.
52	+
53	+	<figure>
54	+	<img src="dbloginform.png" title="dbloginform.png" />
55	+	<figcaption>dbloginform.png</figcaption>
56	+	</figure>
57	+
58	+	We use a separate dbconfig.pas unit with a TDBConnectionConfig class to store our chosen connection details. This class has support for reading default settings from an ini file.
59	+
60	+	This allows use without GUI login forms (e.g. when running unattended/batch operations), and allows reuse in e.g. web applications.
61	+
62	+	This TDBConnectionConfig class is surfaced in the login form as the Config property, so that the main program can show the config form modally, detect an OK click by the user and retrieve the selected configuration before closing the config form.
63	+
64	+	### Connection test callback function
65	+
66	+	To keep the login form flexible (it may be used with other database layers like Zeos), we implement the test section as a callback function and let the main program deal with it.
67	+
68	+	The definition in the login form in dbconfiggui.pas:
69	+
70	+	``` pascal
71	+	type
72	+	TConnectionTestFunction = function(ChosenConfig: TDBConnectionConfig): boolean of object;
73	+	```
74	+
75	+	The main form must implement a function that matches this definition to handle the test request from the config form.
76	+
77	+	The callback function takes the config object passed on by the config form, and uses that to construct a connection with the chosen database type. It then simply tries to connect with the server; if it is succesful, it sets the function result to true, otherwise the result stays false.
78	+
79	+	Because database connection attempts to non-existing servers can have a long timeout, we indicate to the user that he must wait by setting the cursor to the hourglass icon.
80	+
81	+	``` pascal
82	+	uses
83	+	...
84	+	dbconfig, dbconfiggui
85	+	...
86	+	procedure TForm1.FormCreate(Sender: TObject);
87	+	LoginForm:=TDBConfigForm.Create(self);
88	+	try
89	+	// The test button on dbconfiggui will link to this procedure:
90	+	... this links the callback in ''dbconfiggui.pas'' to the ConnectionTest function here...
91	+	LoginForm.ConnectionTestCallback:=@ConnectionTest;
92	+	...
93	+	function TForm1.ConnectionTest(ChosenConfig: TDBConnectionConfig): boolean;
94	+	// Callback function that uses the info in dbconfiggui to test a connection
95	+	// and return the result of the test to dbconfiggui
96	+	var
97	+	// Generic database connector...
98	+	Conn: TSQLConnector;
99	+	begin
100	+	result:=false;
101	+	Conn:=TSQLConnector.Create(nil);
102	+	Screen.Cursor:=crHourglass;
103	+	try
104	+	// ...actual connector type is determined by this property.
105	+	// Make sure the ChosenConfig.DBType string matches
106	+	// the connectortype (e.g. see the string in the
107	+	// T*ConnectionDef.TypeName for that connector .
108	+	Conn.ConnectorType:=ChosenConfig.DBType;
109	+	Conn.HostName:=ChosenConfig.DBHost;
110	+	Conn.DatabaseName:=ChosenConfig.DBPath;
111	+	Conn.UserName:=ChosenConfig.DBUser;
112	+	Conn.Password:=ChosenConfig.DBPassword;
113	+	try
114	+	Conn.Open;
115	+	result:=Conn.Connected;
116	+	except
117	+	// Result is already false
118	+	end;
119	+	Conn.Close;
120	+	finally
121	+	Screen.Cursor:=crDefault;
122	+	Conn.Free;
123	+	end;
124	+	end;
125	+	```
126	+
127	+	Finally, the code in dbconfiggui.pas that actually calls the callback is linked to the Test button. It tests if the callback function is assigned (to avoid crashes), for completeness also checks if there is a valid configuration object and then simply calls the callback function:
128	+
129	+	``` pascal
130	+	...
131	+	TDBConfigForm = class(TForm)
132	+	...
133	+	private
134	+	FConnectionTestFunction: TConnectionTestFunction;
135	+	public
136	+	property ConnectionTestCallback: TConnectionTestFunction write FConnectionTestFunction;
137	+	...
138	+	procedure TDBConfigForm.TestButtonClick(Sender: TObject);
139	+	begin
140	+	// Call callback with settings, let it figure out if connection succeeded and
141	+	// get test result back
142	+	if assigned(FConnectionTestFunction) and assigned(FConnectionConfig) then
143	+	if FConnectionTestFunction(FConnectionConfig) then
144	+	showmessage('Connection test succeeded.')
145	+	else
146	+	showmessage('Connection test failed.')
147	+	else
148	+	showmessage('Error: connection test code has not been implemented.');
149	+	end;
150	+	```
151	+
152	+	### Additions/modifications
153	+
154	+	Possible additions/modifications for the login form:
155	+
156	+	- Add command line arguments handling for dbconfig to preload suitable defaults, so the program can be used in batch scripts, shortcuts etc
157	+	- Add a "Select profile" combobox in the login form; use multiple profiles in the ini file that specify database type and connection details.
158	+	- Hide database type combobox when only one database type supported.
159	+	- Hide username/password when you're sure an embedded database is selected.
160	+	- Add support for specifying port number, or instance name with MS SQL Server connector
161	+	- Add support for trusted authentication for dbs that support it (Firebird, MS SQL): disable ussername/password controls
162	+	- If an embedded database is selected but the file does not exist: show a confirmation request and create the database
163	+	- Create a command-line/TUI version of the login form (e.g. using the curses library) for command-line applictions
164	+
165	+	Updates to this article/the code warmly welcomed.
166	+
167	+	## Getting database data into normal controls
168	+
169	+	> Note: Before starting this section, please make sure you have set up the sample employee database as specified in [[SQLdb Tutorial0\|SQLdb Tutorial0#Requirements]]
170	+
171	+	In previous tutorials, data-bound controls were covered: special controls such as the [[TDBGrid]] that can bind its contents to a [[TDataSource]], get updates from that source and send user edits back.
172	+
173	+	It is also possible to programmatically retrieve database content and fill any kind of control (or variable) with that content. As an example, we will look at filling a stringgrid with some salary details for the sample employee database table.
174	+
175	+	On the main form, let's add a [[TStringGrid]] and retrieve the data (e.g. via a procedure LoadSalaryGrid called in the OnCreate event):
176	+
177	+	``` pascal
178	+	// Load from DB
179	+	try
180	+	if not FConn.Connected then
181	+	FConn.Open;
182	+	if not FConn.Connected then
183	+	begin
184	+	ShowMessage('Error connecting to the database. Aborting data loading.');
185	+	exit;
186	+	end;
187	+
188	+	// Lowest salary
189	+	// Note: we would like to only retrieve 1 row, but unfortunately the SQL
190	+	// used differs for various dbs. As we'll deal with db dependent SQL later
191	+	// in the tutorial, we leave this for now.
192	+	// MS SQL: 'select top 1 '...
193	+	FQuery.SQL.Text:='select ' +
194	+	' e.first_name, ' +
195	+	' e.last_name, ' +
196	+	' e.salary ' +
197	+	'from employee e ' +
198	+	'order by e.salary asc ';
199	+	// ISO SQL+Firebird SQL: add
200	+	//'rows 1 '; here and below... won't work on e.g. PostgreSQL though
201	+	FTran.StartTransaction;
202	+	FQuery.Open;
203	+	SalaryGrid.Cells[1,1]:=FQuery.Fields[0].AsString; // i.e. Cells[Col,Row]
204	+	SalaryGrid.Cells[2,1]:=FQuery.Fields[1].AsString;
205	+	SalaryGrid.Cells[3,1]:=FQuery.Fields[2].AsString;
206	+	FQuery.Close;
207	+	// Always commit(retain) an opened transaction, even if only reading
208	+	// this will allow updates by others to be seen when reading again
209	+	FTran.Commit;
210	+	...
211	+	end;
212	+	except
213	+	on D: EDatabaseError do
214	+	begin
215	+	MessageDlg('Error', 'A database error has occurred. Technical error message: ' +
216	+	D.Message, mtError, [mbOK], 0);
217	+	end;
218	+	end;
219	+	```
220	+
221	+	Things to note: we catch database errors using try..except. You'll notice we forgot to roll back the transaction in case of errors - which is left as an exercise to the reader.
222	+
223	+	We Open the query object, thereby asking FQuery to query the database via its SQL statement. Once this is done, we're on the first row of data. We simply assume there is data now; this is actually a programming error: it would be tidier to check for FQuery.EOF being true (or FQuery.RecordCount being \>0).
224	+
225	+	Next, we retrieve the data from the first row of results. If we wanted to move to the next row, we'd use FQuery.Next, but that is not necessary here. We put the results in the stringgrid, giving the lowest salary in the list. A similar approach can be taken for the highest salary.
226	+
227	+	## Adapting SQL for various databases
228	+
229	+	As we noticed above, various databases support various versions of SQL (either in addition to or in contradiction to the official ISO SQL standards). Fortunately, you can customize your application based on which DB it ends up using, which will be demonstrated by getting the standard deviation of the employees' salaries - built into e.g. PostgreSQL SQL but not available by default in e.g. Firebird.
230	+
231	+	In our LoadSalaryGrid procedure, we'll use the SQL for PostgreSQL and build a code solution for all other databases. First detect which database is loaded, below the other lines add:
232	+
233	+	``` pascal
234	+	...
235	+	SalaryGrid.Cells[3,2]:=FQuery.Fields[2].AsString;
236	+	FQuery.Close;
237	+	// Always commit(retain) an opened transaction, even if only reading
238	+	FTran.Commit;
239	+	//end of existing code
240	+
241	+	if FConn.ConnectorType = 'PostGreSQL' then
242	+	begin
243	+	// For PostgreSQL, use a native SQL solution:
244	+	FQuery.SQL.Text:='select stddev_pop(salary) from employee ';
245	+	FTran.StartTransaction;
246	+	FQuery.Open;
247	+	if not FQuery.EOF then
248	+	SalaryGrid.Cells[3,3]:=FQuery.Fields[0].AsString;
249	+	FQuery.Close;
250	+	// Always commit(retain) an opened transaction, even if only reading
251	+	FTran.Commit;
252	+	end
253	+	else
254	+	begin
255	+	// For other database, use the code approach:
256	+	....see below...
257	+	end;
258	+	```
259	+
260	+	Notice the use of ConnectorType; the string used must match exactly. We also properly check for empty results from the query (which might happen if the employee table is empty).
261	+
262	+	... now let's implement a code-based solution for other databases that do not support standard deviation:
263	+
264	+	``` pascal
265	+	// For other databases, use the code approach:
266	+	// 1. Get average of values
267	+	FQuery.SQL.Text:='select avg(salary) from employee ';
268	+	FQuery.Open;
269	+	if FQuery.EOF then
270	+	SalaryGrid.Cells[3,3]:='No data'
271	+	else
272	+	begin
273	+	Average:=FQuery.Fields[0].AsFloat;
274	+	FQuery.Close;
275	+	// 2. For each value, calculate the square of (value-average), and add it up
276	+	FQuery.SQL.Text:='select salary from employee where salary is not null ';
277	+	FQuery.Open;
278	+	while not FQuery.EOF do
279	+	begin
280	+	DifferencesSquared:=DifferencesSquared+Sqr(FQuery.Fields[0].AsFloat-Average);
281	+	Count:=Count+1;
282	+	FQuery.Next;
283	+	end;
284	+	// 3. Now calculate the average "squared difference" and take the square root
285	+	if Count>0 then //avoid division by 0
286	+	SalaryGrid.Cells[3,3]:=FloatToStr(Sqrt(DifferencesSquared/Count))
287	+	else
288	+	SalaryGrid.Cells[3,3]:='No data';
289	+	end;
290	+	FQuery.Close;
291	+	```
292	+
293	+	Note that we use FQuery.EOF to check for empty data (and avoid division by zero errors etc). The loop shows how to:
294	+
295	+	- retrieve a database value into a variable
296	+	- use FQuery.Next to move to the next record
297	+	- properly check if the query dataset has hit the last record, then stop retrieving data.
298	+
299	+	The resulting screen should show something like this - note the use of a decimal comma - while your computer may show a decimal point depending on your locale:
300	+
301	+	<figure>
302	+	<img src="sqldbtutorial3mainform.png" title="sqldbtutorial3mainform.png" />
303	+	<figcaption>sqldbtutorial3mainform.png</figcaption>
304	+	</figure>
305	+
306	+	### Revisiting our lowest/highest salary
307	+
308	+	This section gives some more useful details on SQL but is not required to work through for the rest of the tutorial
309	+
310	+	Now we know how to deal with detecting various database connections, we can adjust the SQL that gets the lowest and highest salary as well to make use of db specific functionality.
311	+
312	+	An example: this would work for MS SQL Server by limiting the number of returned rows to just the first:
313	+
314	+	``` SQL
315	+	select top 1
316	+	e.first_name, e.last_name, e.salary
317	+	from employee e
318	+	order by e.salary asc
319	+	```
320	+
321	+	to get the lowest salary.
322	+
323	+	This efficiently returns one record. Other databases use other syntax, such as the ISO ROWS 1. The diligent SQL student will soon learn not to miss out that important part and request entire large recordsets just for one required record!
324	+
325	+	Let's briefly examine other ways to achieve the same thing, that are worth knowing.
326	+
327	+	Another way to retrieve the record(s) with the minimum salary would be :''
328	+
329	+	``` SQL
330	+	SELECT e.first_name, e.last_name, e.salary FROM employee e WHERE e.salary=(SELECT min(salary) FROM employee)
331	+	```
332	+
333	+	SQL students would greatly benefit from researching Common Table Expressions.
334	+
335	+	A CTE allows a virtual temporary table to be used in a following expression, allowing you to clearly code some very complex queries that otherwise may not be possible. Knowing about CTEs will catapult you ahead of colleagues who have never heard of them! For example the above may be rewritten (example in Microsoft SQL Server syntax) as : ''
336	+
337	+	``` SQL
338	+	WITH TheMinimum as
339	+	(
340	+	SELECT min(salary) as MinimumPay FROM Employee
341	+	)
342	+	SELECT e.first_name, e.last_name, e.salary FROM Employee e WHERE e.salary=(SELECT MinimumPay FROM TheMinimum)
343	+	```
344	+
345	+	Several such temporary tables may be chained together, each using the results from the previous tables. You can treat these virtual tables as though they were real tables in the database, using JOINs to link recordsets together. And it can be very useful for quick tests using hardcoded data - this can be run without any database connection :
346	+
347	+	``` SQL
348	+	WITH TestEmployee as
349	+	(
350	+	SELECT 'Fred' as first_name, 'Bloggs' as last_name, 10500 as salary
351	+	UNION
352	+	SELECT 'Joe' as first_name, 'Public' as last_name, 10000 as salary
353	+	UNION
354	+	SELECT 'Mike' as first_name, 'Mouse' as last_name, 11000 as salary
355	+	),
356	+	TheMinimum as
357	+	(
358	+	SELECT min(salary) as MinimumPay FROM TestEmployee
359	+	)
360	+	SELECT e.first_name, e.last_name, e.salary FROM TestEmployee e WHERE e.salary=(SELECT MinimumPay FROM TheMinimum)
361	+	```
362	+
363	+	You can end up with quite long strings for the code of such SQL queries, but it is only one query and may be called from anywhere where you are limited to a simple single expression - it can be useful to answer complex queries without resorting to functions or stored procedures.
364	+
365	+	## Getting data out of normal controls into the database
366	+
367	+	Previously, we have seen:
368	+
369	+	- how to let SQLDB update the database with data-bound controls (earlier tutorials)
370	+	- how to get data out of the database using queries (the section above)
371	+
372	+	You can also execute SQL to get arbitrary data back into the database via code. This allows you to use variables or controls that have no db aware equivalent such as sliders or custom controls to enter data into the database, at the expense of a bit more coding.
373	+
374	+	As an example, we are going to allow the user to change the lowest and highest salary in the stringgrid.
375	+
376	+	For ease of editing, set the grid's Options/goEditing to true; then assign the procedure below to the OnValidate event for the grid, which will be called every time a user has finished updating the grid.
377	+
378	+	### Parameterized queries
379	+
380	+	The following code also demonstrates how to use parameterized queries to avoid SQL injection, fiddling with quoting for string values, date formatting, etc.
381	+
382	+	As you can see in the code, you can name your parameters whatever you wish and prefix them with : in the SQL. In code, you can set/get their values by \<somequery\>.Params.ParamByName('\<thename\>').As'\<variabletype\>'; the code demonstrates.AsFloatand.AsString''.
383	+
384	+	Parameterized queries are especially useful (and can be much faster) if you run the same query, only with different parameters, in a loop (think e.g. bulk loading of data).
385	+
386	+	Continuing with our example: after having set up the query SQL and parameters, the transaction is started (and later on committed) as usual, then the query is run by calling ExecSQL (which does not return a result set; if the SQL statement were e.g. a SELECT or INSERT...RETURNING that does return data, you would use Open as in the examples above):
387	+
388	+	``` pascal
389	+	procedure TForm1.SalaryGridValidateEntry(sender: TObject; aCol, aRow: Integer;
390	+	const OldValue: string; var NewValue: String);
391	+	begin
392	+	// Only these cells have min and max salary:
393	+	if (aCol=3) and ((aRow=1) or (aRow=2)) then
394	+	begin
395	+	// Allow updates to min and max salary if positive numerical data is entered
396	+	if StrToFloatDef(NewValue,-1)>0 then
397	+	begin
398	+	// Storing the primary key in e.g. a hidden cell in the grid and using that in our
399	+	// update query would be cleaner, but we can do it the hard way as well:
400	+	FQuery.SQL.Text:='update employee set salary=:newsalary '+
401	+	' where first_name=:firstname and last_name=:lastname and salary=:salary ';
402	+	FQuery.Params.ParamByName('newsalary').AsFloat:=StrToFloatDef(NewValue,0);
403	+	FQuery.Params.ParamByName('firstname').AsString:=SalaryGrid.Cells[1,aRow];
404	+	FQuery.Params.ParamByName('lastname').AsString:=SalaryGrid.Cells[2,aRow];
405	+	FQuery.Params.ParamByName('salary').AsFloat:=StrToFloatDef(OldValue,0);
406	+	FTran.StartTransaction;
407	+	FQuery.ExecSQL;
408	+	FTran.Commit;
409	+	LoadSalaryGrid; //reload standard deviation
410	+	end
411	+	else
412	+	begin
413	+	// Notify user that his input was wrong... he'll be wondering otherwise:
414	+	Showmessage('Invalid salary entered.');
415	+	NewValue:=OldValue;
416	+	end;
417	+	end
418	+	else
419	+	begin
420	+	// Silently discard edits to any other cells
421	+	NewValue:=OldValue;
422	+	end;
423	+	end;
424	+	```
425	+
426	+	Note how we forgot to add a try..except block to this code to nicely catch database errors and display a sensible error message. If you are running the Firebird sample EMPLOYEE database for this tutorial, try to change the salary to a very low value (say 1) and see what happens.
427	+
428	+	Finally, while this example showed an UPDATE SQL query, you could just as well run INSERT queries to insert new data programmatically. Also, you can use parameters in any kind of SQL query (SELECT, UPDATE, etc) as long as you use them for fields, not for table/view/procedure names.
429	+
430	+	## Summary
431	+
432	+	This tutorial explained:
433	+
434	+	- how to code for multiple database types
435	+	- how to use a login form to decouple db access configuration from your program
436	+	- how to retrieve and update data programmatically
437	+
438	+	## Code
439	+
440	+	Since November 2012, the code can be found in \$(lazarusdir)examples/database/sqldbtutorial3
441	+
442	+	If you have an older version (e.g. Lazarus 1.0.2), you can also download the code via [the Lazarus SVN website](http://svn.freepascal.org/svn/lazarus/trunk/examples/database/sqldbtutorial3/)
443	+
444	+	## See also
445	+
446	+	- [[SQLdb Tutorial0]]: Instructions for setting up sample tables/sample data for the tutorial series.
447	+	- [[SQLdb Tutorial1]]: First part of the DB tutorial series, showing how to set up a grid that shows database data
448	+	- [[SQLdb Tutorial2]]: Second part of the DB tutorial series, showing editing, inserting etc.
449	+	- [[SQLdb Tutorial4]]: Fourth part of the DB tutorial series, showing how to use data modules
450	+	- [[Lazarus Database Overview]]: Information about the databases that Lazarus supports. Links to database-specific notes.
451	+	- [[SQLdb Package]]: information about the SQLdb package
452	+	- [[SQLdb Programming Reference]]: an overview of the interaction of the SQLdb database components
453	+	- [using parameters](http://www.freepascal.org/docs-html/fcl/sqldb/usingparams.html)
454	+	- [[SqlDBHowto]]: information about using the SQLdb package
455	+	- [[Working With TSQLQuery]]: information about TSQLQuery
456	+
457	+	---
458	+	From the Free Pascal wiki, page "SQLdb Tutorial3", as it stood on 2024-03-28 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