Computing: Free Pascal Programming

Using Microsoft SQL Server databases with Lazarus/Free Pascal.


SQL server is a relational database management solution offered by Microsoft. It can be installed on a workstation or server computer or in the cloud and is primarily used to store and retrieve data it receives from other applications. There are several distributions of SQL Server available: Two of them, SQL Server Express Edition and SQL Server Developer Edition are freely available at the Microsoft site. The Microsoft documentation webpage Using SQL Server in Windows shows which version of the RDBMS may be used on which version of Windows. As I run MSSQL on Windows 8.1 Pro, 64bit, where SQL Server 2019 is not supported, this tutorial uses SQL Server 2017 Developer Edition (with Lazarus 2.2.0). The tutorial presupposes that you have SQL Server and Lazarus installed on your machine and that you know how to work with them.
The tutorial covers the very basics of using SQL Server databases from within a Lazarus/Free Pascal application, primarily how to connect to an MSSQL database. In fact, working with SQL Server is quite the same than 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. This tutorial uses the BikeStores sample database from the sqlservertutorial.net website. Please, download the files and create the database, as explained on their webpage.
The tutorial shows, how to implement a simple Lazarus application, reading and displaying the number of customers in the "sales.customers" table of the "BikeStores" database using a TMSSQLConnection object (connecting via ODBC not being covered in this text). 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.
Simple Lazarus/Free Pascal database project with Microsoft SQL Server
There are 3 database specific components (available in the Lazarus SQLdb menu) to be added to the form:
  • TMSSQLConnection (that I named "dbConn"): this is the component, that takes the requests of the TSQLQuery and TSQLTransaction components and translates them into requests specifically tailored for the database that you are using.
  • TSQLTransaction (that I named "dbTrans"): 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 "dbQuery"): 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...).
Concerning the TMSSQLConnection object, I'll include the database specific information (host name, database name, user name and password) within the code. In the dbConn property sheet, I just set "Transaction" to the name of the transaction object, in our case "dbTrans".
Lazarus/Free Pascal database project with MSSQL: The TMSSQLConnection component
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 TMSSQLConnection object's name, in our case "dbConn". The "Database" property of the TSQLTransaction object should now also be set to the name of the TMSSQLConnection object's name.
Lazarus/Free Pascal database project with MSSQL: The TSQLQuery component
Connecting to the database.
To connect to SQL Server by pushing the "Connect" button, use the following code within your TfMSSQL.btConnectClick method (fMSSQL being the name of my form, btConnect the name of the button):
    procedure TfMSSQL.btConnectClick(Sender: TObject);
    begin
        if dbConn.Connected then
            dbConn.Close;
        // Set the connection parameters
        dbConn.HostName := 'localhost';
        dbConn.UserName := 'nemo';
        dbConn.Password := 'nemo';
        dbConn.DatabaseName := 'BikeStores';
        // Connect to the BikeStores database
        try
            dbConn.Open;
            edMess.Text := 'Connection to SQL Server database "BikeStores" = OK!';
        except
            on E: ESQLDatabaseError do
                edMess.Text := 'Connection to SQL Server database "BikeStores" FAILED!';
        end;
    end;
When you run the application and try to connect, you will probably get the error condition, shown on the screenshot on the left. The message (here with the German version of Lazarus) tells us that the DB-Lib client library dblib.dll can't be loaded. This means that dblib.dll cannot be found (similarly as for libmysql.dll with MySQL); in fact you'll have to copy it to the directory that contains the Free Pascal executable. Rerunning the application with dblib.dll in the project's output directory, will result in the error condition, shown on the screenshot on the right. The message says that libiconv.dll is missing on the computer. The reason, why this error occurs, simply is that dblib.dll requires libiconv.dll to work correctly.
Lazarus/Free Pascal database project with MSSQL: Error because of missing library dblib.dll
Lazarus/Free Pascal database project with MSSQL: Error because of missing library libiconv.dll
You can find the missing DLLs on the Free Pascal download site. Be sure to pick the correct file! For a 64bit Windows, the file is actually called dblib_x64.zip. Unzip the archive and browse to the Win64 subfolder. Here, you'll find both dblib.dll and libiconv.dll. Copy the two DLLs into your application's output directory, i.e. put them together with the executable.
The connection with the database server should now be possible. Below 2 screenshots, the first with a connection failure due to the fact, that SQL Server wasn't running, the second one with the connection being established. In the case of connection failure, instead of displaying a hard coded message, it would be more useful to display the value of E.Message (ESQLDatabaseError). As a difference with MySQL, E.Message, returned by SQL Server, contains a detailed description what went wrong.
Lazarus/Free Pascal database project with MSSQL: MySQL Server connection failure (server down)
Lazarus/Free Pascal database project with MSSQL: MySQL Server connection success
Disconnecting from the database.
The disconnection from the database may be coded within the TfMSSQL.btExitClick method (activated when pushing the "Exit" button):
    procedure TfMSSQL.btExitClick(Sender: TObject);
    begin
        if dbConn.Connected then
            dbConn.Close;
        Close;
    end;
Querying the number of customers.
The number of customers in the "sales.customers" table of the "BikeStores" database should be read from SQL Server when the "Query" button is pushed, so has to be coded within the TfMSSQL.btQueryClick method. To define the SQL statement, to query the database, as well as to retrieve the data, that SQL Server returns, properties and methods of the TSQLQuery object may be used. In particular, dbQuery.SQL.Text to define the SQL SELECT. Here the code. To note, that it is independent of the connection method used (TXXXConnection or TODBCConnection); this also means that the code, working for MySQL, will work with SQL Server (except, if the SQL contains statements that are specific for a given RDBMS).
    procedure TfMSSQL.btQueryClick(Sender: TObject);
    var
        Count: Integer;
    begin
        edCount.Text := '';
        if dbConn.Connected then begin
            // Query the database
            dbQuery.SQL.Text := 'SELECT COUNT(*) FROM sales.customers';
            try
                dbQuery.Open;
                if dbQuery.EOF then
                    Count := 0
                else
                    Count := dbQuery.Fields[0].AsInteger;
                dbQuery.Close;
                // Display the query result
                edCount.Text := IntToStr(Count);
            except
                on E: ESQLDatabaseError do
                    edMess.Text := E.Message;
            end;
        end;
    end;
The screenshot shows a successful query, with the number of customers in the sales.customers table returned.
Lazarus/Free Pascal database project with MSSQL: Successful query execution
If you find this text helpful, please, support me and this website by signing my guestbook.