Wiki Home

Not ATable


Namespace: Wiki
Directions for repairing a table after you get the famous "Not a table" (Error 15):

This error is usually caused by a discrepancy between the number of records noted in the table header, and the actual number of records.

When adding a record to the table, FoxPro increments the number of records as noted in the header by 1, and then adds 1 record to the table. If anything happens between these 2 processes, table corruption results: the header's count is 1 higher than the number of records in the table.

The repair involves fixing the record count in the header by decrementing it by 1. The only thing you really need to get from Table File Structure is: "bytes offset 4�7, Number of records in file".

You can use Hexedit.app (comes with VFP6, C:\Microsoft Visual Studio\Vfp98\Tools\Hexedit\Hexedit.pjx) to decrease the number of records, as stored in the table header.

Please experiment with a COPY of your corrupt table!

*JC: If you are working with visual foxpro 8, try using visual foxpro 9. This resolved the problem for us.

Just as a helper (I think using hexedit for this thing is hard for the novice) I use these routines for years :
*Sample call
RepairHeader('mytable.dbf')
RepairHeader('c:\SomePath\mytable.dbf')

Function RepairHeader
Lparameters tcDBF
Local handle, lnFileSize, lnReccount, lnHeaderSize, lnRecordSize, ;
	lnCalculatedReccount, llHasMemo
handle=fopen(tcDBF,12) && Opened readwrite
lnFileSize = fseek(handle,0,2) && Get file size
* Read header info
lnReccount   = ReadBytes(handle, 4,4)
lnHeaderSize = ReadBytes(handle, 8,2)
lnRecordSize = ReadBytes(handle,10,2)

lnCalculatedReccount = floor((lnFileSize-lnHeaderSize)/lnRecordSize)
If lnCalculatedReccount # lnReccount && If calculated # recorded fix it
	WriteBytes(handle, 4,4,lnCalculatedReccount)
Endif
=fclose(handle)

function WriteBytes
Lparameters tnHandle, tnPos, tnSize, tnNumber, tlLR
Local lcString, lnLowDword, lnHighDword,ix
lcString=''
If tlLR
	For ix=tnSize-1 to 0 step -1
		lcString=lcString+chr(tnNumber/256^ix%256)
	Endfor
Else
	For ix=0 to tnSize-1
		lcString=lcString+chr(tnNumber/256^ix%256)
	Endfor
Endif
=fseek(tnHandle, tnPos,0) && Go to Pos
Return fwrite(tnHandle,lcString)

function ReadBytes
Lparameters tnHandle, tnPos, tnSize, tlLR
Local lcString, lnRetValue,ix
=fseek(tnHandle, tnPos,0) && Go to Pos
lcString = fread(tnHandle, tnSize) && Read tnSize bytes
lnRetValue = 0
For ix=0 to tnSize-1  && Convert to a number
	lnRetValue = lnRetValue + asc(substr(lcString,ix+1)) * ;
		iif(tlLR,256^(tnSize-1-ix),256^ix)
Endfor
Return int(lnRetValue)
Cetin Basoz

Question: So I am ready to try out modifying the structure using Hex Edit, but I am not sure what to open. Do I select the CorruptedTable.dbf or the CorruptedTable.dbc? ?cs

The DBF (Database File), I would think. A DBC (Database Container) could give "Not a Table". Try USEing the DBC (USE database.dbc), and if that gives a "Not a table", then try to repair the DBC. Otherwise, it's the DBF. Something which hasn't been mentioned is Fox Fix (www.foxfix.com), which is a much easier, and more reliable way of fixing tables. As far as I know, by the way, the data in this page (Not ATable) doesn't apply to VFP 7 - it automatically performs the repair described here, if needed.

So my recommendation is to get Fox Fix. It's worth it (no, I do not work for the makers of Fox Fix or any affiliates - just a very satisfied user :). -- Peter Crabtree

