SqlDBHowto: what changed

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

Old	New		Line
1	1		This text is setup as a 'how-to'. I want to answer a number of questions one by one, and explain how you can use the various classes. All those questions are put one after the other and form a sort of tutorial.
2	2
3	3		I will try to word it in such a way that the text can be used for Lazarus as well as Free Pascal. However, the examples are for FreePascal (i.e. they are console applications.)
4	4
5	5		## Where can I find official documentation?
6	6
7	7		Please see the official documentation at [SQLDB documentation](http://www.freepascal.org/docs-html/fcl/sqldb/index.html).
8	8
9	9		## How to connect to a database server?
10	10
11	11		SqlDB doesn't connect to a database server directly but uses a client that corresponds to the used database server. SqlDB sends the commands to the client library; the client library connects to the database and and transfers the commands. This means that a client library must be installed on the computer to make a connection to a database. Under Windows a client is usually a .dll, under Linux an .so and under OS/X a .dylib.
12	12
13	13		When the client library is installed properly you can connect to a database server using a TSQLConnection component. Various TSQLConnection components are available for different database servers (see [[SQLdb Package]]):
14	14
15	15		- Firebird/Interbase: [[TIBConnection]]
16	16		- MS SQL Server: [[TMSSQLConnection]] (available since FPC 2.6.1)
17	17		- MySQL v4.0: [[TMySQL40Connection]]
18	18		- MySQL v4.1: [[TMySQL41Connection]]
19	19		- MySQL v5.0: [[TMySQL50Connection]]
20	20		- MySQL v5.1: [[TMySQL51Connection]] (available since FPC version 2.5.1
21	21		- MySQL v5.5: [[TMySQL55Connection]] (available since Lazarus 1.0.8/FPC version 2.6.2
22	22		- MySQL v5.6: [[TMySQL56Connection]] (available in Lazarus 1.2.4/FPC version 2.6.4
23	23		- ODBC: [[TODBCConnection]] (see [[ODBCConn\|ODBCConn#TODBCConnection]])
24	24		- Oracle: [[TOracleConnection]] (see [[Oracle]])
25	25		- PostgreSQL: [[TPQConnection]] (see [[postgresql\|postgresql#SQLDB]])
26	26		- Sqlite3: [[TSQLite3Connection]] (available since FPC version 2.2.2, see [[SQLite\|SQLite#Built-in_SQLDB]])
27	27		- Sybase ASE: [[TSybaseConnection]] (available since FPC 2.6.1, see [[Lazarus Database Overview\|Lazarus and MSSQL/Sybase]])
28	28
29	29		Note for MySQL - There are many differences between the client versions to the extent that the clients and connections cannot be interchanged. If a MySQL client library version 4.1 is installed, you have to use a TMySQL41Connection. This is not related to the MySQL server; using the MySQL 4.1 client library you can probably connect to a MySQL 5.0 server (see MySQL documentation regarding what combinations are supported).
30	30
31	31		Although details differ for the various databases, in general you need to set four properties to connect to a database server:
32	32
33	33		- the server name or IP address
34	34		- the name of the database
35	35		- the username
36	36		- the password
37	37
38	38		When these properties are set, you can create a connection with the 'open' method. If the connection fails, a EDatabaseError exception is thrown. Use the property 'connected' to test if a connection has been made with the database server. Use the 'close' method to end the connection with the server.
39	39
40	40		``` pascal
41	41		Program ConnectDB;
42	42
43	43		{$mode objfpc}{$H+}
44	44
45	45		uses
46	46		IBConnection;
47	47
48	48		function CreateConnection: TIBConnection;
49	49		begin
50	50		result := TIBConnection.Create(nil);
51	51		result.Hostname := 'localhost';
52	52		result.DatabaseName := '/opt/firebird/examples/employee.fdb';
53	53		result.UserName := 'sysdba';
54	54		result.Password := 'masterkey';
55	55		end;
56	56
57	57		var
58	58		AConnection : TIBConnection;
59	59
60	60		begin
61	61		AConnection := CreateConnection;
62	62		AConnection.Open;
63	63		if Aconnection.Connected then
64	64		writeln('Successful connect!')
65	65		else
66	66		writeln('This is not possible, because if the connection failed, ' +
67	67		'an exception should be raised, so this code would not ' +
68	68		'be executed');
69	69		AConnection.Close;
70	70		AConnection.Free;
71	71		end.
72	72		```
73	73
74	74		If an exception is thrown, read the error message carefully. It may be that the database server is not running, the user name or password are incorrect or the database name or IP address are typed incorrectly. If the error message states that the client library cannot be found, then check if the client is installed correctly. Often the error message states literally the name of the file looked for.
75	75
76	76		## How to execute direct queries/make a table?
77	77
78	78		SqlDB - the name says it all - only works with database server that make use of SQL. SQL stands for 'Structured Query Language' SQL is a language developed to allow working with relational databases. Virtually every database system has its own dialect, but a large number of SQL statements are the same for all database systems.
79	79
80	80		In FPC, there is a difference between:
81	81
82	82		- SQL statements that return information (a dataset). For this, you have to use the TSQLQuery component; see #How to read data from a table?.
83	83		- statements that do not return information but do something else, e.g. update data. For this, you may also use the 'ExecuteDirect' method of a TSQLConnection. (You can also use this if you get a dataset back but are not interested in the results, e.g. in a selectable stored procedure).
84	84
85	85		Most database system execute SQL statements within a transaction. If you want changes made within a transaction available in other transactions, or have those changes available even after closing the transaction(!), then you have to 'commit' the transaction.
86	86
87	87		To support transactions Sqldb contains the TSQLTransaction component. A SQL statement that is executed by Sqldb must always be executed within a transaction, even if the database system does not support transactions. Also, there are database systems that do support transaction for which TSQLConnection does not (yet) support transaction. Even then, you must use the TSQLTransaction component.
88	88
89	89		To use `TSQLConnection.ExecuteDirect` to execute a SQL statement you must specify which 'Transaction' must be used. In turn, to use TSQLTransaction you must specify which TSQLConnection component must be used.
90	90
91	91		The following example creates a table 'TBLNAMES' with fields 'NAME' and 'ID' and inserts two records. This time using SQLite. The used SQL statements are not explained. For more information about the SQL statements, their use and syntax, please refer to the database system documentation. Note that this example does not attempt to catch any errors, thats a bad thing! Look into [Exceptions](https://www.freepascal.org/docs-html/current/ref/refch17.html#x226-24800017).
92	92
93	93		``` pascal
94	94		program CreateTable;
95	95		{$mode objfpc} {$ifdef mswindows}{$apptype console}{$endif}
96	96		uses
97	97		sqldb, sqlite3conn;
98	98
99	99		var
100	100		AConnection : TSQLite3Connection;
101	101		ATransaction : TSQLTransaction;
102	102
103	103		begin
104	104		AConnection := TSQLite3Connection.Create(nil);
105	105		AConnection.DatabaseName := 'test_dbase';
106	106
107	107		ATransaction := TSQLTransaction.Create(AConnection);
108	108		AConnection.Transaction := ATransaction;
109	109		AConnection.Open;
110	110		ATransaction.StartTransaction;
111	111		AConnection.ExecuteDirect('create table TBLNAMES (ID integer, NAME varchar(40));');
112	112
113	113		ATransaction.Commit;
114	114
115	115		ATransaction.StartTransaction;
116	116		AConnection.ExecuteDirect('insert into TBLNAMES (ID,NAME) values (1,''Name1'');');
117	117		AConnection.ExecuteDirect('insert into TBLNAMES (ID,NAME) values (2,''Name2'');');
118	118		ATransaction.Commit;
119	119		AConnection.Close;
120	120		ATransaction.Free;
121	121		AConnection.Free;
122	122		end.
123	123		```
124	124
125	125		## How to read data from a table?
126	126
127	127		Use the TSQLQuery component to read data from a table. A TSQLQuery component must be connected to a TSQLConnection component and a TSQLTransaction component to do its work. Setting the TSQLConnection and TSQLTransaction is discussed in #How to connect to a database server? and #How to execute direct queries/make a table?.
128	128
129	129		When the TSQLConnection, TSQLTransaction and TSQLQuery are connected, then TSQLQuery needs to be further configured to work. TSQLQuery has a 'SQL' property containing a TStrings object. The 'SQL' property contains a SQL statement that must be executed. If all data from a table `tablename` must be read, then set the 'SQL' property to:
130	130
131	131		``` sql
132	132		'SELECT * FROM tablename;'
133	133		```
134	134
135	135		.
136	136
137	137		Use 'open' to read the table from the server and put the data in the TSQLQuery dataset. The data can be accessed through TSQLQuery until the query is closed using 'close'.
138	138
139	139		TSQLQuery is a subclass of TDataset. TDataset has a 'Fields' collection that contains all columns of the table. The TDataset also keeps track of the current record. Use 'First', 'Next', 'Prior' and 'Last' to change the current record. 'Bof' returns 'True' if the first record is reached, and 'Eof' returns 'True' if the last record is reached. To read the value of a field in the current record, first find the right 'TField' object and then use 'AsString', 'AsInteger', etc.
140	140
141	141		### Example: reading data from a table
142	142
143	143		Below is an example that displays all values of the table as it was made in CreateTable example above. Copy the test_database file into the ShowData working directory. Note, again, no error checking has taken place !
144	144
145	145		``` pascal
146	146		Program ShowData;
147	147		{$mode objfpc} {$ifdef mswindows}{$apptype console}{$endif}
148	148		uses
149	149		DB, Sysutils, sqldb, sqlite3conn;
150	150
151	151		var
152	152		AConnection : TSQLConnection;
153	153		ATransaction : TSQLTransaction;
154	154		Query : TSQLQuery;
155	155
156	156		begin
157	157		AConnection := TSQLite3Connection.Create(nil);
158	158		ATransaction := TSQLTransaction.Create(AConnection);
159	159		AConnection.Transaction := ATransaction;
160	160		AConnection.DatabaseName := 'test_dbase';
161	161		Query := TSQLQuery.Create(nil);
162	162		Query.SQL.Text := 'select * from tblNames';
163	163		Query.Database := AConnection;
164	164		Query.Open;
165	165		while not Query.Eof do
166	166		begin
167	167		Writeln('ID: ', Query.FieldByName('ID').AsInteger, 'Name: ' +
168	168		Query.FieldByName('Name').AsString);
169	169		Query.Next;
170	170		end;
171	171		Query.Close;
172	172		AConnection.Close;
173	173		Query.Free;
174	174		ATransaction.Free;
175	175		AConnection.Free;
176	176		end.
177	177		```
178	178
179	179		(The code above of course is not quite finished, it misses 'try...finally' blocks. However, the above code intends to show the database code and thus the finishing touches are left out.) Please note that 'TSQLTransaction.StartTransaction' is not used. This is not necessary. When TSQLQuery is opened, the SQL statement is executed and if no transaction is available then a transaction is automatically started. The programmer does not need to start the transaction explicitly. The same applies for the connection maintained by TSQLConnection. The connection is opened as needed, the line 'Aconnection.Open' is not really required. If a TSQLTransaction is destroyed, an automatic 'rollback' will be executed. Possible changes to data contained in the transaction will be lost.
180	180
181	181		### Why does TSQLQuery.RecordCount always return 10?
182	182
183	183		To count the records in a dataset, use '.RecordCount'. However, notice that '.RecordCount' shows the number of records that is already loaded from the server. For performance reasons, SqlDB does not read all records when opening TSQLQuery by default, only the first 10. Only when the eleventh record is accessed will the next set of 10 records be loaded, etc. Using '.Last', all records will be loaded.
184	184
185	185		When you want to know the real number of records on the server you can first call '.Last' and then call '.RecordCount'.
186	186
187	187		An alternative is available. The number of records returned by the server is set by the '.PacketRecords' property. The default value is 10; if you make it -1 then all records will be loaded at once.
188	188
189	189		In current stable FPC, '.RecordCount' does not take filters into account, i.e. it shows the unfiltered total.
190	190
191	191		If you need the exact number of records, it often is a better idea to directly query the number of records in a query using another SQL query, but you would have to do that in the same transaction, as other transactions may have changed the number of records in the meanwhile.
192	192
193	193		### Lazarus
194	194
195	195		Lazarus has various components to show data from a TDataset on a form. Instead of a While-loop and Writeln statements as used above, you can use the components to show the data in a table. Place the right TSQLConnection, TSQLTransaction and TSQLQuery components on a form, then connect them and set them properly. In addition you will need a TDatasource; set the 'TDatasource.Dataset' property to the TSQLQuery component you used. (Note do not set the 'TSQLQuery.Datasource' property to the TDatasource compnent you used. The 'TSQLQuery.Datasource' property is used only in master-detail tables - see [[MasterDetail]]) Subsequently you may put a TDBGrid onto the form and set the 'Datasource' property of the grid to the TDatasource component you added before.
196	196
197	197		To see if it all works, set the 'Connected' property of the TSQLConnection to 'True' in the Lazarus IDE. The IDE will try to connect to the database server immediately. If this works you can set the 'TSQLQuery.Active' property to 'True'. If everything is right, you will see - within the IDE - all data from the table immediately on the screen.
198	198
199	199		## How to change data in a table?
200	200
201	201		To change the data in a record (or records), the general process is get TSQLQuery to search for the records you wish to change, make the changes there and then push them back to the database. The [[TDataSet]] (from which TSQLQuery is derived) must be set to edit mode. To enter edit mode call the '.Edit', '.Insert' or '.Append' methods. Use the '.Edit' method to change the current record. Use '.Insert' to insert a new record before the current record. Use '.Append' to insert a new record at the end of the table. In edit mode you can change field values through the 'Fields' property. Use 'Post' to validate the new data, if the data is valid then the edit mode is left. If you move to another record - for example by using '.Next' - and the dataset is in edit mode, then first '.Post' is called. Use '.Cancel' to discard all changes you made since the last '.Post' call and leave the edit mode.
202	202
203	203		``` pascal
204	204		Query.Edit;
205	205		Query.FieldByName('NAME').AsString := 'Edited name';
206	206		Query.Post;
207	207		```
208	208
209	209		The above is not the complete story yet. TSQLQuery is derived from TBufDataset which makes use of buffered updates. Buffered update means that after you called 'Post' the changes in the dataset are visible immediately, but they are not sent to the database server. What does happen is that the changes are maintained in a change log. When the '.ApplyUpdates' method is called, then all changes in the change log are sent to the database. Only then will database server know of the changes. The changes are sent to the server within a transaction of TSQLTransaction. Make sure to properly set the transaction before 'ApplyUpdates'. After applying the updates, a commit must be executed to save the changes on the database server.
210	210
211	211		The below is an example of changing the data in a table, sending the changes to the server and committing the transaction. Again, no error checking, again, thats bad!
212	212
213	213		``` pascal
214	214		Program EditData;
215	215		{$mode objfpc} {$ifdef mswindows}{$apptype console}{$endif}
216	216		uses
217	217		db, sqldb, sqlite3conn;
218	218		var
219	219		AConnection : TSQLConnection;
220	220		ATransaction : TSQLTransaction;
221	221		Query : TSQLQuery;
222	222
223	223		begin
224	224		AConnection := TSQLite3Connection.Create(nil);
225	225		ATransaction := TSQLTransaction.Create(AConnection);
226	226		AConnection.Transaction := ATransaction;
227	227		AConnection.DatabaseName := 'test_dbase';
228	228		Query := TSQLQuery.Create(nil);
229	229		Query.DataBase := AConnection;
230	230		Query.SQL.Text := 'select * from tblNames where ID = 2';
231	231		Query.Open;
232	232		Query.Edit;
233	233		Query.FieldByName('NAME').AsString := 'Name Number 2';
234	234		Query.Post;
235	235		Query.UpdateMode := upWhereAll; // defined in db
236	236		Query.ApplyUpdates;
237	237		ATransaction.Commit;
238	238		Query.Free;
239	239		ATransaction.Free;
240	240		AConnection.Free;
241	241		end.
242	242		```
243	243
244	244		The actual works starts with the SQL statement "select * from tblNames where ID = 2" identifying the record (or records) you wish to change. If you leave out the "where ID = 2" bit, the TSQLQuery apparently sets ID (and other integer fields?) to 1. And therefore will operate on lines where ID=1 only. For a discussion of 'UpdateMode' continue reading.
245	245
246	246		## How does SqlDB send the changes to the database server?
247	247
248	248		In the code example in #How to change data in a table?, you will find the line
249	249
250	250		``` pascal
251	251		Query.UpdateMode := upWhereAll;
252	252		```
253	253
254	254		without explanation of what it does. The best way to find out what that line does is to leave it out. If you leave out the statement and the followed this howto precisely, then you will receive the following error message:
255	255
256		-	~~`No update query specified and failed to generate one. (No fields for inclusion in where statement found)`~~
	256	+	```text
	257	+	No update query specified and failed to generate one. (No fields for inclusion in where statement found)
	258	+	```
257	259
258	260		To understand what went wrong, you must understand how changes are sent to the database server. The only way to get data in a SQL server is by executing SQL queries. SQL has three types of queries for three different ways of manupulating a record. To create a new record, change or delete a record insert, update and delete statements are executed respectively. An update statement may be as follows:
259	261
260	262		``` sql
261	263		update TBLNAMES set NAME='Edited name' where ID=1;
262	264		```
263	265
264	266		To send a change to the database server, Sqldb must assemble an update query. To assemble the query, three things are needed:
265	267
266	268		The name of the table
267	269		The table name is retrieved from parsing the select query, although this doesn't always work.
268	270
269	271		`UPDATE` or `INSERT` clause
270	272		These contain the fields that must be changed.
271	273
272	274		`WHERE` clause
273	275		This contains the fields that determine which records should be changed.
274	276
275	277		Every field (each TField in Fields) has a ProviderFlags property. Only fields with pfInUpdate in ProviderFlags will be used in the update or insert cluase of a query. By default all fields have pfInUpdate set in their ProviderFlags property.
276	278
277	279		Which fields are used in the `WHERE` clause depends on the UpdateMode property of the query and the ProviderFlags property of the fields. Fields with pfInkey in their ProviderFlags are always used in the `WHERE` clause. A field will have the pfInKey flag set automatically if the field is part of the primary key of the table and 'TSQLQuery.UsePrimaryKeyAsKey' returns 'True'.
278	280
279	281		The default value for UpdateMode of the query is upWhereKeyOnly. In this update mode only fields with pfInkey in their ProviderFlags property are used in the `WHERE` clause. If none of the fields have their pfInKey flag set, then no fields are available for the `WHERE` clause and the error message from the beginning of this section will be returned. You can solve the issue by:
280	282
281	283		- Adding a primary key to the table and set TSQLQuery.UsePrimaryKeyAsKey to 'True', or
282	284		- Setting the pfInkey flag for one or more fields in code.
283	285
284	286		The UpdateMode property knows two more possible values. 'upWhereAll' can be used to add all fields with the 'pfInWhere' flag set to the `WHERE` clause. By default all fields have this flag set. 'upWhereChanged' can be used to add only those fields that have the 'pfInWhere' flag set and that are changed in the current record.
285	287
286	288		## How to handle Errors
287	289
288	290		Run time errors are unavoidable, disks may fill up, necessary libraries or helper apps may not be available, things go wrong and we need to allow for that. The FPC detects and handles run time errors quite well. It usually gives you a concise and reasonable explanation of what went wrong. However, you will want to monitor and handle errors yourself for a number of reasons -
289	291
290	292		- You probably don't want the programme to teminate at the first sign of trouble.
291	293		- If we do keep going, lets make sure any memory allocated in the problem area is recovered, we don't want any memory leaks.
292	294		- If we are going to go under however, lets give the user a context sensitive explanation.
293	295
294	296		The following bit of code is based on the above examples but this time it DOES check for errors in critical places. Key is the try...finally...end and try...except...end blocks. You can test it by doing things like uninstalling SQLite3, putting a dummy file in place of the test_dbase database and so on.
295	297
296	298		``` pascal
297	299		program DemoDBaseWithErrors;
298	300		{$mode objfpc} {$ifdef mswindows}{$apptype console}{$endif}
299	301		uses
300	302		DB, Sysutils, sqldb, sqlite3conn;
301	303		var
302	304		Connect : TSQLite3Connection;
303	305		Trans : TSQLTransaction;
304	306
305	307		procedure WriteTable (Command : string);
306	308		begin
307	309		Connect.ExecuteDirect(Command);
308	310		Trans.Commit;
309	311		end;
310	312
311	313		procedure ReadTable ();
312	314		var
313	315		Query : TSQLQuery;
314	316		Count : smallint;
315	317		begin
316	318		Count := 0;
317	319		try
318	320		Query := TSQLQuery.Create(nil);
319	321		Query.DataBase := Connect;
320	322		Query.SQL.Text:= 'select * from tblNames';
321	323		Query.Open; // This will also open Connect
322	324		while not Query.EOF do begin
323	325		writeln('ID: ', Query.FieldByName('ID').AsInteger, ' Name: ' +
324	326		Query.FieldByName('Name').AsString);
325	327		Query.Next;
326	328		Count := Count + 1;
327	329		end;
328	330		finally
329	331		Query.Close;
330	332		Query.Free;
331	333		end;
332	334		writeln('Found a total of ' + InttoStr(Count) + ' lines.');
333	335		end;
334	336
335	337		procedure FatalError(ClassName, Message, Suggestion : string);
336	338		begin
337	339		writeln(ClassName);
338	340		writeln(Message);
339	341		writeln(Suggestion);
340	342		Connect.Close; // Its possibly silly worrying about freeing
341	343		Trans.free; // if we are going to call halt() but its
342	344		Connect.Free; // a demo, alright ?
343	345		halt();
344	346		end;
345	347
346	348		begin
347	349		Connect := TSQLite3Connection.Create(nil);
348	350		Trans := TSQLTransaction.Create(Connect);
349	351		Connect.Transaction := Trans;
350	352		Connect.DatabaseName := 'test_dbase';
351	353		try
352	354		if not fileexists(Connect.DatabaseName) then begin
353	355		Connect.Open; // give EInOutError if (eg) SQLite not installed
354	356		Trans.StartTransaction;
355	357		WriteTable('create table TBLNAMES (ID integer Primary Key, NAME varchar(40));');
356	358		Trans.Commit;
357	359		end;
358	360		Connect.open;
359	361		Trans.StartTransaction;
360	362		WriteTable('insert into TBLNAMES (NAME) values (''AName1'');');
361	363		WriteTable('insert into TBLNAMES (NAME) values (''AName2'');');
362	364		except
363	365		on E : EDatabaseError do
364	366		FatalError(E.ClassName, E.Message, 'Does the file contain the correct database ?');
365	367		on E : EInOutError do
366	368		FatalError(E.ClassName, E.Message, 'Have you installed SQLite (and dev package)?');
367	369		on E : Exception do
368	370		FatalError(E.ClassName, E.Message, 'Something really really bad happened.');
369	371		end;
370	372		ReadTable();
371	373		Connect.Close;
372	374		Trans.Free;
373	375		Connect.Free;
374	376		end.
375	377		```
376	378
377	379		## How to execute a query using TSQLQuery?
378	380
379	381		Next to statements that return a dataset (see #How to read data from a table?) SQL has statements that do not return data. For example `INSERT`, `UPDATE` and `DELETE` statements do not return data. These statements can be executed using TSQLConnection.ExecuteDirect, but TSQLQuery can also be used. If you do not expect return data use TSQLQuery.ExecSQL instead of TSQLQuery.Open. As mentioned earlier, use TSQLQuery.Open to open the dataset returned by the SQL statement.
380	382
381	383		The following procedure creates a table and inserts two records using TSQLQuery.
382	384
383	385		``` pascal
384	386		procedure CreateTable;
385	387
386	388		var
387	389		Query : TSQLQuery;
388	390
389	391		begin
390	392		Query := TSQLQuery.Create(nil);
391	393		try
392	394		Query.Database := AConnection;
393	395
394	396		Query.SQL.Text := 'create table TBLNAMES (ID integer, NAME varchar(40));';
395	397		Query.ExecSQL;
396	398
397	399		Query.SQL.Text := 'insert into TBLNAMES (ID,NAME) values (1,''Name1'');';
398	400		Query.ExecSQL;
399	401
400	402		Query.SQL.Text := 'insert into TBLNAMES (ID,NAME) values (2,''Name2'');';
401	403		Query.ExecSQL;
402	404		finally
403	405		Query.Free;
404	406		end;
405	407		end;
406	408		```
407	409
408	410		## How to use parameters in a query?
409	411
410	412		In the code example of #How to execute a query using TSQLQuery? the same query is used twice, only the values to be inserted differ. A better way to do this is by using parameters in the query.
411	413
412	414		The syntax of parameters in queries is different per database system, but the differences are handled by TSQLQuery. Replace the values in the query with a colon followed by the name of the parameter you want to use. For example:
413	415
414	416		``` pascal
415	417		Query.SQL.Text := 'insert into TBLNAMES (ID,NAME) values (:ID,:NAME);';
416	418		```
417	419
418	420		This query will create two parameters: 'ID' and 'NAME'. To determine the parameters, the query is parsed at the moment the text of TSQLQuery.SQL is assigned or changed. All existing parameters will be removed and the new parameters will be added to the 'TSQLQuery.Params' property. Assigning a value to a parameter is similar to assigning a value to a field in the dataset:
419	421
420	422		``` pascal
421	423		Query.Params.ParamByName('Name').AsString := 'Name1';
422	424		```
423	425
424	426		You can't tell from the query what kind of data must be stored in the parameter. The data type of the parameter is determined at the moment a value is first assigned to the parameter. By assigning a value using '.AsString', the parameter is assigned the data type 'ftString'. You can determine the data type directly by setting the 'DataType' property. If an incorrect datatype is assigned to the parameter, then problems will occur during opening or executing the query. See [[Database field type]] for more information on data types.
425	427
426	428		### Select query
427	429
428	430		An example of a select query with parameters would be to change something like this:
429	431
430	432		``` pascal
431	433		Query.SQL.Text := 'select ID,NAME from TBLNAMES where NAME = '''+Edit1.Text+''' ORDER BY NAME ';
432	434		```
433	435
434	436		to something like this:
435	437
436	438		``` pascal
437	439		Query.SQL.Text := 'select ID,NAME from TBLNAMES where NAME = :NAMEPARAM ORDER BY NAME ';
438	440		Query.Params.ParamByName('NAMEPARAM').AsString := Edit1.Text;
439	441		```
440	442
441	443		### Example
442	444
443	445		The following example creates the same table as the previous example, but now parameters are used:
444	446
445	447		``` pascal
446	448		procedure CreateTableUsingParameters;
447	449
448	450		var
449	451		Query : TSQLQuery;
450	452
451	453		begin
452	454		Query := TSQLQuery.Create(nil);
453	455		try
454	456		Query.Database := AConnection;
455	457
456	458		Query.SQL.Text := 'create table TBLNAMES (ID integer, NAME varchar(40));';
457	459		Query.ExecSQL;
458	460
459	461		Query.SQL.Text := 'insert into TBLNAMES (ID,NAME) values (:ID,:NAME);';
460	462		Query.Prepare;
461	463
462	464		Query.Params.ParamByName('ID').AsInteger := 1;
463	465		Query.Params.ParamByName('NAME').AsString := 'Name1';
464	466		Query.ExecSQL;
465	467
466	468		Query.Params.ParamByName('ID').AsInteger := 2;
467	469		Query.Params.ParamByName('NAME').AsString := 'Name2';
468	470		Query.ExecSQL;
469	471
470	472		//Query.UnPrepare; // no need to call this; should be called by Query.Close
471	473		Query.Close;
472	474		finally
473	475		Query.Free;
474	476		end;
475	477		end;
476	478		```
477	479
478	480		Notice that this example requires more code than the example without the parameters. Then what is the use of using parameters?
479	481
480	482		Speed is one of the reasons. The example with parameters is faster, because the database server parses the query only once (in the .Prepare statement or at first run).
481	483
482	484		Another reason to use prepared statements is prevention of [SQL-injection](http://en.wikipedia.org/wiki/SQL_injection) (see also [[Secure programming]].
483	485
484	486		Finally, in some cases it just simplifies coding.
485	487
486	488		## Troubleshooting: TSQLConnection logging
487	489
488	490		You can let a TSQLConnection log what it is doing. This can be handy to see what your Lazarus program sends to the database exactly, to debug the database components themselves and perhaps to optimize your queries. NB: if you use prepared statements/parametrized queries (see section above), the parameters are often sent in binary by the TSQLConnection descendent (e.g. TIBConnection), so you can't just copy/paste the logged SQL into a database query tool. Regardless, connection logging can give a lot of insight in what your program is doing.
489	491
490	492		Alternatives are:
491	493
492	494		1. you can use the debugger to step through the database code if you have built FPC (and Lazarus) with debugging enabled.
493	495		2. if you use ODBC drivers (at least on Windows) you could enable tracelog output in the ODBC control panel.
494	496		3. many databases allow you to monitor all statements sent to it from a certain IP address/connection.
495	497
496	498		If you use TSQLConnection logging, two things are required:
497	499
498	500		1. indicate which event types your TSQLConnection should log
499	501		2. point TSQLConnection at a function that receives the events and processes them (logs them to file, prints them to screen, etc.).
500	502
501	503		That function must be of type TDBLogNotifyEvent (see sqldb.pp), so it needs this signature:
502	504
503	505		``` pascal
504	506		TDBLogNotifyEvent = Procedure (Sender : TSQLConnection; EventType : TDBEventType; Const Msg : String) of object;
505	507		```
506	508
507	509		### FPC (or: the manual way)
508	510
509	511		A code snippet can illustrate this:
510	512
511	513		``` pascal
512	514		uses
513	515		...
514	516		TSQLConnection, //or a child object like TIBConnection, TMSSQLConnection
515	517		...
516	518		var
517	519		type
518	520		TMyApplication = class(TCustomApplication); //this is our application that uses the connection
519	521		...
520	522		private
521	523		// This example stores the logged events in this stringlist:
522	524		FConnectionLog: TStringList;
523	525		...
524	526		protected
525	527		// This procedure will receive the events that are logged by the connection:
526	528		procedure GetLogEvent(Sender: TSQLConnection; EventType: TDBEventType; Const Msg : String);
527	529		...
528	530		procedure TMyApplication.GetLogEvent(Sender: TSQLConnection;
529	531		EventType: TDBEventType; const Msg: String);
530	532		// The procedure is called by TSQLConnection and saves the received log messages
531	533		// in the FConnectionLog stringlist
532	534		var
533	535		Source: string;
534	536		begin
535	537		// Nicely right aligned...
536	538		case EventType of
537	539		detCustom: Source:='Custom: ';
538	540		detPrepare: Source:='Prepare: ';
539	541		detExecute: Source:='Execute: ';
540	542		detFetch: Source:='Fetch: ';
541	543		detCommit: Source:='Commit: ';
542	544		detRollBack: Source:='Rollback:';
543	545		else Source:='Unknown event. Please fix program code.';
544	546		end;
545	547		FConnectionLog.Add(Source + ' ' + Msg);
546	548		end;
547	549
548	550		...
549	551		// We do need to tell our TSQLConnection what to log:
550	552		FConnection.LogEvents:=LogAllEvents; //= [detCustom, detPrepare, detExecute, detFetch, detCommit, detRollBack]
551	553		// ... and to which procedure the connection should send the events:
552	554		FConnection.OnLog:=@Self.GetLogEvent;
553	555		...
554	556		// now we can use the connection and the FConnectionLog stringlist will fill with log messages.
555	557		```
556	558
557	559		You can also use TSQLConnection's GlobalDBLogHook instead to log everything from multiple connections.
558	560
559	561		### Lazarus (or: the quick way)
560	562
561	563		Finally, the description above is the FPC way of doing things as indicated in the introduction; if using Lazarus, a quicker way is to assign an event handler to the TSQLConnection's OnLog event.
562	564
563	565		## See also
564	566
565	567		- [[Working With TSQLQuery]]
566	568
567	569		---
568	570		From the Free Pascal wiki, page "SqlDBHowto", as it stood on 2020-12-10 after a change by Wp, 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