Page 1 of 1

Delphi – Retrieve Corrupted Data from DBF Databases

Delphi – Retrieve Corrupted Data from DBF Databases
   0

dBase files, and several “clones” of this format, have been used for decades, and this format is no stranger to developers who use environments like Delphi and Lazarus Pascal.

Sometimes however, we run into the issue that we cannot access our data and in this article, sponsored by Devart, show how you may recover your data with one of their great tools: UniDAC.

On that note: I have used their MyDAC (database access for MySQL) in the past to develop a full fledged LIMS and can only say that Devart delivers one of the best and most reliable database access components you can find.

Keep in mind that these components are commercial products.
However, almost all of them have full-featured Trial versions and free Express versions with limited functionality available for commercial development. I highly recommend them. Also note that quite a few Devart products are suitable for application development for other platforms, for example macOS.




About dBase (dbf) files

A DBF file (.dbf) is a standard database file originally used by dBase, one of the first database management systems, originally released way back in 1979. It was the most successful DBMS at the time, and is still being maintained and actively developed.
Besides a database engine and query system, dBase includes a forms engine and a programming language. 

Over time, a host of dBase clones emerged,  which were called xBase databases because they used the .dbf file format to store structured data. The list of such databases includes FoxPro2, Visual FoxPro, various versions of dBase, Clipper, CodeBase, Hiper-Six, and others.  

Getting started with Embarcadero Delphi … for free … 

For those interested in getting started with Delphi, keep in mind that there is a so called Delphi Community edition available for free, offering pretty much everything the commercial version would offer. There are limitation though to its commercial use.

 

Using UNIDac, to recover dBase data

Ultimately, this has lead to situations where users are unable to access data in a .dbf file due to the use of incompatible data types from various xBase dialects in a single file. 

Developers of Delphi database applications can prevent possible errors when accessing DBF files from their applications by using  special options in the DBF data provider in Universal Data Access Components (UniDAC). They are called IgnoreDataErrors and IgnoreMetaDataErrors.

The first option, IgnoreDataErrors, simply helps to ignore any corrupted data in a table. A DBF table has “header” and “data” sections; the header contains metadata of the table. As dBase and its dialects evolved over time, new formats of DBF files emerged, such as dBase III, dBase IV, dBase V, dBase VII, dBase 10, dBase for Windows, HiPer-Six, FoxPro 2, Visual FoxPro. Each new format introduced some new enhancements over the previous one.

