Introduction

DBF is still a universal data source format and even if the choices widen or vary in the future, there will be a great deal of legacy data of this format to support for many years to come. The DBF format still holds popularity ultimately because it’s cheap, easy to use and easy to repair. It is readily converted to any other format, especially to XML, where direct on-disk string manipulation makes DBF a superior internet format. But all this format flexibility appears to come at a cost. The potential for data corruption (and more particularly index corruption) seems to be greater than the auto-integrity providing environments like Oracle and SQL.

Well, if this is the case then it is just as easy to detect and make the repairs. There have been many articles in the past covering the structure of DBFs and our initial discussion will cover the same ground. However, we will go beyond this into memo file structure and then show you how to extend the repair technology into your code. Once you have this technology built into your app, you will be able to claim with some confidence that you are providing true relational and structural integrity.

Language Base

This presentation will provide two formats: CA Visual Objects and Clipper. From the Clipper code it will be easy to extend the concepts to any other Xbase format and the VO code will assist those developing in FiveWin or Delphi. The fully written samples will be available from the website. Further, when we get to the discussion on indexes, my coded examples will be based on CDX but for those who use NTX, we will provide some parallel examples. The concepts are obviously identical.

Tools to Use When Repairing DBF Files

The most basic tool is a hex editor. I use Hex Workshop from Break Point Software. It costs about US$ 45 and comes with all the built in calculators and editors. For those tricky byte manipulations you just can’t do with a text editor or VO, this is the only way. There are many commercial hex editors around.

The second most important tool is a Database utility such as dbAdmin from Eddie Çelebi or dbAssist from Senad Burak. Lest you think this is an unpaid commercial, there are several other popular tools, but these two are superior by a long margin. Especially if you need to convert DBF, index or memo formats, these two contain superior tools. They also have the Windows equivalent of command lines utilities and can basically perform every operation your application can.

Finally, the third set of tools will be the diagnostics you build into your app (by way of auditing) or the methods we discuss in here.

The Design and Nature of DBF Files

A DBF file is essentially a text file, a “flat file” in mainframe jargon. It follows a fixed record sized format and whilst this is its main limitation (in terms of potential storage efficiency), it is also the key to understanding how to repair broken DBFs. Let us analyze the positive features of a DBF:

• Being a simple text file, we can access it with low-level file access routines like FOpen() and FRead(). All XBase developers will be familiar with these tools. This is same for index and memo files.
• DBF file formats have well defined and predictable structures. In the case of a DBF there is one header section followed by one data section. The data section is totally defined in the header, right down to the number of records and their contents. This predictable structure makes it extremely easy to repair.
• Memo fields contain an offset into the accompanying memo file. In the case of memo files there is a header and data section defined for each memo entry saved for the DBF. Even if the DBF defines multiple memo fields, they are all saved inside the same memo file. Access is surprisingly easy.
• All data is stored as ASCII strings: numbers are simply as the string equivalent (eg 1234.50), dates in the form of YYYYMMDD and logics as T or F. This makes it easy to read with standard hex editors. Only the memo field requires some computation in order to produce the memo file offset.
• Very low cost when compared with a fully featured RDBMS.
• Simple peer to peer networks are simple to install and operate.

So what are the disadvantages? Well of course there are some:

• Relational and structural integrity for SQL and similar data sources is provided within their runtime engines. We must code this and execute it explicitly with DBFs.
• We access our DBFs through an RDD, not the complex runtime engine of an RDBMS, so data security and access security is much more difficult to implement natively. In fact, there is no true file access security like that achievable with, say, Microsoft’s SQL Server.
• Connecting hundreds of current users to the humble text file is going have unacceptable bottle necks. The RDBMS runtime engine is designed to specifically cope with a client/server design.

Hence we can see from these points that the DBF has a role and a use that probably won’t go away for a long time. So, our goal is to exploit the openness of its architecture to provide the features only found in big RDBMS systems.

DBF File Format

As we indicated before, a DBF file comprises a header, followed by a fixed number of records. The header will define precisely all details of the records. Below is a summary of the DBF file structure. A detailed description can be found in Attachment 1 to this document:

Figure 1: Layout of a DBF Header Structure

Note that we have 19 spare bytes in this structure. Whilst I would not generally advocate messing with a proprietary “standard”, it is reasonably safe to assume that the DBF structure is mature and that these bytes are not going to be used in the future. So, we can use them for our own benefit in a totally benign way. We will use them to store a “signature” as we will discuss later. This signature will be used to save either CRC or version data and it will help manage our structural and relational integrity code.

Next, lets review the structure of the field record:

Figure 2: Field Description Layout

