Provide better way to detect and fix invalid table data.

240 votes
Sign in
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)
    You have left! (?) (thinking…)
    jeremym1234 shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    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.


    Sign in
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      • John Doe commented  ·   ·  Flag as inappropriate

        * 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

        * 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)

        * 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
        * 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

        * Go to top of the file.
        * Write 1st 8 positions back to the header
        * Close the file

      • Anonymous commented  ·   ·  Flag as inappropriate

        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 commented  ·   ·  Flag as inappropriate

        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

      • Martin commented  ·   ·  Flag as inappropriate

        Especially 7010 errors on memo files - a simple zero the pointer record would be fine

      • saras commented  ·   ·  Flag as inappropriate

        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
        DROP TABLE #tmp;
        CATCH ALL

        SELECT * INTO #tmp FROM any_table;

        // detect field's problem
        DROP TABLE #tmp;
        CATCH ALL

        SELECT any_field INTO #tmp FROM any_table;

        // detect record's problem for particular field
        DROP TABLE #tmp;
        CATCH ALL

        SELECT TOP x any_field INTO #tmp FROM any_table;

      • Jim Hildt commented  ·   ·  Flag as inappropriate

        File repair is our number 1 time waster. All the comments below are exactly what dreams are made of.

      • Ray Long commented  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        Include ADTFix (Advantage Database Table Repair Utility) inside ARC (Advantage Data Architect).

      • JaredSherman commented  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        Another popular problem: a FPT file disappeared, which results in inability to open a DBF altogether...

      • isaac_cm commented  ·   ·  Flag as inappropriate

        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

      • pboivin77 commented  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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?

      Feedback and Knowledge Base