Reading from and writing to Visual FoxPro tables.
"FoxPro, originally from Fox Software and later Microsoft, is a relational database that clones the functionality of dBase IV, but offers vast speed improvements. It was based on Fox Software's FoxBASE (a dBASE II clone) and FoxBASE+ (a dBase III Plus clone). FoxPro 3 adds the word 'Visual' to the name". That's how they describe the product on WinWorld. Being available for DOS, Windows, Mac OS and Unix at the beginning, the later versions were Windows only software.
Visual FoxPro databases are primarily made of tables stored as DBF files (plus optional index files). This is the file format that is also used by dBASE. The following dBASE tables related facts (cf. my tutorial Working with DBF (dBASE) files apply to Visual FoxPro, too.
- You don't neither need the Visual FoxPro development environment, nor the Visual FoxPro runtime to work with VFP tables. There are several DBF viewers available; most of them also allow to create and manage VFP tables and their data. The freeware DBF Viewer Plus, mentioned in my dBASE tutorial, works fine with Visual FoxPro 9 tables on my Windows 11 (it seems, however, not recognize the .cdx index files?).
- Lazarus supports access to DBF files thanks to the TDbf component. It is included with Lazarus by default, so no need to install any new packages. Lazarus is said to support DBF files of level 30, the DBF file format level used by Visual FoxPro. This fully applies to VFP 9; reading data from such tables on my Windows 11 works correctly, as does writing data to such tables.
This tutorial includes two sample programs, the first one showing how to handle data searches with multiple rows return, the second one showing how to append, delete, and update records in a Visual FoxPro table. The programs have been written and tested on Windows XP (where I have VFP 9 installed; cf. Installing Visual FoxPro on Windows XP). The sources can be build without modifications (you'll have to set the path to the DBF file as is on the system where the build is done, of course) on Windows 11, where the executables created also run all correctly.
The table used in the examples is the file products.dbf (and the associated index products.cdx), that I created by exporting the data of the corresponding table of the VFP 9 NorthWind database (cf. the Windows XP tutorial). To note, that reading the data from the original table also works correctly.
The screenshot below shows the content of this table opened in DBF Viewer Plus on Windows 11.
 |
Searching a table for given data.
The program sample "FoxPro" searches and displays the products for a given supplier ID, entered by the user. The screenshot shows the project opened in Lazarus. As said above, we access DBF files using a TDbf object (I called my object "dbfProducts"). The files related information can be set tn the object's property sheet. Use the "browse" button at the right of the TableName property and browse to the table (DBF file). This automatically sets the properties FilePath and FilePathFull. Important: You have also to indicate the name of the index file, filling in the property IndexName (table and index file having to be located in the same directory).
 |
Here is the code of the Lazarus project main unit.
unit foxpro_main;
{$mode objfpc}{$H+}
interface
uses
Classes, SysUtils, Forms, Controls, Graphics, Dialogs, StdCtrls, dbf, db;
type
TfFoxPro = class(TForm)
StaticText2: TStaticText;
edProducts: TMemo;
Label1: TLabel;
edSupplier: TEdit;
btSearch: TButton;
btExit: TButton;
dbfProducts: TDbf;
procedure FormCreate(Sender: TObject);
procedure btSearchClick(Sender: TObject);
procedure btExitClick(Sender: TObject);
private
iSupplier, N: Integer;
sProduct: string;
end;
var
fFoxPro: TfFoxPro;
implementation
{$R *.lfm}
{ Application start: Open FoxPro file (PRODUCTS table) }
procedure TfFoxPro.FormCreate(Sender: TObject);
begin
dbfProducts.Open;
dbfProducts.OpenIndexFile('products.cdx');
end;
{ Button "Search" clicked: Search for products from given supplier }
procedure TfFoxPro.btSearchClick(Sender: TObject);
begin
if edSupplier.Text <> '' then begin
iSupplier := StrToInt(edSupplier.Text);
edProducts.Lines.Clear; N := 0;
// Selection filter on table field SUPPLIERID
dbfProducts.Filter := 'supplierid=' + IntToStr(iSupplier);
dbfProducts.Filtered := True;
// Read first record
dbfProducts.First;
// If there was a record, continue reading until end of file
while not dbfProducts.EOF do begin
sProduct := dbfProducts.FieldByName('productnam').AsString;
edProducts.Append(sProduct);
Inc(N);
dbfProducts.Next;
end;
if N = 0 then
edProducts.Append('No products found for supplier = ' + IntToStr(iSupplier))
else
edProducts.Append(LineEnding + 'Number of products by supplier (' + IntToStr(iSupplier) + '): ' + IntToStr(N));
end;
end;
{ Button "Exit" clicked: Close FoxPro file and exit application }
procedure TfFoxPro.btExitClick(Sender: TObject);
begin
dbfProducts.CloseIndexFile('products.cdx');
dbfProducts.Close;
Close;
end;
end.
I think that the code is understandable without further explanations. Important to note that each time you open a table (DBF file), you must also open the associated index (CDX file)!
The screenshot shows the output of an execution of the application.
 |
Appending, deleting, and updating table data.
The program sample "FoxPro2" can be used to display the data of a given product, to add a new product to the table, to delete an existing one, or to modify the data of a given product. Depending on the operation to perform, the user has to enter the product ID and/or the product information. The screenshot shows the project form in the Lazarus IDE.
 |
In the original products.dbf table (part of the NorthWind database), the field "productid" is an autoincrement integer, and there is a primary key index on that field. When creating the indexes on my exported table (cf. the Windows XP tutorial), I first chose to use a cardinal index (i.e. an index that does not allow duplicates) on the "productid" field. However, this resulted somehow in problems, and I decided to use a regular index (i.e. an index that may have duplicates). Due to the fact that "productid" is an autoincrement, this shouldn't be a problem. However, I did not figure out how to add a record without specifying a value for the "productid" field. The application uses the following work-around: When a new record is appended, the product ID, entered by the user, is ignored, and the application uses the last product ID in the table + 1. This should ensure that there aren't any duplicates. The only problem, that might occur, is if the user deletes the last record in the table. In this case, the following record insertion will be done using the product ID of the deleted record (or of some other record deleted before). As a record deletion does not physically remove the record from the table, there will be two table entries with this product ID: a "regular" record, accessible by a normal selection, and a deleted record. This is not a real issue, however, to be sure to avoid all problems, you should never delete the last record (the record with the highest product ID).
To determine the product ID of a new record, the application reads the last record in the table, using the following code:
dbfProducts.Filtered := False;
dbfProducts.Last;
iLastRec := dbfProducts.FieldByName('productid').AsInteger;
and then increments the value read by 1.
Besides the ID of the next product to be appended, the application displays the number of physical and logical records in the table (number of logical records = number of physical records - number of deleted records). These values may be retrieved using the methods TDbf.PhysicalRecordCount resp. TDbf.ExactRecordCount. Be sure to remove all filters before calling the second one of these methods!
Here is the code of a procedure that displays the number of records and the program ID of the next record to be appended ("fFoxPro2" being the name of my form):
procedure RecsDisplay(LastRec: Integer);
begin
fFoxPro2.dbfProducts.Filtered := False;
fFoxPro2.laPhysical.Caption := 'Physical records = ' + IntToStr(fFoxPro2.dbfProducts.PhysicalRecordCount);
fFoxPro2.laLogical.Caption := 'Logical records = ' + IntToStr(fFoxPro2.dbfProducts.ExactRecordCount);
fFoxPro2.laProduct.Caption := 'New product ID = ' + IntToStr(LastRec + 1);
end;
At application start, we open the DBF and the related CDX files (table and related index), read the last record in the table to get the highest product ID value, and then call the procedure above to display the record number and the ID of the next product to be appended.
procedure TfFoxPro2.FormCreate(Sender: TObject);
begin
dbfProducts.Open;
dbfProducts.OpenIndexFile('products.cdx');
// Read last record in order to get next product ID for appending new records
dbfProducts.Filtered := False;
dbfProducts.Last;
iLastRec := dbfProducts.FieldByName('productid').AsInteger;
RecsDisplay(iLastRec);
end;
The end of application code (user pushing the "Exit" button) is the same as in the first program sample.
procedure TfFoxPro2.btExitClick(Sender: TObject);
begin
dbfProducts.CloseIndexFile('products.cdx');
dbfProducts.Close;
Close;
end;
Searching for a product with a given ID (when the user pushes the "Search" button) is similar as in the first program sample, but simpler, as there is only one single record returned.
procedure TfFoxPro2.btSearchClick(Sender: TObject);
begin
if edID.Text <> '' then begin
edName.Text := ''; edCategory.Text := ''; edSupplier.Text := '';
edQuantity.Text := ''; edPrice.Text := '';
iProductID := StrToInt(edID.Text);
// Selection filter on table field PRODUCTID
dbfProducts.Filter := 'productid=' + IntToStr(iProductID);
dbfProducts.Filtered := True;
// Read record
dbfProducts.First;
// If there is a record, display product data
if not dbfProducts.EOF then begin
sProductName := dbfProducts.FieldByName('productnam').AsString;
iCategory := dbfProducts.FieldByName('categoryid').AsInteger;
iSupplier := dbfProducts.FieldByName('supplierid').AsInteger;
sQuantity := dbfProducts.FieldByName('quantitype').AsString;
rPrice := dbfProducts.FieldByName('unitprice').AsCurrency;
edName.Text := sProductName;
edCategory.Text := IntToStr(iCategory);
edSupplier.Text := IntToStr(iSupplier);
edQuantity.Text := sQuantity;
edPrice.Text := FloatToStrF(rPrice, ffCurrency, 0, 2);
edPrice.Text := StringReplace(edPrice.Text, '€', '$', []);
end
else
MessageDlg('Products table', 'Product not found!', mtWarning, [mbOK], 0);
end
else
MessageDlg('Products table', 'Missing product ID!', mtError, [mbOK], 0);
end;
Note: To display the price, I use the ffCurrency format of the FloatToStrF function. This adds the locale dependent currency sign to the float number. As the values in the table are in US dollars, and on my Windows 11, with locale = Luxembourg, the currency symbol is "€", I use the StringReplace function to transform "€" to "$".
Adding a record to the table (when the user pushes the "Add" record) may be done using the TDbf.AppendRecord method, the method's parameter being an array containing the values of the different record fields. The application issues an error message if there isn't a value for some field, except for the product ID field, that is supposed to be empty (as its value is set by the application).
procedure TfFoxPro2.btAddClick(Sender: TObject);
begin
if (edName.Text <> '') and (edCategory.Text <> '') and (edSupplier.Text <> '') and
(edQuantity.Text <> '') and (edPrice.Text <> '') then begin
if (edID.Text <> '') then begin
MessageDlg('Products table', 'Product ID will be set by application!', mtWarning, [mbOK], 0);
edID.Text := '';
end;
sProductName := edName.Text; iCategory := StrToInt(edCategory.Text); iSupplier := StrToInt(edSupplier.Text);
sQuantity := edQuantity.Text;
sPrice := StringReplace(edPrice.Text, ' $', '', []);
rPrice := StrToFloat(sPrice);
Inc(iLastRec); edID.Text := IntToStr(iLastRec);
dbfProducts.AppendRecord([iLastRec, sProductName, iCategory, iSupplier, sQuantity, rPrice]);
RecsDisplay(iLastRec);
end
else
MessageDlg('Products table', 'Missing product data!', mtError, [mbOK], 0);
end;
Note: It's with TDbf.AppendRecord, that I did not figure out what to specify as first array element to let the database set the "productid" value using the autoincrement feature (?).
Deleting a record from the table (when the user pushed the "Delete" button) is done using the TDbf.Delete method, after having set the selection filter to the product ID of the record to be deleted. In fact, the application first searches for this product ID, and does the deletion only if it actually exists (otherwise, it issues an error message).
procedure TfFoxPro2.btDeleteClick(Sender: TObject);
begin
if edID.Text <> '' then begin
edName.Text := ''; edCategory.Text := ''; edSupplier.Text := '';
edQuantity.Text := ''; edPrice.Text := '';
iProductID := StrToInt(edID.Text);
// Selection filter on table field PRODUCTID
dbfProducts.Filter := 'productid=' + IntToStr(iProductID);
dbfProducts.Filtered := True;
// Check if record exists
sProductName := dbfProducts.FieldByName('productnam').AsString;
if not dbfProducts.EOF then begin
// Delete the record
dbfProducts.Delete;
RecsDisplay(iLastRec);
end
else
MessageDlg('Products table', 'Invalid product ID!', mtError, [mbOK], 0);
end
else
MessageDlg('Products table', 'Missing product ID!', mtError, [mbOK], 0);
end;
Modifying a record (when the user pushes the "Modify" button) is done using the TDbf.UpdateRecord method, after having set the selection filter to the product ID of the record to be updated. However, this method may only be called when we are in edit mode (we'll have to call the method TDbf.Edit), and to actually perform the update, the method TDbf.Post has to be called. The application issues an error message if there isn't a value for some field. Before starting the update procedure, it searches for the record with the given product ID, and the update related code is only executed if the record exists (error message otherwise).
procedure TfFoxPro2.btModifyClick(Sender: TObject);
begin
if edID.Text <> '' then begin
if (edName.Text <> '') and (edCategory.Text <> '') and (edSupplier.Text <> '') and
(edQuantity.Text <> '') and (edPrice.Text <> '') then begin
iProductID := StrToInt(edID.Text);
// Selection filter on table field PRODUCTID
dbfProducts.Filter := 'productid=' + IntToStr(iProductID);
dbfProducts.Filtered := True;
// Check if record exists
sProductName := dbfProducts.FieldByName('productnam').AsString;
if not dbfProducts.EOF then begin
// Update the record
dbfProducts.Edit;
dbfProducts.FieldByName('productid').Value := StrToInt(edID.Text);
dbfProducts.FieldByName('productnam').Value := edName.Text;
dbfProducts.FieldByName('categoryid').Value := StrToInt(edCategory.Text);
dbfProducts.FieldByName('supplierid').Value := StrToInt(edSupplier.Text);
dbfProducts.FieldByName('quantitype').Value := edQuantity.Text;
dbfProducts.FieldByName('unitprice').Value := StrToFloat(StringReplace(edPrice.Text, ' $', '', []));
dbfProducts.UpdateRecord;
dbfProducts.Post;
RecsDisplay(iLastRec);
end
else
MessageDlg('Products table', 'Invalid product ID!', mtError, [mbOK], 0);
end
else
MessageDlg('Products table', 'Missing product data!', mtError, [mbOK], 0);
end
else
MessageDlg('Products table', 'Missing product ID!', mtError, [mbOK], 0);
end;
The screenshot on the left shows the addition of a record, with the warning that the product ID used will not be the one in the corresponding edit field, but will be set by the application (set to 79 in this case). The screenshot on the right shows how the number of records has been changed after the record addition and some record deletion have been done. The number of physical records is incremented by the record addition, and not affected by the record deletion (as the deleted records remain in the table), thus passes from 77 to 78. The number of logical records is incremented by the record addition and decremented by the record deletion, thus stays at 74. One record having been appended, the last record's product ID equals the product ID of this record (79), and the ID of the next product to be added will be this value plus 1 (80).
![Lazarus/Free Pascal: Add, delete, modify a record in a Visual FoxPro 9 table - Application output [1]](./screenshots/vfp5a.jpg "Lazarus/Free Pascal: Add, delete, modify a record in a Visual FoxPro 9 table - Application output [1]")
|
![Lazarus/Free Pascal: Add, delete, modify a record in a Visual FoxPro 9 table - Application output [2]](./screenshots/vfp5b.jpg "Lazarus/Free Pascal: Add, delete, modify a record in a Visual FoxPro 9 table - Application output [2]")
|
Click the following link to download the program samples sources.
If you find this text helpful, please, support me and this website by signing my guestbook.