Computing: Free Pascal Programming

Using Oracle Database with Lazarus/Free Pascal.

The famous database from Oracle is available for free for Personal Computers. This tutorial is about the access of Oracle Database 23ai Free from a Lazarus/Free Pascal application. It is supposed that you have installed Oracle Database (and, if you want to use the tutorial sample application as such, the Oracle Sample Database) on your computer; for help, please, have a look at my tutorial Web development environment setup on MS Windows: Oracle database server. The sample application has been developed and tested on Windows 11 Professional; it should also work with the latest releases of Windows 10 Professional (Oracle Database is not supported on Windows Home Edition!). I used Lazarus 3.0 64-bit (with FPC 3.2.2); other, in particular newer versions of Lazarus, should also work fine; the same should be the case for newer versions of Oracle Database.

Accessing Oracle Database from a Lazarus/Free Pascal application has nothing complicated. Lazarus comes with the TOracleConnection component, and using it together with a TSQLTransaction and a TSQLQuery component is more or less the same coding as using MySQL with a TMySQLConnection.

The tutorial shows, how to implement a simple Lazarus application, reading and displaying the last name, first name and job title of accountant employees in the Oracle Sample Database "employees" table. It is supposed that you are familiar with Lazarus and Free Pascal. The tutorial details are limited to the database specific aspects. Click the following link if you want to download the Lazarus/Free Pascal source code of the sample application.

The screenshot below shows the form layout of the application. The program should connect to Oracle Database at startup, and fill in the string grid with the employee data when the "Query" button is pushed. The edit field at the left of the "Query" button is used for success/error messages.

Simple Lazarus database project with Oracle Database: Application form

The components allowing access to a variety of databases can be found in the Lazarus SQLdb menu. There are 3 of them, that we need to connect to Oracle Database:

Add theses 3 components to the form as you do with any other items from the Lazarus menus. As you can see on the screenshot, I renamed them respectively to dbSQLTransaction, dbSQLQuery, and dbOracleConnection.

We can "connect" these 3 components by filling in some data into their property sheets.

As I said above, the application should connect to the Oracle Sample Database when it starts up. Thus, the connection related code has to be part of the TfOracle.FormCreate method ("fOracle" being the name of my form). Here is the source:
    procedure TfOracle.FormCreate(Sender: TObject);
    begin
        if dbOracleConnection.Connected then
            dbOracleConnection.Close;
        // Set the connection parameters
        dbOracleConnection.HostName := 'localhost';
        dbOracleConnection.UserName := 'aly';
        dbOracleConnection.Password := 'oracle';
        dbOracleConnection.DatabaseName := 'FREEPDB1';
        // Connect to the database
        try
            dbOracleConnection.Open;
            edMess.Color := clDefault;
            edMess.Text := 'Connected to Oracle SAMPLE database';
        except
            on E: ESQLDatabaseError do begin
                edMess.Color := clRed;
                edMess.Text := E.Message;
            end;
        end;
    end;

To connect to the database, use the method TOracleConnection.Open. Obvious, that before doing so, you have to set the connection parameters. I do it here in the program source; an alternative is to do it in the component's property sheet. Making the method part of a try ... except statement, we can use ESQLDatabaseError to catch the error message in the case where the connection fails.

The employees, who work in accounting, should be displayed when we push the "Query" button. Here is the source of the TfOracle.btQueryClick method ("fOracle" being the name of my form; "btQuery" being the name of the button):
    procedure TfOracle.btQueryClick(Sender: TObject);
    var
        Count: Integer;
        Query: string;
    begin
        if dbOracleConnection.Connected then begin
            sgEmployees.Clean(0, 1, 2, 8, []);
            // Create query
            Query := 'SELECT last_name AS LName, first_name AS FName, job_title AS Job FROM employees ';
            Query += 'WHERE job_title LIKE ''%Account%'' ';
            Query += 'ORDER BY last_name, first_name';
            // Query the database
            dbSQLQuery.SQL.Text := Query; Count := 0;
            try
                dbSQLQuery.Open;
                dbSQLQuery.First;
                while not dbSQLQuery.EOF do begin
                    Inc(Count);
                    sgEmployees.Cells[0, Count] := dbSQLQuery.FieldByName('LName').AsString;
                    sgEmployees.Cells[1, Count] := dbSQLQuery.FieldByName('FName').AsString;
                    sgEmployees.Cells[2, Count] := dbSQLQuery.FieldByName('Job').AsString;
                    dbSQLQuery.Next;
                end;
                dbSQLQuery.Close;
                // Display count of employees
                if Count = 0 then
                    edMess.Text := 'No employees found in database!'
                else
                    edMess.Text := 'Number of accounting employees in database = ' + IntToStr(Count);
                edMess.Color := clDefault;
            except
                on E: ESQLDatabaseError do begin
                    edMess.Text := E.Message;
                    edMess.Color := clRed;
                end;
            end;
        end;
    end;

To execute a SELECT query, set the TSQLQuery.SQL.Text property to the SQL statement, then use the method TSQLQuery.Open to start the query. The rows of the result dataset can be retrieved using the methods TSQLQuery.First and TSQLQuery.Next. As before, ESQLDatabaseError can be used to catch the error message if the query fails.

Finally, when pushing the "Exit" button, the application should disconnect from the database. Here is the source of the TfOracle.btExitClick method ("fOracle" being the name of my form; "btExit" being the name of the button):
    procedure TfOracle.btExitClick(Sender: TObject);
    begin
        if dbOracleConnection.Connected then
            dbOracleConnection.Close;
        Close;
    end;

The screenshot on the left shows how I tried to access the database using a bad password. The screenshot on the right shows the successful query of the accountant employees' data.

Simple Lazarus database project with Oracle Database: Trying to connect with a bad password
Simple Lazarus database project with Oracle Database: Successful SELECT query

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