Computing: Free Pascal Programming

Using MariaDB databases with Lazarus/Free Pascal.

MariaDB is a 100% open source RDBMS, based on the MySQL Community Edition. It offers backward compatibility, a whole set of new storage engines, new features and extensions including the JSON, WITH and KILL statements, a Graph DBMS and is said being lots faster than MySQL. It's relatively young (2009) in comparison with MySQL (1995) and is lots less used than MySQL, the database server that is actually the "standard" offered by nearly all web space hosting providers. However, it's a real alternative to MySQL and maybe the database server of the future. For details about MariaDB, you may want to have a look at my article, concerning the installation of MariaDB as part of a MS Windows web development environment.

The following tutorial is about using MariaDB 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 2.0.10 64bit (with FPC 3.2.0); the database server used has been MariaDB 10.6.4 64bit. The tutorial is based on my lots more complete tutorial Using MySQL databases with Lazarus/Free Pascal, and only covers the connection to the MariaDB server, read and write operations from resp. to a MariaDB database being essentially the same as with MySQL.

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. TMySQLConnection may be used with MariaDB (normally without any modification of the code) and, it may even be possible (thanks to the backward compatibility) that if you have found a version, that works with the actual MariaDB release, it will continue to work with future releases. The TMySQL57Connection perfectly works with MariaDB 10.6, the only thing you need is the corresponding MySQL client (cf. below).

Another way to connect to MariaDB is 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, that you need to do so.