Note that in the field description there is plenty of free space. Again, it would be perfect for the storage of integrity results and version information. We won’t go into this level for this session and the only reminder I would give would be to watch for changes in the format, unlikely as that may be.

So in summary, what do we know about a DBF?

Well, we know now that the RDD reads in structural information from the header but that the data fields can only comprise a finite number of field types and that the structure is easily deduced. What do we about the size of the file?

Total DBF file length = 32 + field count x 32 + 1 + record count x (record length+1)

Note that the record length is the sum of the field lengths plus one. Each record is preceded by one byte which is either (20 h) for a normal record or (2A h) if it has been marked for deletion. Other than this, there are no field separators or terminators. This information is important to repairing individual records. Let’s check this out with a Hex Editor. I have created a simple DBF and copied it over whilst empty.

Figure 3: Hex Workshop view of an empty DBF

Now, a quick word about the length of a file. The DBF format for my sample file specifies 193 bytes: 5 fields + header + 1. Yet looking at the hex editor, it is clear we have 195 bytes. In Figure 3 we can see that it is a DBFCDX format, last modified on 1 Mar 01, no records in the file, each record is 40 bytes (not including the delete byte), the header length is 194 bytes (adjusted for the 1 byte leftover after the OD h marker) but not including the EOF marker of 1A h. You must be a little bit careful of this as some copy utilities fail to append the 1A. This is not important as long as you check the file for this byte and account for its presence or not.

All of the important structural information is available in the definition part of the header but verification can be obtained from the field descriptors and the data itself. Finally, overall file length provides one more source of verification (except as explained above). I already hope you can see that it is but a simple matter to even rebuild the structure at runtime using only low-level file handling like FRead() and FWrite(). However, for now, let’s also investigate the companion memo file.

Memo File Format

We will start off by analyzing the FPT structure as it has the simplest structure to study. A full description of both DBT structures (for DBase III and IV) as well as for the FPT structure can be found in appropriate attachments. The empty FPT memo file created by any DBF utility for DBFCDX has a very simple structure:

Figure 4: Field Description Layout

Figure 5: Memo File Data Element Layout

Interestingly, there is no EOF marker. The DWORDs can easily be created by simply saving a space to byte numbers 3 and 7. The DBT format is only 512 bytes long, but it is filled with all sorts of garbage which seems to be simply copied out of memory. Quite obviously, either format is easy to create.

Although the relative efficiency obtainable with FPT memos over their DBT counterparts is quite high, the efficiency mainly resides with the inclusion of block size control. The DBT standard block size is 512 bytes whereas with CDX it is 32 bytes and is variable:

From Figure 4 above you can see where we test for this using low-level file access for diagnostic purposes. The memo data element itself is assigned and reassigned under the most erratic of rules. I can set 4 x 10 byte memos and watch the FPT file grow by 128 bytes. Let me edit just one of those memos and add 6 bytes and 1024 bytes get mysteriously added. Obviously the RDD maintains some storage algorithms that manage efficiency but it is far from perfect. The basic nature of the RDD is to keep memos contiguous: this means that as memos are lengthened, they must be moved and the memo file can ONLY append to its file, it cannot reuse old space. However, none of this matters, the memo pointer in the DBF allows us adequate and accurate access to the correct location.

Please see detailed file format descriptions in the attachments to this document. They will be handy references.

Investigating Corruption

Now that we have a description of the basic file structure, we can discuss the possible corruptions and how to address them. There are five basic corruption categories:

• DBF header corruption
• DBF data corruption
• Index corruption
• Memo header corruption
• Memo data corruption

DBF Header Corruption

Corruptions in the header are most likely to cause file opening to fail completely. This is fortunate because proceeding under these circumstances is to ensure disaster will follow. Let’s analyze some of the more common situations and discuss their relative severity:

Detecting header corruption is very easy. As we saw before, we have all the details in the file and can compare them against actual data for verification. When corruption is detected, however, we need to assess which was wrong: the header or the detail. Generally, the developer will need to manually inspect the outcome and make a choice and rebuild the file. As long as you kept backups, you can always try different choices.

*** Please see the accompanying application for some code for header validation. ***

DBF Data Corruption (except Memos)

Data corruption is difficult to detect because in some instances, corrupted data may still result in valid values. Possibly only the user is able to identify these problems, so it is beyond the scope of our activities here. However, we can scan the DBF for valid field types and total file length. There are three specific types of corruptions:

