Computing: Free Pascal Programming

Using Firebird databases with Lazarus/Free Pascal.


This tutorial is about the access to a Firebird 4.0 (64bit) database from a Lazarus/Free Pascal application; I actually use Lazarus 2.2.6 64bit (with FPC 3.2.2) on Windows 10. The tutorial should also apply to other versions of Firebird, Lazarus and Windows. To note that the tutorial supposes that you have installed Firebird in client/server (not in embedded) mode.
The tutorial covers the very basics of using Firebird databases from within a Lazarus/Free Pascal application, primarily how to connect to a database. In fact, working with Firebird 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 "employee" sample database, installed by default with Firebird 4,0 (Windows x64 installer). It shows, how to implement a simple Lazarus application, reading and displaying the number of customers in the "customer" table using a TIBConnection object (if for one reason or another you prefer to connect via ODBC, please have a look at the ODBC part of my Web development environment setup on MS Windows: Firebird database server tutorial in the Website and Database Programming section of my site). Click the following link to download the Lazarus/Free Pascal source code of the TIBConnection based application.
Create a Lazarus application project with the form shown on the screenshot below.
Simple Lazarus/Free Pascal database project with Firebird
You can see on the screenshot that there are 3 database specific components (available in the Lazarus SQLdb menu) that have been added to the form:
  • TIBConnection (that I named "dbFBSQLConnection"): this is the component, that takes the requests of the TSQLQuery and TSQLTransaction components and translates them into requests specifically tailored for the Firebird 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 (e.g. INSERT INTO, stored procedures...).
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.
Concerning the TIBConnection object, I'll include the database specific information (in particular the database name) within the code. In the dbFBSQLConnection property sheet, I just set "Transaction" to the name of the transaction object, in our case "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 TIBConnection object's name, in our case "dbFBSQLConnection". The "Database" property of the TSQLTransaction object should now also be set to the name of the TIBConnection object's name.
Lazarus/Free Pascal database project with Firebird: The TIBConnection component
Connecting to the database.
To connect to the Firebird "employee" database by pushing the "Connect" button, use the following code within your TfFbSQL.btConnectClick method (fFbSQL being the name of my form, btConnect the name of my button):
    procedure TfFbSQL.btConnectClick(Sender: TObject);
    begin
        if dbFBSQLConnection.Connected then
            dbFBSQLConnection.Close;
        // Set the connection parameters
        dbFBSQLConnection.HostName := 'localhost';
        dbFBSQLConnection.Port := 3050;
        dbFBSQLConnection.DatabaseName := 'employee';
        dbFBSQLConnection.UserName := 'nemo';
        dbFBSQLConnection.Password := 'nemo';
        // Connect to the employee database
        try
            dbFBSQLConnection.Open;
            edMess.Text := 'Connection to Firebird database "employee" = OK!';
        except
            on E: ESQLDatabaseError do
                edMess.Text := E.Message;
        end;
    end;
The connection to the Firebird database should normally succeed without that you have to put the client DLL (called fbclient.dll) into your project output folder (as you have to do with MySQL, for example). In fact, the Firebird installer does the copy to C:\Windows\System32 and C:\Windows\SysWOW64 by default and thus the DLL should be found without that you have to take any manual action.
Setting the hostname and the port are optional. If not set, the default values (hostname = localhost and port = 3050) will be used. Please, note that if you want to try out the application with your own Firebird server, you'll have to either create user "nemo", or change user name and password in the TfFbSQL.btConnectClick procedure.
The screenshots show the application after the "Connect" button has been pushed: on the left the situation where the Firebird server is offline; on the right a successful connection.
Lazarus/Free Pascal database project with Firebird: Connection failure because the server is offline
Lazarus/Free Pascal database project with Firebird: Successful connection to the server
Disconnecting from the database.
The disconnection from the database may be coded within the TfFbSQL.btExitClick method (activated when pushing the "Exit" button):
    procedure TfFbSQL.btExitClick(Sender: TObject);
    begin
        if dbFBSQLConnection.Connected then
            dbFBSQLConnection.Close;
        Close;
    end;
Querying the database.
To read and display the number of records in the "customer" table of the "employee" database, by pushing the "Query" button, use the following code within your TfFbSQL.btQueryClick method:
    procedure TfFbSQL.btQueryClick(Sender: TObject);
    var
        Count: Integer;
    begin
        if dbFBSQLConnection.Connected then begin
            // Query the database
            dbSQLQuery.SQL.Text := 'SELECT count(*) FROM customer';
            try
                dbSQLQuery.Open;
                if dbSQLQuery.EOF then
                    Count := 0
                else
                    Count := dbSQLQuery.Fields[0].AsInteger;
                dbSQLQuery.Close;
                // Display the query result
                edCustomers.Text := IntToStr(Count);
            except
                on E: ESQLDatabaseError do
                    edMess.Text := E.Message;
            end;
        end;
    end;
The screenshot shows our application after the "Query" button has been pushed: successful query with the display of the number of customers.
Lazarus/Free Pascal database project with Firebird: Successful SELECT query

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