The following document shows, how to implement a simple Lazarus application, reading and displaying the number of cities in the "world" database (cf. my installation of MariaDB tutorial), 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 MariaDB specific aspects. Some explanations (such those 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.

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

Using MariaDB with a TMySQL57Connection.

If you have read my Connecting to a MySQL database tutorial, you can skip the following paragraphs: The connection to MariaDB is exactly identical as it is to MySQL (the only difference in my case being MySQL listening on standard port 3306 and MariaDB listening on port 3307).

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 MariaDB: TMySQL57Connection (where 57 is the version number 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 that 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, 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 MariaDB by pushing the "Connect" button, use the following code within your TfMariaDB1.btConnectClick method (fMariaDB1 being the name of my form, btConnect the name of the button):

procedure TfMariaDB1.btConnectClick(Sender: TObject);     begin         if dbMySQLConnection.Connected then             dbMySQLConnection.Close;         dbMySQLConnection.HostName := 'localhost';         dbMySQLConnection.Port := 3307;   // with MySQL running on port 3306, I use port 3307 for MariaDB         dbMySQLConnection.UserName := 'nemo';         dbMySQLConnection.Password := 'nemo';         dbMySQLConnection.DatabaseName := 'world';         try             dbMySQLConnection.Open;             edMess.Text := 'Connection to MariaDB database "world" = OK!';         except             on E: ESQLDatabaseError do                 edMess.Text := 'Connection to MariaDB database "world" FAILED!';         end;     end;

If you run the application and try to connect, you may get one of the two error conditions, shown on the screenshots below. The screenshot on the left corresponds to the case where the default MySQL library cannot be loaded. This probably means that libmysql.dll cannot be found; in fact you have to copy it to the directory that contains the Free Pascal executable (during development, this corresponds to your project's output directory). The screenshot on the right corresponds to the case where the TMySQLConnection and the MySQL client used are incompatible. As I said above, for the given TMySQLXYConnection used, it's mandatory to use the MySQL client version X.Y. In the case as shown on the screenshot, the error occurred because I tried to use the DLL shipped with MySQL 8.0 with the TMySQL57Connection.

So, we have to copy libmysql.dll version 5.7 to the directory containing MariaDB1.exe. But where to find the DLL? If you have installed MySQL, libmysql.dll may be found in the MySQL installation folders, in the case of MySQL 5.7 in C:\Program Files\MySQL\MySQL Server 5.7\lib\. If you have MySQL 8 installed, the library shipped with this version will not work with the TMySQL57Connection and you'll have to find libmysql.dll elsewhere; the same, of course, if you do not use MySQL. Not being sure, if you can find the DLL on the official MySQL or related 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.

After having copied the correct libmysql.dll to your project output directory and 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 TfMariaDB1.btExitClick method (activated when pushing the "Exit" button):

procedure TfMariaDB1.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 MariaDB server when the "Query" button is pushed, so has to be coded within the TfMariaDB1.btQueryClick method. To define the SQL statement, to query the database, as well as to retrieve the data, that MariaDB returned, properties and methods of the TSQLQuery object will be used. In particular, dbSQLQuery.SQL.Text to define the SQL SELECT.

procedure TfMariaDB1.btQueryClick(Sender: TObject);     var         Count: Integer;     begin         if dbMySQLConnection.Connected then begin             // Query the database             dbSQLQuery.SQL.Text := 'SELECT count(*) FROM city';             try                 dbSQLQuery.Open;                 if dbSQLQuery.EOF then                     Count := 0                 else                     Count := dbSQLQuery.Fields[0].AsInteger;                 dbSQLQuery.Close;                 // Display the query result                 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 MariaDB 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 that you want to connect to.

ODBC Driver Manager and MariaDB 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) and choose the Drivers tab. If you have installed MySQL, using the "all-in-one" MySQL Installer (cf. my Installation of MySQL article), you see that the drivers for MySQL have been installed. On the other side, no driver listed for MariaDB.

Use the following link to download the MariaDB ODBC connector. The driver, that I used, when writing this tutorial, is version 3.1.13 (x64). Installation is straight forward, I choose to do a complete (i.e. all features included) installation. You may let Make user DSN's for older connector versions to use this version unchecked, as there actually are no MariaDB DNS yet. After installation is terminated, the driver will be listed with the other ones in the Drivers tab of Windows 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 MariaDB databases on localhost, in ODBC Data Source Administrator, choose the User DSN tab and click the Add... button. In the opening Create new data source window, select MariaDB ODBC 3.1 Driver.

There are 6 steps (6 windows with options to set) to create the new data source for MariaDB. I considered only the two first ones, letting all fields in the other 4 blank (default options or feature not used). In the first window (screenshot on the left), I set Data source name = MariaDB64 and entered "MariaDB Database (x64)" as data source description. In the second window (screenshot on the right), I choose TCP/IP as connection method, setting Server name = localhost and Port = 3307. I let the user name and the password fields empty. This means, that it has to be given within the application code (with the possibility to choose the MariaDB user, that you want).

You can at any moment edit the settings for the DSN by selecting it in the DSN tab of ODBC Data Source Administrator and pushing the Configure... button. You can also use the administrator to test the connection to the MariaDB server. On the configuration page with the connection options, fill in a database name and the user, that you want to use to access it. If the data entered is correct, the connection should normally succeed.

Using MariaDB with a TODBCConnection.

To use MariaDB 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 "MariaDB64". Driver, host and port having been defined in the DSN (see above), the only properties among those listed above, that we need to set are UserName and Password, then specify the name of the 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 MariaDB by pushing the "Connect" button, use the following code within your TfMariaDB2.btConnectClick method (fMariaDB2 being the name of my form, btConnect the name of the button). The disconnection from MariaDB may be coded as part of the TfMariaDB2.btExitClick method, as described with the TMySQL57Connection.

procedure TfMariaDB2.btConnectClick(Sender: TObject);     begin         if dbODBCConnection.Connected then             dbODBCConnection.Close;         // Set the connection parameters         dbODBCConnection.DatabaseName := 'MariaDB64';         dbODBCConnection.UserName := 'nemo';     dbODBCConnection.Password := 'nemo';         dbODBCConnection.Params.Add('DATABASE=world');   // the name of the database is given here!         dbODBCConnection.Params.Add('AUTOCOMMIT=1');         // Connect to the world database         try             dbODBCConnection.Open;             edMess.Text := 'Connection to MariaDB database "world" = OK!';         except             on E: ESQLDatabaseError do                 edMess.Text := E.Message;         end;     end;

The screenshot on the left was made with MariaDB stopped, the one on the right, when the server was running and a successful connection could be established to the "world" database.

To read the number of cities from the "city" table in the "world" database, just use the TSQLQuery object and the code described with the TMySQL57Connection object (changing the name of the connection, of course).

Note: The connection to the MariaDB server being established, you can perform SELECT, INSERT, UPDATE and DELETE operations in exactly the same way, as you would do with MySQL. For those, who haven't any experience with MySQL and who need further help to develop Lazarus/Free Pascal applications, using a (MariaDB) database: Have a look at my Reading data from a MySQL database and Writing data to a MySQL database tutorials. All, what is said for MySQL, is exactly the same with MariaDB and the corresponding code samples (being part of the download files MySQL3_src.zip respectively MySQL4_src.zip) should work without any problem with MariaDB (except, that you'll have to set the port, if you run MariaDB on port 3307, as in this tutorial). Also note, that with MariaDB 10.6, as with MySQL 8, you must NOT execute the dbSQLQuery.SQL.Text := 'SET CHARACTER SET "utf8"'; and dbSQLQuery.SQL.Text := 'SET NAMES "utf8"'; queries, otherwise you'll get "garbage", when displaying countries or cities with non-ANSI characters; in the downloaded source files, comment these two lines out, or simply remove them...

---

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