Visual FoxPro - Corrupt Tables Due to Header Record Count
Since Version 8 of VFP programmers have been able to set how tables are validated when they are opened.
The level of validation performed is via the command
SET TABLEVALIDATE TO [nlevel]
For a full descriptions of [nlevel] see the VFP 8/9 Help system.
The default value for [nlevel] is 3; which is described in Help as:
Check .dbf header before saving append operation to disk and modifying header.
This means that the default action is to validate the table header record count on opening and on update.
What happens when the record count in the header does not match the actual number of records in the file?
The program returns an error message
2091 - Table "name" has become corrupted. The table will need to be repaired before using again.
This unexpected message appears to be quite terminal to recovering the VFP table! As no 'REPAIR' command is available nor is any advice given on what to do next.
How come this message is generated in the first place!
a) it could be due to a corruption caused by bad programming, system crashes etc.
b) it would appear that some virus checking programs do not release/unlock the tables header record correctly at a time when VFP is writing a record to the table and this causes the header record count to become out of sync. with the number of records held in the table.
The timing of these events appears to be crucial in causing the error.
So for 1,000 records written to a table it may only occur once.
How do you recover the table and its contents when this happens?
First you need to have an environment under which the table is NOT validated when it is opened. This can be achieved with the command -
SET TABLEVALIDATE TO 0
0 being - Do not perform table validation.
Now the table can be opened, you need to reset the header record count to the correct value. The structure of a table file can be found in the Help system at "Table File Structure".
This shows that the record count number is held in the first record at positions 4-7 byte offset and is held in hexidecimal format.
Resetting to the correct value is not straight forward, as commands like
RECCNT()
and
COUNT ... TO
only report back the invalid number in the header record. As does the code
i = 0
scan
i = i + 1
endscan
i only contains the number of records held in the header.
Most likely the table will hold more records at the end of the table that are not reached.
In order to regain these records 3 actions need to be taken, in outline they are:
- set the header record count to a very high number
- PACK and REINDEX the table
- reset the header record count to the actual number of records held.
This can be done with code something like (a log file is used to record the actions taken):
Local nHdlr, i, lslog, ltn
...
...
...
CLOSE TABLES ALL
* create/open log file
nHdlr = FOPEN('compact_repair.log', 11)
IF nHdlr = -1 then
nHdlr = FCreate('compact_repair.log')
ELSE
FSEEK(nHdlr,0,2) && attempt to get to end of file
ENDIF
lslog = "**** Started " + TTOC(DATETIME(),1) + " ****"
FPUTS(nHdlr, lslog)
ADBOBJECTS(aTabs, "Table")
=ASORT(aTabs)
FOR i = 1 TO ALEN(aTabs)
* when tables not validated, any records
* after the headers record count are deleted
* so we need to check/reset the record count
* in the header before doing a PACK
* gdatabasename is a global for the database name
ltn = gdatabasename + "!" + aTabs(i)
* set rRecCount in header to a high value
resettabheader(ALLTRIM(aTabs(i)), nHdlr, .T., .F.)
USE (ltn) IN 1 EXCLUSIVE
PACK
REINDEX
USE IN 1
* 2nd call resets RecCount in header to correct
* value and logs changes
resettabheader(ALLTRIM(aTabs(i)), nHdlr, .F., .T.)
ENDFOR
USE IN 1
...
...
...
The procedure "resettabheader" is coded as:
PROCEDURE resettabheader
LPARAMETERS ptab, nloghdlr, psethigh, plog
LOCAL ls1, ls9, lslog, nHdler, lfn
LOCAL ln1, ln2, ln3, ln4, nRC, nCount as integer
nCount = 0
ls9 = ".dbf"
IF psethigh then
* set record count to high number *
* maximum record count allowed in VFP
nCount = 999999999
ELSE
* not setting high value, so count the
* number of records using Scan
* which automatically stops when the
* end of the table is reached
USE (gtn) IN 1
SCAN
nCount = nCount + 1
ENDSCAN
USE IN 1
ENDIF
* open current table file using low level
* file commands and reset the record counter
* gcnetfldr and gcdatafolder are globals
* that hold the path data to the current database
lfn = gcnetfldr+"\+gcdatafolder+LOWER(ptab)+ls9
nHdler = FOPEN(lfn, 2)
IF nHdler = -1 then
lslog = "File not found - " + lfn
ELSE
* convert our record count number to hex format
ln4 = FLOOR(nCount / 16777216)
nRC = nCount - (ln4 * 16777216)
ln3 = FLOOR(nRC / 65536)
nRC = nRC - (ln3 * 65536)
ln2 = FLOOR(nRC / 256)
ln1 = nRC - (ln2 * 256)
lslog=lfn+" contains "+STR(nCount)+" records."
* move file pointer to the 4th byte
FSEEK(nHdler, 4)
ls1 = CHR(ln1) + CHR(ln2) + CHR(ln3) + CHR(ln4)
* write record count number to the table file
FWRITE(nHdler, ls1, 4)
* low level file close
FCLOSE(nHdler)
ENDIF
* output log data
IF plog then
FPUTS(nloghdlr, lslog)
ENDIF
RETURN
***************
Warning - you use this code at your own risk!
After records have been recovered in this manner, the validity of the table and its records MUST be verified.