Computing: Free Pascal Programming

Working with DBF (dBASE) files.

Before starting this tutorial, here some important facts to know:

This tutorial is about reading data from a DBF file and creating dBase tables and indices from a Lazarus application. The program samples (click the following link to download the source code) have been tested on Windows 11 (Lazarus 3.2, 64bit) and Windows 2000 (Lazarus 3.2, 32bit). The DBF file, used in the first application, is an export to dBase III+ of a sample included with Visual dBase 7 (the DBF file is included in the download archive). The second application creates a level 7 DBF file (that is correctly opened in DBF Viewer Plus, version 1.74).

Installing DBF Viewer Plus.

You don't need any other software to create or read DBF files with a Lazarus application. But, installing a DBF viewer/editor may be helpful. DBF Viewer Plus is one of the rare DBF viewers that is freeware; most applications that are called free on some "Best DBF viewers" website are just free trials. The application may not only be used to view dBase tables or search for given data, but also to create them, alter their content, export the data... You can download the software from Alex Nolan's website. The download file is the application itself. Store it in Program Files, or similar directory; if you want, you can manually create a shortcut on your desktop, or in the Windows Start menu.

When I tried to run the application on Windows 11, the program aborted with the message There is no default printer currently selected. This is the case on a Windows system with a single printer, that is managed by Windows. To set the default printer, open Settings > Bluetooth & Devices > Printers & Scanners. Set "Let Windows manage my default printer" from on to off. The word "default" will now appear beneath the "Microsoft Print to PDF" printer.

Manually selecting a default printer on Windows 11

The screenshot below shows the table customer.dbf (dBASE III+ export of a sample table included with Visual dBase 7) opened in DBF Viewer Plus. Choosing Table > Table info from the menu bar, lets you view the table structure (field definitions).

dBase III+ table opened in the DBF Viewer Plus freeware application

Reading data from a DBF file.

Create the project "DBase" with a form layout as shown on the screenshot below. The dBase related component is called TDbf and you can find it in the Data Access menu of the components bar. I named my TDbf object "dbfCustomer".

To access a dBase table, there are 3 properties of the TDbf object that have to be set:

  1. FilePathFull: Full path to the directory, where the DBF file is located; when you set this property, the FilePath property is or may be set accordingly (screenshot).
  2. TableLevel: The level corresponding to the dBase version that was used to create the DBF file; in our case, level 3, corresponding to dBase III+.
  3. TableName: Name of the DBF file (including the extension); in our case: customer.dbf.

Simple Lazarus dBase appliaction: Reading a DBF file [1]

Note: To use the sample application on your computer, you have to change the path of the DBF file, of course.

To open a DBF file we use the TDbf.Open method, to close it, we use the method TDbf.Close. In my sample, the file is opened when the application is started, and closed when the user pushes the "Exit" button. Here is the code of the TfDBase.FormCreate method ("fDBase" being the name of my form) and the TfDBase.btExitClick method ("btExit" being the name of my "Exit" button):

    { Application start: Open dBASE file (CUSTOMER table) }
    procedure TfDBase.FormCreate(Sender: TObject);
    begin
        dbfCustomer.Open;
    end;

    { Button "Exit" clicked: Close dBASE file and exit application }
    procedure TfDBase.btExitClick(Sender: TObject);
    begin
        dbfCustomer.Close;
        Close;
    end;

Reading data from a DBF file is easy. Just use the following code (general format):

    <TDbf-object-name>.First;
    while not <TDbf-object-name>.EOF do begin
        ... retrieve the data of actual record and do something with it ...
        <TDbf-object-name>.Next;
    end;

I would say that the simplest way to retrieve the data of the actually read record is to use the TDbf.FieldByName method, that requires the column-name of the concerned field as argument and that returns the actual value. To assign this value to a Pascal variable, you'll have to use a type qualifier; e.g. AsInteger, AsFloat. Here is, for example, the code to retrieve the customer name from the customer.dbf table records (the column name in customer.dbf being "NAME" and "sCustomer" being a string variable):

    sCustomer := dbfCustomer.FieldByName('NAME').AsString;

Proceeding this way, we would read the entire table, what is not really the sense of a database application. In SQL based databases, we use a SELECT query to tell the database engine what records we actually want (in our case, data concerning customers from a given country). In dBase related applications, we select given records by using a filter. Filters are defined by setting the Filter property to a logical expression made up of compare operations; they become only active if the property Filtered is set to True. The property FilterOptions may be used to set further filter properties. Example: to use the value in the string variable "sCountry" to search for customers from a given country (i.e. customers for whom the table column COUNTRY equals a given country name), and if we want the search to be case-insensitive, we can use the following code (note the usage of the function QuotedStr to quote string values):

    dbfCustomer.FilterOptions := [foCaseInsensitive];
    dbfCustomer.Filter := 'COUNTRY=' + QuotedStr(sCountry);
    dbfCustomer.Filtered := True;