Thanks for the tip Peter. I had gone to the Fox Fix website prior to your posting but wanted to give this one more try before putting out the $$ (but it is actually a bargain when you have a problem!) Your posting gave me a hint of something to try, I have been using VFP beta 8.0 and playing around with new features, and out of habit tried to open the table in 8.0. When I finally realized it might be a version thing (per your posting) I tried opening the "corrupted" table in 7.0 and was able to do so without difficulty. Strangely the one table acted corrupted in 8.0, while none of the other tables in the dbc appeared to. Hmm maybe 8.0 doesn't do the auto repair described here but 7.0 does. Anyway for the moment my dbf seems happy. cs Glad I could help! -- PeterCrabtre

Hi All,

Just an FYI on our experiences with Fox Fix lately -

We've stayed current with the Fox Fix product for over 5 years, but only
recently have had the opportunity to use it on (what I call) massive table
corruption in our production systems after multiple server crashes. After
being in the hot seat trying to get our systems back online, I have the
following to report about Fox Fix.

1. Fox Fix fails regularly when scanning multiple tables, whether you specify
a wildcard or select individual tables. What I mean by fails is that the
program window just disappears with no indication of what happened. Fox Fix
has acknowledged that they have heard of this problem from other customers,
but has no information on when it will be fixed.

2. It does not compare index definitions between the .DBC and the tables.
We had one table that lost it's primary key tag definition in the table (but
the .DBC apparently still knew about it), but Fox Fix did not report any
problems with the table or it's indexes. Only after issuing a VALIDATE DATABASE
from the VFP command line was it reported that there was a missing index tag.

It shouldn't be too much to expect better handling of my corrupt
data from such a mature product as Fox Fix, and when I'm under the gun I
don't appreciate having to resort to manual methods when the product that
we've paid for (for years) comes up short. Fox Fix's unresponsiveness when
it comes to dealing with what I call bugs is very frustrating.

It would be nice if there were alternatives to Fox Fix. We also use the
StoneField Database Toolkit, but unfortunately it doesn't have the feature
set of Fox Fix.

- William Fields


I am and have been a Visual FoxPro developer for over 10 years. If you have data that is so important and have dbc/table/server crashes often, then your backend should be in SQL Server 2000. This will eliminate your issues along with a 1000 others not reported in this article. I would not blame Fox Fix for your problems, and I have never used Fox Fix before.

- StvnRkr


Well, personally, I expect Fox Fix to repair the DBF and FPT - forget the CDX. There are plenty of tools on UT and other places. It would be nice if Fox Fix included this, though. As for the multiple files bug, I have never had to do more than a single file - I'm not even sure how you would do more than one. -- Peter Crabtree

Of course you're right, there are many tools available that we can use to fix table/index problems. My issue with Fox Fix is that we've been paying for upgrades and the tool has stagnated or comes up short in a .DBC environment. Here's what XiTech says about their index support:

Index Expression Validation - Fox Fix 5 scans the tags in your CDX files list expressions that contain unknown identifiers.

Index Header Validation - To aid in the early diagnosis of index corruption Fox Fix 5 scans the CDX header and the header of each individual tag looking for inconsistencies.

Index Regeneration Code - During the CDX scan Fox Fix 5 can optionally generate FoxPro code to regenerate those index files in an emergency.

What comes up short with this support is that there's Index information in the .DBC that Fox Fix doesn't even look at. If the table corruption/repair results in a missing index, Fox Fix will tell you that everything is fine with the table AND indices. I do expect more comprehensive index support from Fox Fix - DBC's have been around for over 5 years and Fox Fix still doesn't include them in its' validation. When I read the following, I do expect more than just rudimentary table and memo scanning:

"Fox Fix automates many of the tasks, so that the user can sit back and let Fox Fix lead the way with regard to table error detection".