• Data Type corruption – this is easiest to detect and fix, although the fix is usually to replace corrupt data with a null value of the same data type.
• Content corruption – generally we can’t do much about this, but at least it’s often noticeable to the end user. In this case the user simply re-enters the data or recomputes balance fields etc. Where the corruption occurs in key fields or expressions, the user should delete and re-enter the offending record.
• Record length corruption. This is easy to detect and the fix necessarily involves rebuilding the entire DBF. For these errors we either have to pad or remove the extra bytes and fix up the fields accordingly. When you have detected an erroneous record, you can be quick and simply delete the entire record or clever and analyze each field for validity.

*** Please see the accompanying application for some code for the repair of data corruption. ***

After any data repair, reindexing is essential. After that, your user should run any audit or validation routines you have provided.

Index Corruption

We won’t go into the structural repair of indexes to any depth, simply because index repair is built into all DBF formats: you just reindex. For serious index corruptions where even Reindex() won’t cut the mustard, the issue is still not a disaster. Although index structure is just as well laid out as the other structures, it is simple enough to delete the index and rebuild it. There would not seem to be the need to develop any further detail on the structure of an index.

Detecting index corruptions, however, is an entirely different matter. How often have you seen clients “create” a corruption and fail to notice it for some time, thus compounding the problem? Fortunately, determining an index corruption existence is not hard, but it can be time consuming. There are two methods: deterministic and absolute.

• Deterministic: This involves obtaining a random record number and constructing a key from the current index expression. We then “Seek” this key and compare the found record number with the original. They obviously must be the same. We do this for a random number of tests for each order. Next, we again take our random record, increment/decrement the key expression at that record and do a SoftSeek on these values. We should obviously turn up either side of our random record, in terms of the Logical Record Number. Lastly, we should check appending the next higher key value (this after all is where the greatest number of corruptions occur). It should yield the bottom of the file. This is fine for quick tests, but it’s not perfect. Obviously, the greater the number of tests, the greater is the reliability of the outcome.
• Absolute: Now we must inspect the logical order of every record in every order (or index). This will be time consuming, but we will know with certainty if a corruption exists. You could have this as a maintenance option if the user suspects hanky-panky, but in the main the best repair is still simply to delete and rebuild the index.

Assessing key expressions is a little more difficult because often, the appropriate logic is built into the application and is not necessarily built into the index expression. For example, an invoice program might expect to have unique keys (invoice numbers) without “unique” set as an index property. Likewise, the index may be conditional, not using an index-internal condition but one buried within the application. Under these circumstances there is nothing for it but to manually inspect the data. Later I will discuss some common audit techniques to help assess the true conditional validity of data in key fields.

*** Please see the accompanying application for some code for the detection of index corruption. ***

One last comment about reindexing. The command Reindex(), whether in Clipper or VO, is far from perfect. If it is the index header that is corrupted, no manner or quantity of reindexing will repair the situation. The whole index must be deleted. Similarly, “deleted” orders in production indexes (ie CDX) are not removed from the header and can interfere with one another. The only safe index rebuild is one involving total deletion of the original file. A small draw-back but at least you can be guaranteed of integrity.

Memo Header Corruption

Just like data header corruption, this is fairly straightforward and generally results in the failure of the file to open. Whether we are using DBT or FPT, it is relatively easy to rebuild the header and block size, as long as the related DBF is intact. Generally, we can calculate the length of the file and check the validity of the last “block”. With this information we can probably rebuild the file and reset memo pointers (or at least check for the missing ones). Some manual intervention may be required, but I have constructed the rudiments of a tool to inspect a memo and compare it with the “parent” DBF.

One thing to match is the DBF type found in the DBF header. From this you can “guess” block size and then compare with real memo datum elements. You can really only experiment with block sizes for CDX under FlexFile3. All other memo types use fixed block sizes (see the accompanying attachments for details).

*** Please see the accompanying application for some code for the inspection of memo headers. ***

Memo Data Corruption

When memos corrupt, they corrupt progressively, so file use must cease immediately the first time the user sees any odd behavior. However, what went wrong? Unfortunately there are two conflicting causes: an incorrect pointer in the DBF or kludge in the memo file itself. An invalid pointer is easy to detect; the hard part is to know what to do about it. Secondly, properly formed memo entries can be detected and we can form a list of valid entries.

The problem is compounded by the fact that the memo file can re-use some space, but it leaves behind partially used areas. Hence, it’s difficult to extract data if several versions are lying throughout the file. Hence we can build a small tool to inspect the file, hiding non-valid entries.

*** Please see the accompanying application for some code for a demo of the memo inspection tool and the matching tool. ***

