Provide better way to detect and fix invalid table data.
jeremym1234
shared this idea
jeremym1234
responded
We have some ideas on this, but would welcome any ideas or comments on what kind of functionality and utilties you would like to see.
16 comments
-
John Doe
commented
AFTER 20 YEARS THIS OLD CODE IS STILL FIXING DBF PROBLEM :)
*************************************************
* Program Name: FIXRECNT.PRG
* Author: Thomas D Bellmer 816/854-7750
* Copyright: (c) 1993, Sprint
* Purpose: Write correct RECCOUNT() value
* to header
* Last Modified: 08/11/93
* Usage: DO fixrecnt WITH <dbf name> OR
* =fixrecnt(<dbf name>)
* Returns: nothing
*************************************************PARAMETERS lcfname
PRIVATE ALL LIKE l** Open the file with unbuffered read/write access
lnhandle = FOPEN(lcfname,12)* Test for possible file opening error
IF lnhandle = -1
WAIT WINDOW "Error: "+STR(FERROR(),2)
RETURN
ENDIF* Read the first 12 characters of the header
lchdrinfo =FREAD(lnhandle,12)* Calculate the header length
lnhdrsize = INT(ASC(SUBSTR(lchdrinfo,09,01)) ;
+ ASC(SUBSTR(lchdrinfo,10,01)) ;
* 256)
* Calculate the record length
lnreclngth = INT(ASC(SUBSTR(lchdrinfo,11,01)) ;
+ ASC(SUBSTR(lchdrinfo,12,01)) ;
* 256)* lafsize[2] contains the file size in bytes
=ADIR(lafsize,lcfname)
* Calculate the number of records
lnreccnt = ;
INT((lafsize[2] - lnhdrsize) / lnreclngth)
lcfirst8 = LEFT(lchdrinfo,4) + SPACE(4)FOR lni = 3 TO 0 STEP -1
* Replace characters from right to left
* starting at 8 and going to 5
lcfirst8 = STUFF(lcfirst8,lni+5,1, ;
CHR(INT(lnreccnt / 256^lni)))
* Perform modulus division and use remainder
* as the remaining record count in next pass
* of the FOR loop.
lnreccnt = lnreccnt % 256^lni
ENDFOR* Go to top of the file.
=FSEEK(lnhandle,0,0)
* Write 1st 8 positions back to the header
=FWRITE(lnhandle,m.lcfirst8)
* Close the file
=FCLOSE(lnhandle)
RETURN -
Anonymous
commented
I have a program which fix's errors in the table with Medisoft. The program is desiged as such I could create a repair program for each customer needs and support for updates to the program. Which is what I do with Medisoft. Program was designed and been working since 1992
Let me know if there is interest. Dave -
richard of tdoc.net
commented
And another check ... to avoid 7038 errors which is when you have an invalid index. typically after a server crash, the client restores all the dbf's but fails to clear the cdx's
-
richard of tdoc.net
commented
The 5004 error does NOT tell you the name of the missing part of the table ... dbf, or cdx, or fpt.
-
Martin
commented
Especially 7010 errors on memo files - a simple zero the pointer record would be fine
-
saras
commented
We can detect some problem but sometimes we can not fix it.
- a memo corrupt: we must export to a new table
- an invalid date: we must edit it
- an invalid value: we cannot restructure it if an invalid value found in two or more field. we must edit it one by one before restructure it Or we must use FoxPro to restructure it.// detect table's problem
TRY
DROP TABLE #tmp;
CATCH ALL
END;SELECT * INTO #tmp FROM any_table;
// detect field's problem
TRY
DROP TABLE #tmp;
CATCH ALL
END;SELECT any_field INTO #tmp FROM any_table;
// detect record's problem for particular field
TRY
DROP TABLE #tmp;
CATCH ALL
END;SELECT TOP x any_field INTO #tmp FROM any_table;
-
Jim Hildt
commented
File repair is our number 1 time waster. All the comments below are exactly what dreams are made of.
-
Ray Long
commented
If we could have some way to verify the content of the ADM files associated with tables. Our #1 problem over the last 5 years has been corrupted BLOB's and memo's. Our users extensively store text and pictures and getting ADM corruptions or the possibility of them keep me up at night.
andorauk's suggestion of a checksum would be excellent. If I could only identify a problem as soon as it happens we can restore from a nightly backup. Most of our major issues(my customers yelling at me) come from not knowing the data was corrupt for weeks or sometimes months after it happens and not having a way to fix it.
Having the DB server app checking for corruptions as it writes and reads from tables and then send a message(via email) to someone would be a great help.
-
andorauk
commented
Would it be possible to [optionally] generate a 'checksum/parity/ecc/crc' index on a table and have this provide error-checking or some [limited] data correction? A table-check could be run against the data and errors optionally dumped out to an error-table?
-
ma1
commented
Include ADTFix (Advantage Database Table Repair Utility) inside ARC (Advantage Data Architect).
-
JaredSherman
commented
I would second what pboivin77 said. I have a table right now with 9 corrupt records and 10+ thousand good records. If I could just port out the good ones and drop out the bad ones it would make my file repair work infinitely easier.
A side note issue I see frequently is if you have the "autocreate" set to true some times if the network is flakey it will wipe out your table and recreate it because it doesn't think it exists. the newly created table isn't bound to my dictionary anymore either, which just makes it even more fun.
-
Lev P.
commented
Another popular problem: a FPT file disappeared, which results in inability to open a DBF altogether...
-
isaac_cm
commented
It is very important that changing the language did not corrupt the data, may be display error or even prevent open the table, but corrupt data for changing the language is not acceptable
-
Rodd Graham
commented
Validate the table header with the .ADD metadata if a database table.
-
pboivin77
commented
We must be able to copy good data to a new table. Something like "SELECT * INTO NEWTABLE FROM BUGGYTABLE WITH ERROR INTO ERRTABLE". ErrTable would contains a list of recno and field name with invalid data (Record 12: Field: CustNote Error: 7019 Corrupt memo field). Invalid field in the new table can just be blank, zero or null depending on the field type.
-
gatoz
commented
I can only pass to you my experience:
1. The most possible problem is a corrupt table, where we get a file with different header and actual size. To resolve this i just change the filesize to be equal to the header suggested and is ok! Some automation here?
2. Another possibility, is to have new records with wrong values (e.g. filed with zeros). An arc option to open table without index?