As far as scanning multiple tables go, that's the first thing I do when we have a server crash. How can you know that your tables are OK without scanning each and every one? Fox Fix's GUI allows you to specify a path and wildcard (C:\SOMEDIR\*.DBF), which would be great if it worked all the time. But even if you don't specify a wildcard and browse to each table individually (real fun with dozens of tables), the program just goes away sometimes when you hit "scan". Again, after having v5 and upgrading to v5.1 over 2.5 years ago, the product has stagnated and bugs that plague the current version have not been fixed. This is one of those products that you hopefully rarely use, but when you need it, it should perform flawlessly.

For me, it's easy to have low expectations when I'm using free tools that I've assembled from various sources. But when I pay for a product, it's hard to accept incomplete support when the product's website implies the opposite and it's also also hard to accept the complete lack of bug fixes.

- William Fields


Please experiment with a COPY of your corrupt table!

When you get your table open with Hex Edit, the number of records is in bytes 4 - 7, in reverse order. That means, using Hexadecimal arithmetic, you should subtract 1 from the byte under the column numbered "4" in Hex Edit (the right-hand digit). Please remember that in Hexadecimal the numbers are 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F, 10 (= 16 base 10), and that if you need to "borrow," you will need to "borrow" from the digit on the left of the colum numbered "4" and then from the digit on the right of the column numbered "5".

The root of this format goes way back to the early days of processor desing, and has been carried forward for better or for worse.

Here is another way: Take the bytes from the header, flip them around into normal order, convert hex to decimal, subtract 1, flip back to reverse order, and put back into the header.

here are some examples:
headerflipeddecimal-1hexflipped back
02 00 00 0000 00 00 022100 00 00 0101 00 00 00
01 02 03 0404 03 02 0167,305,98567,305,98404 03 02 0000 02 03 04
00 02 03 0404 03 02 0067,305,98467,305,98304 03 01 FFFF 01 03 04
06 97 01 0000 01 97 06104,198104,19700 10 97 0505 97 01 00

Another common cause of Not ATable is that the memo part of the metadata, for example, the SCT file for Forms, gets munged up. IME, this is the main type of corruption when VFP projects are open in Windows 2000 and an abend takes place. The SCT, FRT, etc gets completely fried and there is no easy recovery.

In that case, one way to partially recover is to run the compiled EXE or APP with the Debugger up, and then set a breakpoint for something like ALIAS() in the Watch window. When the break is kicked off, you can copy code out of the Trace window and paste it into text files or PRGs so that the original form can be reconstructed as much as possible.

In FoxPro, the Form, Visual Class Library, Report, and Project files are all just tables. Since this is the case, any of them can have the Not ATable problem.

This article document just saved me a lot of frustration. I didn't know about the debugger trick. All my objects with their method code just there for the cut-n-pastin. This site is great!!!

