Weirdest dbf thing ever seen
D.J.W. van Kooten
DBF where I import some data. I've noticed a problem with a table it
creates and I finally the situation is as follows:
- there's an index tag on a C3 field+dtos(date) field
- as soon as I set the index and browse to 1 (and only one) specific
record (179) with tools like dbMax =, and browse up, it keeps showing
record 179 10-15 times! Then suddenly it looks normal again. The index
key fields contain the value "032" and 16-12-2008.
- Any other index tag doesn't give that problem
- When I change the content of any of the 2 fields in that record to
any other date/number, the problem is gone
- When I change it back the problem is back
- When I create a new field and enter the dtos(date) in there, and
include that field in the index, the problem remains
Of course I recreated the dbf and index from scratch but it doesn't
make any difference.
I know this looks too crazy for words but I've got really no idea
anymore.
Has someone experienced something like this and found any cause for
it?
Dick van Kooten
Marc Verkade [Marti IT]
--
Grtz, Marc
"D.J.W. van Kooten" <pub...@ic2remove.this.com> schreef in bericht
news:c1pns45c6tgme9umr...@4ax.com...
Ginny Caughey
I imagine Robert would want a small reproducible example. Probably lots of
the rest of us would test it too to see if language, etc., makes a
difference.
--
Ginny Caughey
www.wasteworks.com
"D.J.W. van Kooten" <pub...@ic2remove.this.com> wrote in message
news:c1pns45c6tgme9umr...@4ax.com...
Massimo Bighelli
Dick,
and what happens browsing with another tool ?
Massimo
"D.J.W. van Kooten" <pub...@ic2remove.this.com> ha scritto nel messaggio
news:c1pns45c6tgme9umr...@4ax.com...
D.J.W. van Kooten
<in...@arcasistemi.it> wrote:
Hello Massimo,
>and what happens browsing with another tool ?
>
I've used 2 tools with the same result. The problem started when a
date was counted twice which happened because on browsing through the
database it was correctly seen as the next date but when reading the
value it was reading an incorrect date again. Or something like that.
It's not the tool, it's the data.
I'm going to eliminate all I can - I still hope that there is
corruption in the database which somehow remained on recreating by
exporting from dbMax. If all fails I will certainly create a sample
for anyone who thinks (s)he has a clue....
Thanks,
Dick
D.J.W. van Kooten
<pub...@ic2remove.this.com> wrote:
SOLVED...
How? I just recreated the dbf (all 69 fields) manually (the original
one was created from a spreadsheet).
When I refill the database I notice that the dbf files have the exact
same length but the faulty cdx is 25% smaller. I did throw away and
recreate the cdx however so it must have been created incorrectly
through something in the dbf. A filecompare shows one line different
in the dbf but I can not see immediately where it is. Probably that
caused the faulty index in the first place.
Also I learned that an export from dbMax does apparently also copy the
corruption.
Anyhow, thanks for all replies, I can now finish my program...
Dick
Stephen Quinn
> key fields contain the value "032" and 16-12-2008.
The return value of DTOS( aDate ) can never be what you wrote.
CYA
Steve
D.J.W. van Kooten
<stev...@bigpondSPAM.net.au> wrote:
Hello Steve,
>
>> key fields contain the value "032" and 16-12-2008.
>
>The return value of DTOS( aDate ) can never be what you wrote.
Theese were the field values; returnvalue was 03220081216.
Dick
Paul D B
>
> Of course I recreated the dbf and index from scratch but it doesn't
> make any difference.
>
Your problems are typical for DBF corruption.
But since you said (see above) that you recreated the DBF, I didn't know
what else it could be so I didn't answer.
But, it appears now that you did not recreate it. You copied it. Of course
you will copy the corruption with it.
Now the bottom line is: DBF's can get corrupted (and memo files or indexes
as well).
The users have absolutely no way of telling that the DBF is corrupted nor
can they repair it. They will _maybe_ notice that the application is
behaving weird (browsers showing double records, updates failing, browsers
freezing or crashing etc), but maybe they don't notice it at all. Either
way, a corruption can live for years in a DBF
SO: your maintenance/reindex routines should also check for DBF corruption!
Below some code I use (originally from Glen Townsley, but modified by me
because it sometimes crashed... on corrupted files!). You will have to
replace the AnimateWindow stuff by something else (or I can send you the
class). You'll see that there is some double coude in the accompanying
functions, but that is because they are also being used indepently in other
apps of mine.
Since I have added this routines before my reindex routines, I have never
had any problem anymore with file corruption.
PS: I always keep a few copies of really corrupted files in a folder
somewhere, for testing purposes. It is surprising to see how you "good" code
starts to behave when you feed it with a corrupted file <g<
HTH
FUNCTION PDBTestCorruption(cFileandPathName AS STRING) AS LOGIC
/*
Purpose: Check and correct file integrity. When there are memos, the FPT
file is also checked field by field.
If corruption found, the field is overwritten with a blank string.
This function accepts a filename, if not given, you will get a File Open
Dialog.
Returns: TRUE when there was corruption, FALSE when no corruption
Based on code by Glen Townsley (Pronamics Pty Ltd) en modified by Paul De
Bie
Important: it is recommended that your application makes a safety copy of
the DBF and FPT file first!!
*/
LOCAL oOD AS Opendialog
LOCAL cRecord AS STRING
LOCAL cLastByte AS STRING
LOCAL cBuffer AS STRING
LOCAL nLen AS DWORD
LOCAL nFCounter AS DWORD
LOCAL iCorruptedCount:=0 AS INT
LOCAL nRecCount AS LONGINT
LOCAL nHeaderSize AS WORD
LOCAL nRecSize AS WORD
LOCAL nFileLen AS LONG
LOCAL nCalcLen AS LONG
LOCAL nRecs AS LONG
LOCAL aStruct AS ARRAY
LOCAL pHandle AS PTR
LOCAL lContinue, lHasMemo AS LOGIC
LOCAL uVal AS USUAL
LOCAL oErr AS USUAL
LOCAL lCorrupted := FALSE AS LOGIC
LOCAL oAnimWin AS AnimateWin
// No Filename? let the user choose:
IF Empty(cFileandPathNAme)
oOD := Opendialog{, "*.DBF" }
oOD:Show()
IF !Empty( oOD:FileName )
cFileandPathName := oOD:FileName
lContinue := TRUE
ENDIF
ELSE
lContinue := TRUE
ENDIF
// Show animatebox with hourglass
oAnimWin := AnimateWin{,"HOURGLASS"}
oAnimWin:caption := "File Integrity check "
oAnimWin:Melding := "The files are being checked for corruption. Checking
file: " + CRLF + cFileandPathName
oAnimWin:Show(showcentered) // non-modal
// OK, we got a filename.
// Does it exist?
IF .NOT. File(cFileandPathName)
PDBErrorBox("Corruption check impossible; this file does not exist: "+
cFileandPathName)
lContinue := FALSE
ENDIF
// Can we lock it excl.?
IF .NOT. PDBIsLockPossible(cFileandPathName)
PDBErrorBox("Corruption check impossible; this file cannot be locked
exclusively: " + cFileandPathName)
lContinue := FALSE
ENDIF
IF lContinue
// Is it a valid Foxpro-DBF?
IF !PDBIsValidFoxproDBF(cFileandPathName)
lContinue := FALSE
ENDIF
ENDIF
IF lContinue
// Is there a memeofile (FPT)?
lHasMemo := FALSE
IF PDBHasMemoFile(cFileandPathName)
lHasMemo := TRUE
ENDIF
ApplicationExec( EXECWHILEEVENT )
// test 1: the header of the DBF
pHandle := FOpen( PDBReplaceExt(cFileandPathName,"DBF"), FO_READWRITE )
IF pHandle != NULL_PTR
// Record count: Offset 4 - 7
FSeek( pHandle, 4, FS_SET )
cRecord := Buffer( 4 )
FRead( pHandle, @cRecord, 4 )
nRecCount := Bin2L( cRecord ) // as marked in header
// Header size (start of data): offset 8 - 9
cRecord := Buffer( 2 )
FRead( pHandle, @cRecord, 2 )
nHeaderSize := Bin2W( cRecord ) // as marked in header
// Record size: offset 10 - 11
FRead( pHandle, @cRecord, 2 )
nRecSize := Bin2W( cRecord ) // remark: this can fail when nREcsize=0 (will
raise a divide by zero further on)!
cLastByte := Buffer( 1 )
nFileLen := FSeek( pHandle, 0, FS_END ) // as marked in header
FSeek( pHandle, -1, FS_RELATIVE )
FRead( pHandle, @cLastByte, 1 )
IF cLastByte != CHR( 26 ) // there was not eof marker, add it
cBuffer := CHR(26)
FSeek( pHandle, 0, FS_END )
IF FWrite( pHandle, cBuffer, 1 ) = 1
++nFileLen // to allow for the eof marker that we added
ELSE
++nFileLen // to allow for the eof marker that we could not add
ENDIF
ENDIF
nCalcLen := ( nRecSize * nRecCount ) + nHeaderSize + 1
IF nCalcLen != nFileLen // if calculated length <> marked length
lCorrupted := TRUE
// we have a problem with a wrong record count
// truncate the file to the last good record and alter the header
nRecs := INT( ( ( nFileLen - 1 ) - nHeaderSize ) / nRecSize )
// write this to the header
FSeek( pHandle, 4, FS_SET ) // go to the position for the record count
cRecord := L2Bin( nRecs )
FWrite( pHandle, cRecord, 4 ) // write the new value
nCalcLen := ( nRecSize * nRecs ) + nHeaderSize
// truncate the file to this length then add the EOF marker
FChSize( pHandle, DWORD(nCalcLen) )
cBuffer := CHR(26)
FSeek( pHandle, 0, FS_END )
FWrite( pHandle, cBuffer, 1 )
ENDIF
FClose( pHandle )
ELSE
// file could not be opened -> do nothing
ApplicationExec( EXECWHILEEVENT )
ENDIF
// Test 2: Test the integrity of the memofile
IF lHasMemo
oAnimWin:Melding := "The files are being checked for corruption. Checking
the individual memofields of all records of file: " + CRLF +
PDBReplaceExt(cFileandPathName,"FPT")
ApplicationExec( EXECWHILEEVENT )
// open the file
DBUSEAREA( TRUE, 'dbfcdx', PDBReplaceExt(cFileandPathName,"DBF"), 'TEMP',
DBEXCLUSIVE, DBREADWRITE )
IF !DBINFO( DBI_BLOB_INTEGRITY ) // integrity NOT OK!
lCorrupted := TRUE
DbInfo( DBI_BLOB_RECOVER ) // function to recover the file
ENDIF
// test 3: Test the individual memofields of each record for corruption
aStruct := TEMP -> ( DBSTRUCT() )
nLen := ALen( aStruct )
TEMP -> ( DBGOTOP() )
DO WHILE !( TEMP -> ( EOF() ) )
FOR nFCounter := 1 UPTO nLen
IF aStruct[nFCounter][DBS_TYPE] == 'M'
oErr := ErrorBlock( { |o| _Break( o ) } )
BEGIN SEQUENCE
uVal := ( TEMP -> ( FIELDGET( nFCounter ) ) ) // try to get the value of the
memofield. If it fails, then there is a corruption
RECOVER
lCorrupted := TRUE
iCorruptedCount += 1
PDBWarningBox('Memo field corruption Detected', ' RecordNo: '+NTrim(TEMP ->
( RECNO() ) )+ ' The field ' + aStruct[nFCounter][DBS_NAME] + ' has invalid
data.' )
TEMP -> FieldPutSym( String2Symbol( aStruct[nFCounter][DBS_NAME] ), '' ) //
blank out the field
ApplicationExec( EXECWHILEEVENT )
END SEQUENCE
ErrorBlock( oErr )
ENDIF
NEXT
TEMP -> ( DBSKIP() )
ENDDO
TEMP -> ( DBCLOSEAREA() )
ENDIF
IF lCorrupted
IF iCorruptedCount == 0
PDBInfoBox("File Integrity check " , "File corruption was detected and
repaired. ")
ELSE
PDBInfoBox("File Integrity check " , "File corruption was detected. " +CRLF
+ NTrim(iCorruptedCount)+ " memofields had to be repaired and were blanked
out" )
ENDIF
ELSE
** Infobox if everything OK: optional!
*** PDBInfoBox("File Integrity check " , "No file corruption was detected!")
ENDIF
ENDIF
IF oAnimWin <> NULL_OBJECT
oAnimWin:destroy()
ENDIF
ApplicationExec( EXECWHILEEVENT )
RETURN lCorrupted
FUNCTION PDBIsLockPossible(cFileName AS STRING) AS LOGIC PASCAL
// Check with low-level fOpen() if a file can be locked exclusively.
// very nice, before trying to open a DBServer exclusively
// bv. IF .NOT. IsLockPossible(GetDefault()+ "budgetten.dbf")
LOCAL lSuccess AS LOGIC
LOCAL ptrHandle AS PTR
PTRHandle := FOpen2(cFileName, FO_EXCLUSIVE + FO_WRITE)
IF ptrHandle != F_ERROR // als geen fout
lsuccess := TRUE // lock is mogelijk
ELSE
lsuccess := FALSE // lock is onmogelijk
ENDIF
FClose(PTRHandle)
RETURN lSuccess
FUNCTION PDBIsValidFoxproDBF(cFileName AS STRING) AS LOGIC PASCAL
// a) Check if File exists and can be opened
// b) Cjeck if valid Foxpro-DBF
// c) Check if memos and for presence of FPT file
// this function shows all necessarry errorboxes!
// -------------------------------------------------------------------------------------
LOCAL cFPTFileName AS STRING
LOCAL cRecord, cChar AS STRING
LOCAL ptrHandle AS PTR
PTRHandle := FOpen(cFileName, FO_SHARED)
IF ptrHandle != F_ERROR
// read DBF-header
// Signature offset 0: 03h (DBFCDX without memo) or F5h (DBFCDX with FPT
Memo)
FSeek(ptrHandle, 0, FS_SET) // pre-position
cRecord := Buffer(1)
FRead(ptrHandle, @cRecord, 1) // read 1st position 1strecord (Signature
byte)
cChar := RTrim(AsHexString(cRecord))
IF !cChar == "03" .AND. !cChar == "F5" // no DBF-file
FClose(PTRHandle)
PDBErrorBox(cFileName +CRLF+"This is no real DBF file! / Ce n'est pas un
vrai fichier DBF! / Geen echt DBF bestand!")
RETURN FALSE
ELSE // DBF file
IF cChar == "F5" // OK there must be a FPT file
cFPTFileName := PDBReplaceExt(cFileName,"FPT")
IF !File(cFPTFileName)
FClose(PTRHandle)
PDBErrorBox(cFPTFileName + ": This memofile is missing! / Ce fichier (memo)
manque! / Memo-bestand ontbreekt!")
RETURN FALSE
ENDIF
ENDIF
ENDIF
// error ppening
ELSE
PDBErrorBox(cFileName +CRLF+"Can't find or open file! / Impossible de
localiser/ouvrir un fichier! / Kan bestand niet vinden of openen! " +CRLF+
DosErrString(FError()))
RETURN FALSE
ENDIF
FClose(PTRHandle)
RETURN TRUE
FUNCTION PDBHasMemoFile(cFileName AS STRING) AS LOGIC PASCAL
// geeft TRUE terug indien de er bij de DBF een FPT file behoort.
// opgelet: Het moet een echt DBF bestand zijn. Dus doe eerst de nodige
controles met PDBIsValidFoxProDBF()
// we checken enkel op FPT files, geen DBT
// -------------------------------------------------------------------------------------
LOCAL cRecord, cChar AS STRING // voor validatie DBF + FPT file
LOCAL ptrHandle AS PTR // voor validatie DBF + FPT file
LOCAL lReturn AS LOGIC
PTRHandle := FOpen(cFileName, FO_SHARED)
IF ptrHandle != F_ERROR
// read DBF-header
// Signature offset 0: 03h (DBFCDX without memo) or F5h (DBFCDX with FPT
Memo)
FSeek(ptrHandle, 0, FS_SET) // pre-position in case we were mucking around!
cRecord := Buffer(1)
FRead(ptrHandle, @cRecord, 1) // read 1st position 1strecord (Signature
byte)
cChar := RTrim(AsHexString(cRecord))
IF cChar == "F5" // OK there must be a FPT file
lReturn := TRUE
ELSE
lReturn := FALSE
ENDIF
ENDIF
FClose(PTRHandle)
RETURN lReturn
FUNCTION PDBReplaceExt(cFileName AS STRING,cExt AS STRING) AS STRING PASCAL
// small function to replace the extension of a filename
// E.G. TEST01.DBF -> TEST01.CDX
// Remark: extension without the "." !
LOCAL dPos AS DWORD
LOCAL dLen AS DWORD
LOCAL dLenEXt AS DWORD
cFileName := AllTrim(cFileName)
dLen := Len(cFileName)
IF (dPos := At(".", cFileName)) <> 0
dLenExt := dLen-dPos
cFileName := Stuff(cFileName, dPos+1, dLenExt, cExt)
// There is no dot, so no extension. paste the extension at the end
ELSE
cFileName += "."+cExt
ENDIF
RETURN cFilename
FUNCTION PDBErrorBox (cText AS STRING) AS LOGIC STRICT
// Shows an errorbox.
// Since it return always FALSE, you can us it like this too; e.g.
// IF (ptrFile := FCreate(cFilename, FC_NORMAL)) = F_ERROR
// RETURN PDBErrorBox(DosErrString(FError()))
// ELSE
LOCAL dwFlags AS DWORD
LOCAL cMessage AS STRING
cMessage := AsString(cText)
dwFlags := MB_OK + MB_ICONERROR + MB_TASKMODAL + MB_TOPMOST
MessageBox(NULL_PTR, PSZ(_CAST, cMessage), PSZ(_CAST, "ERROR"), dwFlags)
RETURN FALSE // permanently false so it can be used as a return value of
FALSE
FUNCTION PDBInfoBox (cCaption AS STRING, cText AS STRING) AS LOGIC STRICT
// Shows Infobox
LOCAL dwFlags AS DWORD
dwFlags := MB_OK + MB_ICONINFORMATION + MB_TASKMODAL + MB_TOPMOST
MessageBox(NULL_PTR, PSZ(_CAST, ctext), PSZ(_CAST, cCaption), dwFlags)
RETURN TRUE
--
Paul
Marc Verkade [Marti IT]
--
Grtz, Marc
"Paul D B" <pol...@NOMORESPAMhnt.be> schreef in bericht
news:49d08630$0$2864$ba62...@news.skynet.be...
D.J.W. van Kooten
<pol...@NOMORESPAMhnt.be> wrote:
Hello Paul,
>But, it appears now that you did not recreate it. You copied it. Of course
>you will copy the corruption with it.
Indeed I did, although I was under the impression that dbMax Export
(witohout content) would just create a new structure, not copy the old
one.
>Below some code I use (originally from Glen Townsley, but modified by me
>because it sometimes crashed... on corrupted files!).
This sounds VERY promissing. I am going to check it out shortly,
thanks for the contribution!
Dick
Paul D B
The code might need some cleaning up (there's a mix of OO and old DB syntax)
but it never failed on me.
One word of advice: always make a backup copy first of the files. Because,
After a "repair" such as this you might actually loose information that was
still visible in the corrupted file. Visible but not necessarily useable
anymore.
--
Paul