Computing: Free Pascal Programming

Connecting to a MySQL database using Lazarus/Free Pascal.

Note: The tutorial examples have been build and tested on Windows 10, using Lazarus 1.8.4 64bit (with FPC 3.0.4); the database server used has been MySQL 5.7.22 64bit. If you experience problems with newer versions of the database, the Connecting to MySQL 8 paragraph at the tutorial start page may be helpful.

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 TODBCConnection 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 article concerning the installation of MySQL on MS Windows), first, using a TMySQL57Connection and second, using a TODBCConnection. It is supposed that you are familiar with Lazarus and Free Pascal. The tutorial details are limited to the MySQL specific aspects. Some explanations (such DSN concerning the installation of the ODBC drivers) apply to MS Windows only. Click the following link to download the Lazarus/Free Pascal source code of this application. Note: If you use MySQL 8.0 with a recent version of Lazarus, you may prefer to download the source code, using a TMySQL80Connection.

Create a Lazarus application project with the form shown on the screenshot below. Save it as MySQL1.lpi and then re-save it as MySQL2.lpi. The TMySQLConnection resp. TODBCConnection specific controls will be added, as explained in the paragraphs below.

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, I just set "Transaction" to the name of the transaction object ("dbSQLTransaction").

Similarly, 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.

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.

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

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. The second screenshot shows a successful query, with the number of cities returned.

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.

ODBC Driver Manager and MySQL driver.

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.

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.

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 Data Source Administrator, choose the User DSN tab and click the "Add" button.

Select a driver from DSN available. I choose the MySQL ODBC 8.0 Unicode Driver. When pushing "Finish", the configuration tab opens. I filled in the following: Data source 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.

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.

Using MySQL with a TODBCConnection.

To use MySQL with a TODBCConnection, add the following components to your form: TODBCConnection, TSQLTransaction and TSQLQuery. Set the "Database" property of the TSQLTransaction object to the name of the TODBCConnection 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".

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 FILEDSN (name of the FileDSN, 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 database, 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 DSN 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 a TODBCConnection, the usage of E.Message (ESQLDatabaseError) is a good choice, as it does display the reason for connection failure. The second screenshot shows a successful connection.

And, to terminate this rather long documentation: To read the number of cities from the "city" table in the "world" database, just use the TSQLQuery object and the code described above (changing the name of the connection, of course).

Using MySQL databases with Lazarus/Free Pascal       |       2. Reading data from a MySQL database       |       3. Writing data to a MySQL database

---

If you find this text helpful, please, support me and this website by signing my guestbook.