Consultancy and Programming
Advice, tips, techniques and downloads for Visual Foxpro developers.
If you've been working with Visual FoxPro databases for a while, you might have come up against a "broken backlink" scenario. This happens when certain pointers in the database become invalid or out of sync. It's usually caused by something as simple as moving or renaming a table, but the solution isn't always as easy as you might think. In this article, we'll describe the symptoms of a broken backlink, explain the reasons for it, and tell you how to fix it.
In most cases, you'll first become aware of the problem when you try to open a table that has broken backlinks. Typically, you'll see a message like this:
c:\..\data\mytable.dbf. The database for table c:\..\data\mydatabase.dbc could not be opened. Would you like to try to locate the owning database or delete the link (and free the table)?
Alternatively, the message might look like this:
c:\data\mydatabase.dbc. Cannot link table ..\mytable.dbf to this database: duplicate or missing fields. Would you like to try to locate the owning database or delete the link (and free the table)?
With both messages, you also see three buttons: Locate, Delete and Cancel (Figure 1).
One of the above messages will appear if, at the time you try to open the table, its parent database container (the DBC file) is not yet open. (By the way, that second message is misleading in that it has nothing to do with duplicate or missing fields; it indicates essentially the same problem as the first one.)
If the database container is already open when you try to open the table, you'll probably see a simple "File not found" message, without the options to locate, delete or cancel. Just to confuse the issue, the filename displayed in the message might not match the name of the table that you're trying to open.
Here's another variant that you might see:
The table 'mytable' could not be found along the specified path. Would you like to locate the file or delete this object from the database?
You'll typically see this message if you try to open the table from inside the database design window, for example, by right-clicking on it and selecting Browse. Like the first two messages shown above, it has Locate, Delete and Cancel buttons.
All these messages are symptoms of the same problem: a broken backlink. So what exactly are backlinks? And how do they get broken?
You probably know that each table within a FoxPro database is held in a physical file, with the extension DBF. It's this file that contains the actual data. (There might also be accompanying CDX and FPT files, which hold indexes and memo data respectively, but these aren't relevant to this discussion.)
The database container is a file with the extension DBC (it's always accompanied by a pair of files with extensions DCX and DCT, but, again, these aren't relevant to the discussion). The database container doesn't contain any data, but rather holds meta-data, that is, information about the database itself. Among other things, the meta-data includes a list of the tables in the database, along with pointers to the corresponding DBF files.
To complete the picture, each DBF file holds a pointer back to the parent DBC. It's these pointers - both in the DBC and the DBFs - that are referred to as backlinks. If the backlink points to a file that doesn't exist or is in the wrong place, the backlink is said to be broken. The usual reason for that is that the file in question has been moved, renamed or deleted.
Does that mean that you can never move a database to a different drive or a different part of the directory tree? Fortunately, that's not necessarily a problem. Because the backlinks are stored as relative paths, you're free to move the entire database, provided you retain the overall structure of its directory tree. To illustrate, take a look at Figure 2. The left-hand half of the diagram shows a database containing two groups of tables, for customers and products respectively, with each group stored in its own sub-directory. Each sub-directory is immediately below the parent DBC's directory. We're free to move these three directories to the directory tree shown in the right of the diagram, as the relative paths between them remain the same.
On the other hand, problems would arise if we changed the overall shape of the sub-tree, for example, if we moved the table folders down one level (Figure 3), or if we renamed the sub-directories. In those circumstances, the relative paths will have changed, so the backlinks will be broken.
In practice, most developers avoid the issue by storing the entire database - the DBC, all the DBFs, and all the accompanying files - in a single folder. This is a good strategy because, provided you always keep all the files in the same directory, you can place the directory itself anywhere you like.
Broken backlinks can also crop up as a result of renaming or deleting a table. We'll explain those issues in more detail later in the article.
If you suspect that your database has broken backlinks, fixing them is relatively easy, provided the database is reasonably small - say, fewer than a dozen table. All you have to do is to click the Locate button whenever you see one of the error messages shown above. A File Open dialogue will appear, in which you can navigate to the appropriate DBC or DBF file. Once you've done that, the backlink will be restored and all will be well.
In some cases, however, you might see another message after locating the DBC:
Table 'MyTable" is not marked as belonging to the 'MyDatabase' database. Would you like to create the back link to mark it?
This message might appear if the backlinks in both the DBF and the DBC were broken, and you have just fixed the DBF's link, but the DBC's link is still bad. The message will be accompanied by Yes and No buttons. Just click Yes to fix the problem.
As we mentioned earlier, if you try to open a table while the database is already open, you might see a "File not found" message, without the options to locate, delete or cancel. In that case, you can use an alternative solution: VALIDATE DATABASE. This is also a good approach if you suspect the database has broken backlinks but you don't know which tables are involved - or you don't even know the names of the tables in the database. It can also help fix other DBC-related problems, such as invalid or out-of-sync field names and index tags. All you have to do is type the following commands in the command window:
CLOSE DATABASES ALL OPEN DATABASE MyDatabase EXCLUSIVE VALIDATE DATABASE RECOVER
VFP will scan the DBC and the DBFs. Whenever it finds a broken backlink, it will display a message prompting you either to locate the table or create the link. As before, click Locate and navigate to the file in question, or click Yes to create the backlink.
As you will have seen, most of the error messages we've mentioned also include a Delete button. That button never deletes any data, but it can - and does - delete meta-data from the database. The effect is to keep the DBF intact but to remove it from the database - in other words, to make it a free table. As you probably know, free tables exist independently of a DBC, and so never experience any kind of backlink problem.
You might think that liberating a table in this way is the ideal solution. It's not. When you convert a DBC-bound table to a free table, three things happen:
The first of the above items probably won't matter. The second one won't matter if you never use the meta-data (many programmers don't). The third one almost certainly will matter. If your field names get truncated, you'll break any program code that refers to the fields in question. Worse, any index tags that reference the fields will become invalid, which means that you'll have no way of opening the table.
By all means, click the Delete button if you're sure you'll never user the meta-data, you're not bothered about the primary key, none of the field names exceeds ten characters, and you're confident there are no other implications for the database. If you're uncertain about any of this, leave well alone. (Note that you have to open the table exclusively in order to delete the link.)
For a discussion on the merits of free tables vs. DBC-resident tables, see the Foxstuff article, DBCs or free tables? The pros and cons.
The techniques we've described so far are fine with small databases, but are impractical if the number of tables is large. (A client recently asked us to repair a database that had over 800 tables, every one of them in the "wrong" directory. It would have been much too tedious to manually navigate to each table in turn in order to fix the invalid links.) In those cases, you need a programmatic solution.
The first step is to examine the files to see exactly where the problems lie. First, check the backlinks in the DBC. The easiest way to do that is with code similar to the following:
OPEN DATABASE MyDatabase ADBOBJECTS(laLinks, "TABLE") FOR lnI = 1 TO ALEN(laLinks) ? DBGETPROP(laLinks(lnI), "TABLE", "Path") ENDFOR
This will display a list of the DBC's backlinks on the screen (you can use SET ALTERNATE or SET PRINTER to redirect the list to a text file or printer respectively). Check these links against the actual paths to the physical DBF files. If any is different, then that's a broken link and needs fixing. (Remember, the path to the DBF is relative to the directory holding the DBC. If only the filename appears in the link, without a qualifying path, the DBC is expecting the DBF to be in the same folder as itself.)
Regardless of whether you see any broken links in the DBC, you also need to check the backlinks in the DBFs. You won't necessarily have to check all the DBFs - just the ones that you suspect of having bad links. If you're not sure which those are, check a representative sample of all the tables.
To do that, you have to read the last 263 bytes of the DBF's header block. You can't do that with VFP's standard table-handling commands. Instead, you must resort to low-level file functions (or FILETOSTR(), but only if the entire table is less than 16 MB), as in the following example:
lnHandle = FOPEN("MyTable.dbf") * Read past preliminary bytes FSEEK(lnHandle, 32) * Look for flag that marks end of field list lcData = CHR(0) DO WHILE lcData <> CHR(0x0D) lcData = FREAD(lnHandle ,1) ENDDO * Read the 263-byte backlink ? FREAD(lnHandle, 263) FCLOSE(lnHandle)
This will display the relative path from the DBF back the DBC (you'll also see a series of empty squares after the path; these are simply binary zeroes that are used as padding and should be ignored). Again, check to see if the path matches the actual relative path from the DBF to the DBC.
If the above check show bad links within the DBC, you can fix them with some program code (remember, you only need to do this if there are many tables; with a small database, it's easier to use the interactive methods described earlier).
Now, before going any further, we want to make an important point. What follows is based on our own observations and testing, and is not documented by Microsoft. We've used these techniques successfully, but we can't guarantee they'll always work in all cases. If you use any of the code shown here, be sure to test it on a copy of the database, and check that you have up-to-date backups before applying it to production files.
As you probably know, a DBC file is in fact a normal FoxPro table, and can be manipulated using standard VFP commands and functions. The table has one record for each "object" in the database, an object being a table, view, field, index, etc. You can see this for yourself by opening the DBC as a table and browsing it (Figure 4):
USE MyDatabase.DBC BROWSE
Since you're only interested in the tables, you can ignore any rows whose ObjectType is something other than "Table". If you want to hide those rows from the Browse window, execute this command:
SET FILTER TO ALLTRIM(ObjectType) = "Table"
The ObjectName field contains the name of the table, while the Property field contains the table's meta-data, including the backlink. If you double-click on the Property field within the Browse window, you'll see its contents (Figure 5). (Note that this field is editable. You must be careful not to accidentally alter it. If you do, the whole of the meta-data might become corrupted.)
Most of the contents of the Property field is binary (displayed as hollow squares), but some of the meta-data, including the backlink, should be clearly visible.
As an example of how to change the backlink, look back to Figure 2 above. Let's suppose someone has moved all the customer tables to the same folder as the product tables, and for some reason you can't move them back again. You need to change all the links to the customer folder to point to the product folder. The following code will do the job:
USE MyDatabase.DBC lcOld = "cust\" lcNew = "prod\" REPLACE Property WITH STRTRAN(Property, lcOld, lcNew, 1, 1, 1) ; FOR ALLTRIM(UPPER(ObjectType)) = "TABLE" USE IN MyDatabase
Unfortunately, the above code won't work in every case. The only reason it works here is that the lengths of the old and new paths are the same. If the new path was longer than the old, the backlink would appear truncated and would therefore still fail to work. You'd also risk damaging other items of meta-data. If the new path was shorter than the old, the meta-data would definitely become corrupted. With luck, you would still be able to access the actual table data, but the corruption might prevent you from subsequently editing the meta-data (for example, changing the primary key or the persistent relationships). It will also display a "Property list corrupted" message when you validate the database.
You can avoid these problems by changing another byte in the Property field. The ninth byte holds an integer that reflects the length of the backlink. You need to adjust the value of this byte to reflect the different lengths of the old and new links. The following code changes both the backlink and the value of the ninth byte:
USE MyDatabase.DBC lcOld = "cust\" lcNew = "product\" lnChange = LEN(lcNew) - LEN(lcOld) SCAN FOR ALLTRIM(UPPER(ObjectType)) = "TABLE" IF AT(lcOld, Property) > 0 REPLACE Property WITH STRTRAN(Property, lcOld, lcNew, 1, 1, 1) REPLACE Property WITH LEFT(Property, 8) + ; CHR(ASC(SUBSTR(Property, 9, 1)) + lnChange) +; SUBSTR(Property, 10) ENDIF ENDSCAN USE IN MyDatabase
Regardless of whether the DBC's links were broken, you might also need to fix the backlinks in the DBFs. Again, you can do that programmatically, although the technique is different in this case. As we saw earlier, the DBF's backlink is stored in the last 263 bytes of its header block (padded with binary zeroes) and can be accessed via VFP's low-level file functions.
To illustrate, suppose a bunch of tables from our Prod folder have been moved to a new sub-folder - say, from c:\data\prod to c:\data\prod\sales. We need to change the backlinks in those tables from ..\MyDatabase.DBC to ..\..\MyDatabase.DBC. Here's some code that will do it:
lcNewLink = "..\..\MyDatabase.DBC" lcFolder = " c:\data\prod\sales" * Get all the table names into an array ADIR(laTables, FORCEPATH("*.DBF", lcFolder)) * Loop through the array FOR lnI = 1 TO ALEN(laTables, 1) lnHandle = FOPEN(FORCEPATH(laTables(lnI), lcFolder), 12) * Read past preliminary bytes FSEEK(lnHandle, 32) * Look for flag that marks end of field list lcData = CHR(0) DO WHILE lcData <> CHR(0x0D) lcData = FREAD(lnHandle ,1) ENDDO * Write the new backlink FWRITE(lnHandle, PADR(lcNewLink, 263, CHR(0))) FCLOSE(lnHandle) ENDFOR
Unlike with the code to alter the backlinks in the DBC, the code shown here is based on the documented DBF file structure, which is highly unlikely to change. Nevertheless, it would still be prudent to test the code carefully on a copy of the data, and to make sure you have proper backups before using it for real.
We pointed out at the start of the article that backlink problems can arise when tables are deleted or renamed. In fact, deleting tables shouldn't cause any problems, provided you remember always to use the DROP TABLE command rather than simply deleting the physical DBF files.
Renaming tables is more of an issue. As you would expect, the RENAME command simply renames a physical file. It knows nothing about backlinks and so cannot be used on its own to rename tables in a database. RENAME TABLE sounds like it should handle the backlinks, but it doesn't.
The only thing that RENAME TABLE changes is the table's ObjectName field in the DBC (refer back to Figure 4 to see some examples of this field). The ObjectName field doesn't necessarily reflect either the table's physical filename or the table name within the backlink. It is in fact the table's Name property - the field at the top of the third tab in the table designer (Figure 6). (By the way, we mentioned earlier that when a broken backlink causes a "File not found" error, the filename in the message might not match the actual filename. In those cases, it's the filename in the Name property that you're seeing in the message.)
The only reliable way to change the names of tables within a database is to take a three-step approach. First, rename the physical DBF file (along with its accompanying CDX and FPT files, if any). Next, use RENAME TABLE to change the ObjectName field (or change it programmatically by opening the DBC as a table and issuing a REPLACE command). Finally, fix the backlink from the DBC to the DBF, using either the interactive or programmatic techniques described in this article.
This has been a long article, dealing with what, with luck, will be a rare occurrence. If your database is reasonably stable - and, in particular, if you never move, rename or delete your tables - chances are good that you'll never meet a broken backlink. But if this annoying problem does rear its head, we hope the information we've given here will help you deal with it quickly and reliably.
Mike Lewis Consultants Ltd. September 2008.
FoxStuff is maintained by Mike Lewis Consultants Ltd. as a service to the VFP community. Feel free to download and use any code or components, and to pass around copies of the articles (but please do not remove our copyright notices or disclaimers).
The information given on this site has been carefully checked and is believed to be correct, but no legal liability can be accepted for its use. Do not use code, components or techniques unless you are satisfied that they will work correctly in your applications.
© Copyright Mike Lewis Consultants Ltd.