Computing: Free Pascal Programming

Programming Home   Home   Contact

Connecting to a MySQL database using Lazarus / Free Pascal.


MySQL is without any doubt the most used database server on the Web. Beside the commercial editions (standard, enterprise, cluster...) there is a completely free MySQL community edition. For details about MySQL, you may want to have a look at my article, concerning the installation of MySQL as part of a MS Windows web development environment.
It is possible to connect to a MySQL database as well as from Free Pascal command line programs as from Lazarus GUI applications; the following document applies to Lazarus only. The most common method to connect to MySQL from a Lazarus application is to use a TMySQLConnection object. This is relatively easy to implement, but there is one major issue: The MySQL maintainers break binary compatibility between client library versions. This means that a FPC/Lazarus translation needs to be made for each new version and it may be possible that you will not find a version, that actually works with the most recent MySQL distribution. What does not mean, that you can't use Lazarus with this database; you just have to use a different method to connect to it. Fortunately, MS Windows include the so called Microsoft Open Database Connectivity (ODBC), an interface that makes it possible for applications to access data from a variety of database management systems. And Lazarus includes the TODBCConection object, you need to do so. Note, that there are two popular ODBC Driver Managers for Unix-based platforms: unixODBC and iODBC.
The following document shows, how to implement a simple Lazarus application, reading and displaying the number of cities in the "world" database, installed as sample together with MySQL Workbench (cf. my artcile concerning MySQL), first, using a TMySQLConnection and second, using a TODBCConnction. It is pre-supposed that you are familiar with Lazarus and Free Pascal. The tutorial details are limited to the MySQL specific aspects. Some explanations (such those concerning the installation of the ODBC drivers) apply to MS Windows only (perhaps, I'll add details on how to proceed on Linux, later). Click the following link to download the Lazarus / Free Pascal source code of this application.
Create a Lazarus application project with the form showed on the screeenshot below. Save it as MySQL1.lpi and then resave it as MySQL2.lpi. The TMySQLConnection resp. TODBCConnction specific controls will be added, as explained in the paragraphs below.
Simple Lazarus project
Using MySQL with a TMySQLConnection.
The components allowing access to a variety of databases may be found in the Lazarus SQLdb menu. There are 3 of them, that we need to connect to MySQL:
  • TMySQLxyConnection (where xy is a version number, e.g. 57 for MySQL client 5.7). TXXXConnections are components, that take the requests of the TSQLQuery and TSQLTransaction components and translate them into requests specifically tailored for the database you are using.
  • TSQLTransaction. This encapsulates the transaction on the database server. A TXXXConnection object always needs at least one TSQLTransaction associated with it, so that the transaction of its queries is managed.
  • TSQLQuery. This is a descendant of TDataset, and provides the data as a table from the SQL query that you submit. It can also be used to execute SQL queries (e.g. INSERT INTO, stored procedures..)
Add theses 3 components to the form as you do with any other items from the Lazarus menus. I renamed them to dbMySQLConnection, dbSQLTransaction and dbSQLQuery respectively. Concerning the TMySQLConnection object, I'll include the database specific information (host name, database name, eventually the port number, user name and password) within the code, in the dbMySQLConnection property sheet, just setting "Transaction" to the name of the transaction object ("dbSQLTransaction").
TMySQLConnection component
Similarily, I'll include the SQL statements within the code, just setting the "Transaction" property of the TSQLQuery object in the property sheet; doing so, its "Database" property should be automatically set to the name of the TMySQLConnection object's name ("dbMySQLConnection"). The "Database" property of the TSQLTransaction object should now also be set to the name of the TMySQLConnection object's name.
TSQLQuery component
Connecting to the database.
To connect to MySQL by pushing the "Connect" button, use the following code within your TfMySQL1.btConnectClick method (fMySQL1 being the name of my form, btConnect the name of the button):
procedure TfMySQL1.btConnectClick(Sender: TObject);

begin
    if dbMySQLConnection.Connected then
        dbMySQLConnection.Close;
    dbMySQLConnection.HostName := 'localhost';
    dbMySQLConnection.UserName := 'nemo';
    dbMySQLConnection.Password := 'nemo';
    dbMySQLConnection.DatabaseName := 'world';
    try
        dbMySQLConnection.Open;
        edMess.Text := 'Connection to MySQL database "world" = OK!';
    except
        on E: ESQLDatabaseError do
            edMess.Text := 'Connection to MySQL database "world" FAILED!';
    end;
end;
If you run the application and try to connect, however you get an EInOutError telling that the default MySQL library cannot be loaded.
MySQL library not found error
You find the DLL for the currently installed client in your MySQL installation folders, in my case C:\Program Files\MySQL\MySQL Server 5.7\lib\. Copy the file libmysql.dll to your project output directory. Re-running the application, the connection with the database server should now succeed. Below 2 screenshots, the first with a connection failure due to the fact, that I coded an invalid password, the second with the connection being established. In the case of connection failure, instead of displaying a hard coded message, you may display the value of E.Message (ESQLDatabaseError). What isn't helpful, however, because it doesn't tell anything about why the connection did not establish, just saying "dbMySQLConnection : Server connect failed".
MySQL connection failed error   MySQL connection established
Disconnecting from the database.
The disconnection from the database may be coded within the TfMySQL1.btExitClick method (activated when pushing the "Exit" button):
procedure TfMySQL1.btExitClick(Sender: TObject);

begin
    if dbMySQLConnection.Connected then
        dbMySQLConnection.Close;
    Close;
end;
Querying the number of cities.
The number of cities in the "city" table of the "world" database should be read from the MySQL server when the "Query" button is pushed, so has to be coded within the TfMySQL1.btQueryClick method. To define the SQL statement, to query the database, as well as to retrieve the data, that MySQL returned, properties and methods of the TSQLQuery object will be used. In particular, dbSQLQuery.SQL.Text to define the SQL SELECT.
procedure TfMySQL1.btQueryClick(Sender: TObject);

var
    Count: Integer;

begin
    if dbMySQLConnection.Connected then begin
        dbSQLQuery.SQL.Text := 'SELECT count(*) FROM city';
        try
            dbSQLQuery.Open;
            if dbSQLQuery.EOF then
                Count := 0
            else
                Count := dbSQLQuery.Fields[0].AsInteger;
            dbSQLQuery.Close;
            edCities.Text := IntToStr(Count);
        except
            on E: ESQLDatabaseError do
                edMess.Text := E.Message;
        end;
    end;
end;
Here, the usage of E.Message (ESQLDatabaseError) is a good choice, as shows the first screenshot below, situation where I ran the application with a spelling error in the SQL SELECT statement.
Query failure due to a SQL error   Successful MySQL query
Using MySQL with a TODBCConnection.
ODBC (Open Database Connectivity) is a technology that allows one to connect to a whole variety of databases using a single API, the ODBC API. There are ODBC implementations for various platforms and there are drivers for most Database Management Systems. Official ODBC documentation can be found at the MSDN ODBC site.
Before you can connect to your database using ODBC, you need to install
  • an ODBC Driver Manager
  • an ODBC driver specific to the DBMS you want to connect to
Installing ODBC Driver Manager and MySQL driver (MS Windows).
MS Windows includes the ODBC Data Source Administrator, a built in ODBC Driver Manager, which allows DSNs to be set up, and other configuration. You can find it in "Control Panel > Administrative Tools". There are two ODBC Driver Managers available: one for 32bit and one for 64bit data sources.
Windows Administrative Tools
Open ODBC Data Sources (64bit) (both my MySQL server and my Lazarus are 64bit; I think, that if you use Lazarus 32bit, you will have to use ODBC Data Sources 32bit). In the Drivers tab, you see that the drivers for MySQL are already installed. This is, because they are included in the all-in-one binary distribution, called MySQL Installer, that I use (For details, have a look at my Installation of MySQL article). If no MySQL driver for your actual platform is listed here, you may download it from the DevArt website.
ODBC data source administrator
Connecting to an ODBC data source.
The parameters for connecting to an ODBC data source are described in a connection string. This is a string of the form: NAME=VALUE;NAME=VALUE...
An ODBC driver manager provides alternative ways to make DSN shortcuts for a set of parameters:
  • DSN (DataSource Name): a system or user defined data source, identified by a (unique) name. DSNs can be configured using the ODBC Data Source Administrator (or by manually editing the odbc.ini file or the registry).
  • File DSN: a file which contains the connection parameters.
The parameters in a DSN or File DSN can always be combined with additional parameters in the connection string (for example to specify a password).
By setting up a DSN, the connection details can be verified to work within the manager dialog, and then the named DSN is all that is needed to use the connection later. The connection details are therefore decoupled from the applications, as only the DSN name is used - the DSN acting as a go-between.
To set up a user DSN for MySQL databases on localhost, in ODBC DataSource Administrator, choose the User DSN tab and click the "Add" button.
Adding a DSN for MySQL
Select a driver from those available. I choose the MySQL ODBC 8.0 Unicode Driver. When pushing "Finish", the configuration tab opens. I filled in the following:
Data souurce name = "MySQL64", Description = "MySQL database (x64)", "TCP/IP server" = "localhost", Port = "3306" (default port, automatically filled in).
All the rest, I left empty (to set it in the application). Note that there also is a "Details" button, with dozens of settings; here, too, I let all fields empty.
MySQL DSN: Driver selection   MySQL DSN: Configuration
If you want to test the connection, fill in the values for "User", "Password" and "Database". Normally the connection to the MySQL database chosen should succeed.
MySQL DSN: Connection test
Using MySQL with a TODBCConnection.
To use MySQL with a TODBCConnction, add the following components to your form: TODBCConnction, TMySQL57Connection (for my actual MySQL 5.7 compatible client) and TSQLQuery. Set the "Database" property of the TSQLTransaction object to the name of the TODBCConnction object (I named it "dbODBCConnection") and the "Transaction" property of the TSQLQuery object to the name of the transaction ("dbSQLTransaction"); the "Database" property of this object should be automatically set to "dbODBCConnection".
TODBCConnction component
TODBCConnection provides a wrapper around the ODBC data source connection string. Some of its properties are mapped to name-value pairs in the connection string, the other ones can be specified in the "Params" property (TStrings variable).
TODBCConnection properties to ODBC connection string parameters mapping:
Property Type Connection string parameter
Driver string DRIVER (name of the ODBC driver)
DatabaseName string DSN (name of the DSN, as defined in Windows ODBC DataSource Administrator)
FileDSN string FILEDNS (name of the FileDNS, as defined in Windows ODBC DataSource Administrator)
HostName string none (there is no corresponding parameter in the ODBC standard; may however be set in Windows ODBC DataSource Administrator)
UserName string UID (database user name)
Password string PWD (database user password)
Params TStrings Used to specify custom parameters. Each item must be of the form NAME=VALUE (see below)
Please, note that the property DatabaseName must be set to the name of the DSN and not the one of the databas, that you want to connect to, in our example to "MYSQL64". Driver (host and port) having been defined in the DSN (see above), the only properties among those listed above, we need to set are "UserName" and "Password", then specify the name of our database as element of the "Params" property TStrings.
An important parameter that can be set using "Params" is AUTOCOMMIT. Setting it to "1", results in directly executing a query (instead of having to manually call "StartTransaction" and "Commit").
To connect to MySQL by pushing the "Connect" button, use the following code within your TfMySQL2.btConnectClick method (fMySQL2 being the name of my form, btConnect the name of the button). The disconnection from MySQL may be coded as part of the TfMySQL2.btExitClick method, as described above.
procedure TfMySQL2.btConnectClick(Sender: TObject);

begin
    if dbODBCConnection.Connected then
        dbODBCConnection.Close;
    dbODBCConnection.DatabaseName := 'MYSQL64';
    dbODBCConnection.UserName := 'nemo';
    dbODBCConnection.Password := 'nemo';
    dbODBCConnection.Params.Add('DATABASE=world');
    dbODBCConnection.Params.Add('AUTOCOMMIT=1');
    try
        dbODBCConnection.Open;
        edMess.Text := 'Connection to MySQL database "world" = OK!';
    except
        on E: ESQLDatabaseError do
            edMess.Text := E.Message;
    end;
end;
As the first screenshot below (invalid user name) shows, with TODBCConnction, the usage of E.Message (ESQLDatabaseError) is a good choice, as it does display the reason for connection failure.
MySQL connection failed error   MySQL connection established
And, to terminate this rather long documentation: To read the number of cities from the "city" table in the "world" database, just use TSQLQuery object and the code described above (changing the name of the connection, of course). I hope, that this text is helpful!