Note: If we compile a program using this code, we'll get the error message Identifier not found: foCaseInsensitive. The reason for this is that by adding the TDbf component to the form, the only unit added to the uses clause is "Dbf", and this constant is actually defined in unit "db". For other definitions, the unit "Dbf_Common" might be needed. To include everything, manually change the uses clause to

    uses Dbf, db, Dbf_Common;

The logical expressions used in filters may include the 4 basic arithmetic operators ("+" may also be used to concatenate two strings), the compare operators, of course, the logical operators NOT, AND, and OR, and the following functions:

    STR(num[,size,precision]) Converts a number to a string with optional size and precision;
    DTOS(date) Converts date to a string with representation YYYYMMDD;
    SUBSTR(str,index,count) Extracts a substring from a string, from position "index" and length "count";
    UPPER(str) Returns the uppercase equivalent of a string;
    LOWER(str) Returns the lowercase equivalent of a string.

If the filter concerns strings, beside full matches, you can create partial matches using the asterisk symbol. Example: filter all customers whose name starts with "A":

    dbfCustomer.Filter := 'NAME = "A*"';

Our sample application searches for the customers of the country that the users have entered into the corresponding edit field. Search is initiated when the "Search" button (I called it "btSearch") is pushed. Here is the code:

    { Button "Search" clicked: Search for customers from given country }
    procedure TfDBase.btSearchClick(Sender: TObject);
    begin
        sCountry := edCountry.Text;
        if sCountry <> '' then begin
            edCustomers.Lines.Clear; N := 0;
            // Selection filter on table field COUNTRY
            dbfCustomer.FilterOptions := [foCaseInsensitive];
            dbfCustomer.Filter := 'COUNTRY=' + QuotedStr(sCountry);
            dbfCustomer.Filtered := True;
            // Read first record
            dbfCustomer.First;
            // If there was a record, continue reading until end of file
            while not dbfCustomer.EOF do begin
                sCustomer := dbfCustomer.FieldByName('NAME').AsString;
                sCustomer += ' (' + dbfCustomer.FieldByName('CITY').AsString + ')';
                // Display customer info
                edCustomers.Append(sCustomer);
                // Increment customers count
                Inc(N);
                // Read next record
                dbfCustomer.Next;
            end;
            // Display number of customers found
            if N = 0 then
                edCustomers.Append('No customers found for country = ' + sCountry)
            else
                edCustomers.Append(LineEnding + 'Number of customers in ' + sCountry + ': ' + IntToStr(N));
        end;
    end;

The screenshots below (taken on Windows 2000) show the execution of the application: case where no customers were found (on the left), and the "successful search" case on the right).

Simple Lazarus dBase appliaction: Reading a DBF file [2]
Simple Lazarus dBase appliaction: Reading a DBF file [3]

Creating an indexed DBF file.

The project "DBase2" is a sample application that shows how to proceed to create a dBase table (DBF file), to create an index for this table (MDX file), and to fill data into the table. This does not cover all aspects of working with DBF files, but should be a good starting point for your own applications. For further details concerning TDbf, please have a look at the Lazarus Tdbf Tutorial in the Free Pascal Wiki.

The screenshot below shows the form layout. As for the first project, we have to use a TDbf component (I called it "dbfEmployee") and we have to set the FilePathFull, the TableLevel, and the TableName properties. The path points to the location, where you want to create the table (please, adapt it to your system), as table format, I chose level 7, and my DBF file should be called employee.dbf.

Simple Lazarus dBase appliaction: Creating a DBF file [1]

You can add the table fields in the TDbf object property sheet. To do this click the three dots at the right of the FieldDefs property. This opens the "Edit dbfEmployee.FieldDefs" window, where you can use the Add button to add the fields. Selecting a field, shows its property sheet, where you can fill in the field name, the field data type, and some other properties of this field. Our table should have 4 columns: ID (integer), Lastname and Firstname (strings) and Salary (float). The screenshot shows the edit window with the 4 fields added and the properties of the ID field displayed.

Simple Lazarus dBase appliaction: Creating a DBF file [2]

Important: To make the fields defined in the TDbf object property sheet to be taken into account when the table is created, you must set the StoreDefs property to True.

Creating the table: With the fields defined in the property sheet, the table creation code is just one statement (call of the TDbf.CreateTable method). In our application, we use the "Create table" button (I called this button "btTable") to do so. Here is the code:

    { Button "Create table": Create table employee.dbf }
    procedure TfDBase2.btTableClick(Sender: TObject);
    // All table properties, including the field definitions have been set in the TDbf object Properties sheet...
    begin
        dbfEmployee.CreateTable;
    end;

