Computing: Free Pascal Programming

Using InterBase databases with Lazarus/Free Pascal.


This tutorial is about the access to an InterBase Developer Edition 2020 (64bit) database from a Lazarus/Free Pascal application; I actually use Lazarus 3.0 64bit (with FPC 3.2.2) on Windows 11. The tutorial should also apply to other versions of InterBase, Lazarus and Windows. For details about how to set up InterBase (and create the "Tutorial" database used with the sample applications), please, have a look at my tutorial Trying out InterBase Developer Edition.
The tutorial covers the very basics of using InterBase databases from within a Lazarus/Free Pascal application, primarily how to connect to a database. In fact, working with InterBase is quite the same as working with MySQL. Thus, if you need further information concerning working with relational databases in Free Pascal, my more complete tutorial Using MySQL databases with Lazarus/Free Pascal may be helpful.
The tutorial uses the "Tutorial" sample database, that I created after having installed InterBase. It shows, how to implement a simple Lazarus application, reading and displaying the number of records in the "employee" table, first using a TIBConnection object, then using a TODBCConnection object. Click the following link to download the Lazarus/Free Pascal source code of the two applications.
Connecting using a TIBConnection object.
Create a Lazarus application project with the form shown on the screenshot below.
Simple Lazarus/Free Pascal database project with InterBase (using TIBConnection)
There are 3 database specific components (available in the Lazarus SQLdb menu) that have been added to the form:
  • TIBConnection (that I named "dbIbSQLConnection"): this is the component, that takes the requests of the TSQLQuery and TSQLTransaction components and translates them into requests specifically tailored for the InterBase database.
  • TSQLTransaction (that I named "dbSQLTransaction"): 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 (that I named "dbSQLQuery"): 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 such INSERT INTO, stored procedures, etc.
If you are familiar with using MySQL databases with Lazarus/Free Pascal, you have probably noticed that this is very similar of what you have with MySQL. In fact, the only major difference is the usage of the TIBConnection object instead of a TMySQLnnConnection object.
The "Transaction" property of the TIBConnection object has to set to the name of the TSQLTransaction object (in my case: "dbSQLTransaction"). This is also the case for this property of the TSQLQuery object. Setting these properties should automatically set the "Database" property of the TSQLTransaction and TSQLQuery objects to the name of the TIBConnection object's name (in my case: "dbIbSQLConnection").
Except for the database user and their password, I set the connection parameters of the TIBConnection object in its property sheet: Set HostName to "localhost" (optional), and set DatabaseName to the full path to the "Tutorial" database (file "Tutorial.ib"). If your InterBase server listened to a port other the default one, you would also have to set the Port property.
Concerning the SQL of the SELECT query, I set it in the source code.
To connect to the InterBase database "Tutorial" by pushing the "Connect" button, use the following code within your TfIbSQL.btConnectClick method (fIbSQL being the name of my form, btConnect the name of my button):
    { Button "Connect": Connect to "Tutorial" databse }
    procedure TfIbSQL.btConnectClick(Sender: TObject);
    begin
        if dbIbSQLConnection.Connected then
            dbIbSQLConnection.Close;
        // Set user name and password
        // Other connection params have been set in dbIbSQLConnection propery sheet
        dbIbSQLConnection.UserName := 'ALLU';
        dbIbSQLConnection.Password := 'allu';
        // Connect to "Tutorial" database
        try
            dbIbSQLConnection.Open;
            edMess.Text := 'Connection to InterBase database "Tutorial" = OK!';
        except
            on E: ESQLDatabaseError do
                edMess.Text := E.Message;
        end;
    end;
