Part of the work I do involves supporting existing Visual FoxPro applications. In some cases, the original developers are no longer available and I take over maintaining the code. Some may not like this kind of work, but I enjoy the variety. I also like it when I get the opportunity to track down mysterious bugs like the one I'm about to describe.
The problem occurred in a portion of the code that called the VALIDATE DATABASE command. I don't recall why the code need to perform the command, but when it executed a "Cannot open table 'C:\FolderName\TableName.DBF'" message was displayed on the main Visual FoxPro window.
My first thought was that the table might be corrupt. So I tried viewing the contents of the table with the USE and BROWSE commands as follows. No errors and everything worked as expected.
USE IN SELECT("TableName")
USE "C:\FolderName\TableName.DBF" IN 0 AGAIN ALIAS "TableName" NOUPDATE
SELECT("TableName")
BROWSE
My second thought was that maybe the VALIDATE DATABASE command needed exclusive use of the table and something else had a shared lock on the table. So I tried viewing the contents of the table with the following commands. Once again, no errors and everything worked as expected.
USE IN SELECT("TableName")
USE "C:\FolderName\TableName.DBF" IN 0 ALIAS "TableName" EXCLUSIVE
SELECT("TableName")
BROWSE
My next thought was that maybe the table or database were corrupt in some unusual way. I could try recreating the database and table, and then copy the data from the bad table into the good table. One of the easiest ways to accomplish this is to use GenDBC. GenDBC is a utility program that is included in the Visual FoxPro Tools folder. It generates a script of native Visual FoxPro commands that can recreate a database along with every connection, table, view, relation, and referential integrity constraint.
I ran GenDBC and executed the script to recreate the database and tables. Everything worked as expected. I was about to start copying the data from the bad table into the good table when it occurred to me that I should try validating the database first. So I performed the VALIDATE DATABASE command on the freshly created database...BAM! I got the "Cannot open table 'C:\FolderName\TableName.DBF'" error message.
This made me wonder if maybe GenDBC failed when it generated the script and just did not display the error message. So I opened the script and scrolled down to the section with the table create code. And that is where I noticed something strange. The CREATE TABLE command looked okay, but the INDEX ON command had the table name included in the expression. It looked something like this.
CREATE TABLE "TableName.DBF" (pk I, description C(20))
INDEX ON pk TAG "PK"
INDEX ON TableName.description TAG "DESCRIPTIO"
Including the table name in the index expression is not something I would ever do. It seemed redundant. So I removed the table name from the index expression, re-ran GenDBC, and then executed the script to recreate the database and tables. This time when I performed the VALIDATE DATABASE command, everything worked as expected. Cool!
However, finding a solution isn't always enough for me. I needed to know more. For example, is this the problem in the INDEX command because it allows the table name to be included in the index expression or is the problem the VALIDATE DATABASE command? I did a little research and I found a MSDN article called "Considerations for Creating Index Expressions". In this article Microsoft states "If you include a field prefaced by a table alias or work area letter in the index expression, Visual FoxPro generates an error message." The article isn't very clear about the error, but at least I had documented proof. Just because Visual FoxPro allows a table name to be included in the index expression, you shouldn't.
On a side note, an interesting thing happened when I got ready to write this blog entry. I wanted to have some code that could reproduce the error. So I wrote a program that would create a database with a both a good table (a table with a valid index expression), and a bad table (a table with an invalid index expression). When I ran the VALIDATE DATABASE command I did not get an error like I expected. It took me a little while to figure out that the error would not occur unless I closed and re-opened the database. The following code reproduces the behaviour.
* create a database
CREATE DATABASE "Sample.DBC"
* add a table with an index and then some records
CREATE TABLE "GoodTable.DBF" (pk I, description C(20))
INDEX ON pk TAG "PK"
INDEX ON description TAG "DESCRIPTIO"
INSERT INTO "GoodTable" (pk, description) VALUES(1, "Description 1")
INSERT INTO "GoodTable" (pk, description) VALUES(2, "Description 2")
INSERT INTO "GoodTable" (pk, description) VALUES(3, "Description 3")
* validate the database, expectation is no error
? "VALIDATE DATABASE (GoodTable)"
VALIDATE DATABASE
* add a 2nd table, but this time put the alias in the index expression
CREATE TABLE "BadTable.DBF" (pk I, description C(20))
INDEX ON pk TAG "PK"
INDEX ON BadTable.description TAG "DESCRIPTIO"
INSERT INTO "BadTable" (pk, description) VALUES(1, "Description 1")
INSERT INTO "BadTable" (pk, description) VALUES(2, "Description 2")
INSERT INTO "BadTable" (pk, description) VALUES(3, "Description 3")
* validate the database, expectation is an error, but VFP does not display one
? "VALIDATE DATABASE (GoodTable + BadTable)"
VALIDATE DATABASE
* close and re-open the database
CLOSE DATABASES ALL
OPEN DATABASE "Sample.DBC" EXCLUSIVE
* validate the database, now VFP displays the error
? "VALIDATE DATABASE (GoodTable + BadTable)"
VALIDATE DATABASE
Links:
Considerations for Creating Index Expressions
http://msdn.microsoft.com/en-us/library/5bxf1b0a(VS.80).aspx