MySQLDatabases: what changed

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

Old	New		Line
1	1		## Introduction
2	2
3	3		This page will explain how to connect to a [[mysql\|MySQL]] server using visual components.
4	4
5	5		> Note: This page has been written a long time ago and may be out of date. Also, most of the concepts described here are not MySQL specific but apply to all SQLDB databases. Therefore, following the SQLdb Tutorial series mentioned below may be easier
6	6
7	7		Note: see also these tutorials that teach data-bound controls, parameterized queries, database independent programming etc:
8	8
9	9		- [[SQLdb Tutorial1]]
10	10		- [[SQLdb Tutorial2]]
11	11		- [[SQLdb Tutorial3]]
12	12
13	13		They are written for all databases that support sqldb, including MySQL.
14	14
15	15		If needed, see also [[mysql\|mysql#SQLDB_tutorials_and_example_code]] for yet more tutorials (that are also written a long time ago.
16	16
17	17		## Available Components
18	18
19	19		### SQLdb Components
20	20
21	21		In any even vaguely recent version of Lazarus, the SQLDB components are installed by default.
22	22
23	23		![](/wiki-images/c/c5/Sqldbcomponents.png%E2%80%8E)
24	24
25	25		On the SQLDB tab you will find:
26	26
27	27		- Various connectors, including [[TMySQL40Connection]]..[[TMySQL56Connection]] (or perhaps even newer versions) and the most versatile of all [[TSQLConnector]] that may load any of the mysql/oracle/postgres/mssql/interbase/firebird/odbc drivers.
28	28		- [[TSQLQuery]]
29	29
30	30		If the SQLDB tab is missing, have a look at [[Install Packages]] for an "Install Howto".
31	31
32	32		## Explanation of the used components
33	33
34	34		### TMySQLConnection
35	35
36	36		The TMySQLConnection is used to store parameters to connect to the database server. It enables you to set the host to connect to, and the userid and password to use in the connection. Another property of the TMySQLConnection is used to indicate the database you want to use. The 'LoginPrompt' is not functional yet, so make sure that next to the HostName and DatabaseName the UserName and Password properties have values as well before you try to open the connection. Be sure to use a TSQLTransaction as well and connect it to your MySQLConnection by setting the Transaction property, or you will not be able to open a SQLQuery.
37	37
38	38		> Note: As indicated above, there are various MySQLConnection components that differ in version number. The version number must match the version number of the client library you use to connect to your server. So if you are running a MySQL 5.1 server but use a MySQL 5.0 client, use the TMySQL50Connection.
39	39
40	40		You should check MySQL documentation to make sure the combination between client version and server version is supported - e.g. you may well have problems connecting to a 4.0 server using a 5.x client.
41	41
42	42		In all cases put a copy of libmysql.dll and any other required files/dlls
43	43
44	44		- in your Lazarus directory and the same directory as your project files or
45	45		- in the Windows system directory (if you don't want to keep copying files). Note that on 64 bit Windows you have to put the 32 bit library in SysWOW64, while 64 bit libraries go into System32.
46	46
47	47		### TSQLTransaction
48	48
49	49		A [[TSQLTransaction]] is needed for some internal housekeeping. A SQLTransaction is automatically activated when you open a dataset using it. Closing a connection also deactivates the related transaction and closes all datasets using it.
50	50
51	51		### TSQLQuery
52	52
53	53		[[TSQLQuery]] is used to execute SQLstatements on the server. You can retrieve data by setting the SQL to some SELECT statement and call the Open method. Or you can manipulate data by issuing some an INSERT, DELETE or UPDATE statement. In the latter case you should not use the Open method but the ExecSQL method.
54	54
55	55		### TDataSource
56	56
57	57		A [[TDataSource]] provides the connection between the visible data aware components like DBEdit, DBGrid and a dataset. It makes the data available for the data aware components to display. A datasource can only be connected to a single dataset at a time but there can be several data aware components connected.
58	58
59	59		### TDBGrid
60	60
61	61		A [[TDBGrid]] can be used to present the data retrieved by a Dataset. The DBGrid needs a datasource to connect to a dataset. When the dataset is opened, the DBgrid will automatically be populated with the data.
62	62
63	63		## Our program
64	64
65	65		### The basics
66	66
67	67		We will try to make a program based on the one made [[Lazarus Database Tutorial/nl\|here (in Dutch)]] which is based on the [[Lazarus Database Tutorial\|original (in English)]] by Chris.
68	68
69	69		### The main form
70	70
71	71		We will use the same main screen and build all functionality from scratch :) As you will see there is a lot less to take care of, because the components really take away all the hard stuff! So lets start by making a screen that looks like this.
72	72
73		-	~~![`Trymysql.png`](/wiki-images/2/28/Trymysql.png)~~
	73	+	```text
	74	+	image:Trymysql.png
	75	+	```
74	76
75	77		From the SQLdb-tab place a [[TMySQL56Connection]] ![](/wiki-images/9/97/Tmysql56connection.png) (or other mysql-client version) a [[TSQLTransaction]] ![](/wiki-images/b/bd/Tsqltransaction.png) and a [[TSQLQuery]] ![](/wiki-images/b/bb/Tsqlquery.png) on this form. Don't change the default names given to this components. Except for the connection component. To make this article the same for all versions of MySQL, name your TMySQL##Connection component: `MySQLConnection1`. We have to link these components together so they can do their job. So the following properties have to be set:
76	78
77	79		\| \| \| \|
78	80		\|------------------\|--------------\|------------------\|
79	81		\| Component \| Property \| Value \|
80	82		\| MySQLConnection1 \| Transaction \| SQLTransaction1 \|
81	83		\| SQLTransaction1 \| Database \| MySQLConnection1 \|
82	84		\| SQLQuery1 \| Transaction \| SQLTransaction1 \|
83	85		\| SQLQuery1 \| Database \| MySQLConnection1 \|
84	86
85	87		The Transaction-property of SQLQuery1 will automatically be set if you have set the Transaction property of MySQLConnection1 first. When you set this, you will notice that SQLTransaction1.Database has been set to MySQLConnection1.
86	88
87	89		As said earlier: Make sure you are using the correct Connection component for your version of MySQL server.
88	90
89	91		### The code
90	92
91	93		As you can see in the screen dump the only buttons available on start of the program are "Connect to server" and "Exit". For the other buttons to work we need more information so these are disabled. We could decide to disable "Connect to Server" as well until the information for the host, username and password has been given. I decided against this because our user might think: "Nothing seems possible, so let's hit exit." :)
92	94
93	95		Before I start giving you any code I would like to stress that there should be more exception handling in the code. Critical sections should be placed in
94	96
95		-	~~`try ... finally`~~
	97	+	```text
	98	+	try ... finally
	99	+	```
96	100
97	101		or
98	102
99		-	~~`try ... except`~~
	103	+	```text
	104	+	try ... except
	105	+	```
100	106
101	107		constructions.
102	108
103	109		#### Connect to a server
104	110
105	111		The first thing we have to do is get connected to our server. As when connecting we don't know what databases are available on the server we will ask for a list of databases on connecting. However there is one catch, to make the connection we have to enter a valid DatabaseName in the properties of the MySQLConnection. You will see in the code that I am using the "mysql" database. This database is used by mysql for some housekeeping so it will always be there.
106	112
107	113		``` pascal
108	114		procedure TFormTryMySQL.ConnectButtonClick(Sender: TObject);
109	115		begin
110	116		// Check if we have an active connection. If so, let's close it.
111	117		if MySQLConnection1.Connected then CloseConnection(Sender);
112	118		// Set the connection parameters.
113	119		MySQLConnection1.HostName := HostEdit.Text;
114	120		MySQLConnection1.UserName := UserEdit.Text;
115	121		MySQLConnection1.Password := PasswdEdit.Text;
116	122		MySQLConnection1.DatabaseName := 'mysql'; // MySQL is allways there!
117	123		ShowString('Opening a connection to server: ' + HostEdit.Text);
118	124		MySQLConnection1.Open;
119	125		// First lets get a list of available databases.
120	126		if MySQLConnection1.Connected then begin
121	127		ShowString('Connected to server: ' + HostEdit.Text);
122	128		ShowString('Retrieving list of available databases.');
123	129		SQLQuery1.SQL.Text := 'show databases';
124	130		SQLQuery1.Open;
125	131		while not SQLQuery1.EOF do begin
126	132		DatabaseComboBox.Items.Add(SQLQuery1.Fields[0].AsString);
127	133		SQLQuery1.Next;
128	134		end;
129	135		SQLQuery1.Close;
130	136		ShowString('List of databases received!');
131	137		end;
132	138		end;
133	139		```
134	140
135	141		The first thing we do is check to see if we are connected to a server, if we are then we call a private method "CloseConnection". In this method some more housekeeping is done. like disabling buttons and clearing comboboxes and listboxes. Then we set the necessary parameters to connect to server.
136	142
137	143
138	144		Throughout our program you may see calls to ShowString. This method adds a line to the memo on our form which acts like a kind of log.
139	145
140	146		With the parameters set, we can connect to the server. This is done by calling
141	147
142	148		``` pascal
143	149		MySQLConnection1.Open;
144	150		```
145	151
146	152		In a proper application one would place this in an exception handling construct to present a friendly message to the user if the connection failed. When we are connected we want to get a list of databases from the server. To get data from the server a TSQLQuery is used. The SQL property is used to store the SQL-statement send to the server. MySQL knows the "SHOW DATABASES" command to get the list of databases. So after we have set the SQL-text, we call
147	153
148	154		``` pascal
149	155		SQLQuery1.Open;
150	156		```
151	157
152	158		On MySQL5 set this to correct error with SQL syntax:
153	159
154	160		``` pascal
155	161		SQLQuery1.ParseSQL := False;
156	162		SQLQuery1.ReadOnly := True;
157	163		```
158	164
159	165		The result set of a SQLQuery can be examined through the fields property. As you can see we iterate through the records by calling
160	166
161	167		``` pascal
162	168		SQLQuery1.Next;
163	169		```
164	170
165	171		When we have added all available databases to our combobox, we close the SQLQuery again.
166	172
167	173		#### Selecting a database
168	174
169	175		If the user selects a database in the DatabaseComboBox we enable the "Select Database" button. In the OnClick event of this button we set the DatabaseName of MySQLConnection1, and request a list of tables. The last statement of this procedure enables the "Open Query" Button, so the user can enter a query in the "Command" Editbox and have it send to the server.
170	176
171	177		``` pascal
172	178		procedure TFormTryMySQL.SelectDBButtonClick(Sender: TObject);
173	179		begin
174	180		// A database has been selected so lets get the tables in it.
175	181		CloseConnection(Sender);
176	182		if DatabaseComboBox.ItemIndex <> -1 then begin
177	183		with DatabaseComboBox do
178	184		MySQLConnection1.DatabaseName := Items[ItemIndex];
179	185		ShowString('Retreiving list of tables');
180	186		SQLQuery1.SQL.Text := 'show tables';
181	187		SQLQuery1.Open;
182	188		while not SQLQuery1.EOF do begin
183	189		TableComboBox.Items.Add(SQLQuery1.Fields[0].AsString);
184	190		SQLQuery1.Next;
185	191		end;
186	192		SQLQuery1.Close;
187	193		ShowString('List of tables received');
188	194		end;
189	195		OpenQueryButton.Enabled := True;
190	196		end;
191	197		```
192	198
193	199		MySQL has a special command to get a list of tables, comparable to getting the list of databases, "show tables". The result of this query is handled in the same way as the list of databases and all the tables are added to the TableComboBox. You might wonder why we do not open the connection again before opening the query? Well, this is done automatically (if necessary) when we activate the SQLQuery.
194	200
195	201		#### Fields in a table
196	202
197	203		In MySQL you can again use a form of "SHOW" to get the fields in a table. In this case "SHOW COLUMNS FROM \<tablename\>". If the user picks a table from the TableComboBox the OnChangeEvent of this ComboBox is triggered which fills the FieldListbox.
198	204
199	205		``` pascal
200	206		procedure TFormTryMySQL.TableComboBoxChange(Sender: TObject);
201	207		begin
202	208		FieldListBox.Clear;
203	209		SQLQuery1.SQL.Text := 'show columns from ' + TableComboBox.Text;
204	210		SQLQuery1.Open;
205	211		while not SQLQuery1.EOF do begin
206	212		FieldListBox.Items.Add(SQLQuery1.Fields[0].AsString);
207	213		SQLQuery1.Next;
208	214		end;
209	215		SQLQuery1.Close;
210	216		end;
211	217		```
212	218
213	219		As well as the names of the fields, the result set contains information on the type of field, if the field is a key, if nulls are allowed and some more.
214	220
215	221		#### Showing the data
216	222
217	223		Well as we said we would use components to get connected to the database, lets use some components to show the data as well. We will use a second form to show a grid with the data requested by the user. This form will be shown when the user typed a SQL command in the "Command" editbox and afterwards clicks the "Open Query" button. This is the OnClick event:
218	224
219	225		``` pascal
220	226		procedure TFormTryMySQL.OpenQueryButtonClick(Sender: TObject);
221	227		begin
222	228		ShowQueryForm := TShowQueryForm.Create(self);
223	229		ShowQueryForm.Datasource1.DataSet := SQLQuery1;
224	230		SQLQuery1.SQL.Text := CommandEdit.Text;
225	231		SQLQuery1.Open;
226	232		ShowQueryForm.ShowModal;
227	233		ShowQueryForm.Free;
228	234		SQLQuery1.Close;
229	235		end;
230	236		```
231	237
232	238		The ShowQueryForm looks like this:
233	239
234	240		![](/wiki-images/4/47/Mysqlshow.png)
235	241
236	242		and contains a
237	243
238	244		\| \| \| \|
239	245		\|----\|----\|----\|
240	246		\| [[TPanel]] \| Align \| alBottom \|
241	247		\| [[TDataSource]] \| \| \|
242	248		\| [[TDBGrid]] \| Align \| alClient \|
243	249		\| \| DataSource \| DataSource1 \|
244	250		\| [[TButton]] \| Caption \| Close \|
245	251
246	252		The button is placed on the panel. What happens in the "Open Query" OnClick is this. First we create an instance of TShowQueryForm. Secondly we set the DataSet property of the DataSource to our SQLQuery1. Then we set the SQLQuery SQL command to what the user entered in the "Command" editbox and open it. Then the ShowQueryForm is shown modally, this means that it will have the focus of our application until it is closed. When it is closed, we "free" it and close SQLQuery1 again.
247	253
248	254		The Form can be further enhanced by inserting a method for modifying the content of the [[TDataSet]] and ultimately the DataBase. A full version can be downloaded from <http://digitus.itk.ppke.hu/~janma/lazarus/MySql5Test.tar.gz> (with thanks to Arwen and JZombi from the Lazarus MySQL Forum) but the relevant details are as follows:
249	255
250	256		Add a Button at the bottom of the ShowQuery Form named AddButton and with Caption 'Add'. Create a method for AddButtonClick like this:
251	257
252	258		``` pascal
253	259		procedure TShowQueryForm.AddButtonClick(Sender: TObject);
254	260		begin
255	261		DataSource1.DataSet.Append;
256	262		end;
257	263		```
258	264
259	265		Change the code for OpenQueryButtonClick to allow for updates to the database when we finish with the Query Form.
260	266
261	267		``` pascal
262	268		procedure TFormTryMySQL.OpenQueryButtonClick(Sender: TObject);
263	269		begin
264	270		ShowQueryForm := TShowQueryForm.Create(nil);
265	271		try
266	272		ShowQueryForm.DataSource1.DataSet := SQLQuery1;
267	273		// We will write in the database, so let's set ReadOnly to false,
268	274		// and for that we need to set ParseSQL true
269	275		SQLQuery1.ParseSQL:=true;
270	276		SQLQuery1.ReadOnly:=false;
271	277		SQLQuery1.SQL.Text := CommandEdit.Text;
272	278		SQLQuery1.Open;
273	279		ShowQueryForm.ShowModal;
274	280		finally
275	281		// set up update mode, and update database
276	282		SQLQuery1.UpdateMode:=upWhereChanged;
277	283		SQLQuery1.ApplyUpdates;
278	284		SQLTransaction1.Commit;
279	285		ShowQueryForm.Free;
280	286		SQLQuery1.Close;
281	287		// set read-only and parsesql back to default
282	288		SQLQuery1.ParseSQL:=false;
283	289		SQLQuery1.ReadOnly:=true;
284	290		end;
285	291		end;
286	292		```
287	293
288	294		We can now add records to the Database. If we add a TDBNavigator to the ShowQuery Form we can move around the Data Grid more easily, and edit records; the database gets updated with our changes each time we close the ShowQuery Form, and we can test this by opening it up again to inspect the database.
289	295
290	296		If you want to be able to DELETE or otherwise modify records on the original database (ie make sure changes you make to the local dataset get committed back to the database) then you need to have one column in your database table that is a primary key autoincremented, as the 'delete' method requires to be able to generate a 'where' clause when writing instructions back to the database, to identify the records selected for deletion. So use the following code in your MySQL client (it can all be typed on one line, but line breaks have been added for clarity):
291	297
292	298		``` sql
293	299		ALTER TABLE TRESTRIG
294	300		ADD COLUMN AUTOID INT
295	301		PRIMARY KEY AUTO_INCREMENT;
296	302		```
297	303
298	304		and then you will find that the Delete button on the navigator works.
299	305
300	306		## Sources
301	307
302	308		The sources for this project can be downloaded [here](http://prdownloads.sourceforge.net/lazarus-ccr/mysql_demo_20050408.tar.gz?download) For more demo projects see [sourceforge](http://sourceforge.net/project/showfiles.php?group_id=92177&package_id=148359)
303	309
304	310		## See also
305	311
306	312		- [[mysql]]
307	313
308	314		---
309	315		From the Free Pascal wiki, page "MySQLDatabases", as it stood on 2020-02-20 after a change by Trev, out of the wiki's dump of 2024-05-26. Copyright stays with the author who wrote it, according to the old wiki's [[Licensing]] page. If you have concerns about this material, [message the admin](/wiki-contact).

Back to history