When pushing the "Connect" button, the error message Cannot load the default Firebird clients pops up.
Lazarus/Free Pascal and InterBase - Client DLL not found
This problem is rather common when connecting with Lazarus/Free Pascal to a database server. The simplest way to solve the issue is to put the client DLL in the Lazarus project folder (or, more generally, together with the executable). The 64-bit InterBase client is called ibclient64.dll and you find it in the "bin" subdirectory of the InterBase installation folder. Copy the DLL to your project folder, and (important to know!), rename the DLL to fbclient.dll.
The disconnection from the database may be coded within the TfIbSQL.btExitClick method (activated when pushing the "Exit" button):
{ Button "Exit": Disconnect from databse and exit application }
    procedure TfIbSQL.btExitClick(Sender: TObject);
    begin
        if dbIbSQLConnection.Connected then
            dbIbSQLConnection.Close;
        Close;
    end;
To read and display the number of records in the "employee" table of the "Tutorial" database, by pushing the "Query" button, use the following code within your TfIbSQL.btQueryClick method:
    { Button "Query": Display number of records in "employee" table }
    procedure TfIbSQL.btQueryClick(Sender: TObject);
    var
        Count: Integer;
    begin
        if dbIbSQLConnection.Connected then begin
            // Query the database
            dbSQLQuery.SQL.Text := 'SELECT count(*) FROM employee';
            try
                dbSQLQuery.Open;
                if dbSQLQuery.EOF then
                    Count := 0
                else
                    Count := dbSQLQuery.Fields[0].AsInteger;
                dbSQLQuery.Close;
                // Display the query result
                edEmployees.Text := IntToStr(Count);
            except
                on E: ESQLDatabaseError do
                    edMess.Text := E.Message;
            end;
        end;
    end;
The screenshot shows the application after the "Query" button has been pushed: successful query with the display of the number of employees.
Lazarus/Free Pascal and InterBase - Successful SELECT query
Connecting using a TODBCConnection object.
The download archive of InterBase Developer Edition includes the installer of the InterBase ODBC Driver. In my tutorial Trying out InterBase Developer Edition, I describe, how to install it and how to create a User DSN for the "Tutorial" database.
Using the InterBase ODBC driver with Lazarus/Free Pascal works the same way as using any other ODBC driver. Create a Lazarus application project with the form shown on the screenshot below.
Simple Lazarus/Free Pascal database project with InterBase (using TODBCConnection)
As with TIBConnection, we have 3 database related components: a TSQLTransaction object (I called it "dbSQLTransaction") and a TSQLQuery object (I called it "dbSQLQuery"), as before, and (replacing the TIBConnection object), a TODBCConnection object (that I called "dbODBCConnection").
The configuration of the database components (in their property sheet) is similar as with TIBConnection. The "Transaction" properties of the TODBCConnection object and the TSQLQuery object have to be set to the name of the TSQLTransaction object (in my case: "dbSQLTransaction"). Setting these properties should automatically set the "Database" property of the TSQLTransaction and TSQLQuery objects to the name of the TODBCConnection object's name (in my case: "dbODBCConnection").
The big difference is that the "DatabaseName" property of the TODBCConnection has to be set to the name of the DSN, not to the name of the database that we want to access. If you have a look at the tutorial mentioned above, you see that in my case the DSN name is "InterBase_Tutorial". As all connection settings (host, user, password, etc) have been set when configuring the DSN, no further property setting is required (we can let empty the corresponding items in the property sheet and no properties need to be added in the source code).
Apart from the fact that I named the new form "fIbSQL2" (instead of "fIbSQL"), the source code of the ODBC version of our program is exactly the same as the code of the TIBConnection version, except for the connection method "TfIbSQL2.btConnectClick". Here is the code of the ODBC version:
    { Button "Connect": Connect to "Tutorial" databse }
    procedure TfIbSQL2.btConnectClick(Sender: TObject);
    begin
        if dbODBCConnection.Connected then
            dbODBCConnection.Close;
        // Connection parameters are set in DSN
        // Connect to the employee database
        try
            dbODBCConnection.Open;
            edMess.Text := 'Connection to InterBase database "Tutorial" = OK!';
        except
            on E: ESQLDatabaseError do
                edMess.Text := E.Message;
        end;
    end;
As the path to the client is set in the DSN, no need here to copy the client DLL to the project folder. The program output (except for possible error messages) is, of course, the same as with the TIBConnection.

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