Connector-type independent database access using Lazarus/Free Pascal.
In previous tutorials, I showed how to connect from a Lazarus/Free Pascal application to MySQL, MariaDB, PostgreSQL, MSSQLServer and Firebird. What now, if we need a Lazarus/Free Pascal application that is able to connect to two or more of these database servers? Globally, we have three possibilities:
- Writing several applications, one application for each connector-type. This would not really be a big effort, because the only part of the code, that changes, is the database connection (the remaining code being inserted using copy/paste). However, from the point of view maintenance, this can't be a good solution.
- Writing one single application and writing several connection methods, one for each connector-type. This should work rather well, but is probably not really ideal, especially that there is a lots more elegant way to do.
- Writing one single application with one single connection method, using the TSQLConnector class. That's what this tutorial is about.
The inheritance of the sqldb classes is as follows: TCustomConnection > TDatabase > TSQLConnection > TSQLConnector > TxConnection, where TCustomConnection is an abstract class for connections to a server, TDatabase is the base class for connecting a Free Pascal application to an external database engine, TSQLConnection is an abstract class representing a connection to an SQL database, TSQLConnector is a universal SQL database connection component and TxConnection are the connector-type specific SQL database connection components (TMySQLnnConnection, TPQConnection, TMSSQLConnection, TIBConnection, ...). When switching database backends, the normal procedure is to replace one instance of TSQLConnection descendant (connector-type dependent object) with another, and connect all instances of TSQLQuery and TSQLTransaction to the new connection. In practice, we will use a TSQLConnector instance, that will (in the background) create the correct TSQLConnection descendant to handle all actual operations on the database. All the programmer will have to do is to set the type of connection using the string property ConnectorType.
The sample application, used in the tutorial (click the following link to download the Lazarus/Free Pascal source code), shows how one same application with one single TSQLConnector instance may be used to read the number of cities in the "world" database (MySQL, MariaDB and PostgreSQL databases), the number of customers in the "BikeStores" database (MSSQLServer), and the number of customers in the "employee" table (Firebird). The application has been developed and tested on Lazarus 2.2.6 (fpc 3.2.2) for Windows 64bit, using the following database engines: MySQL 5.7 and 8.0, MariaDB 10.6.4, PostgreSQL 15.2.2, MSSQLServer 2017 Developer Edition and Firebird 4.0 (client/server mode installation). The operating system used was Windows 10 Home 64bit, except for MSSQLServer 2017, that on my system runs on a Windows 8.1 Pro 64bit VMware virtual machine.
Create a Lazarus application project with the form shown on the screenshot below. The application GUI is essentially the same as the one described in my connector-type dependent tutorials, the major difference being the 6 radio buttons that give the user the possibility to choose among several database distributions.
 |
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 our databases:
- TSQLConnector that we use to create the TSQLConnection instance that will handle all actual operations on the database. The descendants of TSQLConnection 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 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 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 these 3 components to the form as you do with any other items from the Lazarus menus. I renamed them to dbSQLConnector, dbSQLTransaction and dbSQLQuery respectively.
Concerning the TSQLConnector object, the TSQLConnection descendant that has to be used is defined by its property ConnectorType, that actually is a string that may have one of the values shown on the screenshot below. We can leave the corresponding field in the object's Properties sheet empty, because we will not add a value here, and we will neither add any database specific information (host name, database name, port number, user name and password) here (this will be done within the code). In the dbSQLConnector property sheet, we just set the property Transaction to the name of the transaction object ("dbSQLTransaction").
 |
Similarly, concerning the TSQLQuery object we'll include the SQL statements within the code, just setting its Transaction property to the name of the transaction object ("dbSQLTransaction"). Doing so, its Database property should be automatically set to the name of the TSQLConnector object's name ("dbSQLConnector"). The Database property of the TSQLTransaction object should now also be set to the name of the TSQLConnector object's name.
 |
