Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Program to check a table for bad or invalid contents

Program source code

Program to check a table for bad or invalid contents

by dbMark Posted Dec 17, 2003 (Edited Mar 23, 2006)

Here is some code I wrote a while back to find bad data in a table and report the record numbers. What is bad data? I consider it to be non-ASCII characters, particularly CHR(0). Such bad characters can really mess up a table, and if an index contains those compromised fields then it can behave erratically as well.

This program will temporarily turn off TALKing to the screen so it does not become cluttered. Also, it expects SET EXACT ON while testing string contents, so it will also turn that on while the program runs.

A reminder about User Defined Functions (UDFs) in dBase 5 for DOS (I'm not familiar with other versions, sorry): A UDF cannot close a table that was open when it began and a UDF should close every table it opened before returning to the calling code. In other words, when the UDF ends, all tables and indexes should be pretty much the way they were when the UDF started. This is a feature or restriction of the dBase UDF environment.

If you are a FoxPro programmer, most of this code will work as is, but you'll probably have to modify the sections using SELECT and SELECT() since those dBase and FoxPro commands are slightly different.

Code:

*********
PROCEDURE chktable
PARAMETERS p_file, p_skip_eof, p_details
*          (CHARS)  (LOGICAL)  (LOGICAL)
PRIVATE c_Talk, c_Exact, chkMsg, chkMsg1, chkMsg2, chkMsg3, chkMsg_ok
IF SET("TALK")="ON"
   SET TALK OFF
   c_Talk="ON"
ELSE
   c_Talk="OFF"
ENDIF
IF SET("EXACT")="OFF"
   SET EXACT ON
   c_Exact="OFF"
ELSE
   c_Exact="ON"
ENDIF
STORE "" TO chkMsg, chkMsg1, chkMsg2, chkMsg3
chkMsg_ok=chk_table(p_file, p_skip_eof, p_details)
IF LEN(chkMsg1)+LEN(chkMsg2)+LEN(chkMsg3)>0
   ? chkMsg       && FILENAME
ELSE
   IF c_Talk="ON"  && IF TALK WAS ON BUT NO MSG THEN SAY IF OK OR FAILED
      ? "Check "+IIF(chkMsg_ok=.T.,"was OK","FAILED")
   ENDIF
ENDIF
IF LEN(chkMsg1)>0
   ? chkMsg1      && PROBABLE BAD DATA IN TABLE
ENDIF
IF LEN(chkMsg2)>0
   ? chkMsg2      && RECORD COUNT MISMATCH
ENDIF
IF LEN(chkMsg3)>0
   ? chkMsg3      && FILE SIZE MISMATCH
ENDIF
IF c_Exact="OFF"
   SET EXACT OFF
ENDIF
IF c_Talk="ON"
   SET TALK ON
ENDIF
RETURN


********
FUNCTION chk_table
PARAMETERS p_name, p_skip_eof, p_details
*          (CHARS)  (LOGICAL)  (LOGICAL)
* THIS PROGRAM SCANS A TABLE AT LOW LEVEL TO EXAMINE ACTUAL CONTENTS
* SINCE WE MAY NOT DETECT INVALID CHARACTERS WHEN OPENED AS TABLE
* EXAMPLE:  WE CAN BROWSE AND VIEW SOME INVALID CHARS IN NUMERIC FIELDS
*           BUT CANNOT DETECT THEM PROGRAMMATICALLY SINCE DBASE EVALUATES
*           THE FIELD CONTENTS (STRING DATA) INTO NUMERIC VALUES
* NOTE: chkMsg IS _NOT_ PRIVATE
PRIVATE c_table, ne, np, in_path, in_name, in_ext
c_table=UPPER(p_name)
PRIVATE n_sele
n_sele = SELECT(ALIAS())  && SAVE CURRENT WORKAREA LOCATION FOR RETURN
* NO WAY TO DETERMINE CURRENT WORK AREA # IF NO TABLE IN USE
IF n_sele > 0 .AND. .NOT. ALIAS() $ c_table  && ANOTHER TABLE IS OPEN IN CURRENT WORK AREA
   SELECT SELECT()  && GO TO EMPTY WORK AREA
ENDIF
IF LEN(RTRIM(c_table))=0  && EMPTY STRING WOULD CAUSE AN ERROR
   chkMsg="PARAMETER FOR TABLE NAME IS EMPTY"
   RETURN .F.
ENDIF
ne=AT(".",c_table)
in_ext =IIF(ne>0,SUBSTR(c_table,ne),".DBF")
np=RAT("\",c_table)
in_path=IIF(np>0,SUBSTR(c_table,1,np),"")
in_name=SUBSTR(c_table,np+1,IIF(ne=0,LEN(c_table)+1,ne)-(np+1))

PRIVATE x, y, z, c_print
PRIVATE FX, num_files, x_name, x_size, x_rec_cnt, x_rec_size, x_rec_eof
PRIVATE f_handle, f_size, f_hlen, f_rcnt, f_rlen, f_base, f_data, calc_rec
* DECLARE dir_dbf[1,5]  && ADIR() CREATES ARRAY
num_files=ADIR(dir_dbf,in_path+in_name+in_ext)
IF num_files=0
   chkMsg="FILE NOT FOUND"
   RETURN .F.
ENDIF
x=ASORT(dir_dbf)
FOR FX=1 TO num_files
   STORE "" TO chkMsg, chkMsg1, chkMsg2, chkMsg3
   x_name =dir_dbf[FX,1]  && FORMAT IS FILENAME.EXT (NO PATH!)
   x_size=dir_dbf[FX,2]  && FILE SIZE
   USE (in_path+x_name) NOUP  && NOTE: WILL ERROR IF IN USE OR NOT A TABLE
   IF LEN(ALIAS())>0
      * REFER TO x AS M->x IN CASE TABLE HAS A FIELD NAMED "X"
      x_rec_cnt=RECCOUNT()
      x_rec_size=RECSIZE()
      IF RECCOUNT()>0
         M->x=IIF(RECCOUNT()<2001,1,x_rec_cnt-2000)
         GO M->x  && MOVE TO NEAR EOF TO REDUCE TIME SCANNING HUGE TABLES
      ENDIF
      M->x=x_rec_cnt+1000  && ALWAYS LOOK FOR MORE RECORDS THAN YOU EXPECT
      c_print=SET("PRINT")
      SET PRINT OFF
      SET CONSOLE OFF
      LIST NEXT M->x FIELD EVAL(FIELD(1))  && LIST ONE FIELD TO FIND TRUE EOF
      SET CONSOLE ON
      SET PRINT &c_print
      x_rec_eof=RECNO()-1
      USE
      chkMsg=in_path+x_name
   ELSE
      chkMsg=in_path+x_name+" COULD NOT BE OPENED (IN USE OR BAD TABLE)"
      IF num_files>1
         ? chkMsg
      ELSE
         RETURN .F.
      ENDIF
      LOOP  && GET NEXT TABLE
   ENDIF
   f_handle=FOPEN(in_path+x_name)
   IF f_handle=0
      chkMsg=in_path+x_name+" COULD NOT BE OPENED AT LOW LEVEL"
      IF num_files>1
         ? chkMsg
      ELSE
         RETURN .F.
      ENDIF
      LOOP  && GET NEXT TABLE
   ENDIF

   * CHARS 5+(6*256)+(7*256^2) ARE NUMBER OF RECORDS
   * CHARS (8*256)+9 ARE LENGTH OF HEADER
   * CHARS (10*256)+11 ARE LENGTH OF EACH RECORD (INCLUDES DELETED FLAG)
   * LAST CHAR IN HEADER     IS CHR(13)
   * LAST CHAR IN FILE (EOF) IS CHR(26)

   f_data=FREAD(f_handle,12)

   f_rcnt=ASC(SUBSTR(f_data,5,1)) ;
         +ASC(SUBSTR(f_data,6,1))*256 ;
         +ASC(SUBSTR(f_data,7,1))*256^2  && GET RECORD COUNT (UP TO 16M)
   f_hlen=ASC(SUBSTR(f_data,9,1)) ;
         +ASC(SUBSTR(f_data,10,1))*256  && GET HEADER LENGTH
   f_rlen=ASC(SUBSTR(f_data,11,1)) ;
         +ASC(SUBSTR(f_data,12,1))*256  && GET RECORD LENGTH (ONE)

   f_size=f_hlen+(f_rcnt*f_rlen)+1
   IF f_rcnt<>x_rec_cnt .OR. x_rec_eof<>x_rec_cnt
      chkMsg2="RECORD COUNT MISMATCH:  LIST="+LTRIM(STR(x_rec_eof)) ;
                            +"  RECORD COUNT="+LTRIM(STR(x_rec_cnt)) ;
                             +"  HEADER CALC="+LTRIM(STR(f_rcnt))
   ENDIF
   IF f_size<>x_size
      * MOST MISMATCHES ARE THAT EOF MARKER IS MISSING FROM DOS SIZE
      IF f_size<>x_size+1 .OR. .NOT. p_skip_eof
         chkMsg3="FILE SIZE MISMATCH BY "+LTRIM(STR(x_size-f_size)) ;
                              +": ACTUAL="+LTRIM(STR(x_size)) ;
                                 +" CALC="+LTRIM(STR(f_size))
      ENDIF
   ENDIF

   IF p_details
      ? "TABLE DETAILS:      "+in_path+x_name
      ? "TABLE SIZE (BYTES): "+LTRIM(STR(x_size))
      ? "TABLE RECORD FOUND: "+LTRIM(STR(x_rec_eof))
      ? "TABLE RECORD COUNT: "+LTRIM(STR(x_rec_cnt))
      ? "(HDR) RECORD COUNT: "+LTRIM(STR(f_rcnt))
      ? "TABLE RECORD SIZE:  "+LTRIM(STR(x_rec_size))
      ? "(HDR) RECORD SIZE:  "+LTRIM(STR(f_rlen))
      ? "HEADER LENGTH:      "+LTRIM(STR(f_hlen))
      ?
   ENDIF

   x=FSEEK(f_handle,f_hlen)  && MOVE POINTER TO FIRST RECORD
   DO WHILE .NOT. FEOF(f_handle)
      f_base=FSEEK(f_handle,0,1)  && GET CURRENT LOCATION
      f_data=FREAD(f_handle,240)
      FOR y=1 TO LEN(f_data)
         IF "(MORE BAD!)" $ chkMsg1
            EXIT  && SKIP SEARCHING SINCE STRING IS FULL
         ENDIF
         z=ASC(SUBSTR(f_data,y,1))
         IF ( z<32 .OR. z>126 ) .AND. ;
                  .NOT. ( FEOF(f_handle) .AND. y=LEN(f_data) )
            * PROBABLY BAD DATA
            * ? f_base, y, f_hlen, f_rlen, z
            calc_rec=LTRIM(STR(INT(((f_base+y-1)-f_hlen)/f_rlen)+1))
            IF LEN(chkMsg1)=0 .OR. ;
               LEN(chkMsg1)>0 .AND. " "+calc_rec<>RIGHT(chkMsg1,LEN(calc_rec)+1)
               IF LEN(chkMsg1)>230
                  chkMsg1=chkMsg1+" ... (MORE BAD!)"  && NO ROOM FOR MORE
               ELSE
                  chkMsg1=chkMsg1+IIF(LEN(chkMsg1)=0,"BAD REC # ",", ")+calc_rec
               ENDIF
            ENDIF
         ENDIF
      NEXT
      IF FEOF(f_handle)
         IF RIGHT(f_data,1)<>CHR(26)
            * MOST LENGTH MISMATCHES ARE THAT EOF MARKER IS MISSING
            IF f_size=x_size .OR. .NOT. p_skip_eof
               chkMsg3=IIF(LEN(chkMsg3)=0,"",chkMsg3+"; ")+"CHR(26) NOT AT EOF"
               IF num_files>1
                * ? "LENGTH OF LAST PACKET:  "+LTRIM(STR(LEN(f_data)))
                  ? "LAST CHAR (ASC) IN FILE: "+LTRIM(STR(ASC(RIGHT(f_data,1))))
               ENDIF
            ENDIF
         ENDIF
      ENDIF
   ENDDO
   x=FCLOSE(f_handle)

   IF num_files>1
      IF LEN(chkMsg1)+LEN(chkMsg2)+LEN(chkMsg3)>0
         ? chkMsg       && FILENAME
      ENDIF
      IF LEN(chkMsg1)>0
         ? chkMsg1      && PROBABLE BAD DATA IN TABLE
      ENDIF
      IF LEN(chkMsg2)>0
         ? chkMsg2      && RECORD COUNT MISMATCH
      ENDIF
      IF LEN(chkMsg3)>0
         ? chkMsg3      && FILE SIZE MISMATCH
      ENDIF
   ENDIF

NEXT  && NEXT MATCHING FILE

IF n_sele > 0     && GO BACK TO WHERE WE STARTED
   SELECT (n_sele)
ENDIF
RETURN IIF(LEN(chkMsg1)+LEN(chkMsg2)+LEN(chkMsg3)=0,.T.,.F.)

Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.

Accept Learn more…

Back

Top