Difference between revisions of "Lazarus Tdbf Tutorial"

From Lazarus wiki
Jump to navigationJump to search
(→‎Adding fields: Link to database_field_type)
 
(127 intermediate revisions by 20 users not shown)
Line 1: Line 1:
 
{{Lazarus Tdbf Tutorial}}
 
{{Lazarus Tdbf Tutorial}}
  
 +
{{Infobox databases}}
 
== Overview ==
 
== Overview ==
 +
This tutorial is about basic database development using the original [http://tdbf.sf.net TDbf] component (by Micha Nelissen) with Lazarus which is included in the standard FreePascal distribution.
  
This tutorial is about basic database development using the [http://tdbf.sf.net TDbf] component (by Micha Nelissen) with Lazarus. Additional documentation for the TDbf is available. This page was created by [[User:Tonymaro | Tony Maro]] but other contributions are welcome!
+
In Lazarus, the DbfLaz component makes use of the FPC TDbf code.
  
For TDbf documentation pdf go to the [https://sourceforge.net/project/showfiles.php?group_id=34085&package_id=26371  SourceForge] site. It may be useful to keep that pdf alongside this document while reading.
+
=== Documentation ===
 +
For the original TDbf documentation PDF go to the [https://sourceforge.net/project/showfiles.php?group_id=34085&package_id=26371  SourceForge] site. It may be useful to keep that pdf alongside this document while reading.
  
=== What you will need ===
+
This wiki article serves as additional documentation for new features since the original documentation.
Free Pascal 2.0 or superior. It may be possible to download just the TDbf component by itself and use it with the 1.1 version of FreePascal, however this document was written with 2.0 or superior in mind, partly due to bug fixes in the other database components used in Lazarus.
 
  
The DbfLaz package is now (version 0.9.13 or superior) installed by default.
+
=== Alternatives and known problems ===
 +
Alternatives for embedded database use include Firebird embedded (advantage: easily scalable to client/server), SQLite (advantage: easy deployment), or ZMSQL (using CSV files; advantage: only Pascal code required).
  
=== What the TDbf provides ===
+
Current issues: please see the bugs in the bug tracker.
The TDbf provides access to dBase and FoxPro database tables for Lazarus (and others). It allows for reading, writing and creation of dBase III+, dBase IV, Visual dBase VII and FoxPro tables. It does all of this without the need for additional libraries or database engines. Simply drop the TDbf on your form and you have instant access to a cross-platform database environment. The TDbf works in both Windows and Linux using Lazarus.
+
 
 +
==== Limitations ====
 +
* TDBF sets database encoding based on OS language settings (you can choose your own by setting LanguageID; see documentation). Moving a DB to another computer that does not support the encoding may lead to a read-only database. ''Note: investigate whether this applies to Tablelevel 7 (Visual DBase) and 25/30 (Foxpro/Visual Foxpro)''
 +
* None of the DBF formats used support Unicode encoding (whether UTF-8, UTF-16 or UTF-32). There may be a workaround using binary fields, but then you lose sorting abilities etc.
 +
* Indexing a DBase file with [ixCaseInsensitive] currently does not work.
 +
* No support yet for referential integrity in file formats that support it (DBase VII, Visual FoxPro).
 +
* No support (now or ever) for internally encrypted .dbf files: the DbaseIV encryption mechanism is quite weak anyway. Please use one of the encryption methods mentioned below.
 +
 
 +
FPC 2.6.x and lower:
 +
* On ARM processors, the TDBF code is known not to work due to bus alignment issues. This is being addressed in the FPC development/trunk version.
 +
* Choosing the FoxPro tablelevel (25) will produce files that cannot be read by Visual Foxpro drivers. This has been fixed in the FPC development/trunk version.
 +
 
 +
=== No need for installation ===
 +
The DbfLaz package is installed by default in both FPC and Lazarus. The DBFLaz package uses the TDbf and associated units in the Free Pascal Free Component Library. In other words, no need to install anything if you have a fairly recent version of Lazarus.
 +
 
 +
=== What the TDbf package provides ===
 +
The TDbf package provides access to dBase and FoxPro database tables for Lazarus (and others). It allows for reading, writing and creation of dBase III+, dBase IV, Visual dBase VII and FoxPro tables. It does all of this without the need for additional libraries or database engines. Simply drop the TDbf on your form and you have instant access to a cross-platform database environment. The TDbf works in both Windows and Linux using Lazarus.
 +
 
 +
=== What to put in your uses section ===
 +
If you use the visual DBF components in Lazarus, Lazarus will put Dbf in your uses clauses. If you use FPC (console mode) only, you can put it there yourself.
 +
Handy other units are db for DataSet support and Dbf_Common for things such as field type definitions.
 +
 
 +
So you'd get:
 +
<syntaxhighlight lang=pascal>
 +
uses Dbf, db, Dbf_Common; 
 +
</syntaxhighlight>
  
 
== How to create a new database table ==
 
== How to create a new database table ==
 +
There is a "Database Desktop" application for Lazarus (open the project in your Lazarus directory/tools/LazDataDesktop). You could create a new data dictionary, then create code to set up DBF files based on the dictionary. As of now (July 2011), this process is still a bit convoluted.
  
As there is no "Database Desktop" application for Lazarus yet, we must create a new database in code.
+
Another option is to use OpenOffice or LibreOffice Calc and save your spreadsheet as a .dbf file.
  
 
You may also (on windows - works with wine too) try this application: http://www.dirfile.com/cdbf_explorer.htm
 
You may also (on windows - works with wine too) try this application: http://www.dirfile.com/cdbf_explorer.htm
 +
 +
In any event, we shall illustrate creating a new database in code below.
  
 
=== Setting the path ===
 
=== Setting the path ===
It's a good idea to give your application's database it's own directory. This simplifies making backups of the data. There are two ways to set the path. You can set the full path using the FilePathFull property, or you can set a path relative to the current application path with FilePath. For instance, setting "FilePath" at runtime to "data/" would use a data subdirectory just below the executable file. Setting the "FilePathFull" property to "/var/data/" would place everthing in that exact folder, ignoring the application's location.
+
It's a good idea to give your application's database its own directory. This simplifies making backups of the data.  
 +
 
 +
There are two ways to set the path:
 +
* you can set the full path using the FilePathFull property
 +
* you can set a path relative to the current application path with FilePath.  
 +
 
 +
For instance, setting "FilePath" at runtime to "data/" would use a data subdirectory just below the executable file. Setting the "FilePathFull" property to "/var/data/" would place all dbfs, index files etc in that exact folder, whatever the application location is.
 +
 
 +
An example:
 +
<syntaxhighlight lang=pascal>MyDbf.FilePathFull := '/location/to/my/data'; //Directory where all .dbf files will be stored
 +
MyDbf.TableName := 'customers.dbf'; // note: is the .dbf really required?
 +
</syntaxhighlight>
 +
 
 
=== Choosing a TableLevel ===
 
=== Choosing a TableLevel ===
By default, the TDbf will create dBase IV tables. While this is very compatible, there are features you may wish to use that are not supported. To support auto-incrementing fields, you must use something newer. The table types are:
+
By default, the TDbf components will create dBase IV tables. While this is very compatible, there are features you may wish to use that are not supported. E.g. to support auto-incrementing fields, you must use something newer. The table types are:
 
+
{| border="1" cellpadding="2" class="wikitable sortable"
*Level 3 - dBase III+;
+
|-
*Level 4 - dBase IV;
+
| '''Table level'''
*Level 7 Visual dBase VII;
+
| '''*Base product compatibility'''
*Level 25 - FoxPro.
+
| '''Auto-increment field support'''
 +
| '''Field length limit'''
 +
| '''Remarks'''
 +
|-
 +
| Level 3||dBase III+||no||10 characters [http://www.clicketyclick.dk/databases/xbase/format/dbf.html#DBF_STRUCT]||Quite obsolete but very simple format. Handy for in-memory usage.
 +
|-
 +
| Level 4||dBase IV||no||10 characters [http://www.clicketyclick.dk/databases/xbase/format/dbf.html#DBF_STRUCT]||Used to be very popular. Basis for GIS shape files (.shp)
 +
|-
 +
| Level 7||Visual dBase VII||yes||32 characters [http://www.dbase.com/KnowledgeBase/int/db7_file_fmt.htm]||
 +
|-
 +
| Level 25||FoxPro||yes||10 characters [http://www.dynamic.com/v7/what-are-limitations-foxpro-full-isam-file]
 +
[http://www.foxite.com/archives/foxpro-field-names-0000066805.htm]
 +
|FoxPro; compatible with FoxPro for DOS and Visual FoxPro. Note: implementation in FPC 2.6.2 produces files that cannot be read by Visual FoxPro drivers!
 +
|-
 +
| Level 30||Visual FoxPro||yes||Normally 10 characters [http://www.dynamic.com/v7/what-are-limitations-foxpro-full-isam-file]
 +
[http://www.foxite.com/archives/foxpro-field-names-0000066805.htm]
 +
Not supported in FPC: when using a DBC (Database container) file: 27 characters
 +
||Not yet in FPC 2.6.2; only in current development FPC version
 +
|-
 +
|
 +
|}
 +
See bug [http://bugs.freepascal.org/view.php?id=20134] for more details on field length limitations.
  
You choose a table type by setting the TableLevel property appropriately.
+
You choose a table type by setting the TableLevel property appropriately:
 +
<syntaxhighlight lang=pascal>
 +
MyDbf.TableLevel := 7; //Visual dBase 7
 +
</syntaxhighlight>
  
 
=== Adding fields ===
 
=== Adding fields ===
Creating fields for your new table at runtime pretty much follows the old Delphi standard. Once you have set your FilePath, TableLevel, and TableName properties, manipulate the FieldDefs property to set up the structure. For example:
+
Creating fields for your new table at runtime pretty much follows the old Delphi standard. An example:
  
<delphi>MyDbf.FilePathFull := '/location/to/my/data';
+
<syntaxhighlight lang=pascal>
MyDbf.TableLevel := 7;
 
MyDbf.TableName := 'customers.dbf'; // note: is the .dbf really required?
 
 
With MyDbf.FieldDefs do begin
 
With MyDbf.FieldDefs do begin
   Add('Id', ftAutoInc, 0, True);
+
   Add('Id', ftAutoInc, 0, True); //Autoincrement field called Id
   Add('Name', ftString, 80, True);
+
   Add('Name', ftString, 80, True); //80 character string field called Name
End;</delphi>
+
End;</syntaxhighlight>
  
Field types are defined as:
+
'''Note''' the parameter 'Required' is not implemented therefore regardless of how it is set it will be 'False'.
  
 +
List of field types - bold types are currently supported by the DBF code. See [[Database_field_type]] for a explanation of field types:
 
*    ftUnknown
 
*    ftUnknown
 
*    '''ftString'''
 
*    '''ftString'''
Line 53: Line 119:
 
*    '''ftBoolean'''
 
*    '''ftBoolean'''
 
*    '''ftFloat'''
 
*    '''ftFloat'''
*    '''ftCurrency''' (TableLevel 25)
+
*    '''ftCurrency''' (only supported in TableLevel 25)
*    '''ftBCD''' (TableLevel 25)
+
*    '''ftBCD''' (only supported in TableLevel 25)
 
*    '''ftDate'''
 
*    '''ftDate'''
 
*    ftTime
 
*    ftTime
*    '''ftDateTime'''
+
*    '''ftDateTime''': probably the recommended one for storing ftDate or ftDateTime values. At least on FoxPro, seems to store milliseconds as well.
*    '''ftBytes''' (TableLevel 25)
+
*    '''ftBytes''' (only supported in TableLevel 25)
 
*    ftVarBytes
 
*    ftVarBytes
*    '''ftAutoInc''' (TableLevel 7 or 25)
+
*    '''ftAutoInc''' (only supported in TableLevel 7 or 30)
 
*    '''ftBlob'''
 
*    '''ftBlob'''
 
*    '''ftMemo'''
 
*    '''ftMemo'''
Line 85: Line 151:
 
*    ftFMTBcd
 
*    ftFMTBcd
  
Bold types are currently supported by the DBF code. See [[Database_field_type]] for a explanation of field types.
+
=== Adding fields at designtime ===
 +
Use property <tt>FieldDefs</tt> to create fields at designtime - this replaces above call to <tt>FieldDefs.Add</tt>. However, it is important to note that the property <tt>StoreDefs</tt> must be set to true in order to store the definitions in the lfm file and to have them available when the table is created later at runtime.  
  
=== Go ahead and create it! ===
+
=== Creating the table ===
 
Once you have defined the fields you wish to use in your new table, you can go ahead and create it with:
 
Once you have defined the fields you wish to use in your new table, you can go ahead and create it with:
  
<delphi>MyDbf.CreateTable;</delphi>
+
<syntaxhighlight lang=pascal>MyDbf.CreateTable;</syntaxhighlight>
  
 
== How to add indexes to a table ==
 
== How to add indexes to a table ==
 +
If your database is larger than a few records, chances are you will want to have indexes defined to make searching faster. To change the index structure of a table, we need to have exclusive access to the table - which we would have while creating it anyway.
 +
 +
<syntaxhighlight lang=pascal>MyDbf.Exclusive := True;
 +
MyDbf.Open;</syntaxhighlight>
 +
 +
Now, we just have to add the index - or two, as in this example:
 +
 +
<syntaxhighlight lang=pascal>
 +
MyDbf.AddIndex('custid', 'Id', [ixPrimary, ixUnique]);
 +
MyDbf.AddIndex('custname','Name', [ixCaseInsensitive]);
 +
MyDbf.Close;
 +
</syntaxhighlight>
 +
 +
== Expressions ==
 +
Both indexes and filters can use expressions according to the [http://sourceforge.net/projects/tdbf/files/TDbf%20documentation/TDbf%20documentation%201.1/ TDbf documentation].
 +
 +
For more details on filters, see [[#Filters|below]].
 +
 +
=== Index/filter on strings ===
 +
Strings needs to be surrounded/delimited by double quotes (") or single quotes (').
 +
 +
{{Note|In FPC versions lower than 3.0, using the delimited quotes in your filter string does not work (see the example below). See FPC 3.0 New Features page: [[FPC_New_Features_3.0#TDBF.2C_bufdataset_.28and_descendents_such_as_TSQLQuery.29_allow_escaped_delimiters_in_string_expression_filter]].}}
 +
<br/>
 +
 +
<syntaxhighlight lang=pascal>
 +
// We have a field called CUSTOMER
 +
// let's delimit with double quotes
 +
// (of course, double up single ' in string to make FPC happy)
 +
MyDBF.Filter:='CUSTOMER = "Felipe''s Bank" ';
 +
// another tricky one, let's delimit with single quotes. Of course,
 +
// we'll need to double these up for FPC to interpret these correctly
 +
MyDBF.Filter:='CUSTOMER = ''Jim "Mighty" McLanahan'' ';
 +
 +
// If we have both a ' and a " in our string, we're in trouble.
 +
// the example below only works on FPC 2.7.1+
 +
MyDBF.Filter:='CUSTOMER = "Felipe''s ""Mighty"" Bank" ';
  
If your database is larger than a few records, chances are you will want to have indexes defined to make searching faster. To change the index structure of a table, we will want to have exclusive access to the table - which we would have while creating it anyway.
+
MyDBF.Filtered:=true;
 +
</syntaxhighlight>
  
<delphi>MyDbf.Exclusive := True;
+
=== Index 100 character string limit ===
MyDbf.Open;</delphi>
+
Index expressions that return a string may be maximum 100 characters long; use SUBSTR to cut off extra characters, e.g.:
 +
<syntaxhighlight lang=pascal>
 +
MyDbf.AddIndex('SOMEFANCYINDEX',
 +
  'DTOS(DATEFIELD)+SUBSTR(LONGFIELD,1,10)+SUBSTR(LONGFIELD2,1,20)', []);
 +
</syntaxhighlight>
  
Now, we just have to add the index.
+
=== Index/filter on TDateTimes ===
 +
To set an index on a date/time field, use an expression index and the DTOS function:
 +
<syntaxhighlight lang=pascal>
 +
MyDbf.AddIndex('PUBLISHDATEIDX', 'DTOS(PUBLISHDATE)', []);
 +
</syntaxhighlight>
  
<delphi>MyDbf.AddIndex('custid', 'Id', [ixPrimary, ixUnique]);
+
Filtering is similar - note: apparently you need to surround the string value you are comparing against with double quotes. See below for details on the DTOS function; basically you need to format the date as YYYYMMDD:
MyDbf.AddIndex('custname','Name', [ixCaseInsensitive]);
+
<syntaxhighlight lang=pascal>
MyDbf.Close;</delphi>
+
// we have a field named MODIFIED here:
 +
MyDBF.Filter:='DTOS(MODIFIED) > "20051231"';
 +
 
 +
// if you want to compare datetime, that can be done:
 +
// everything later than 2 January 2006, 4:34PM
 +
MyDBF.Filter:='DTOS(MODIFIED) > "20060102 163400"';
 +
 
 +
MyDBF.Filtered:=true;
 +
</syntaxhighlight>
  
== Put it all together and you get... ==
+
For example, if you want to filter a tdbf on a date field using a value selected from a DBlookupComboBox
 +
<syntaxhighlight lang=pascal>
 +
var
 +
mydate: String;
 +
begin
 +
  mydate := formatdatetime('yyyymmdd', strtodate(DBLookupComboBox.Text));
 +
  Filter := 'DTOS(DATE) = ' + AnsiQuotedSTR(mydate,'"');
 +
end;
 +
</syntaxhighlight>
  
The following sample creates a new table "customers" in code. This of course only needs done once, and after that you just OPEN the table, don't create it. ;-)
+
=== Index/filter operators/functions ===
 +
Supported operators (from the documentation mentioned above):
 +
{| {{table}}
 +
| '''Operator/function'''
 +
| '''Description'''
 +
|-
 +
| +||Concatenate two strings, or add two numbers
 +
|-
 +
|-||Subtract two numbers, or (FPC 2.7.1+) take the negative of a number/constant
 +
|-
 +
|*||Multiply two numbers
 +
|-
 +
|/||Divide two numbers
 +
|-
 +
|=||Compare two strings, numbers or booleans for equality
 +
|-
 +
|<>||Compare two strings, numbers or booleans for inequality
 +
|-
 +
|<||True if the first argument is smaller than second argument
 +
|-
 +
|<=||True if the first argument is smaller than or equal to the second argument
 +
|-
 +
|>||True if the first argument is greater than the second argument
 +
|-
 +
|>=||True if the first argument is greater than or equal to the second argument
 +
|-
 +
|NOT||Negate the boolean argument to the right
 +
|-
 +
|AND||Returns true if both the first and second boolean argument are true
 +
|-
 +
|OR||Returs true if the first or second boolean argument is true
 +
|-
 +
|STR(num[,size,precision])||Converts a number num to a string with optional size and precision
 +
num: number to convert to string
 +
size: the total number of characters to output
 +
precision: the number of digits to the right of the decimal point
 +
|-
 +
|DTOS(date)||Converts date to a string with representation YYYYMMDD
 +
|-
 +
|SUBSTR(str,index,count)||Extracts a substring from string str from position index and length count
 +
|-
 +
|UPPER(str)||Returns the uppercase equivalent of string str
 +
|-
 +
|LOWER(str)||Returns the lowercase equivalent of string str
 +
|-
 +
|
 +
|}
  
Please note: when using Windows (at least it was so with XP Professional)
+
== Code example: creating a table/DBF file ==
you must create the relative data directory before trying to run this example, or
+
The following sample creates a new table "customers" in code. This of course only needs done once, and after that you just open the table, instad of creating it. ;-)
else this program will fail.
 
  
<delphi>($MODE OBJFPC}
+
<syntaxhighlight lang=pascal>
 +
{$MODE OBJFPC}{$H+}
 
Program DatabaseTest;
 
Program DatabaseTest;
{ We will require the following units be in the USES clause: }
+
{ We need the following units be in the USES clause: }
uses Dbf, db, Dbf_Common;   
+
uses Dbf, db, Dbf_Common, SysUtils;   
 
                                  
 
                                  
{ The Dbf is put there when you drop the TDbf on a form...  }
+
{ The Dbf is put there e.g. when you drop a TDbf component on a form...  }
{ but you will need db for the DataSet object and Dbf_Common }
+
{ but you will need db for the DataSet object and Dbf_Common             }
{ for things such as the field type definitions             }
+
{ for things such as the field type definitions                           }
 +
{ Finally, use SysUtils for ForceDirectories.                            }
 
var
 
var
 
   MyDbf: TDbf;
 
   MyDbf: TDbf;
Line 126: Line 301:
 
   MyDbf := TDbf.Create(nil);
 
   MyDbf := TDbf.Create(nil);
 
   try
 
   try
 +
    { make sure the data directory exists - otherwise this may fail (e.g. on Windows XP): }
 +
    ForceDirectories('data');
 
     { use relative path to "data" directory }
 
     { use relative path to "data" directory }
     MyDbf.FilePath := 'data/';  
+
     MyDbf.FilePath := 'data' + DirectorySeparator;  
 
     { we want to use Visual dBase VII compatible tables }
 
     { we want to use Visual dBase VII compatible tables }
 
     MyDbf.TableLevel := 7;
 
     MyDbf.TableLevel := 7;
Line 145: Line 322:
 
     MyDbf.Free;
 
     MyDbf.Free;
 
   end;
 
   end;
end;</delphi>
+
end.
 +
</syntaxhighlight>
  
 
== External Index Files ==
 
== External Index Files ==
 +
The TDbf component also supports storing secondary indexes in a separate file. This might be helpful if the database is expected to be very large. Secondary index files are created almost identically to normal indexes, but with the addition of the '.ndx' file extension:
  
The TDbf also supports storing secondary indexes in a separate file. This might be helpful if the database is expected to be very large. Secondary index files are created almost identically to normal indexes, but with the addition of the '.ndx' file extension:
+
<syntaxhighlight lang=pascal>MyDbf.AddIndex('custname.ndx','Name', [ixCaseInsensitive]);</syntaxhighlight>
 
 
<delphi>MyDbf.AddIndex('custname.ndx','Name', [ixCaseInsensitive]);</delphi>
 
  
  
 
Each time the TDbf is opened, the index file must be loaded:
 
Each time the TDbf is opened, the index file must be loaded:
  
<delphi>MyDbf.OpenIndexFile('custname.ndx');</delphi>
+
<syntaxhighlight lang=pascal>MyDbf.OpenIndexFile('custname.ndx');</syntaxhighlight>
  
  
 
And indexes must be referenced including the extension:
 
And indexes must be referenced including the extension:
  
<delphi>MyDbf.IndexName := 'custname.ndx';</delphi>
+
<syntaxhighlight lang=pascal>MyDbf.IndexName := 'custname.ndx';</syntaxhighlight>
 
 
  
Index files are packed separately using:
 
  
<delphi>MyDbf.CompactIndexFile('custname.ndx');</delphi>
+
Index files can be packed (i.e. entries pointing to deleted data rows are removed) separately using:
  
== How to link the TDbf to data-aware components ==
+
<syntaxhighlight lang=pascal>MyDbf.CompactIndexFile('custname.ndx');</syntaxhighlight>
  
 +
== How to link the TDbf component to data-aware components ==
 
The above examples show how to create a new database table in code. Using that table is even more simple.
 
The above examples show how to create a new database table in code. Using that table is even more simple.
  
Line 183: Line 359:
 
Be sure to set the FilePath (or FilePathFulll), TableLevel, and TableName properties of your TDbf component before calling
 
Be sure to set the FilePath (or FilePathFulll), TableLevel, and TableName properties of your TDbf component before calling
  
<delphi>TDbf.Active := True;</delphi>
+
<syntaxhighlight lang=pascal>TDbf.Active := True;</syntaxhighlight>
 
 
 
 
There is much more that can be said about programming with databases in Lazarus, and I would recommend a good Delphi database programming book or two as the underlying concepts are the same. I constantly refer to my copy of "Delphi 2 Unleashed" because the concepts and basic code haven't changed much in 8 years.
 
 
 
== Packing and rebuilding the tables ==
 
  
When a record is deleted, it's not truly removed from the physical table. Periodically you must "pack" a table to recover that lost space. This should be done with exclusive mode set.
 
 
<delphi>MyDbf.Exclusive := True;
 
MyDbf.Open;
 
MyDbf.PackTable;
 
// let's also rebuild all the indexes
 
MyDbf.RegenerateIndexes;
 
MyDbf.Close;
 
MyDbf.Exclusive := False;</delphi>
 
  
 
== Master table relations ==
 
== Master table relations ==
 
+
Real power in database programming begins when you have multiple tables that reference each other. While TDbf does not yet support full referential integrity (''so what is missing?''), it does support a master / detail relationship between TDbf's.
Real power in database programming begins when you have multiple tables that reference each other. While TDbf does not yet support referential integrity, it does support a master / detail relationship between TDbf's.
 
  
 
When there are two tables related, for instance:
 
When there are two tables related, for instance:
Line 222: Line 383:
 
On the invoices TDbf component set the following:
 
On the invoices TDbf component set the following:
  
<delphi>InvDbf.IndexName := 'idxcustid'; // our field that will match the customers table ID
+
<syntaxhighlight lang=pascal>
 +
InvDbf.IndexName := 'idxcustid'; // our field that will match the customers table ID
 
InvDbf.MasterSource := dsCustomers; // datasource that is linked to the customers TDbf
 
InvDbf.MasterSource := dsCustomers; // datasource that is linked to the customers TDbf
InvDbf.MasterFields := 'Id'; // field on the customers table we are matching against our index</delphi>
+
InvDbf.MasterFields := 'Id'; // field on the customers table we are matching against our index
 +
</syntaxhighlight>
 +
 
 +
===Searching and Displaying a data set by using filters===
 +
[[user:svbfieldview.net|Simon Batty]]
 +
 
 +
In this example I wanted to search a database of books for all the titles an author has listed
 +
and then display the list in a memo box
 +
<syntaxhighlight lang=pascal>
 +
Dbf1.FilePathFull := '/home/somelocatio/database_location/'; // path to the database directory
 +
Dbf1.TableName := 'books.dbase';                            // database file (including extension)
 +
DbF1.Open;
 +
memo1.Clear;                                                // clear the memo box
 +
 +
Dbf1.FilterOptions := [foCaseInsensitive];                  // set FilterOptions before Filter !
 +
Dbf1.Filter := 'AUTHOR=' + QuotedStr('anauthor');            // AUTHOR is the field name containing the authors
 +
                                                            // Note that we need to surround the value with double quotes: "James Joyce"
 +
 +
Dbf1.Filtered := true;      // This selects the filtered set
 +
Dbf1.First;                  // moves the the first filtered data
 +
while not dbf1.EOF do        // prints the titles that match the author to the memo box
 +
begin
 +
  memo1.Append(Dbf1.FieldByName('TITLE').AsString);          // TITLE is the field name for titles
 +
  dbf1.next;                                                // use .next here NOT .findnext!
 +
end;
 +
Dbf1.Close;
 +
</syntaxhighlight> 
 +
 
 +
Note that you can use Ddf1.FindFirst to get the first record in the filtered set, then use
 +
Dbf1.Next to move though the data. I found that using Dbf1.FindNext just causes the program to hang.
 +
 
 +
== Filters ==
 +
There are two ways to use filters:
 +
* use filter expressions
 +
* write an event handler for OnFilterRecord; if you want to show the record, set Accept to true.
 +
 
 +
In both cases, set the Filtered property to true to enable filtering.
 +
 
 +
Example: imagine you have a database that identifies the products by a code, for example, B019, and you need to filter only those products that are of this code so that a report is generated with LazReport.
 +
Let's assume your DBF object is called TDBF1 and the field with product codes is called "product_no".
 +
 
 +
=== Expression filters ===
 +
Implement filtering using a filter expression like this:
 +
<syntaxhighlight lang=pascal>
 +
TDBF1.Filter := 'product_code = "B019"'; //string type field; string constants should be quoted by ' or "
 +
TDBF1.Filtered := true;
 +
</syntaxhighlight>
 +
 
 +
<syntaxhighlight lang=pascal>
 +
TDBF1.Filter := 'product_code = "B*"'; //partial match filter; filters all products which code begins with B
 +
TDBF1.Filtered := true;
 +
</syntaxhighlight>
 +
 
 +
Filter on multiple fields:
 +
<syntaxhighlight lang=pascal>
 +
// Needs to be tested. If it works, please remove this line.
 +
MyDBF.Filter:='LASTNAME;FIRSTNAME';
 +
</syntaxhighlight>
 +
Adapted from: forum post [http://forum.lazarus.freepascal.org/index.php/topic,23090.msg137401.html#msg137401]
 +
 
 +
Here is an example of using a DBLookupCombobox value in a filter statement (where NAME is the field from the DBf):
 +
<syntaxhighlight lang=pascal>
 +
Filter := 'NAME=' + QuotedStr(DBLookupcomboBox1.Text);
 +
</syntaxhighlight>
 +
 
 +
Here are two examples of using two comboboxes in a compound (i.e. 2 fields) filter statement (where NAME and TYPE are field names from a DBf):
 +
 
 +
In this example you are going to "and" the two values.
 +
<syntaxhighlight lang=pascal>
 +
// unfortunately, this will fail whenever the user types a ' in TypeComboBox.Text...
 +
Filter := 'NAME=' + QuotedStr(DBLookupcomboBox1.Text) + ' and TYPE=' + QuotedStr(TypeComboBox.Text);
 +
Filter := Format('NAME="%s" and TYPE="%s"',[DBLookupcomboBox1.Text, TypeComboBox.Text]);
 +
</syntaxhighlight>
 +
 
 +
An example of using a combobox to provide the field name for the filter and comparing that to the value in lookup combobox:
 +
<syntaxhighlight lang=pascal>
 +
Filter := FieldComboBox.Text + '=' + QuotedSTR(DBLookupcomboBox1.Text);
 +
</syntaxhighlight>
 +
 
 +
In this example, the FieldCombobox is programmatically populated with the names of the fields from the DBf:
 +
<syntaxhighlight lang=pascal>
 +
// fill the combobox with Field Names
 +
for i:=0 to Dbf.FieldCount-1 do
 +
  FieldComboBox.Items.Add(Dbf.Fields[i].FieldName); 
 +
</syntaxhighlight>
 +
 
 +
Please see the section [[#Expressions]] above for the list of supported expressions as well as more examples.
 +
 
 +
=== OnFilterRecords ===
 +
To use this method, you need to write a function.
 +
 
 +
In your main code, write something like:
 +
<syntaxhighlight lang=pascal>
 +
OnFilterRecord := @TestOnFilterProc; //instruct tdbf to lead all records past your OnFilterProductCode procedure
 +
Filtered := True; //actually enable filtering
 +
</syntaxhighlight>
 +
 
 +
Then write your event handler:
 +
<syntaxhighlight lang=pascal>
 +
procedure TTestCursorDBBasics.TestOnFilterProc(DataSet: TDataSet; var Accept: Boolean);
 +
begin
 +
  // Filter out everything except product_no=B019
 +
  Accept := Copy (Trim (Dataset.FieldByName ('product_no'). AsString),1,4) = 'B019';
 +
end;
 +
</syntaxhighlight>
 +
All records that give a false value for Accept are filtered out i.e. are not displayed in the report.
 +
 
 +
==== Filter on null values ====
 +
You can use this method to show only empty (null) values, something you cannot do with the <code>Filter</code> property.
 +
An example - assuming you have a field called orderdate:
 +
<syntaxhighlight lang=pascal>
 +
procedure TTestCursorDBBasics.TestOnFilterProc(DataSet: TDataSet; var Accept: Boolean);
 +
begin
 +
  // Show only empty order dates
 +
  Accept := Dataset.FieldByName('orderdate').IsNull;
 +
end;
 +
</syntaxhighlight>
 +
 
 +
== Packing and rebuilding the tables ==
 +
When a record is deleted, it's not truly removed from the physical table. Periodically you must "pack" a table to recover that lost space. This should be done with exclusive mode set. See above for remarks on packing external index files.
 +
 
 +
<syntaxhighlight lang=pascal>
 +
MyDbf.Exclusive := True;
 +
MyDbf.Open;
 +
MyDbf.PackTable;
 +
// let's also rebuild all the indexes
 +
MyDbf.RegenerateIndexes;
 +
MyDbf.Close;
 +
MyDbf.Exclusive := False;
 +
</syntaxhighlight>
 +
 
 +
 
 +
== Encryption ==
 +
Apparently at least DBase IV and 5 support some kind of encryption (there is an encryption flag in the file structure), but it may well be that the password/encryption key is in the DBF itself (see [http://www.antionline.com/archive/index.php/t-236215.html]), making it insecure.
 +
 
 +
There are other options if you want to encrypt your data. See here [http://www.lazarus.freepascal.org/index.php/topic,15422.msg83016.htm|forum discussion on DBase encryption]
 +
 
 +
=== Operating system level encryption ===
 +
You can use an encrypted filesystem such as TrueCrypt (containers) to encrypt the directory where the database files get stored.
 +
 
 +
=== Encrypt the DBF file ===
 +
You can use streams (e.g. the Blowfish encyprtion stream code supplied with Lazarus/FPC) to encrypt the data (all tables etc) by storing the unencrypted file e.g. in memory, then writing that to disk:
 +
# Open your file in a TFileStream.
 +
# Decrypt TFileStream to a new stream, YourDecryptedStream
 +
# Tdbf.UserStream:= YourDecryptedStream; //Note: this will not encrypt .dbt/memo files! Use '''UserMemoStream''' for this (in FPC development/trunk since May 2013 only)
 +
# Tdbf.Storage:=stoMemory;
 +
# Tdbf.Open;
 +
 
 +
When finished
 +
#  Tdbf.Close
 +
#  Encrypt YourDecryptedStream to TFileStream
 +
#  Free stream(s)
 +
Detailed code can be found in the forum thread mentioned above. People implementing this are welcome to adapt this article with working code.
 +
 
 +
=== Encrypt field data ===
 +
You could also choose to encrypt field data by implementing the OnGetText and OnSetText events to encrypt/decrypt the field contents.
  
 +
== Code pages and string encoding issues ==
 +
The dbf format is very old and thus can only handle strings with single-byte characters. Since Lazarus uses UTF-8 encoded strings, there must be a conversion between both formats, otherwise the dbf strings are not displayed correctly in the LCL controls.
  
== Sample application - DB Browser ==
+
For this purpose the <tt>TDbf</tt> component fires an event <tt>OnTranslate</tt> for string fields. The event provides a boolean parameter <tt>ToOEM</tt> telling whether the conversion must go to a single-byte codepage or to UTF-8. Typical code is as follows:
I've written a simple application that will use the TDbf to open and display database tables using the dbGrid control.  The Linux executable along with project sources which should compile fine in Windows is available from:  [http://tony.maro.net/mod.php?mod=downloads&op=showcat&id=3&level=1 tony.maro.net]
+
<syntaxhighlight lang="pascal">
 +
uses
 +
  LConvEncoding;
  
== Third party software ==
+
function TForm1.DbfTranslate(Dbf: TDbf; Src, Dest: PChar; ToOEM: Boolean): Integer;
* [http://mydbfstudio.altervista.org/ MyDbf Studio] is an open source admin tool to manage your dbase table entirely written under lazarus IDE and based on TDbf.
+
var
 +
  s: String;
 +
  cp: String;
 +
begin
 +
  cp := 'cp' + IntToStr(Dbf.CodePage);
 +
  if ToOEM then
 +
    s := ConvertEncoding(Src, 'utf8', cp)
 +
  else
 +
    s := ConvertEncoding(Src, cp, 'utf8');
 +
  StrCopy(Dest, PChar(s));
 +
  Result := StrLen(Dest);
 +
end;
 +
</syntaxhighlight>
 +
 
 +
After opening the dataset all string fields to be converted must be defined by setting their property <tt>Transliterate</tt> to <tt>true</tt>.
 +
<syntaxhighlight lang="pascal">
 +
// Open dbf file
 +
Dbf1.Open;
 +
 
 +
// Make sure that strings are converted to and back from UTF8.
 +
Dbf1.OnTranslate := @DbfTranslate;
 +
for i := 0 to Dbf1.Fields.Count-1 do
 +
  if Dbf1.Fields[i] is TStringField then
 +
    TStringField(Dbf1.Fields[i]).Transliterate := true;
 +
</syntaxhighlight>
 +
 
 +
When a dbf file is newly created its codepage can be set by using the <tt>LanguageID</tt> property. This is a combination of locale specific information (for sorting) and the code page. The unit <tt>dbf_lang</tt> contains a list of allowed values, e.g.
 +
<syntaxhighlight lang="pascal">
 +
const
 +
  FoxLangId_ENU_437      = $01; // DOS USA
 +
  FoxLangId_Intl_850      = $02; // DOS multilingual
 +
  FoxLangId_Windows_1252  = $03; // Windows ANSI
 +
  FoxLangId_Mac_10000    = $04; // Standard Macintosh
 +
  DbfLangId_DAN_865      = $08;
 +
  DbfLangId_NLD_437      = $09;
 +
  DbfLangId_NLD_850      = $0A;
 +
  DbfLangId_FIN_437      = $0B;
 +
  // plus many more...
 +
</syntaxhighlight>
 +
The constants beginning with "Fox" are for the FoxPro TableLevels, those beginning with "Dbf" are for the dBase TableLevels.
  
== Things you need to be aware of ==
+
When strings are written to their fields directly, bypassing the LCL, then the encoding must be converted explicitly. The <tt>TDbf</tt> property <tt>CodePage</tt> returns the used codepage as defined by the LanguageID:
 +
<syntaxhighlight lang="pascal">
 +
uses
 +
  LConvEncoding;
 +
...
 +
Dbf1.Edit;
 +
Dbf1.FieldByName('some_string_field').AsString := ConvertEncoding('äöü', 'utf8', 'cp' + IntToStr(Dbf1.CodePage));
 +
Dbf1.Post;
 +
</syntaxhighlight>
  
Currently there is no support for referential integrity, or internally encrypted .dbf files.
+
== Third party software ==
 +
* [http://mydbfstudio.altervista.org/ MyDbf Studio] is an open source admin tool to manage your dbase table. It is entirely written in Lazarus IDE and based on TDbf.
 +
* [http://vlad-karpov.narod.ru/Components.html#VK%20DBF] are Delphi components for DBF files.
 +
** Vlad Karpov's open source VKDBF: [[http://sourceforge.net/projects/vkdbf/?source=directory]]
 +
*fpspreadsheet have a way to process dbf files and save them to excel:[http://wiki.freepascal.org/FPSpreadsheet]
  
[[Category:Databases]]
+
== See also ==
[[Category:Tutorials]]
+
* [[TDbf]]
 +
* [[fpdbfexport]] Exports any kind of dataset to DBF files
 +
* See the dbftool example included in FPC 2.7.1+: creating, using DBF files and exporting data using db export (also downloadable via http://svn.freepascal.org/svn/fpc/trunk/packages/fcl-db/examples/)
 +
* [[Contacts Database]] Another example database
 +
* [[Example: TDbf (creating table and indexes, selecting of index)]]

Latest revision as of 10:27, 20 April 2020

Deutsch (de) English (en) español (es) français (fr) português (pt) русский (ru) 中文(中国大陆)‎ (zh_CN)

Databases portal

References:

Tutorials/practical articles:

Databases

Advantage - MySQL - MSSQL - Postgres - Interbase - Firebird - Oracle - ODBC - Paradox - SQLite - dBASE - MS Access - Zeos

Overview

This tutorial is about basic database development using the original TDbf component (by Micha Nelissen) with Lazarus which is included in the standard FreePascal distribution.

In Lazarus, the DbfLaz component makes use of the FPC TDbf code.

Documentation

For the original TDbf documentation PDF go to the SourceForge site. It may be useful to keep that pdf alongside this document while reading.

This wiki article serves as additional documentation for new features since the original documentation.

Alternatives and known problems

Alternatives for embedded database use include Firebird embedded (advantage: easily scalable to client/server), SQLite (advantage: easy deployment), or ZMSQL (using CSV files; advantage: only Pascal code required).

Current issues: please see the bugs in the bug tracker.

Limitations

  • TDBF sets database encoding based on OS language settings (you can choose your own by setting LanguageID; see documentation). Moving a DB to another computer that does not support the encoding may lead to a read-only database. Note: investigate whether this applies to Tablelevel 7 (Visual DBase) and 25/30 (Foxpro/Visual Foxpro)
  • None of the DBF formats used support Unicode encoding (whether UTF-8, UTF-16 or UTF-32). There may be a workaround using binary fields, but then you lose sorting abilities etc.
  • Indexing a DBase file with [ixCaseInsensitive] currently does not work.
  • No support yet for referential integrity in file formats that support it (DBase VII, Visual FoxPro).
  • No support (now or ever) for internally encrypted .dbf files: the DbaseIV encryption mechanism is quite weak anyway. Please use one of the encryption methods mentioned below.

FPC 2.6.x and lower:

  • On ARM processors, the TDBF code is known not to work due to bus alignment issues. This is being addressed in the FPC development/trunk version.
  • Choosing the FoxPro tablelevel (25) will produce files that cannot be read by Visual Foxpro drivers. This has been fixed in the FPC development/trunk version.

No need for installation

The DbfLaz package is installed by default in both FPC and Lazarus. The DBFLaz package uses the TDbf and associated units in the Free Pascal Free Component Library. In other words, no need to install anything if you have a fairly recent version of Lazarus.

What the TDbf package provides

The TDbf package provides access to dBase and FoxPro database tables for Lazarus (and others). It allows for reading, writing and creation of dBase III+, dBase IV, Visual dBase VII and FoxPro tables. It does all of this without the need for additional libraries or database engines. Simply drop the TDbf on your form and you have instant access to a cross-platform database environment. The TDbf works in both Windows and Linux using Lazarus.

What to put in your uses section

If you use the visual DBF components in Lazarus, Lazarus will put Dbf in your uses clauses. If you use FPC (console mode) only, you can put it there yourself. Handy other units are db for DataSet support and Dbf_Common for things such as field type definitions.

So you'd get:

uses Dbf, db, Dbf_Common;

How to create a new database table

There is a "Database Desktop" application for Lazarus (open the project in your Lazarus directory/tools/LazDataDesktop). You could create a new data dictionary, then create code to set up DBF files based on the dictionary. As of now (July 2011), this process is still a bit convoluted.

Another option is to use OpenOffice or LibreOffice Calc and save your spreadsheet as a .dbf file.

You may also (on windows - works with wine too) try this application: http://www.dirfile.com/cdbf_explorer.htm

In any event, we shall illustrate creating a new database in code below.

Setting the path

It's a good idea to give your application's database its own directory. This simplifies making backups of the data.

There are two ways to set the path:

  • you can set the full path using the FilePathFull property
  • you can set a path relative to the current application path with FilePath.

For instance, setting "FilePath" at runtime to "data/" would use a data subdirectory just below the executable file. Setting the "FilePathFull" property to "/var/data/" would place all dbfs, index files etc in that exact folder, whatever the application location is.

An example:

MyDbf.FilePathFull := '/location/to/my/data'; //Directory where all .dbf files will be stored
MyDbf.TableName := 'customers.dbf'; // note: is the .dbf really required?

Choosing a TableLevel

By default, the TDbf components will create dBase IV tables. While this is very compatible, there are features you may wish to use that are not supported. E.g. to support auto-incrementing fields, you must use something newer. The table types are:

Table level *Base product compatibility Auto-increment field support Field length limit Remarks
Level 3 dBase III+ no 10 characters [1] Quite obsolete but very simple format. Handy for in-memory usage.
Level 4 dBase IV no 10 characters [2] Used to be very popular. Basis for GIS shape files (.shp)
Level 7 Visual dBase VII yes 32 characters [3]
Level 25 FoxPro yes 10 characters [4]

[5]

FoxPro; compatible with FoxPro for DOS and Visual FoxPro. Note: implementation in FPC 2.6.2 produces files that cannot be read by Visual FoxPro drivers!
Level 30 Visual FoxPro yes Normally 10 characters [6]

[7] Not supported in FPC: when using a DBC (Database container) file: 27 characters

Not yet in FPC 2.6.2; only in current development FPC version

See bug [8] for more details on field length limitations.

You choose a table type by setting the TableLevel property appropriately:

MyDbf.TableLevel := 7; //Visual dBase 7

Adding fields

Creating fields for your new table at runtime pretty much follows the old Delphi standard. An example:

With MyDbf.FieldDefs do begin
  Add('Id', ftAutoInc, 0, True); //Autoincrement field called Id
  Add('Name', ftString, 80, True); //80 character string field called Name
End;

Note the parameter 'Required' is not implemented therefore regardless of how it is set it will be 'False'.

List of field types - bold types are currently supported by the DBF code. See Database_field_type for a explanation of field types:

  • ftUnknown
  • ftString
  • ftSmallInt
  • ftInteger
  • ftWord
  • ftBoolean
  • ftFloat
  • ftCurrency (only supported in TableLevel 25)
  • ftBCD (only supported in TableLevel 25)
  • ftDate
  • ftTime
  • ftDateTime: probably the recommended one for storing ftDate or ftDateTime values. At least on FoxPro, seems to store milliseconds as well.
  • ftBytes (only supported in TableLevel 25)
  • ftVarBytes
  • ftAutoInc (only supported in TableLevel 7 or 30)
  • ftBlob
  • ftMemo
  • ftGraphic
  • ftFmtMemo
  • ftParadoxOle
  • ftDBaseOle
  • ftTypedBinary
  • ftCursor
  • ftFixedChar
  • ftWideString
  • ftLargeInt
  • ftADT
  • ftArray
  • ftReference
  • ftDataSet
  • ftOraBlob
  • ftOraClob
  • ftVariant
  • ftInterface
  • ftIDispatch
  • ftGuid
  • ftTimeStamp
  • ftFMTBcd

Adding fields at designtime

Use property FieldDefs to create fields at designtime - this replaces above call to FieldDefs.Add. However, it is important to note that the property StoreDefs must be set to true in order to store the definitions in the lfm file and to have them available when the table is created later at runtime.

Creating the table

Once you have defined the fields you wish to use in your new table, you can go ahead and create it with:

MyDbf.CreateTable;

How to add indexes to a table

If your database is larger than a few records, chances are you will want to have indexes defined to make searching faster. To change the index structure of a table, we need to have exclusive access to the table - which we would have while creating it anyway.

MyDbf.Exclusive := True;
MyDbf.Open;

Now, we just have to add the index - or two, as in this example:

MyDbf.AddIndex('custid', 'Id', [ixPrimary, ixUnique]);
MyDbf.AddIndex('custname','Name', [ixCaseInsensitive]);
MyDbf.Close;

Expressions

Both indexes and filters can use expressions according to the TDbf documentation.

For more details on filters, see below.

Index/filter on strings

Strings needs to be surrounded/delimited by double quotes (") or single quotes (').

Note-icon.png

Note: In FPC versions lower than 3.0, using the delimited quotes in your filter string does not work (see the example below). See FPC 3.0 New Features page: FPC_New_Features_3.0#TDBF.2C_bufdataset_.28and_descendents_such_as_TSQLQuery.29_allow_escaped_delimiters_in_string_expression_filter.


// We have a field called CUSTOMER
// let's delimit with double quotes
// (of course, double up single ' in string to make FPC happy)
MyDBF.Filter:='CUSTOMER = "Felipe''s Bank" ';
// another tricky one, let's delimit with single quotes. Of course,
// we'll need to double these up for FPC to interpret these correctly
MyDBF.Filter:='CUSTOMER = ''Jim "Mighty" McLanahan'' ';

// If we have both a ' and a " in our string, we're in trouble.
// the example below only works on FPC 2.7.1+
MyDBF.Filter:='CUSTOMER = "Felipe''s ""Mighty"" Bank" ';

MyDBF.Filtered:=true;

Index 100 character string limit

Index expressions that return a string may be maximum 100 characters long; use SUBSTR to cut off extra characters, e.g.:

MyDbf.AddIndex('SOMEFANCYINDEX', 
  'DTOS(DATEFIELD)+SUBSTR(LONGFIELD,1,10)+SUBSTR(LONGFIELD2,1,20)', []);

Index/filter on TDateTimes

To set an index on a date/time field, use an expression index and the DTOS function:

MyDbf.AddIndex('PUBLISHDATEIDX', 'DTOS(PUBLISHDATE)', []);

Filtering is similar - note: apparently you need to surround the string value you are comparing against with double quotes. See below for details on the DTOS function; basically you need to format the date as YYYYMMDD:

// we have a field named MODIFIED here:
MyDBF.Filter:='DTOS(MODIFIED) > "20051231"';

// if you want to compare datetime, that can be done:
// everything later than 2 January 2006, 4:34PM
MyDBF.Filter:='DTOS(MODIFIED) > "20060102 163400"';

MyDBF.Filtered:=true;

For example, if you want to filter a tdbf on a date field using a value selected from a DBlookupComboBox

var 
mydate: String;
begin
  mydate := formatdatetime('yyyymmdd', strtodate(DBLookupComboBox.Text));
  Filter := 'DTOS(DATE) = ' + AnsiQuotedSTR(mydate,'"'); 
end;

Index/filter operators/functions

Supported operators (from the documentation mentioned above):

Operator/function Description
+ Concatenate two strings, or add two numbers
* Multiply two numbers
/ Divide two numbers
= Compare two strings, numbers or booleans for equality
<> Compare two strings, numbers or booleans for inequality
< True if the first argument is smaller than second argument
<= True if the first argument is smaller than or equal to the second argument
> True if the first argument is greater than the second argument
>= True if the first argument is greater than or equal to the second argument
NOT Negate the boolean argument to the right
AND Returns true if both the first and second boolean argument are true
OR Returs true if the first or second boolean argument is true
STR(num[,size,precision]) Converts a number num to a string with optional size and precision

num: number to convert to string size: the total number of characters to output precision: the number of digits to the right of the decimal point

DTOS(date) Converts date to a string with representation YYYYMMDD
SUBSTR(str,index,count) Extracts a substring from string str from position index and length count
UPPER(str) Returns the uppercase equivalent of string str
LOWER(str) Returns the lowercase equivalent of string str

Code example: creating a table/DBF file

The following sample creates a new table "customers" in code. This of course only needs done once, and after that you just open the table, instad of creating it. ;-)

{$MODE OBJFPC}{$H+}
Program DatabaseTest;
{ We need the following units be in the USES clause: }
uses Dbf, db, Dbf_Common, SysUtils;  
                                 
{ The Dbf is put there e.g. when you drop a TDbf component on a form...   }
{ but you will need db for the DataSet object and Dbf_Common              }
{ for things such as the field type definitions                           }
{ Finally, use SysUtils for ForceDirectories.                             }
var
  MyDbf: TDbf;
begin
  MyDbf := TDbf.Create(nil);
  try
    { make sure the data directory exists - otherwise this may fail (e.g. on Windows XP): }
    ForceDirectories('data');
    { use relative path to "data" directory }
    MyDbf.FilePath := 'data' + DirectorySeparator; 
    { we want to use Visual dBase VII compatible tables }
    MyDbf.TableLevel := 7;
    MyDbf.Exclusive := True;
    MyDbf.TableName := 'customers.dbf';
    With MyDbf.FieldDefs do begin
      Add('Id', ftAutoInc, 0, True);
      Add('Name', ftString, 80, True);
    End;
    MyDbf.CreateTable;
    MyDbf.Open;
    MyDbf.AddIndex('custid', 'Id', [ixPrimary, ixUnique]);
    { add a secondary index }
    MyDbf.AddIndex('custname','Name', [ixCaseInsensitive]);
    MyDbf.Close;
  finally
    MyDbf.Free;
  end;
end.

External Index Files

The TDbf component also supports storing secondary indexes in a separate file. This might be helpful if the database is expected to be very large. Secondary index files are created almost identically to normal indexes, but with the addition of the '.ndx' file extension:

MyDbf.AddIndex('custname.ndx','Name', [ixCaseInsensitive]);


Each time the TDbf is opened, the index file must be loaded:

MyDbf.OpenIndexFile('custname.ndx');


And indexes must be referenced including the extension:

MyDbf.IndexName := 'custname.ndx';


Index files can be packed (i.e. entries pointing to deleted data rows are removed) separately using:

MyDbf.CompactIndexFile('custname.ndx');

How to link the TDbf component to data-aware components

The above examples show how to create a new database table in code. Using that table is even more simple.

Data aware components in Lazarus (such as the TDbEdit control) link to a TDataSource component using their "DataSource" and "DataField" properties. The TDataSource component handles communication between the database engine and the data aware components. A TDataSource then links to the TDbf component using it's "DataSet" property. The connection looks like this:

TDbEdit-------
             |
TDbEdit------|-->TDataSource-->TDbf
             |
TDbNavigator--


Be sure to set the FilePath (or FilePathFulll), TableLevel, and TableName properties of your TDbf component before calling

TDbf.Active := True;


Master table relations

Real power in database programming begins when you have multiple tables that reference each other. While TDbf does not yet support full referential integrity (so what is missing?), it does support a master / detail relationship between TDbf's.

When there are two tables related, for instance:

[customers]
Id       <----|
Name          |
Phone         |
Address       |
              |  The CustID in invoices references a customer primary  field
[invoices]    |
Id            |
Amount        |
CustID   -----|  * This field indexed as "idxcustid"


If you wanted to display all invoices for a given customer, the detail table (invoices) can stay in sync with the master table (customers) automatically.

On the invoices TDbf component set the following:

InvDbf.IndexName := 'idxcustid'; // our field that will match the customers table ID
InvDbf.MasterSource := dsCustomers; // datasource that is linked to the customers TDbf
InvDbf.MasterFields := 'Id'; // field on the customers table we are matching against our index

Searching and Displaying a data set by using filters

Simon Batty

In this example I wanted to search a database of books for all the titles an author has listed and then display the list in a memo box

Dbf1.FilePathFull := '/home/somelocatio/database_location/'; // path to the database directory
Dbf1.TableName := 'books.dbase';                             // database file (including extension)
DbF1.Open;
memo1.Clear;                                                 // clear the memo box
 
Dbf1.FilterOptions := [foCaseInsensitive];                   // set FilterOptions before Filter !
Dbf1.Filter := 'AUTHOR=' + QuotedStr('anauthor');            // AUTHOR is the field name containing the authors
                                                             // Note that we need to surround the value with double quotes: "James Joyce"
 
Dbf1.Filtered := true;       // This selects the filtered set
Dbf1.First;                  // moves the the first filtered data
while not dbf1.EOF do        // prints the titles that match the author to the memo box
begin
  memo1.Append(Dbf1.FieldByName('TITLE').AsString);          // TITLE is the field name for titles
  dbf1.next;                                                 // use .next here NOT .findnext!
end;
Dbf1.Close;

Note that you can use Ddf1.FindFirst to get the first record in the filtered set, then use Dbf1.Next to move though the data. I found that using Dbf1.FindNext just causes the program to hang.

Filters

There are two ways to use filters:

  • use filter expressions
  • write an event handler for OnFilterRecord; if you want to show the record, set Accept to true.

In both cases, set the Filtered property to true to enable filtering.

Example: imagine you have a database that identifies the products by a code, for example, B019, and you need to filter only those products that are of this code so that a report is generated with LazReport. Let's assume your DBF object is called TDBF1 and the field with product codes is called "product_no".

Expression filters

Implement filtering using a filter expression like this:

TDBF1.Filter := 'product_code = "B019"'; //string type field; string constants should be quoted by ' or "
TDBF1.Filtered := true;
TDBF1.Filter := 'product_code = "B*"'; //partial match filter; filters all products which code begins with B
TDBF1.Filtered := true;

Filter on multiple fields:

// Needs to be tested. If it works, please remove this line.
MyDBF.Filter:='LASTNAME;FIRSTNAME';

Adapted from: forum post [9]

Here is an example of using a DBLookupCombobox value in a filter statement (where NAME is the field from the DBf):

Filter := 'NAME=' + QuotedStr(DBLookupcomboBox1.Text);

Here are two examples of using two comboboxes in a compound (i.e. 2 fields) filter statement (where NAME and TYPE are field names from a DBf):

In this example you are going to "and" the two values.

// unfortunately, this will fail whenever the user types a ' in TypeComboBox.Text...
Filter := 'NAME=' + QuotedStr(DBLookupcomboBox1.Text) + ' and TYPE=' + QuotedStr(TypeComboBox.Text);
Filter := Format('NAME="%s" and TYPE="%s"',[DBLookupcomboBox1.Text, TypeComboBox.Text]);

An example of using a combobox to provide the field name for the filter and comparing that to the value in lookup combobox:

Filter := FieldComboBox.Text + '=' + QuotedSTR(DBLookupcomboBox1.Text);

In this example, the FieldCombobox is programmatically populated with the names of the fields from the DBf:

// fill the combobox with Field Names
for i:=0 to Dbf.FieldCount-1 do
  FieldComboBox.Items.Add(Dbf.Fields[i].FieldName);

Please see the section #Expressions above for the list of supported expressions as well as more examples.

OnFilterRecords

To use this method, you need to write a function.

In your main code, write something like:

OnFilterRecord := @TestOnFilterProc; //instruct tdbf to lead all records past your OnFilterProductCode procedure
Filtered := True; //actually enable filtering

Then write your event handler:

procedure TTestCursorDBBasics.TestOnFilterProc(DataSet: TDataSet; var Accept: Boolean);
begin
  // Filter out everything except product_no=B019
  Accept := Copy (Trim (Dataset.FieldByName ('product_no'). AsString),1,4) = 'B019';
end;

All records that give a false value for Accept are filtered out i.e. are not displayed in the report.

Filter on null values

You can use this method to show only empty (null) values, something you cannot do with the Filter property. An example - assuming you have a field called orderdate:

procedure TTestCursorDBBasics.TestOnFilterProc(DataSet: TDataSet; var Accept: Boolean);
begin
  // Show only empty order dates
  Accept := Dataset.FieldByName('orderdate').IsNull;
end;

Packing and rebuilding the tables

When a record is deleted, it's not truly removed from the physical table. Periodically you must "pack" a table to recover that lost space. This should be done with exclusive mode set. See above for remarks on packing external index files.

MyDbf.Exclusive := True;
MyDbf.Open;
MyDbf.PackTable;
// let's also rebuild all the indexes
MyDbf.RegenerateIndexes;
MyDbf.Close;
MyDbf.Exclusive := False;


Encryption

Apparently at least DBase IV and 5 support some kind of encryption (there is an encryption flag in the file structure), but it may well be that the password/encryption key is in the DBF itself (see [10]), making it insecure.

There are other options if you want to encrypt your data. See here discussion on DBase encryption

Operating system level encryption

You can use an encrypted filesystem such as TrueCrypt (containers) to encrypt the directory where the database files get stored.

Encrypt the DBF file

You can use streams (e.g. the Blowfish encyprtion stream code supplied with Lazarus/FPC) to encrypt the data (all tables etc) by storing the unencrypted file e.g. in memory, then writing that to disk:

  1. Open your file in a TFileStream.
  2. Decrypt TFileStream to a new stream, YourDecryptedStream
  3. Tdbf.UserStream:= YourDecryptedStream; //Note: this will not encrypt .dbt/memo files! Use UserMemoStream for this (in FPC development/trunk since May 2013 only)
  4. Tdbf.Storage:=stoMemory;
  5. Tdbf.Open;

When finished

  1. Tdbf.Close
  2. Encrypt YourDecryptedStream to TFileStream
  3. Free stream(s)

Detailed code can be found in the forum thread mentioned above. People implementing this are welcome to adapt this article with working code.

Encrypt field data

You could also choose to encrypt field data by implementing the OnGetText and OnSetText events to encrypt/decrypt the field contents.

Code pages and string encoding issues

The dbf format is very old and thus can only handle strings with single-byte characters. Since Lazarus uses UTF-8 encoded strings, there must be a conversion between both formats, otherwise the dbf strings are not displayed correctly in the LCL controls.

For this purpose the TDbf component fires an event OnTranslate for string fields. The event provides a boolean parameter ToOEM telling whether the conversion must go to a single-byte codepage or to UTF-8. Typical code is as follows:

uses
  LConvEncoding;

function TForm1.DbfTranslate(Dbf: TDbf; Src, Dest: PChar; ToOEM: Boolean): Integer;
var
  s: String;
  cp: String;
begin
  cp := 'cp' + IntToStr(Dbf.CodePage);
  if ToOEM then
    s := ConvertEncoding(Src, 'utf8', cp)
  else
    s := ConvertEncoding(Src, cp, 'utf8');
  StrCopy(Dest, PChar(s));
  Result := StrLen(Dest);
end;

After opening the dataset all string fields to be converted must be defined by setting their property Transliterate to true.

// Open dbf file
Dbf1.Open;

// Make sure that strings are converted to and back from UTF8.
Dbf1.OnTranslate := @DbfTranslate;
for i := 0 to Dbf1.Fields.Count-1 do
  if Dbf1.Fields[i] is TStringField then
    TStringField(Dbf1.Fields[i]).Transliterate := true;

When a dbf file is newly created its codepage can be set by using the LanguageID property. This is a combination of locale specific information (for sorting) and the code page. The unit dbf_lang contains a list of allowed values, e.g.

const
  FoxLangId_ENU_437       = $01; // DOS USA
  FoxLangId_Intl_850      = $02; // DOS multilingual
  FoxLangId_Windows_1252  = $03; // Windows ANSI
  FoxLangId_Mac_10000     = $04; // Standard Macintosh
  DbfLangId_DAN_865       = $08;
  DbfLangId_NLD_437       = $09;
  DbfLangId_NLD_850       = $0A;
  DbfLangId_FIN_437       = $0B; 
  // plus many more...

The constants beginning with "Fox" are for the FoxPro TableLevels, those beginning with "Dbf" are for the dBase TableLevels.

When strings are written to their fields directly, bypassing the LCL, then the encoding must be converted explicitly. The TDbf property CodePage returns the used codepage as defined by the LanguageID:

uses
  LConvEncoding;
...
Dbf1.Edit;
Dbf1.FieldByName('some_string_field').AsString := ConvertEncoding('äöü', 'utf8', 'cp' + IntToStr(Dbf1.CodePage));
Dbf1.Post;

Third party software

  • MyDbf Studio is an open source admin tool to manage your dbase table. It is entirely written in Lazarus IDE and based on TDbf.
  • [11] are Delphi components for DBF files.
    • Vlad Karpov's open source VKDBF: [[12]]
  • fpspreadsheet have a way to process dbf files and save them to excel:[13]

See also