The tutorial program is a sample to show how to proceed to connect to a SQL database independently of the database distribution, not a "really useful" application. In fact, it does within the same program all that what the samples used in the specific databases related tutorials do. The server configurations (in particular MariaDB listening on port 3307), the databases accessed ("world", "BikeStores", "employee") and the user information (user = "nemo", password = "nemo") are the same as in these tutorials. Thus, the application sets these values, depending on the connector-type selection done by the user. However, there is a problem with MySQL. As told in the Connecting to a MySQL database using Lazarus/Free Pascal, the default MySQL library has to be present in the project output directory (resp. together with the executable). This is not a problem by itself (to use MSSQLServer, we'll also have to copy the files dblib.dll and libiconv.dll to the project folder; cf. Using Microsoft SQL Server databases with Lazarus/Free Pascal). But, our application should be able to connect to a MySQL 5.7 as well as to a MySQL 8.0 server. The issue is that the MySQL clients are not backward compatible and that therefor we have to use different DLLs with the two versions of MySQL. Unfortunately, these two DLLs are both called libmysql.dll. Thus, we can only have one of the DLLs available to our application, that therefor the program can either connect to MySQL 5.7, or MySQL 8.0 (but not both, what in practice shouldn't be a problem, because if you are running MySQL 5.7, you aren't probably also running MySQL 8.0 and vice-versa). The question is now, how to tell the application what MySQL client has to be used (in other words if it should use a TMySQL57Connection, or a TMySQL80Connection object). This is not only an issue with MySQL, but also with MariaDB. MariaDB 10.6.4 works well with both clients, but if the DLL present is, for example, version 5.7, we'll have to use TMySQL57Connection (the usage of TMySQL80Connection resulting in a connection failure).
To make my application as generic as possible I decided to do the following:
- The application should be able to connect to either MySQL 5.7, or MySQL 8.0, depending on what version of MySQL is actually used.
- With the correct version of libmysql present (together with the executable), the application should be able to choose the correct TMySQLnnConnection component.
- To avoid all problems, the application should disable the selection (radio button) for the version of MySQL that will produce a connection failure because the corresponding DLL is not present
Application initialization.
If you use MySQL or MariaDB, it is supposed that libmysql version 5.7 or 8.0 is present in the project output directory (to run the application, the DLL has to be in the same directory as the executable). At its start, the application checks if the DLL is there; if not the radio buttons for MySQL and MariaDB will be disabled. To determine the version of the DLL, the application simply checks the file size. This is not really a proper way to do but with the size of the 5.7 file being less than 5 MB and the size of the 8.0 file being greater than 6.5 MB, it's ok to do so. Depending on the version of the DLL, one of the two MySQL buttons will be disabled. Here is the code:
procedure TfDBConnect.FormCreate(Sender: TObject);
begin
bStart := True;
// Check which version of libmysql.dll is present
if FileExists('libmysql.dll') then begin
// DLL is present
bMissingDLL := False;
if FileSize('libmysql.dll') > 6E+6 then begin
// "Big size" DLL indicates MySQL 8.0: MySQL 5.7 databases cannot be accessed
rbConnector1.Enabled := False;
rbConnector2.Enabled := True;
rbConnector2.Checked := True;
end
else begin
// "Small size" DLL indicates MySQL 5.7: MySQL 8.0 databases cannot be accessed
rbConnector1.Enabled := True;
rbConnector2.Enabled := False;
rbConnector1.Checked := True;
end;
end
else begin
// DLL is missing: MySQL and MariaDB databases cannot be accessed
bMissingDLL := True;
rbConnector1.Enabled := False;
rbConnector2.Enabled := False;
rbConnector3.Enabled := False;
rbConnector4.Checked := True;
end;
end;
procedure TfDBConnect.FormActivate(Sender: TObject);
begin
if bStart then begin
// Do only at start of application
if bMissingDLL then
MessageDlg('DLL not found', 'libmysql.dll not found! Access to MySQL and MariaDB databases will not be supported!', mtWarning, [mbOK], 0);
bStart := False;
end;
end;
Note: Including the error message dialog box pop-up within the FormActivate method and only executing it at application start-up is not the same than including it within the FormCreate method. In fact, it ensures that the application GUI is displayed when the message pops up; including the code within FormCreate would display the message box all alone.
Connecting to the database.
The application will try to connect to the selected database when the user pushes the "Connect" button. The code within the corresponding method has to perform three tasks:
- Defining the database connector that has to be used.
- Setting the connection parameters (host, database, user...).
- Connecting to the database and in the case of a failure, displaying an error message.
Lets have a look at the code:
procedure TfDBConnect.btConnectClick(Sender: TObject);
const
// Connector-type strings that define the TSQLConnection descendant to be used
Connectors: array[0..5] of string = (
'MySQL 5.7', 'MySQL 8.0', 'MySQL n.m', 'PostgreSQL', 'MSSQLServer', 'Firebird'
);
// Ports of the different database servers
Ports: array[0..5] of string = (
'3306', '3306', '3307', '5432', '1433', '3050'
);
// Databases that will be accessed on the different servers
Databases: array[0..5] of string = (
'world', 'world', 'world', 'world', 'BikeStores', 'employee'
);
var
Connector: string;
begin
if dbSQLConnector.Connected then // close (open) database connection
dbSQLConnector.Close;
// Determine the database specific TSQLConnection descendant
// In practice, this means: Determine the connector-type specific string value
Connector := Connectors[iConnector]; // connector-type value taken from the array constant
if iConnector = 2 then begin
// Special case for MariaDB: We must use the TMySQLnnConnection corresponding to the DLL actually present
if rbConnector1.Enabled then begin
// Use MySQL57Connection component
Connector := StringReplace(Connector, 'n.m', '5.7', []);
end
else begin
// Use MySQL80Connection component
Connector := StringReplace(Connector, 'n.m', '8.0', []);
end;
end;
// Set the "ConnectorType" property of the TSQLConnector component
// This will create the corresponding TSQLConnection descendant instance
dbSQLConnector.ConnectorType := Connector;
// Set the connection parameters.
dbSQLConnector.HostName := 'localhost';
dbSQLConnector.UserName := 'nemo';
dbSQLConnector.Password := 'nemo';
dbSQLConnector.DatabaseName := Databases[iConnector];
// Set the port as additional parameter (not directly)
dbSQLConnector.Params.Clear;
dbSQLConnector.Params.Add('port=' + Ports[iConnector]);
// Connect to the database
btQuery.Enabled := False;
try
// Connection success: Enable "Query" button
dbSQLConnector.Open;
edMess.Text := 'Connection to database = OK!';
btQuery.Enabled := True;
except
// Connection failure: Display database error message
on E: Exception do
edMess.Text := E.Message;
end;
end;
I use three array constants (their indexes being an integer variable set when a given connector is selected by checking the corresponding radio button) to define the connector strings, the server ports and the databases to be accessed. All servers listen to the default port, except for MariaDB that, on my system, listens to port 3307. The databases accessed are either those that were created during the installation of the server, or those that I manually created to test the installation (for details, please have a look at the database specific tutorials in the Web and Database Development section of my site). Concerning the connector strings, they may have any value, provided that they have been defined before, i.e. if they have been associated with a specific TSQLConnection descendant. If the connector type string is unknown to the system, the error message Error reading dbSQLConnector.ConnectorType will pop up.
 |
The definition of the connector type string is automatically done when loading the corresponding TSQLConnection descendant related unit. Thus our
uses statement has to be something like this:
uses
Classes, Forms, Controls, Graphics, Dialogs, StdCtrls, SysUtils, FileUtil,
sqldb, mysql57conn, mysql80conn, PQConnection, MSSQLConn, IBConnection;
In this case, of course, the connector type strings are predefined values. To view the actually possible values, select the TSQLConnector component on the form and extend the listbox available for the ConnectorType property (as shown on the second screenshot at the beginning of the tutorial).
The specific TSQLConnection descendant instance is created by setting the TSQLConnector component's ConnectorType property to the correct connector string value. In our example, that's the value defined in the array constant, except for MariaDB, where the connector type has to be chosen accordingly to the libmysql version present.
The connection properties "HostName", "UserName", "Password" and "DatabaseName" may be set directly. The port has to be set as additional parameter; the reason for this is, that the PostgreSQL connection component has no "Port" property. Important: In order to be able to change the connector type during runtime, you have to clear the parameter list, before adding the connector specific port! Otherwise the new value would be ignored and the old old would continue be used, what either results in an error message, or (in the case of accessing MySQL and then MariaDB, or vice-versa) in reading from the bad database.
The TSQLConnector.Open method should be included in a try ... except structure, in order to be able to throw an exception if the connection fails. Using E: Exception (instead of E: ESQLDatabaseError) seems not to be the best choice, but it has the advantage to correctly work with all 5 databases.
Disconnecting from the database.
You should disconnect from the database before closing the application. Here the code within the method evoked when the "Exit" button is pushed:
procedure TfDBConnect.btExitClick(Sender: TObject);
begin
if dbSQLConnector.Connected then
dbSQLConnector.Close;
Close;
end;
Querying the database.
When the user pushes the "Query" button (only enabled if the connection to the database succeeded), the application will try to read the number of records in the table specified of the database specified. Here is the code (note that this is essentially the same code as the one you would use with a TMySQLnnConnection for MySQL, a TPQConnection for PostgreSQL, etc):
procedure TfDBConnect.btQueryClick(Sender: TObject);
const
// Tables to be accessed on the different servers
Tables: array[0..5] of string = (
'city', 'city', 'city', 'city', 'sales.customers', 'customer'
);
var
Count: Integer;
begin
if dbSQLConnector.Connected then begin
// Query the database
dbSQLQuery.SQL.Text := 'SELECT count(*) FROM ' + Tables[iConnector];
try
// Successful SELECT operation: Display the query result
dbSQLQuery.Open;
if dbSQLQuery.EOF then
Count := 0
else
Count := dbSQLQuery.Fields[0].AsInteger;
dbSQLQuery.Close;
edCount.Text := IntToStr(Count);
edMess.Text := 'Select from database = OK!';
except
// SELECT operation failure: Display database error message
on E: Exception do
edMess.Text := E.Message;
end;
end;
end;
As above for the database, I use an array constant (the index being an integer variable set when one of the radio buttons is checked) to choose the table that has to be used with a given database. And, as above, I use the generic E: Exception to be sure that throwing an exception if the SELECT fails correctly works with all 5 databases.
Selecting the database connector.
The database connector to be used is selected by the user when they check the corresponding radio button. The action to be taken if a radio button is checked may be coded in a series of "radio button changed" event triggered methods. Here the code of these methods, that (after having set an integer variable to a value between 0 and 5, allowing to identify the actual connector type by this variable) executes the procedure ConnectorChange, the code of which you can find after the one of the 6 methods (one for each radio button).
procedure TfDBConnect.rbConnector1Change(Sender: TObject);
begin
if rbConnector1.Checked then begin
iConnector := 0;
ConnectorChange(dbSQLConnector, iConnector, stTitle, laCount, edMess, edCount, btQuery);
end;
end;
procedure TfDBConnect.rbConnector2Change(Sender: TObject);
begin
if rbConnector2.Checked then begin
iConnector := 1;
ConnectorChange(dbSQLConnector, iConnector, stTitle, laCount, edMess, edCount, btQuery);
end;
end;
procedure TfDBConnect.rbConnector3Change(Sender: TObject);
begin
if rbConnector3.Checked then begin
iConnector := 2;
ConnectorChange(dbSQLConnector, iConnector, stTitle, laCount, edMess, edCount, btQuery);
end;
end;
procedure TfDBConnect.rbConnector4Change(Sender: TObject);
begin
if rbConnector4.Checked then begin
iConnector := 3;
ConnectorChange(dbSQLConnector, iConnector, stTitle, laCount, edMess, edCount, btQuery);
end;
end;
procedure TfDBConnect.rbConnector5Change(Sender: TObject);
begin
if rbConnector5.Checked then begin
iConnector := 4;
ConnectorChange(dbSQLConnector, iConnector, stTitle, laCount, edMess, edCount, btQuery);
end;
end;
procedure TfDBConnect.rbConnector6Change(Sender: TObject);
begin
if rbConnector6.Checked then begin
iConnector := 5;
ConnectorChange(dbSQLConnector, iConnector, stTitle, laCount, edMess, edCount, btQuery);
end;
end;
procedure ConnectorChange(SQLConnector: TSQLConnector; Connector: Integer; Title: TStaticText; LCount: TLabel; Mess, Count: TEdit; BQuery: TButton);
begin
if SQLConnector.Connected then // close (open) database connection!
SQLConnector.Close;
// Set title caption and query result label
if Connector = 5 then begin
Title.Caption := 'Reading the number of customers in the "employee" database.';
LCount.Caption := 'Customers';
end
else if Connector = 4 then begin
Title.Caption := 'Reading the number of customers in the "BikeStores" database.';
LCount.Caption := 'Customers';
end
else begin
Title.Caption := 'Reading the number of cities in the "world" database.';
LCount.Caption := 'Cities';
end;
// Clear output fields
Mess.Text := ''; Count.Text := '';
// Disable "Query" button (user must first connect to the database)
BQuery.Enabled := False;
end;
Besides initializing the form for the actually used connector type (setting title and label caption, clearing the output fields, disabling the "Query" button), the procedure common to change events triggered by the selection of any of the 6 radio buttons, has a really important task: closing the database connection, in the case where it is active. This has to be done, not only because it's good practice, but also because not doing so could result in problems. For example, if you are connected to a PostgreSQL database and then change the connector type and directly connect to some other database, PostgreSQL considers that the connection has been lost (as it wasn't properly closed) and issues an error dialog box (that gives you the possibility to abort the application in order to prevent possible data corruption if you would continue.
To terminate the tutorial, here are some screenshots from the application, running on my Windows 10. The first two screenshots show a successful connection to MaraiDB resp. Firebird. The last two show a connection failure to PostgreSQL because the server is not running resp. a connection failure to MSSQLServer because dblib.dll and/or libiconv.dll are/is not present in the project directory.
 |
 |
 |
 |
If you find this text helpful, please, support me and this website by signing my guestbook.