Given that we can find valid memos and their pointers, this opens up some interesting ideas. Why don’t we compress out wasted space in our memo file, or re-block it a more efficient blocking factor? For example, for little-edited data, you could even use 1 byte. For often-edited data holding typically 200 bytes or so, perhaps you should use 512 bytes. Of course we can then easily extend this to rebuilding the memo in a different format: DBT to FPT or vice versa.

*** Please see the accompanying application for some code for code and a demo of the packing and reblocking tool. ***

From Clipper 5.0 onwards, we can also store Binary Large Objects (BLOBs) in a memo file and there are a large variety of functions to achieve this. CA Visual Objects went further: we can store OLE objects, arrays and any valid data type in a memo. Such binary objects are obviously a lot more complex to deal with because we can no longer just “look” at the contents and expect to see recognizable data. However, the prefix, type, length and postfix bytes are all still there. With the exception of BLOBs, these other data types are stored as stringified versions, so in some sense they may be recognizable if you know the data they contain.

Causes of Corruption and Preventative Measures

The main causes for corruptions are:

• power failure
• network failure
• software bugs

Power Failures

Generally, power failures are by far and away the greatest cause of corruption. A file merely opened or even reading during power failure is no cause for alarm, but one during a writing operation certainly is. In fact, corruption is almost certain and immediately after a power failure, your user should initiate a full audit and recovery. Many apps are able to determine abnormal closure and so should take matters into their own hands. This can be accomplished quite easily by writing to the registry a simple flag to say that the app closed normally. If it is missing, institute your recovery procedures (like MS Word, for example). The only way to properly prevent power-caused corruptions is to employ a UPS.

Network Failures

Network failures comprise the next most common cause of corruptions: choked or slow networks, faulty NICs, faulty packet switches, inadequate network buffering and other settings… the list is endless. Novell, whilst being a very rugged network, is particularly prone to buffering settings. Multiple protocols (NETBEUI, IPX and TCP/IP together) on a network can cause bottlenecking and even poor cabling has an effect. Faulty NICs contribute to many cases of known corruption. Unfortunately, there is no way around this other than isolating machines one at a time to find the cause.

Software Bugs

…and not yours! In fact, it is almost impossible for the developer to corrupt a DBF, because your code will generally yield a runtime error. However, RDD bugs are not unknown and CA have caused a few issues with memos and indexes over the years. I have yet to see a data storage bug, so in general, I would urge you to remain vigilant to the possibility. When software bugs are discovered, they are generally well documented through the various news group and user group forums.

Preventative Measures

Auditing measures are your strongest methodology for detecting and correcting data errors. Prepare some code to do the following:

• Check all indexes for duplicated keys if they are not supposed to exist.
• Check the detail file keys in master/detail set-ups for corresponding keys in the master.
• Seek “definition” fields in their respective description files.
• Retotal running balances.
• Check all forward/backward references you may have provided internally.

Summary

We have covered a lot of ground concerning the structure and nature of DBF and memo files. We examined the causes of corruption and discussed some coded example tools to aid in their repair. It’s now up to you to build a full working tool to either bury within your apps or to compliment your developer’s armory. At least I hope I have demonstrated that corruptions are not the end and that careful methodology should help you fix most situations.

Bibliography

Bachman, Erik: Xbase File Format Description, Roskilde, Denmark; Clickety Click Software, 2000-12-14; http://www.e-bachmann.dk/docs/xbase.htm

Çelebi, Eddie: Extending the DBServer and Coping with Database Corruption, CA World Technicon 1999; http://www.alt128.co.uk/

dbAdmin, Database tool from Alt128 Services Pty Ltd; http://www.alt128.co.uk/

dbAssist, Database tool from Senad Burak; http://www.cybercrow.net.au/s_burak/

Attachments:

1.         Detailed File Structure of DBF Files

2.         Detailed File Structure of DBT (Dbase III/IV) Memo Files

3.         Detailed File Structure of FPT (FoxPro) Memo Files

DETAILED FILE STRUCTURE OF DBF FILES

 

 

 

 

Click on the image to zoom in.

 

 

DBF Structure Notes:

 

 

*1        dBASE III+: Also called signature byte.

 

            02h  - FoxBase.

            03h  - File without DBT.

            30h  - Visual FoxPro

            83h  - File with DBT. i.e. bit 0-3 version, 3-5 SQL, 7 DBT flag

 

            dBASE IV: (bits)

 

            0-2       Version no.

            3          Presence of memo file

            4-6       Presence of SQL table

            7          DBT flag

 

Examples:

03h      dBASE III w/o memo file

04h      dBASE IV or IV w/o memo file

8Bh      dBASE IV w. memo

8Eh      dBASE IV w. SQL table

30h      Visual FoxPro w. DBC

7Bh      dBASE IV with memo

