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:

1) set the header record count to a very high number

2) PACK and REINDEX the table

3) 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.

Data Import and Export (from web sites)

Today database designers and programmers have numerous options to support when it comes to the import and export of data. Common formats supported are:
Comma Separated Values - CSV
Lotus Symphony spreadsheets
Lotus 1-2-3 spreadsheets
Excel spreadsheets
Other databases - SQL, Visual FoxPro, MS Access
XML
Common application formats - MS Word, MS OutLook
All of these have their pros and cons. However, considering them all, XML (Extensible Markup Language) has; as intended; become the most important.

This is due to its wide spread support and flexible nature. Its flexibility allows for any form of data from any database to be exported to, or imported from, an XML file.

So any modern ERP software system worth considering, must be able to import/export data into/from any of its tables. Not just a few tables chosen by the developers.

This ability to import data is very important for SME's with an e-commerce web site. As if it cannot import web site data into its ERP system without manually re-entering the data, it seriously needs to consider replacing the system with one that can. Just to avoid the errors and mistakes generated by the process of human intervention.

With a capable ERP system data from an e-commerce web site can be handled in various ways depending on whether the site is hosted or held on an internal server.

Hosted Site
Two approaches are commonly taken -
1) e-mail of the data to the company when the customer confirms the order, or
2) FTP access to an order file produced when the customer confirms the order.

1) E-mail - given a low volume of orders; less than 100 per hour; the e-mail route is considered the best approach to take.
With this approach when the customer clicks the Confirm button on the web site, the underlying program formats the data into XML format, either as a separate file that becomes an e-mail attachment or as the body contents of an e-mail. The e-mail is then automatically sent to the companies e-mail address.
Depending upon volume and required response times the e-mail address is read on a frequent basis with rules to place the e-mail(s) into a special folder. When XML attachments are used, these need to be saved into a folder on the server ready for the ERP system to process.
When the XML is in the body of the e-mail they can be read directly by the ERP system.
Having now got access to the data, the ERP system can be run manually or automatically under a timer to read the XML data and update its tables, ready for the normal sales fullfilment process to begin.
After processing each e-mail/file the system should move the source e-mail/file to an archive folder prior to deleting it from the source folder. The e-mails/files need to be available for when a processing error occurs.

2) FTP - here the ERP system can run automatically under a timer to read; on a frequent basis; the XML data files held in a web site folder, process their contents and then move the file(s) to an archive folder, prior to removing the file(s) from the web site folder, in order that they are not processed again. (An alternative approach is to rename each file as it is processed.) The file(s) need to be available for when a processing error occurs.
Once the data is imported into the ERP's tables, the normal sales fullfilment process can begin.

Internal Server Site
Many more options are available here, but the essential feature is that a folder is available to both the web server and ERP system. The web server places XML files in the folder as orders are confirmed, whilst the ERP system reads these files and updates its own tables on a regular basis and removes or renames the XML files after processing.

On all these approaches the format and data content of the XML file is dependent upon the specfics of the ERP systems and its requirements.

The only manual intervention (other than dettaching XML e-mail files) in these processes is when the user company decides to manually run the import process instead of it running automatically under a timer.

When an ERP system can export its data in XML format, the resulting file(s) can be FTP'd to the web site. Then using XSL (Extensible Scripting Lanaguage) the contents of these XML files can be easily incorporated into web pages.

So products, stock levels, customer accounts, etc. can all be made available on the web based upon the primary source (ERP system) with very little manual intervention. Thus removing the need for dual data maintenance.

An example of a web site using this approach is www.places-to-go.org here advertisors and people requesting a listing complete a form which is e-mailed to the company. Who import the data from the e-mail using Acceptum Business Software as its ERP software. This data can then be exported in XML format and then FTP'd to the web site to provide the list of attractions, which is formatted, sorted and extracted on a county basis using XSL.

XML use has opened up a whole new world for the SME allowing web sites to have the power and feel of a much larger organisation. In fact the very flexibility of SME's over monolithic large corporations gives them the opportunity to have a far better web presence than larger organisations.

SQL - LIKE and IN clauses

Reading PC World recently, I was spurred into action by an article about using the Like clause in SQL statements to report disparate values.

In MS Access the clause has a syntax of - Accounts.Acct LIKE "[ADS]*" which will extract records with the account field containing values beginning with A or D or S.
The * denoting the wild character.

With MS Visual FoxPro the syntax is not quite so neat, but can be achieved with the clause -
Accounts.acct LIKE "A%" OR Accounts.acct LIKE "D%" OR Accounts.acct LIKE "S%"
The wild character here is %.

An alternative approach, is to use the SQL IN clause, for which the above becomes
LEFT(accounts.acct,1) IN ("A", "D", "S")

Notice the use of the function LEFT() to test just the first character of field values.

We could even have - LIKE "%a%" - to report those records whose field contains an 'a'.
Or - LIKE "_a%" - to report those records whose field contains an 'a' in the second position.
With the '_' wild carding a single character.

When are these clauses used?
Not often is the obvious reply, due to a few reasons:-

1) SQL writers and users are not familiar with them, especially if they are not writing SQL statements every day.

2) The SQL clause syntax changes from one database to another.

3) Not often required to report disparate data values.

This last point is, I contend, a false assumption based on the restrictions imposed by the majority of todays ERP business software packages.
It need not be so!
As if available, they can often be used in normal business processing and coding structures.
To explain, it is best to look at a common example.
Suppose you have a company with numerous locations/branches spread throughout the country, say - Leeds, London, Glasgow, Cardiff, etc. and at each location the typical functional departments of Sales, Marketing, Manufacturing/Assembly, Accounts, Personnel, Warehousing all report to the local senior manager on a day to day basis. With central control of each functional department being maintained via head office.
On a Cost Centre coding basis we can have the location coded as
100 - London
200 - Glasgow
300 - Leeds
400 - Cardiff
...

and functional departments coded as
10 - Sales
15 - Marketing
21 - Manufacturing
22 - Assembly
30 - Warehousing
40 - Accounts
50 - Personnel
...

Combining the 2 elements together, assuming Location to be the primary element we get
10010 - London Sales
10015 - London Marketing
10021 - London Manufacturing
...
20010 - Glasgow Sales
20015 - Glasgow Marketing
20021 - Glasgow Manufacturing
...
30010 - Leeds Sales
30015 - Leeds Marketing
...

Such a coding structure using the standard From/To selection range can easily report on a Location basis.

But how do head office functional departments see the costs of their own departments spread across the company?
With the use of SQL LIKE and IN clauses!

Under Visual FoxPro the SQL LIKE clause for all for Accounts Depts. at all locations would be written as
CostCentre.code LIKE "___40"
The '_' wild cards a single space, so 3 are required to wild card the location element.

Being able to use such powerful SQL features can remove a lot of restrictions imposed on coding structures and similiar data elements.

So when reviewing ERP software packages for potential use/purchase, look for the flexibility to be able to use such SQL features when reviewing data and drawing reports.

You may not thing you need it today, but who knows what tomorrow brings.

First Entry

Rowanberry Consultancy Ltd offers Acceptum Business Software from its web site for UK SME's looking to use an ERP system.

One major advantage of an ERP system is that it provides information on a real-time basis. So management can always be sure that the data reported by the system is the current state of affairs for the entered data.
A disadvantage is caused by management not understanding or appreciating the phrase '... for the entered data.', believing that the data is entirely correct, when in fact the data is incomplete in some way. Making decisions on such incomplete data can cause problems, even severe financial loss.