How to validate dates in a corrupted database

[TimStone]

I have a .dbf file. Three of the fields are type DATE.

The file has had some corrupted values in it. As I skip through the database, sometimes the system will throw an error because one of the date fields values is somehow wrong.

The error is ADSCDX 5095 : Cannot convert to a Juliene date. It sees the date time stamp as a negative value but I can't see anything in the field.

So, here is my question. How can I test the dates in record fields to prevent the error from occurring. In other words, if the date is invalid ( or corrupted ), I want to place a default value in it's place.

The line of code failing:
AADD( aPMitems, { oPMtable:PMSCOD, dBaseD, 0, oPmtable:recno() } )

The file is oPMtable. dBaseD is simply CTOD( "01/01/80" ). The problem is not here, but when database.prg skips to the record, apparently it does not like one of the values in the date fields it sees. It fails at line 1048:
for n = 1 to Len( ::aBuffer )
::aBuffer[ n ] = ( ::cAlias )->( FieldGet( n ) )
next

SO ... Any thoughts on a strategy here to trap in invalid date existing in a field ?

All ideas will be appreciated.

[nageswaragunupudi]

Code: Select all  Expand view

USE <dbfname> EXCLUSIVE
do while !eof()
   TRY
      x := FIELD->dBaseD
   CATCH
      FIELD->dBaseD := STOD( "19800101" ) // default value
   END
   SKIP
enddo
 

Another alternative is to open the file in raw mode and check the date field values and replace invaild fields.

[TimStone]

OK, let me try explaining this again.

I am skipping through an existing database. It has this structure:

Code: Select all  Expand view

VEHLIC  C  12  0        Vehicle license
PMSCOD  C  15  0        Labor code
PMSDES  C  35  0        Labor description
PMSLSM  N   8  0        Last mileage
PMSISM  N   8  0        Interval for service in miles
PMSPSM  N   8  0        Projected service odometer
PMSLSD  D   8  0        Last date of this service
PMSISD  N   8  0        Interval for service in days
PMSPSD  D   8  0        Projected service date
PMSCLC  D               Calculated date by average miles
 

I am skipping through the data base with this process, but only taking the value for PMSCOD to save in an array with other default ( valid ) elements:

Code: Select all  Expand view

        DO WHILE TRIM( oPMtable:vehlic) = cVehLic
            AADD( aPMitems, { oPMtable:PMSCOD, dBaseD, 0, oPmtable:recno() } )
            oPMtable:skip( )
        ENDDO
 

The first value of the array is the code from the record, and the last value is the record number. dBaseD is a valid date, and 0 will be an accumulator.

When I exercise the oPMTable:skip( ), the following code is activated within the most recent database.prg

Code: Select all  Expand view

     for n = 1 to Len( ::aBuffer )
         ::aBuffer[ n ] = ( ::cAlias )->( FieldGet( n ) )
      next
 

In other words, after the skip(), a load() is issued for the contents of the record. IF n has corrupted data ( in the database itself ), it is in one of the 3 date fields in the structure. FWH then returns the error:
Error description: Error ADSCDX/5095 Error 5095: The date specified is not valid. Date won't convert to julian. when trying to load the contents of the record into the buffer.
It is called from:
Called from: => FIELDGET( 0 )
Called from: C:\Projects\MLS2015\Source\database.prg => TPMSTABLE:LOAD( 1048 )

The error occurs on a READ ( GET ), not a Write. Of course with the generated error, the program aborts. When I look at the database using Advantage Data Architect, the field may show the word [contaminated], or it might appear blank. If I look at it with a dbu utility, it just shows the field as blank. However something is apparently imbedded in there.

So somehow I need to trap the record itself before it loads, test it, and perhaps overwrite the value with one that is legitimate.

Hopefully this utility will only have to handle this task once and we won't see the errors in the future, but for now I'm looking for a solution. As I say, the error occurs in Database.prg when loading values into a buffer.

[James Bott]

Tim,

I think you are going to have to do an ON ERROR trap and when it occurs replace the data with a valid date.

