Difference between revisions of "MySQLDatabases"

From Free Pascal wiki
(SQLdb Components)
m (Fixed syntax highlighting; removed categories included in template)
 
(46 intermediate revisions by 15 users not shown)
Line 1: Line 1:
 
{{MySQLDatabases}}
 
{{MySQLDatabases}}
  
'''Work in progress'''
+
{{Infobox databases}}
  
 
==Introduction==
 
==Introduction==
  
In the [[Lazarus Database Tutorial#Lazarus and MySQL|Database tutorial]] we saw a first attempt to connect to a MySQL server. We did not use any components, either visual or non-visual at that time. This page will explain how to connect to a MySQL server in a (perhaps) easier way.
+
This page will explain how to connect to a [[mysql|MySQL]] server using visual components.
==Available Components ==
 
  
===MySQL Components===
+
{{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}}
If you look in the $Lazarus/components/mysql directory you will find two lazarus packages. There is a package for MySQL version 3.2x (mysql3laz.lpk) and one for MySQL version 4.x (mysql4laz.lpk). If you did not do so yet, install the appropriate package, depending on the server version. Remember that it is not possible to use the package for version 3 to connect to a server running version 4 or vice versa.  
 
When you've installed this package you will see a new tab in the component palet, called MySQL. On this tab there are two components, TMySQLDatabase and TMySQLDataset.
 
  
NB. Before trying to install this components in Lazarus be sure that you have appropiated MySQL client libraries or else Lazarus may not start after install.
+
Note: see also these tutorials that teach data-bound controls, parameterized queries, database independent programming etc:
===SQLdb Components===
+
* [[SQLdb Tutorial1]]
Another possibility is the installation of the package in the $Lazarus/components/sqldb directory. In this directory you see a package file called sqldblaz.lpk. You need to install this package and the mysql4connlaz.lpk form the $Lazarus/components/sqldb/mysql directory. The first package contains some general components used for all databases. These component are TSQLTransaction and TSQLQuery and can be found on the new SQLdb tab. After installation of the mysql4connlaz.lpk you will find a third component on the SQLdb tab called TMySQLConnection (depicted by a dolphin).
+
* [[SQLdb Tutorial2]]
 +
* [[SQLdb Tutorial3]]
 +
They are written for all databases that support sqldb, including MySQL.
  
If you do not know how to install components / packages, have a look at this [[Install Packages|page]] for an "Install Howto".
+
If needed, see also [[mysql#SQLDB_tutorials_and_example_code]] for yet more tutorials (that are also written a long time ago.
As the SQLdb components are the more general and can be used for other databases just by replacing the TMySQLConnection with for instance a TIBConnection, we will develop a program with the SQLdb components.
+
 
 +
== Available Components ==
 +
 
 +
=== SQLdb Components ===
 +
 
 +
In any even vaguely recent version of Lazarus, the SQLDB components are installed by default.
 +
 
 +
[[File:sqldbcomponents.png‎]]
 +
 
 +
On the SQLDB tab you will find:
 +
* 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.
 +
* [[TSQLQuery]]
 +
 
 +
If the SQLDB tab is missing, have a look at [[Install Packages]] for an "Install Howto".
  
 
==Explanation of the used components==
 
==Explanation of the used components==
 
===TMySQLConnection===
 
===TMySQLConnection===
  
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.
+
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 be setting the Transaction property, or you will not be able to open a SQLQuery.
+
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.
 +
 
 +
{{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.}}
 +
 
 +
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.
 +
 
 +
In all cases put a copy of libmysql.dll ''and any other required files/dlls''
 +
* in your Lazarus directory and the same directory as your project files or
 +
* 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.
  
 
===TSQLTransaction===
 
===TSQLTransaction===
A SQLTransaction is needed for some internal housekeeping. A SQLTransaction is automatically activated when you open a dataset using it. When you want to close the SQLConnection however, you first have to make sure the SQLTransaction is not active.
+
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.
  
 
===TSQLQuery===
 
===TSQLQuery===
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.
+
[[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.
  
 
===TDataSource===
 
===TDataSource===
 
+
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 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.
 
A datasource can only be connected to a single dataset at a time but there can be several data aware components connected.
  
 
===TDBGrid===
 
===TDBGrid===
A DBGrid 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.
+
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.
  
 
==Our program==
 
==Our program==
  
 
===The basics===
 
===The basics===
We will try to make a program based on the one made [[Lazarus Database Tutorial/nl#Verbinding met MySQL vanuit een Lazarus Programma|here (in Dutch)]] which is based on the [[Lazarus Database Tutorial#Connecting to MySQL from a Lazarus Application|original (in English)]] by [[user:kirkpatc|Chris]].
+
We will try to make a program based on the one made [[Lazarus Database Tutorial/nl#Verbinding met MySQL vanuit een Lazarus Programma|here (in Dutch)]] which is based on the [[Lazarus Database Tutorial#Connecting to MySQL from a Lazarus Application|original (in English)]] by [[user:Kirkpatc|Chris]].
  
 
===The main form===
 
===The main form===
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.<br> [[image:Trymysql.png]]<br>
+
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.
From the SQLdb-tab place a TMySQLConnection, a TSQLTransaction and a TSQLQuery [[image:Components.png]] on this form. Don't change the default names given to this components. We have to link these components together so they can do their job. So the following properties have to be set:
+
 
 +
[[image:Trymysql.png]]
 +
 
 +
From the SQLdb-tab place a [[TMySQL56Connection]] [[image:tmysql56connection.png]] (or other mysql-client version) a [[TSQLTransaction]] [[image:tsqltransaction.png]] and a [[TSQLQuery]] [[image: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: <tt>MySQLConnection1</tt>. We have to link these components together so they can do their job. So the following properties have to be set:
 
{|
 
{|
 
|-
 
|-
Line 54: Line 76:
 
|-
 
|-
 
| SQLTransaction1 || Database || MySQLConnection1
 
| SQLTransaction1 || Database || MySQLConnection1
 +
|-
 +
| SQLQuery1 || Transaction || SQLTransaction1
 
|-
 
|-
 
| SQLQuery1 || Database || MySQLConnection1
 
| SQLQuery1 || Database || MySQLConnection1
|-
 
| SQLQuery1 || Transaction || SQLTransaction1
 
 
|}
 
|}
 
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.
 
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.
 +
 +
As said earlier: Make sure you are using the correct Connection component for your version of MySQL server.
  
 
===The code===
 
===The code===
Line 73: Line 97:
 
====Connect to a server====
 
====Connect to a server====
 
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.
 
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.
procedure TFormTryMySQL.ConnectButtonClick(Sender: TObject);
+
 
begin
+
<syntaxhighlight lang=pascal>procedure TFormTryMySQL.ConnectButtonClick(Sender: TObject);
  // Check if we have an active connection. If so, let's close it.
+
begin
  if MySQLConnection1.Connected then CloseConnection(Sender);
+
  // Check if we have an active connection. If so, let's close it.
  // Set the connection parameters.
+
  if MySQLConnection1.Connected then CloseConnection(Sender);
  MySQLConnection1.HostName := HostEdit.Text;
+
  // Set the connection parameters.
  MySQLConnection1.UserName := UserEdit.Text;
+
  MySQLConnection1.HostName := HostEdit.Text;
  MySQLConnection1.Password := PasswdEdit.Text;
+
  MySQLConnection1.UserName := UserEdit.Text;
  MySQLConnection1.DatabaseName := 'mysql'; // MySQL is allways there!
+
  MySQLConnection1.Password := PasswdEdit.Text;
  ShowString('Opening a connection to server: ' + HostEdit.Text);
+
  MySQLConnection1.DatabaseName := 'mysql'; // MySQL is allways there!
  MySQLConnection1.Open;
+
  ShowString('Opening a connection to server: ' + HostEdit.Text);
  // First lets get a list of available databases.
+
  MySQLConnection1.Open;
  if MySQLConnection1.Connected then begin
+
  // First lets get a list of available databases.
    ShowString('Connected to server: ' + HostEdit.Text);
+
  if MySQLConnection1.Connected then begin
    ShowString('Retrieving list of available databases.');
+
    ShowString('Connected to server: ' + HostEdit.Text);
    SQLQuery1.SQL.Text := 'show databases';
+
    ShowString('Retrieving list of available databases.');
    SQLQuery1.Open;
+
    SQLQuery1.SQL.Text := 'show databases';
    while not SQLQuery1.EOF do begin
+
    SQLQuery1.Open;
      DatabaseComboBox.Items.Add(SQLQuery1.Fields[0].AsString);
+
    while not SQLQuery1.EOF do begin
      SQLQuery1.Next;
+
      DatabaseComboBox.Items.Add(SQLQuery1.Fields[0].AsString);
    end;
+
      SQLQuery1.Next;
    SQLQuery1.Close;
+
    end;
    ShowString('List of databases received!');
+
    SQLQuery1.Close;
  end;
+
    ShowString('List of databases received!');
end;
+
  end;
 +
end;</syntaxhighlight>
  
 
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.
 
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.
 
:''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.''
 
:''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.''
 
With the parameters set, we can connect to the server. This is done by calling
 
With the parameters set, we can connect to the server. This is done by calling
MySQLConnection1.Open;
+
<syntaxhighlight lang=pascal>MySQLConnection1.Open;</syntaxhighlight>
 
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.
 
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
 
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
SQLQuery1.Open;
+
<syntaxhighlight lang=pascal>SQLQuery1.Open;</syntaxhighlight>
The result set of a SQLQuery can be examined through the fields property. As you can see we iterate through the records be calling
+
On MySQL5 set this to correct error with SQL syntax:
SQLQuery1.Next;
+
<syntaxhighlight lang=pascal>SQLQuery1.ParseSQL := False;
 +
SQLQuery1.ReadOnly := True;</syntaxhighlight>
 +
The result set of a SQLQuery can be examined through the fields property. As you can see we iterate through the records by calling
 +
<syntaxhighlight lang=pascal>SQLQuery1.Next;</syntaxhighlight>
 
When we have added all available databases to our combobox, we close the SQLQuery again.
 
When we have added all available databases to our combobox, we close the SQLQuery again.
  
 
====Selecting a database====
 
====Selecting a database====
 
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.
 
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.
procedure TFormTryMySQL.SelectDBButtonClick(Sender: TObject);
+
 
begin
+
<syntaxhighlight lang=pascal>procedure TFormTryMySQL.SelectDBButtonClick(Sender: TObject);
  // A database has been selected so lets get the tables in it.
+
begin
  CloseConnection(Sender);
+
  // A database has been selected so lets get the tables in it.
  if DatabaseComboBox.ItemIndex <> -1 then begin
+
  CloseConnection(Sender);
    with DatabaseComboBox do
+
  if DatabaseComboBox.ItemIndex <> -1 then begin
      MySQLConnection1.DatabaseName := Items[ItemIndex];
+
    with DatabaseComboBox do
    ShowString('Retreiving list of tables');
+
      MySQLConnection1.DatabaseName := Items[ItemIndex];
    SQLQuery1.SQL.Text := 'show tables';
+
    ShowString('Retreiving list of tables');
    SQLQuery1.Open;
+
    SQLQuery1.SQL.Text := 'show tables';
    while not SQLQuery1.EOF do begin
+
    SQLQuery1.Open;
      TableComboBox.Items.Add(SQLQuery1.Fields[0].AsString);
+
    while not SQLQuery1.EOF do begin
      SQLQuery1.Next;
+
      TableComboBox.Items.Add(SQLQuery1.Fields[0].AsString);
    end;
+
      SQLQuery1.Next;
    SQLQuery1.Close;
+
    end;
    ShowString('List of tables received');
+
    SQLQuery1.Close;
  end;
+
    ShowString('List of tables received');
  OpenQueryButton.Enabled := True;
+
  end;
end;
+
  OpenQueryButton.Enabled := True;
 +
end;</syntaxhighlight>
 +
 
 
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.
 
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.
 
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.
Line 136: Line 166:
 
====Fields in a table====
 
====Fields in a table====
 
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.
 
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.
procedure TFormTryMySQL.TableComboBoxChange(Sender: TObject);
+
 
begin
+
<syntaxhighlight lang=pascal>procedure TFormTryMySQL.TableComboBoxChange(Sender: TObject);
  FieldListBox.Clear;
+
begin
  SQLQuery1.SQL.Text := 'show columns from ' + TableComboBox.Text;
+
  FieldListBox.Clear;
  SQLQuery1.Open;
+
  SQLQuery1.SQL.Text := 'show columns from ' + TableComboBox.Text;
  while not SQLQuery1.EOF do begin
+
  SQLQuery1.Open;
    FieldListBox.Items.Add(SQLQuery1.Fields[0].AsString);
+
  while not SQLQuery1.EOF do begin
    SQLQuery1.Next;
+
    FieldListBox.Items.Add(SQLQuery1.Fields[0].AsString);
  end;
+
    SQLQuery1.Next;
  SQLQuery1.Close;
+
  end;
end;
+
  SQLQuery1.Close;
 +
end;</syntaxhighlight>
 +
 
 
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.
 
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.
  
 
====Showing the data====
 
====Showing the data====
 
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:
 
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:
procedure TFormTryMySQL.OpenQueryButtonClick(Sender: TObject);
+
 
begin
+
<syntaxhighlight lang=pascal>procedure TFormTryMySQL.OpenQueryButtonClick(Sender: TObject);
  ShowQueryForm := TShowQueryForm.Create(self);
+
begin
  ShowQueryForm.Datasource1.DataSet := SQLQuery1;
+
  ShowQueryForm := TShowQueryForm.Create(self);
  SQLQuery1.SQL.Text := CommandEdit.Text;
+
  ShowQueryForm.Datasource1.DataSet := SQLQuery1;
  SQLQuery1.Open;
+
  SQLQuery1.SQL.Text := CommandEdit.Text;
  ShowQueryForm.ShowModal;
+
  SQLQuery1.Open;
  ShowQueryForm.Free;
+
  ShowQueryForm.ShowModal;
  SQLQuery1.Close;
+
  ShowQueryForm.Free;
end;
+
  SQLQuery1.Close;
 +
end;</syntaxhighlight>
 +
 
 
The ShowQueryForm looks like this:
 
The ShowQueryForm looks like this:
<center>[[Image:Mysqlshow.png]]</center>
+
<center>[[Image:Mysqlshow.png]]</center>
 
and contains a  
 
and contains a  
 
{|
 
{|
 
|-
 
|-
| TPanel || Align || alBottom
+
| [[TPanel]] || Align || alBottom
 
|-
 
|-
| TDataSource || ||  
+
| [[TDataSource]] || ||  
 
|-
 
|-
| TDBGrid || Align || alClient
+
| [[TDBGrid]] || Align || alClient
 
|-
 
|-
 
|  || DataSource || DataSource1
 
|  || DataSource || DataSource1
 
|-
 
|-
| TButton || Caption || Close
+
| [[TButton]] || Caption || Close
 
|}
 
|}
 
The button is placed on the panel.
 
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.
 
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.
 +
 +
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:
 +
 +
Add a Button at the bottom of the ShowQuery Form named AddButton and with Caption 'Add'. Create a method for AddButtonClick like this:
 +
 +
<syntaxhighlight lang=pascal>procedure TShowQueryForm.AddButtonClick(Sender: TObject);
 +
begin
 +
  DataSource1.DataSet.Append;
 +
end;</syntaxhighlight>
 +
 +
Change the code for OpenQueryButtonClick to allow for updates to the database when we finish with the Query Form.
 +
 +
<syntaxhighlight lang=pascal>procedure TFormTryMySQL.OpenQueryButtonClick(Sender: TObject);
 +
begin
 +
  ShowQueryForm := TShowQueryForm.Create(nil);
 +
  try
 +
    ShowQueryForm.DataSource1.DataSet := SQLQuery1;
 +
    // We will write in the database, so let's set ReadOnly to false,
 +
    // and for that we need to set ParseSQL true
 +
    SQLQuery1.ParseSQL:=true;
 +
    SQLQuery1.ReadOnly:=false;
 +
    SQLQuery1.SQL.Text := CommandEdit.Text;
 +
    SQLQuery1.Open;
 +
    ShowQueryForm.ShowModal;
 +
  finally
 +
    // set up update mode, and update database
 +
    SQLQuery1.UpdateMode:=upWhereChanged;
 +
    SQLQuery1.ApplyUpdates;
 +
    SQLTransaction1.Commit;
 +
    ShowQueryForm.Free;
 +
    SQLQuery1.Close;
 +
    // set read-only and parsesql back to default
 +
    SQLQuery1.ParseSQL:=false;
 +
    SQLQuery1.ReadOnly:=true;
 +
  end;
 +
end;</syntaxhighlight>
 +
 +
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.
 +
 +
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):
 +
 +
<syntaxhighlight lang="sql">ALTER TABLE TRESTRIG
 +
ADD COLUMN AUTOID INT
 +
PRIMARY KEY AUTO_INCREMENT;</syntaxhighlight>
 +
 +
and then you will find that the Delete button on the navigator works.
  
 
==Sources==
 
==Sources==
 
The sources for this project can be downloaded [http://prdownloads.sourceforge.net/lazarus-ccr/mysql_demo_20050408.tar.gz?download here]
 
The sources for this project can be downloaded [http://prdownloads.sourceforge.net/lazarus-ccr/mysql_demo_20050408.tar.gz?download here]
 
For more demo projects see [http://sourceforge.net/project/showfiles.php?group_id=92177&package_id=148359 sourceforge]
 
For more demo projects see [http://sourceforge.net/project/showfiles.php?group_id=92177&package_id=148359 sourceforge]
 +
 +
==See also==
 +
* [[mysql]]

Latest revision as of 00:43, 21 February 2020

Deutsch (de) English (en) français (fr) 日本語 (ja) slovenčina (sk)

Databases portal

References:

Tutorials/practical articles:

Databases

Advantage - MySQL - MSSQL - Postgres - Interbase - Firebird - Oracle - ODBC - Paradox - SQLite - dBASE - MS Access - Zeos

Introduction

This page will explain how to connect to a MySQL server using visual components.

Note-icon.png

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

Note: see also these tutorials that teach data-bound controls, parameterized queries, database independent programming etc:

They are written for all databases that support sqldb, including MySQL.

If needed, see also mysql#SQLDB_tutorials_and_example_code for yet more tutorials (that are also written a long time ago.

Available Components

SQLdb Components

In any even vaguely recent version of Lazarus, the SQLDB components are installed by default.

sqldbcomponents.png

On the SQLDB tab you will find:

If the SQLDB tab is missing, have a look at Install Packages for an "Install Howto".

Explanation of the used components

TMySQLConnection

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.

Note-icon.png

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.

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.

In all cases put a copy of libmysql.dll and any other required files/dlls

  • in your Lazarus directory and the same directory as your project files or
  • 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.

TSQLTransaction

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.

TSQLQuery

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.

TDataSource

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.

TDBGrid

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.

Our program

The basics

We will try to make a program based on the one made here (in Dutch) which is based on the original (in English) by Chris.

The main form

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.

Trymysql.png

From the SQLdb-tab place a TMySQL56Connection tmysql56connection.png (or other mysql-client version) a TSQLTransaction tsqltransaction.png and a TSQLQuery 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:

Component Property Value
MySQLConnection1 Transaction SQLTransaction1
SQLTransaction1 Database MySQLConnection1
SQLQuery1 Transaction SQLTransaction1
SQLQuery1 Database MySQLConnection1

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.

As said earlier: Make sure you are using the correct Connection component for your version of MySQL server.

The code

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." :)

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

try ... finally

or

try ... except

constructions.

Connect to a server

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.

procedure TFormTryMySQL.ConnectButtonClick(Sender: TObject);
begin
  // Check if we have an active connection. If so, let's close it.
  if MySQLConnection1.Connected then CloseConnection(Sender);
  // Set the connection parameters.
  MySQLConnection1.HostName := HostEdit.Text;
  MySQLConnection1.UserName := UserEdit.Text;
  MySQLConnection1.Password := PasswdEdit.Text;
  MySQLConnection1.DatabaseName := 'mysql'; // MySQL is allways there!
  ShowString('Opening a connection to server: ' + HostEdit.Text);
  MySQLConnection1.Open;
  // First lets get a list of available databases.
  if MySQLConnection1.Connected then begin
    ShowString('Connected to server: ' + HostEdit.Text);
    ShowString('Retrieving list of available databases.');
    SQLQuery1.SQL.Text := 'show databases';
    SQLQuery1.Open;
    while not SQLQuery1.EOF do begin
      DatabaseComboBox.Items.Add(SQLQuery1.Fields[0].AsString);
      SQLQuery1.Next;
    end;
    SQLQuery1.Close;
    ShowString('List of databases received!');
  end;
end;

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.

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.

With the parameters set, we can connect to the server. This is done by calling

MySQLConnection1.Open;

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

SQLQuery1.Open;

On MySQL5 set this to correct error with SQL syntax:

SQLQuery1.ParseSQL := False; 
SQLQuery1.ReadOnly := True;

The result set of a SQLQuery can be examined through the fields property. As you can see we iterate through the records by calling

SQLQuery1.Next;

When we have added all available databases to our combobox, we close the SQLQuery again.

Selecting a database

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.

procedure TFormTryMySQL.SelectDBButtonClick(Sender: TObject);
begin
  // A database has been selected so lets get the tables in it.
  CloseConnection(Sender);
  if DatabaseComboBox.ItemIndex <> -1 then begin
    with DatabaseComboBox do
      MySQLConnection1.DatabaseName := Items[ItemIndex];
    ShowString('Retreiving list of tables');
    SQLQuery1.SQL.Text := 'show tables';
    SQLQuery1.Open;
    while not SQLQuery1.EOF do begin
      TableComboBox.Items.Add(SQLQuery1.Fields[0].AsString);
      SQLQuery1.Next;
    end;
    SQLQuery1.Close;
    ShowString('List of tables received');
  end;
  OpenQueryButton.Enabled := True;
end;

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.

Fields in a table

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.

procedure TFormTryMySQL.TableComboBoxChange(Sender: TObject);
begin
  FieldListBox.Clear;
  SQLQuery1.SQL.Text := 'show columns from ' + TableComboBox.Text;
  SQLQuery1.Open;
  while not SQLQuery1.EOF do begin
    FieldListBox.Items.Add(SQLQuery1.Fields[0].AsString);
    SQLQuery1.Next;
  end;
  SQLQuery1.Close;
end;

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.

Showing the data

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:

procedure TFormTryMySQL.OpenQueryButtonClick(Sender: TObject);
begin
  ShowQueryForm := TShowQueryForm.Create(self);
  ShowQueryForm.Datasource1.DataSet := SQLQuery1;
  SQLQuery1.SQL.Text := CommandEdit.Text;
  SQLQuery1.Open;
  ShowQueryForm.ShowModal;
  ShowQueryForm.Free;
  SQLQuery1.Close;
end;

The ShowQueryForm looks like this:

Mysqlshow.png

and contains a

TPanel Align alBottom
TDataSource
TDBGrid Align alClient
DataSource DataSource1
TButton Caption Close

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.

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:

Add a Button at the bottom of the ShowQuery Form named AddButton and with Caption 'Add'. Create a method for AddButtonClick like this:

procedure TShowQueryForm.AddButtonClick(Sender: TObject);
begin
  DataSource1.DataSet.Append;
end;

Change the code for OpenQueryButtonClick to allow for updates to the database when we finish with the Query Form.

procedure TFormTryMySQL.OpenQueryButtonClick(Sender: TObject);
begin
  ShowQueryForm := TShowQueryForm.Create(nil);
  try
    ShowQueryForm.DataSource1.DataSet := SQLQuery1;
    // We will write in the database, so let's set ReadOnly to false, 
    // and for that we need to set ParseSQL true
    SQLQuery1.ParseSQL:=true;
    SQLQuery1.ReadOnly:=false;
    SQLQuery1.SQL.Text := CommandEdit.Text;
    SQLQuery1.Open;
    ShowQueryForm.ShowModal;
  finally
    // set up update mode, and update database
    SQLQuery1.UpdateMode:=upWhereChanged;
    SQLQuery1.ApplyUpdates;
    SQLTransaction1.Commit;
    ShowQueryForm.Free;
    SQLQuery1.Close;
    // set read-only and parsesql back to default
    SQLQuery1.ParseSQL:=false;
    SQLQuery1.ReadOnly:=true;
  end;
end;

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.

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):

ALTER TABLE TRESTRIG 
ADD COLUMN AUTOID INT 
PRIMARY KEY AUTO_INCREMENT;

and then you will find that the Delete button on the navigator works.

Sources

The sources for this project can be downloaded here For more demo projects see sourceforge

See also