*2        Sum of lengths of all fields + 1 (deletion flag)

*3        (dBASE IV) Filled with 00h.

*4        (dBASE IV) Production index / Multiple index file

 

01h      MDX file present

00h      no MDX file (index upon demand).

 

(FoxBase)

 

01h      CDX compound index file present,

00h      no CDX file.

 

(Visual FoxPro)

 

02h      With memo

04h      Database Container (DBC)

07h      DBC (incl. memo & indexes)

*5        (Foxpro) Code page:

01h	DOS USA	code page 437
02h	DOS Multilingual	code page 850
03h	Windows ANSI	code page 1251
C8h	Windows EE	code page 1250
64h	EE MS-DOS	code page 852
66h	Russian MS-DOS	code page 866
65h	Nordic MS-DOS	code page 865

 *6       (FoxPro) 12-13. offset of field from beginning of record.
            The field address is irrelevant for other applications.

*7        (FoxPro, Clipper)
            16-17: Field length for non-numerical fields. Byte 16 is normally field length (0-255) and       byte 17 represents the high byte for field length (256-65535).

*8        (dBASE IV) Index field flag:

            00h  -   No key for this field (ignored)

            01h  -   Key exists for this field (in MDX)

*9        2Ah (*)  - Record is deleted

            20h      (blank)  - Record is valid

*10      There are no field separators for record terminators.

*11      dBASE II regards any End-of-File 1Ah value as the end of the file. dBASE III regard an End-of-File as an ordinary character, however it appends an extra End-of-File character at the physical end of the file.
            If the file is packed the physical size of the file may be larger than the logical i.e. there may be garbage after the EOF mark

*12      (dBASE IV) Incomplete transaction 00h Transaction ended (or rolled back) 01h Transaction started

*13      (dBASE IV) Encryption flag 00h Not encrypted 01h Data encrypted

*14      Stored at binary i.e. value is generated as: byte#1 + (byte#2 * 256) + (byte#3 * 256²) +   (byte#4 * 256³) .......

*15      (Visual FoxPro) Database Container (DBC) 263 bytes for backlist. Included in header            structure.

*16      Work area ID is 01h in all dBASE III files

*17      An empty memo field has a reference filled with 10 blanks.

*18      (FoxPro/FoxBase) Field Flags:

01h  -   System column (not visible to user)

02h  -   Column can store null values

04h  -   Binary column (for CHAR and MEMO only)

 

The Structure of Memo Files (*.dbt)

 

dBASE III+

Click on the image to zoom in

*1)    (FoxPro, Fox??) Is reported to use only one field terminator (1Ah). A memo field can be longer than the 512 byte block. It simply continues through the next block. The field is logically terminated by two End-of-file marks in the field. The remainder of the block is unused.

 

Every time you re-write a memo field to the memo file in DBASE the field is APPENDED to the memo file (i.e. the file expands for each update). DBASE V for DOS (and perhaps others) may reuse the space from the deleted text, if memo size <= allocated number of block in dbt file.

 

         The Memo file itself tells nothing about it's use. You need the corresponding DBF file to interpret the content of the memo file. Many of the clones and DBASE 5 have binary elements stored in the memo file marked with file type B or G.

 

dBASE IV

 

 

Click on the image to zoom in

*1)  (FoxBase, dBASE IV ??) Size of blocks in memo file (SET BLOCKSIZE). Default is 512 bytes.

*2)  End of text mark is 0Dh 0Ah and line breaks are 8Dh 0Ah

*3)  (FoxPro, Fox??) Is reported to use only one field terminator (1Ah). A memo field can be longer than the 512 byte block. It simply continues through the next block. The field is logically terminated by two End-of-file marks in the field. The reminder of the block is unused.

 

File Structure of FPT (FoxPro) Memo Files

 

The file format is used by Fox Pro 2.x and later. The size of the header is 512 bytes for FlexFile2 and 1024 bytes for FlexFile3 (Clipper 5.3 and VO).

Click on the image to zoom in

*1)  Binary value with high byte first.

*2)  Size of blocks in memo file (SET BLOCKSIZE). Default is 512 bytes for FlexFile2 and 32 bytes for FlexFile 3.

*3)  Header size of 512 or 1024 bytes as described above.

*4)  Record type

                   00h  -   Picture

                   01h  -   Memo

                   02h  -   Object

*5)  Seems only to be present for FlexFile3.

            A memo field can be longer than the 512 byte block. It simply continues through the next block. The field is logically terminated by two End-of-file marks in the field. The reminder of the block is unused.

 

Downloads

• DBF_Repair.zip (123kb)

Geoff Schaller