dBase is still a commercial product as of today (See: http://www.dbase.com/dbasesql/). The owner company has only made public the table structure for dBase VII. UniDAC understands all the above mentioned formats and can connect to your database either directly, without involving any driver at all, or through an ODBC driver installed in the system.

Below is a table to help you understand the header format for dBase III — dBase V (source):

dBase file format

dBase file format

As noted above, dBase is a commercial product and versions prior to dBaseVII are not made publicly available. Anything you will find on the internet is a result of many trial and error methods that different enthusiasts or companies have tried. As such, it is possible that you’ll get a DBF file that you’re able to open, but not able to fetch all or some data from it because of an incorrect data format.

Below is a dBaseIII table that holds more than 3700 detailed parameter records. At first glance, everything looks correct. For example, if you open the file in a hex editor, its header and data will be presented as follows:

dBaseIII records

dBaseIII records

Offset 240h is marked in red.

This is where the header of the dBase file ends and the data part (records) starts.

As there is no visible problem, the developer usually uses default parameters of TUniConnection and opens the table using this simple code:


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
uses
  Uni,
  UniProvider,
  ODBCUniProvider,
  DBFUniProvider;

...

procedure TForm2.FormCreate(Sender: TObject);
begin
  UniConnection1.Close();
  UniConnection1.Provider := 'DBF';
  UniConnection1.Database := 'c:\data';
  UniConnection1.Open();
end;

Note: The above example assumes that we are developing a Window application, where the database file is located in the ‘data’ directory in the C drive.

However, I do know that that this table of mine contains corrupted data. More specifically, the column definition in the header doesn’t match the data type in that column, e.g. float data is stored in an integer column, the value 00000000 is kept in a date column, etc.

Also, issues with hardware can easily corrupt your table data, e.g. because of a power outage.  It may not be common in your place of living, but in some areas, power outages still happen from time to time. In Holland I have seen outages twice in my entire life, so they have a pretty stable powergrid. But after I moved to the US, I learned that power outage are relatively common in certain areas (even in metropolitan areas like Houston).

Since there are quite a few records in the table, it is not easy to find  the exact location/record number. Trying to fix the error manually is out of the question as it is easy to corrupt the data in adjacent columns or other records while using a “hex editor”.

Accessing the data anyway …

By default, UniDAC fetches all records of the table. If the table contains corrupted data, you are most likely to receive an exception like the one illustrated below. The error message may differ depending on your UniDAC version. It clearly indicates that you have corrupted data in the table. At the time of writing, the latest version 8.1.2 of UniDAC raises the following exception:

UniDAC exception

UniDAC exception

In a situation like that, you can use the IgnoreDataErrors specific option as a workaround. Actually, it is highly possible that you won’t be able to open such a table in any other way. In order to enable the option at run-time, you code may look like this:


1
2
3
4
5
6
7
8
9
10
11
12
13
uses
Uni,
UniProvider,
ODBCUniProvider,
DBFUniProvider;

procedure TForm2.FormCreate(Sender: TObject);
begin
UniConnection1.Close();
UniConnection1.SpecificOptions.Values['IgnoreDataErrors'] := 'True';
UniConnection1.Database := 'c:\data';
UniConnection1.Open();
end;

In order to enable the option at design-time, you can double click on the UniConnection1 component (the name may be different, depending on the name of your TUniConnection class instance) on your DataModule/Form, switch to the Options tab, set the Provider to DBF, and set the value of the IgnoreDataErrors option to True, as shown in screenshot below:

UniDAC Connection

UniDAC Connection

If that doesn’t work …

If this doesn’t help to open your table, you may also enable the IgnoreMetaDataErrors option, which forces UniDAC to ignore metadata errors – another type of errors which you can occasionally see in DBF files.

Also, errors may occur in a DBF table that has null values in non-nullable columns, in which case the AllFieldsAsNullable helps to properly retrieve data.

I’ve spent quite some time trying to identify the corrupted data and fix it in my table. It is also possible that there is more than one corrupted column or field in the table, which makes it even more difficult to identify and fix the issue manually. However, the IgnoreDataErrors option has helped me to open the table and retrieve data from it.

One thing you must be aware of is that, because the data is corrupted, it is impossible to find out what values were stored in the fields: UniDAC retrieves them and displays on a data-aware component as values of the type defined in the table header. One way to solve this problem is to create an empty table with the same structure and use the TUniLoader or TCRBatchMove component to copy the data into a new table. After that, the new table can be used instead of the one with corrupted data.

Platform Support … 

UniDAC supports multiple target platforms: you can create database applications that work on Windows (32-bit and 64-bit), macOS (32-bit and 64-bit), iOS (32-bit and 64-bit), Android (32-bit and 64-bit), Linux (32-bit and 64-bit).

Since UniDAC also supports Lazarus Pascal, I hope to be able to post an article on how to use UniDAC under Lazarus Pascal as well.

Donation options


Donations are very much appreciated, but not required. Donations will be used for web-hosting expenses, project hardware or a motivational boost (a drink or snack). Thank you very much for those have donated already! It's truly AwEsOmE to see that folks like our articles and small applications.

Please note that clicking affliate links, like the ones from Amazon, may result in a small commission for us - which we highly appreciate as well.

Comments


There are no comments yet.
You can post your own comments by using the form below, or reply to existing comments by using the "Reply" button.



Your Comment …

Do not post large files here (like source codes, log files or config files). Please use the Forum for that purpose.

Please share:
*
*
Notify me about new comments (email).
       You can also use your RSS reader to track comments.


Tweaking4All uses the free Gravatar service for Avatar display.
Tweaking4All will never share your email address with others.