Computing: Free Pascal Programming

Using MySQL databases with Lazarus/Free Pascal.


MySQL is probably 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.
The following tutorial is about using MySQL databases from within a Lazarus/Free Pascal application. It covers the basics, but I think, that's enough to know (at least for beginners) in order to be able to transfer data between the database server and your program. Please, note that 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. Concerning newer versions, some important details are given in the Connecting to MySQL 8 paragraph below.
The tutorial is divided into 3 parts:
1. Connecting to a MySQL database
Tutorial, showing in detail, how to proceed to connect to a MySQL database, using a TMySQLConnection object, or, as an alternative, using a TODBCConnection object.
2. Reading data from a MySQL database
Tutorial, showing the very basics of reading (selecting) data from a MySQL database from within a Lazarus application. It also contains the information of what to do, to correctly handle UTF-8 characters.
3. Writing data to a MySQL database
Tutorial, about how to change the actual content of a MySQL database. The tutorial shows a simple Lazarus application, to insert, delete and update database records. You also find there, how you can proceed, to check if your queries were executed successfully or failed.
Note: Connecting to MySQL 8.
The tutorial examples should work with other versions than Windows 10 64bit, as well as with other (more recent) Lazarus and MySQL versions, than those, I used when writing the tutorial. Just, be aware, that there may be problems with the client DLL, as explained in part 1 of the tutorial; in the case, where you don't succeed using the TMySQLxxConnection, the TODBCConnection should always work.
If you experience problems with your installation and for whatever reason you don't want to use ODBC, the description of my experiences with MySQL 8 may be helpful.
Here my installation from January 2021:
  • Lazarus 2.0.10 64bit (with FPC 3.2.0)
  • MySQL 8.0.22 64bit. Running the server on localhost, I chose to use legacy (MySQL 5 compatible) authentication during setup. This allows the usage of a MySQL 5 client, and by this the usage of a TMySQL57Connection in Lazarus. However, the TMySQL57Connection will not work with the DLL shipped with MySQL 8. Thus, I used the DLL of my previous MySQL 5.7 installation, and this works fine. As it may be difficult to find this file on official websites, I have decided to store a copy on my server. Click the following link to download libmysql.dll for MySQL 5.7 64bit from streetinfo.lu.
And here my actual (April 2023) installation:
  • Lazarus 2.2.0 64bit (with FPC 3.2.2)
  • MySQL 8.0.22 64bit. Again, I chose to use legacy (MySQL 5 compatible) authentication. This allows the usage of a MySQL 5 client (as described above). But, there is a simpler way to proceed: The recent versions of Lazarus include TMySQL80Connection objects, that actually do work with the DLL shipped with MySQL 8. Thus, all you'll have to do is to copy the DLL from your MySQL installation to your project output directory.
UTF-8 access to MySQL 8.0 seems to be different from earlier versions. Not only that the 2 queries described in the Reading data from a MySQL database tutorial, are no longer needed, but on the contrary, non-ANSI characters are handled correctly by default and appear as garbage, if these queries are actually executed!
The MySQL 8 "world" database (database used in the tutorial), seems to be badly configured. With Latin character set tables, accentuated characters appeared as "garbage" in my tables. Drop the database and recreate it manually, using CHARSET=utf8mb4 (the server default), then insert the countries, cities and languages records. You can use the following link to download the SQL of the "world" database. If you use the SQL in this file, a foreign key index, relating the "city" and the "country" tables, will be created, thus no need to do it manually, as described in the third part of the tutorial.

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