-- Rodney Dixon
(hey, what happend to the blank lines in the text below ? colors won't work either. -> is caused by te "table" after which blank lines arn't interpreted anymore to BR's; I put them in myself now !)

Of course tables can be found as Not a table, only in those cases the table has been written to. Thus, all the static tables (Form's etc.) won't have any problems at runtime, unless of course, they are dynamically created and saved to disk.

Now note that the process between increasing the Reccount in the tableheader and adding the record, must virtually be seen as a process that can take a long time. I mean, that at the moment a first user (network-environment) adds a record, the tableheader is updated immedeately, a record is appended immedeately, but no other user sees this, unless the proper commands are being used. The discrepancy here, is the fact that the Reccount is always made available to the "other" user when "necessary", while the visibility of the new record depends on what the first user wants about is (Unlock etc.) and what command the other user persorms. "Necessary" now means that for instance an Append Blank makes the Reccount available, although the other user (performing the Append Blank) doesn't know anything about it. Thus, VFP know and does herself.
On the other hand, when the other user performs a Go Bottom, it depends on many things whether the "real" last record is shown, but everone must anticipate on "not" (never mind for all the why's for now).
For this can be reasoned that a discrepancy is there at the logical level anyway, which doesn't say too much about the physical level. However ...

As anyone know, the tablecorruption occurs after a power-failure, or whatever makes the client go down. Now bear in mind the big difference between the server going down and the client going down, where to my experience the server going down is not as bad as the client, but ... when the server goes down, the clients will virutally too, because the cached data of course cant' go anywhere anymore.
Now for the physical discrepancy, for sure things are about, and though this is fuzzy stuff, note the following situations all derived from lot's of experience at our's :

1. When the server is NT(4), a client going down (for whatever reason) doesn't create tablecorruption for server-tables. That is, I don't think I've ever heard of that.
2. When the server is Novell (any version), a client going down, 90 % (100 % ?) for sure will imply a not a table for server-tables. But :
3. When the server is Novell, a client "hanging", gives the opportunity to delete the Connection at the server, and for almost sure nothing will be wrong with server-tables.
4. When the server is Novell 4 of 5, the server going down may imply any kind of unrecoverable corruption (too many things happpened wrongly in too many tables -> where to start ?), but which is rare.
5. When the server is Novell 3 (or less), the server going down never implies corruption ever, but the "normal" Not a table.
6. Where for sure Novell is implying much more problems on this subject, we don't recognize that the client's-parameters influence on this. Conclusion : things are within the server itself. Ref. no 3, where you allow the server to perform things, which appearantly are performed automatically by an NT server.
Note that where I claim that client-parameters don't influence, it may be the Novell-client itself as a whole, causing the trouble. Another note : we ourselves use MS-Client under Novell server (on behalf of trying to knock down our VFP Corruption problem), which is a legitimate option, as long as stuff like NwAdmin isn't needed (implying having at least one PC somewhere with Novell-client).
7. When the server is Novell (not sure whether this is version 3 only), the server going down may imply "lost records" from even the last 1.5 hours ! Thus, where the client for sure during this time performed lots of Unlock All's (etc.), it's written records are not there, and are instead appearing as blank records (so ok, there are there, but ...).
To our opinion this can only have to do with the server's cache, which presents all data as long as the server is running, but when it is down (and up again), the data appears not to have written to the server's disk. Nice stuff huh ? but for sure not the client's problem, since we all work in a "database" here, and when the records wouldn't have gone to the network, all users would have picked up the phone immedeately.

Now above list (which is problably not complete on all known differences) may imply the physical discrepancy, where the logical discrepancy is underlaying. In other words, the way VFP (FoxPro) works, may imply to heavy stuff for the other layers, ie, all the chacheing mechanisms can't cope with it properly.

Now back to the topic, it may be more clear why the Not a table occurs, and may indicate how to prevent it as much as possible, which for sure is related to the app itself;
I don't think it can be avoided in all cases, but I know that table corruption at an NT (well, 4) server is rare. This doesn't say anything about the client, which may be W95/98 or NT or even native Dos : whenever a client is going down, it's tables end with Not beging a table. I'm talking local tables here (or stand-alone useage). Now please note that the latter is rather the same as the server itself going down, leaving Not a table's.

Thus what it comes to, is that where the header has already been updated with the new record, and which datablock the header resides in already being written to disk, the datablock where the new record resides in, is not (written back). So here is our problem.

Despite of all the beautiful repair-tools, we don't use them, because we don't need them;
Not knowing whether these tools can perform their task controlled by VFP itself (thus, starting them and letting them run started from within the app), we use a "tool" which does; FoxPro 2.10 's EXE (small exe, MFOXPLUS) can be ran from within the app;
In 99,99 % of cases it is just a "normal" Not a table, which Fox210 won't recognize, and we just let it Append a record, and Delete it again. Now VFP can cope with it again, which is logical, because the Reccount in the tableheader causes the implied offset to be filled up at the end of the table with blanks. So yes, you end up with a number of blank records, but you can use the table again normally.
For us a real cool thing, where a small function tries to open all the existing tables (ijn VFP), and on error 15, writes a small PRG for the Appending and the Deleting and parses this to Fox210 (Fox200 will do too) (note : works on free tables only).

Please note that I "know" this stuff only based on experience, and I don't know anything of the VFP-Kernel-code. Guessing what it does I can, never knowing whether I'm really right. For that matter I'd really like to invite anyone knowing it really (or just more), to invite having comments on my writing. Remember, I still have a problem to solve ...
-- Peter Stordiau
To help people grasp that a DBF is more than just the data in the records, and that the header and even some of the data isn't stored in a human readable form:

* Make a little test table
Create Table foo ( cFid1 c(5), nFid2 n(10,3), nFid3 i )
Insert into foo values ("hello", 12345.678, 1111 )
Insert into foo values ("there", 12345.678, 1111 )
Use
* Pull the whole file into a var
lcDBF = FileToStr( "foo.dbf" )

* length of the string
lnLen = Len(lcDBF)
? "length:", lnLen

* Dump the contents of the string ( some values don't display well )
? lcDbf

* Print the decimal versions of the values of the first 10 bytes (characters)
For lnI = 1 to 10
	? lnI, Asc( Substr( lcDbf, lnI, 1 ))
endfor

And here is a little prg to fix funky field names. It currently only takes care of ones beginning with a digit by prepending an f. It does not check for the resulting field name being too long, nor does it look for duplicating field names.
lcDbfName  = "foo.DBF"

lcDbf = FileToStr( lcDbfName )

lcHeader = Substr( lcDbf, 1, 32 )
lnHeaderSize = Asc( Substr( lcDbf, 9, 1 ) ) + Asc( Substr( lcDbf, 10, 1 )) * 256
lnFieldHeaderSize = lnHeaderSize - 33
lnFields = lnFieldHeaderSize / 32
lcFieldHeaders = Substr( lcDbf, 33, lnHeaderSize - 33 )

lnFieldNo = 0
DO while Asc( Substr( lcFieldHeaders, lnFieldNo*32+1, 1 ) ) <> 0

	lcFieldName = ""
	lnI = 0
	lcChar = Substr( lcFieldHeaders, lnFieldNo*32+lnI+1, 1 )
	DO while Asc( lcChar ) <> 0
		lcFieldName = lcFieldName + lcChar
		lni = lni + 1
		lcChar = Substr( lcFieldHeaders, lnFieldNo*32+lnI+1, 1 )
	EndDo
	
	If Between( Substr( lcFieldName, 1, 1 ), "0", "9" )
		? "fixing: ", lcFieldName
		lcFieldName = "f" + lcFieldName + Chr(0)
		lcFieldHeaders = Stuff( lcFieldHeaders, lnFieldNo*32+1, Len(lcFieldName), lcFieldName )
	EndIf

	lnFieldNo = lnFieldNo + 1
	
EndDo

lcDbf = Stuff( lcDbf, 33, len(lcFieldHeaders), lcFieldHeaders )
StrToFile( lcDbf, "fixed.dbf" )

Return

Carl Karsten

---
Don't forget VFP's SET TABLEVALIDATE TO command.

SET TABLEVALIDATE TO 0
Will turn off header validation, and may allow the table to be opened.
If so, you know the cause it a header off-by-one corruption.

You can then use the appropriate repair technique.
For speed I appended a blank record, then deleted it.
Then SET TABLEVALIDATE TO 1 and closed/reoneped the table.
Becuase the applicaiton program didn't work with deleted records, I had to PACK to restore integrity.


PAul Maskens
---
Another possible cause of this error is when you try open a table using an earlier version of FoxPro than the table was created in.

Anonymous

See also: Just ATable
Contributors: Cindy Winegarden, Carl Karsten Peter Crabtree Cetin Basoz
Category VFP Meta Data Category Data Corruption
( Topic last updated: 2012.01.10 05:44:50 AM )