If, instead to create the fields in the property sheet, you want to create them during runtime, use the following code:

    with dbfEmployee.FieldDefs do begin
        Add('ID', ftInteger, 0, True);
        Add('Lastname', ftString, 0, True);
        Add('Firstname', ftString, 0, True);
        Add('Salary', ftFloat, 0, 0, True);
    end;

Note: The zeros used as size create fields with the default size of the concerned data type. In a real world application, you would set "real" values, I guess (size and precision, as well as several attributes, may also be set in the field properties sheet).

Creating the index: The index of the table is created using the AddIndex method. Before we can do it, we have to open the DBF file, and as index creation is a crucial operation, we should open the file with exclusive access rights. In our sample, the index is created when the "Create index" button (I called it "btIndex") is pushed. Here is the code:

    { Button "Create index": Add primary key to table employee.dbf }
    procedure TfDBase2.btIndexClick(Sender: TObject);
    begin
        dbfEmployee.Exclusive := True;
        dbfEmployee.Open;
        dbfEmployee.AddIndex('employee_id', 'ID', [ixPrimary]);
        dbfEmployee.Close;
        dbfEmployee.Exclusive := False;
    end;

This creates an index (called "employee_id") on the table field "ID". The index type is primary key: This ensures that you cannot add two records with the same ID.

Adding records: In a real world application, you would add the table by reading it from a text file, or create a form, where users can enter it. In our sample application, we'll just add some data hard-coded within the source. Adding the table data is done when the "Add records" button (I called it "btRecords") is pushed. Here is the code:

    { Button "Add records": Add some data to table employee.dbf }
    procedure TfDBase2.btRecordsClick(Sender: TObject);
    begin
        dbfEmployee.Open;
        dbfEmployee.AppendRecord([100, 'Jones', 'Bob', 4400]);
        dbfEmployee.AppendRecord([110, 'Smith', 'Jessica', 4500]);
        dbfEmployee.AppendRecord([120, 'Lincoln', 'Edward', 3800]);
        dbfEmployee.AppendRecord([130, 'Lincoln', 'Melissa', 3500]);
        dbfEmployee.AppendRecord([140, 'Baxter', 'Tom', 5000]);
        dbfEmployee.Close;
    end;

Searching the table: To test our newly created database, the sample application includes the "Search database" button (I called it btSearch), that searches the employee.dbf table for an ID, entered by the user, and displays their name and salary. Here is the code:

{ Button "Search database": Display data for employee with given ID }
    procedure TfDBase2.btSearchClick(Sender: TObject);
    begin
        if not dbfEmployee.Active then
            dbfEmployee.Open;
        edData.Text := '';
        if edID.Text <> '' then begin
            dbfEmployee.Filter := 'ID=' + edID.Text;
            dbfEmployee.Filtered := True;
            dbfEmployee.First;
            if dbfEmployee.EOF then begin
                edData.Text := 'Not found!';
            end
            else begin
                edData.Text := dbfEmployee.FieldByName('Lastname').AsString;
                edData.Text := edData.Text + ' ' + dbfEmployee.FieldByName('Firstname').AsString;
                edData.Text := edData.Text + ': ' + FloatToStr(dbfEmployee.FieldByName('Salary').AsFloat);
            end;
        end;
    end;

And finally, closing the database and terminating the application, when the "Exit" button is pushed.

    { Button "Exit": Exit application }
    procedure TfDBase2.btExitClick(Sender: TObject);
    begin
        if dbfEmployee.Active then
            dbfEmployee.Close;
        Close;
    end;

The screenshots below show the execution of the application: case where no employee was found (on the left), and the "successful search" case (on the right).

Simple Lazarus dBase appliaction: Creating a DBF file [3]
Simple Lazarus dBase appliaction: Creating a DBF file [4]

We can test the primary key feature by pushing the "Add records" button a second time. The error message is somewhat weird, but the primary key index seems to work all correctly.

Simple Lazarus dBase appliaction: Creating a DBF file [5]

The screenshot below shows the newly created table opened in DBF Viewer Plus. As said at the beginning of the tutorial, level 7 files are recognized as "unknown file type". Our file is indexed (the actual index values are displayed in the last column of the display) and not encrypted. We can see on the screenshot that the default string size is 30 characters, and the default precision for float values is 8 digits. We can also see that we missed some important settings when creating the table: First, we should have set a code page. And second, we should have marked as "required" those fields that cannot be NULL.

Simple Lazarus dBase appliaction: Newly created DBF file opened in DBF Viewer Plus

This terminates my tutorial concerning Lazarus and DBF files. It's an introduction only, but I hope that it's of some help to those, who want to write some Free Pascal program that works with dBase tables. As said above, you can find a more detailed description of TDbf in the Lazarus Tdbf Tutorial in the Free Pascal Wiki.

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