James

[nageswaragunupudi]

1) Can you reproduce full error.log? That shows the value of n and from that we know which field is corrupted.
2) The sample I gave above works. You need to check the two date fields using the same logic. If run once it will rectify the total table
3) It is also possible that the calculated field is not a valid date if the calculation formula does not check all error conditions.
In that case, you need to improve the calculation formula.

One time program:

Code: Select all  Expand view

function Main()

local x

USE <dbfname> NEW VIA "ADSCDX" EXCLUSIVE
do while !eof()
   TRY
      x := FIELD->PMSLSD
   CATCH
      FIELD->PMSLSD := STOD( "19800101" ) // default value
   END
   TRY
      x := FIELD->PMSPSD
   CATCH
      FIELD->PMSPSD := STOD( "19800101" ) // default value
   END
   DBSKIP( 0 )
   TRY
      x := FIELD->PMSCLC
   CATCH
      ? "Unsafe calculation forumla for PMSCLC"
      QUIT
   END
   
   SKIP
enddo
CLOSE DATA

return nil

 

[nageswaragunupudi]

Tim,

I think you are going to have to do an ON ERROR trap and when it occurs replace the data with a valid date.

James

This is what I am planning to revise database.prg's Load() method.

fwh 17.09

Code: Select all  Expand view

     for n = 1 to Len( ::aBuffer )
         TRY
            ::aBuffer[ n ] := ( ::cAlias )->( FieldGet( n ) )
         CATCH
            ::aBuffer[ n ] := ::xBlankRow[ n ]
            ( ::cAlias )->( FieldPut( ::xBlankRow[ n ] ) )
         END
      next

 

[TimStone]

I used your sample to first go through the database and replace each data field that returned an error with a default date. Then I ran the process and it did not error out.

However, when I tried to run the process a 2nd time, I got a different error ... very strange one ... that had nothing to do with the original issue ... but now my bed is calling so I will be back on it in the morning.

[James Bott]

Nages,

This is what I am planning to revise database.prg's Load() method.

I wouldn't advise automatically fixing data. Or at least not without logging every change to a record, perhaps by logging all the field data that was readable, and then the field that was changed and what it was changed to. Also, a popup message to the user so they can notify the administrator or programmer that there was an error.

Automatically, changing data without any notice and not knowing what was changed and what it was changed to, would create a later problem trying to figure out what happened. And the problem could continue to happen for a long time before anyone noticed.

James

[Rick Lipkin]

Tim

You might want to try this simple suggestion and incorporate it into a function .. you might also add another couple of lines to see if empty(dDate) ???

Rick Lipkin

Code: Select all  Expand view

dDate := Table->Date

If _ChkDate( dDate )
Else
    dDate := date()    // or a date of your choice
Endif



//---------------------
Func _ChkDate( dDate )

If ValType( dDate ) = "D"
   Return(.t. )
Endif


Return(.f.)

 

[TimStone]

Rick,

Actually, I tried that option and several others before posting the data. The problem is that the corrupted data occurred during a brief time when I used the 17.07 database.prg. It did not happen when I substituted in the 17.06 database.prg, but by that time the corruption was already in the file. I did run the scan from the TRY / CATCH solution and was able to replace the corrupted fields, and then process my data. I believe the data error may still be in 17.09 database.prg but I haven't isolated it yet.

I believe, but have not yet confirmed, that the corruption occurs with this type of process where oDBF is a database object from a .dbf file:

dOldDate := oDBF:MyDate
dNewDate := oOldDate + 120
oDbf:CalcDate :- dNewDate
oDbf:save( )

I took a value stored in a dbf date field, added 120 days to it, put the result in a different data field, and saved it. Later, when skipping through the oDbf, when that record is encountered, and database.prg tries to load the record into a buffer variable, the error occurs.

Again, I have not fully tested this with 17.09. For production purposes I still have to use 17.07 with the 17.06 database.prg as the last known reliable FWH combination. However, I am working with 17.09 for construction and testing purposes. ( With Harbour / MSVC++ 2017 )

Tim