Count # of records WITHOUT opening the table?? 2

[DanEvansJr]

I'm in a bit of a bind. Not major, but a bind nonetheless. I'm trying to populate a table with data about OTHER tables. The table name and record count. I'd like to do this with a quick and dirty function without having to open the actual tables. Here is the code:

* Get a snapshot of the data\backup folder for all of the import files. Throw them all into an array called _rollerback
store adir(_rollerback,(_pathdata)+'backups\wprotimp-*.dbf') to _count

* Create a temporary table to show the user all of these files.
create table (_pathlocal+"rollback_file.dbf&quot (file_name c(100),rec_count c(15))
for x = 1 to _count
insert into rollback_file (file_name,rec_count) values(alltrim(_rollerback(x,1)),'# records')
endfor


Unfortunately, reccount('tablename') only works for tables that are in an open work area. Since the number of tables in my array can get quite large, I don't want to have to open and close that many tables on the fly.

Does anyone have any ideas??

 

[jimstarr]

the ADIR() function will give you the file sizes.

Jim

 

[DanEvansJr]

I need record count, not file size.

 

[wgcs]

The foxpro DIR command will give you the record count, but may not be easy to capture to use.

You could use low level file functions to examine the table headers directly... Technically, you CAN'T find the # records without opening the table in one way or another (the DIR command must briefly open each table). However, the DIR command and LLFF would avoid error messages if the tables are damaged, etc.

if the tables are already opened somewhere exclusively, there is NO way you can get the Record count, unless you pre-Cache the record sizes and header sizes, then calculate from the file size from ADIR()....

 

[craigsboyd]

DanEvansJr,

Sorry it took me little while to get back to you, I've been trying to figure this out for you. The header holds the information in the 4-7 bytes and I had to figure out a way to get that information back in a way that would actually mean something to you...decided to use ASC function and multiply it by 256, I think my thinking is right on this and all the tables I tried it on gave me the right response. Cut-n-paste the code below into a prg and run it in VFP. When the getfile dialog box comes up go select a dbf file that you want to know record count for. I wrote this really fast so it could be much better, but shows what you want i think. If not let me know.


LOCAL nTotalRecords
nTotalRecords = 0
pnHandle = FOPEN(GETFILE()) && Open file
ON ERROR FCLOSE(pnHandle) &&just in case don't wanna leave that file locked open
IF pnHandle > 0
= FSEEK(pnHandle,4,0) &&Record Count is bytes 4-7 in dbf header (0 offset)
nTotalRecords = ASC(FREAD(pnHandle, 1))
nTotalRecords = (ASC(FREAD(pnHandle, 1)) * 256) + nTotalRecords
nTotalRecords = (ASC(FREAD(pnHandle, 1)) * (256 * 256)) + nTotalRecords
nTotalRecords = (ASC(FREAD(pnHandle, 1)) * (256 * 256 * 256)) + nTotalRecords
=FCLOSE(pnHandle) && Close file
MESSAGEBOX("Total records in table:" + TRANSFORM(nTotalRecords))
ELSE
MESSAGEBOX("Unable to open table&quot
ENDIF



Slighthaze = NULL

 

[AirCon]

Dan,

Like wgcs said, you can calculate from filesize if you know the headersize.

(filesize - (headersize+1)) / recordsize


-- AirCon --

 

[wgcs]

Good Job, Slighthaze, it looks good

I'd recommend a slight adjustment, to avoid sequential reads (the ON ERROR FCLOSE() would cause subsequent errors, if an earlier read had a problem...)

LOCAL nTotalRecords
PRIVATE nErr
nErr = 0
nTotalRecords = 0
pnHandle = FOPEN(GETFILE())   && Open file
IF pnHandle > 0
  ON ERROR nErr=Error()
  FSEEK(pnHandle,4,0) && Record Count is bytes 4-7 in dbf header (0 offset)
  lcRecCnt = FREAD(pnHandle,4)
  FCLOSE(pnHandle) 
  if nErr=0
    nTotalRecords = ASC(substr(lcRecCnt,1))
    nTotalRecords = ASC(substr(lcRecCnt,2)) *256 + nTotalRecords
    nTotalRecords = ASC(substr(lcRecCnt,3)) * (256 * 256)) + nTotalRecords
    nTotalRecords = ASC(substr(lcRecCnt,4)) * (256 * 256 * 256)) + nTotalRecords
    MESSAGEBOX("Total records in table:" + TRANSFORM(nTotalRecords))
  else
    MESSAGEBOX("Error reading table")
  endif
ELSE
  MESSAGEBOX("Unable to open table")
ENDIF

 

[wgcs]

Oops, I left a couple of &quot" in that weren't needed... here's a better version, tested and works:

FUNCTION fRecCount( pcFile )
* Adapted From code by Slighthaze on Tek-Tips May 16, 2003
LOCAL lnTot, lnHandle, lcRecCnt, laJnk[1]
PRIVATE nErr
nErr  = 0
lnTot = 0
if adir(laJnk,pcFile)=0
  RETURN -1
endif
lnHandle = FOPEN(pcFile)   && Open file
IF lnHandle > 0
  oErr = On("ERROR")
  ON ERROR nErr=Error()
  FSEEK(lnHandle,4,0) && Record Count is bytes 4-7 in dbf header (0 offset)
  lcRecCnt = FREAD(lnHandle,4)
  FCLOSE(lnHandle) 
  ON ERROR &oErr
  if nErr=0
    lnTot = ASC(substr(lcRecCnt,1))
    lnTot = ASC(substr(lcRecCnt,2)) * 256 + lnTot
    lnTot = ASC(substr(lcRecCnt,3)) * (256 * 256) + lnTot
    lnTot = ASC(substr(lcRecCnt,4)) * (256 * 256 * 256) + lnTot
  else
    lnTot = -3
  endif
ELSE
  lnTot = iif(lnHandle=0,-1,lnHandle)
ENDIF
RETURN lnTot
ENDFUNC

 

[craigsboyd]

wgcs,

Nice adaptation (star)...we could also use 256^2 and 256^3 to replace all the multiplication factors. And I would use the third parameter in the substr function calls to limit what is returned to 1 character. Your point on the error handling is a good one.

A little more work and maybe a FAQ is in the works...

Header holds all kinds of useful info...

http://msdn.microsoft.com/library/d...help/html/contable_file_structure_lpdbfrp.asp

...and the code we've posted could be modified to grab quite a few of the info things available in the header. I keep thinking there has to be a slicker way to convert a binary string to it's numeric equivalent (an API call maybe?).


Slighthaze = NULL

 

[wgcs]

In my testing, the difference is almost negligable, yet consistant:
Using only one parameter on SUBSTR() is faster (ASC() automatically only looks at the first character)
and Multiplication is fastest (val * 256*256*256),
Val * 256^3 comes in second,
and BITLSHIFT(Val,24) is slowest.

I only really point this out because of curiosity: The difference is so negligable that it's really just a personal style issue.

 

[wgcs]

Here's another adaptation, that might prove useful:
( I don't have the error handling for fixing in this clip yet )

FUNCTION fRecCount( pcFile, plActual, plFix )
* Adapted From code by Slighthaze on Tek-Tips May 16, 2003
* pcFile   : the file to find the record count of
* plActual : if .T., find the actual number of records
*              based on FileSize vs Record Size
* plFix    : if .T., and the Header # records doesn't match the actual
*              number, then fix the header.
LOCAL lnHandle, lcRecCnt, laJnk[5], lcRecSize, lcFirstRec, ;
      lnRecCnt, lnRecSize, lnFirstRec, lnRes, lnFileSize, ;
      lnActRecCnt
PRIVATE nErr
nErr  = 0
lnRes = 0
if adir(laJnk,pcFile)=0
  RETURN -1
endif
lnFileSize = laJnk[2]
lnHandle = FOPEN(pcFile)   && Open file
IF lnHandle > 0
  oErr = On("ERROR")
  ON ERROR nErr=Error()
  FSEEK(lnHandle,4,0) && Record Count is bytes 4-7 in dbf header (0 offset)
  lcRecCnt   = FREAD(lnHandle,4)
  FSEEK(lnHandle,8,0) && First Record Pos is bytes 8-9 in dbf header (0 offset)
  lcFirstRec = FREAD(lnHandle,2)
  FSEEK(lnHandle,10,0) && Record Size is bytes 10-11 in dbf header (0 offset)
  lcRecSize  = FREAD(lnHandle,2)
  FCLOSE(lnHandle) 
  ON ERROR &oErr
  if nErr=0
    lnRecCnt   = BinStrToInt( lcRecCnt   )
    lnFirstRec = BinStrToInt( lcFirstRec )
    lnRecSize  = BinStrToInt( lcRecSize  )
    lnActRecCnt = (lnFileSize-lnFirstRec-1)/lnRecSize
    do case
      case plActual 
        lnRes = lnActRecCnt
      case plFix
        if lnActRecCnt<>lnRecCnt
          lnHandle = FOPEN(pcFile,12)   && Open file R/W, Unbuffered
          FSEEK(lnHandle,4,0) && Record Count is bytes 4-7 in dbf header (0 offset)
          lcRecCnt = FWRITE( lnHandle, Num2DWord(lnActRecCnt) )
          FCLOSE(lnHandle) 
        endif
      otherwise
        lnRes = lnRecCnt
    endcase    
  else
    lnRes = -3
  endif
ELSE
  lnRes = iif(lnHandle=0,-1,lnHandle)
ENDIF
RETURN lnRes
ENDFUNC

FUNCTION BinStrToInt( pcStr )
LOCAL lnTot, lnI
lnTot = 0
for lnI = 1 to len(pcStr)
  lnTot = lnTot + ASC(substr(pcStr,lnI)) * 256^(lnI-1)
endfor
RETURN lnTot

*!*	    lnTot = ASC(substr(lcRecCnt,1))
*!*	    lnTot = ASC(substr(lcRecCnt,2)) * 256   + lnTot
*!*	    lnTot = ASC(substr(lcRecCnt,3)) * 256^2 + lnTot
*!*	    lnTot = ASC(substr(lcRecCnt,4)) * 256^3 + lnTot

FUNCTION num2dword( lnValue ) 
#DEFINE m0       256 
#DEFINE m1     65536 
#DEFINE m2  16777216 
  LOCAL b0, b1, b2, b3 
  b3 = Int(lnValue/m2) 
  b2 = Int((lnValue - b3*m2)/m1) 
  b1 = Int((lnValue - b3*m2 - b2*m1)/m0) 
  b0 = Mod(lnValue, m0) 
  RETURN Chr(b0)+Chr(b1)+Chr(b2)+Chr(b3) 
ENDFUNC

 

[craigsboyd]

wgcs,

awesome (star for you)...here's a little more info from the header of the dbf structured file...as I was talking about above.


LOCAL sDBFStructuredFile
sDBFStructuredFile = GETFILE()
?"File Type:"+TRANSFORM(fFileType(sDBFStructuredFile))
?"Last Updated On:"+TRANSFORM(fLastUpdate(sDBFStructuredFile))
?"Record Count:"+TRANSFORM(fRecCount(sDBFStructuredFile))

FUNCTION fLastUpdate(pcFile)
LOCAL lnHandle, lcFileUpdate, laJnk[1], ldReturn
LOCAL nYear, nMonth, nDay
PRIVATE nErr
nErr = 0
ldReturn = {}
IF ADIR(laJnk,pcFile)=0
RETURN -1
ENDIF
lnHandle = FOPEN(pcFile) && Open file
IF lnHandle > 0
oErr = ON("ERROR&quot
ON ERROR nErr=ERROR()
FSEEK(lnHandle,1,0) &&Beginning
lcFileUpdate = FREAD(lnHandle,3) && Read the 0 byte
FCLOSE(lnHandle)
ON ERROR &oErr
IF nErr=0
nYear = ASC(SUBSTR(lcFileUpdate, 1,1)) + 2000
nMonth = ASC(SUBSTR(lcFileUpdate, 2,1))
nDay = ASC(SUBSTR(lcFileUpdate, 3,1))
ldReturn = DATE(nYear,nMonth,nDay)
ENDIF
ENDIF
RETURN ldReturn
ENDFUNC

FUNCTION fFileType(pcFile)
LOCAL lnType, lnHandle, lcFileType, laJnk[1], lcReturn
PRIVATE nErr
nErr = 0
lnType = 0
IF ADIR(laJnk,pcFile)=0
RETURN -1
ENDIF
lnHandle = FOPEN(pcFile) && Open file
IF lnHandle > 0
oErr = ON("ERROR&quot
ON ERROR nErr=ERROR()
FSEEK(lnHandle,0,0) &&Beginning
lcFileType = FREAD(lnHandle,1) && Read the 0 byte
FCLOSE(lnHandle)
ON ERROR &oErr
IF nErr=0
lnType = BinToDecimal(lcFileType)
ELSE
lnType = -3
ENDIF
ELSE
lnType = IIF(lnHandle=0,-1,lnHandle)
ENDIF

DO case
CASE lnType = 2
lcReturn = "FoxBASE"
CASE lnType = 3
lcReturn = "FoxBASE+/dBASE III PLUS, no memo"
CASE lnType = 48
lcReturn = "Visual FoxPro"
CASE lnType = 67
lcReturn = "dBASE IV SQL table files, no memo"
CASE lnType = 99
lcReturn = "dBASE IV SQL system files, no memo"
CASE lnType = 131
lcReturn = "FoxBASE+/dBASE III PLUS, with memo"
CASE lnType = 139
lcReturn = "dBASE IV with memo"
CASE lnType = 203
lcReturn = "dBASE IV SQL table files, with memo"
CASE lnType = 245
lcReturn = "FoxPro 2.x (or earlier) with memo"
CASE lnType = 251
lcReturn = "FoxBASE"
OTHERWISE
lcReturn = "UNKNOWN"
ENDCASE

RETURN lcReturn
ENDFUNC

FUNCTION fRecCount( pcFile )
* Adapted From code by Slighthaze on Tek-Tips May 16, 2003
LOCAL lnTot, lnHandle, lcRecCnt, laJnk[1]
PRIVATE nErr
nErr = 0
lnTot = 0
IF ADIR(laJnk,pcFile)=0
RETURN -1
ENDIF
lnHandle = FOPEN(pcFile) && Open file
IF lnHandle > 0
oErr = ON("ERROR&quot
ON ERROR nErr=ERROR()
FSEEK(lnHandle,4,0) && Record Count is bytes 4-7 in dbf header (0 offset)
lcRecCnt = FREAD(lnHandle,4)
FCLOSE(lnHandle)
ON ERROR &oErr
IF nErr=0
lnTot = BinToDecimal(lcRecCnt)
ELSE
lnTot = -3
ENDIF
ELSE
lnTot = IIF(lnHandle=0,-1,lnHandle)
ENDIF
RETURN lnTot
ENDFUNC

FUNCTION BinToDecimal(pcBinaryString)
LOCAL lnReturn, lnFactor
lnReturn = 0
lnFactor = 1
FOR lnCounter = 1 TO LEN(pcBinaryString)
lnReturn = ASC(SUBSTR(pcBinaryString, lnCounter,1)) * lnFactor + lnReturn
lnFactor = lnFactor * 256
ENDFOR
RETURN lnReturn
ENDFUNC


Slighthaze = NULL

 

[wgcs]

I'm out of time right now, but I was thinking that another good use for this technique could be a Low Level I/O File Info function, something like:

AFILEINFO( @aFileInfo, cFileName )
( since it's an array, aFileInfo would have to always be passed with the "@" sign )
cFileName could be a skeleton, too, making aFileInfo a two dimension array, one file per row, with each row showing:
1: FileName
2: RecCount (in Header)
3: RecCount (by File Size
4: RecSize
5: FileSize
6: File Type (Type of file is in byte 0:
0x02 FoxBASE
0x03 FoxBASE+/dBASE III PLUS, no memo
0x30 Visual FoxPro
0x43 dBASE IV SQL table files, no memo
0x63 dBASE IV SQL system files, no memo
0x83 FoxBASE+/dBASE III PLUS, with memo
0x8B dBASE IV with memo
0xCB dBASE IV SQL table files, with memo
0xF5 FoxPro 2.x (or earlier) with memo
0xFB FoxBASE)
7: Last Update
8: Header Length (value at byte 8-9, minus 1, I think)
9: (L) Has CDX
10: (L) Has Memo Field
11: (L) Is a DBC
[/code]

This also makes me think of making a function, RemVCX, to aid in reindexing features, to avoid having to error-handle the "Missing CDX" error.

 

[wgcs]

BTW: I noticed I accidently left an assignment on the FWRITE() command which could mess up the value of lcRecCnt (though it isn't reference after that, but it's unsafe to leave that in, in case of future adjustments)

 

[wgcs]

Here's the code for RemVcx:
( This works, but isn't thoroughly tested for what happens if the file is read only, or already open, etc. )

Perhaps you'd like to put all our code samples together into a FAQ: "Can I get information direct from a DBF's header?" or something like that...

FUNCTION RemVcx( pcFile )
LOCAL   lnHandle
PRIVATE nErr
nErr  = 0
lnRes = 0
if adir(laJnk,pcFile)=0
  RETURN -1
endif
lnFileSize = laJnk[2]
lnHandle = FOPEN(pcFile,12)   && Open file, R/W, Unbuffered
IF lnHandle > 0
  oErr = On("ERROR")
  ON ERROR nErr=Error()
  FSEEK(lnHandle,28,0) && Flags
  lcFlags = FREAD(lnHandle,1)
  * Remove flag for HAS VCX
  lcFlags = CHR( BITAND( ASC(lcFlags), BITNOT(0x01) ) )
  FSEEK(lnHandle,28,0) && Flags
  FWRITE( lnHandle, lcFlags )
  FCLOSE(lnHandle) 
  ON ERROR &oErr
ELSE
  lnRes = iif(lnHandle=0,-1,lnHandle)
ENDIF
RETURN lnRes
ENDFUNC

 

[craigsboyd]

wgcs,

Yeah if we put a little more work in I think we could have a pretty extensive and thorough FAQ on the DBF structured files. I really like your ideas and additions...nothing like just taking over someone's thread and going crazy...I like it!

Slighthaze = NULL

 

[AirCon]

slighthaze
I keep thinking there has to be a slicker way to convert a binary string to it's numeric equivalent (an API call maybe?)

For VFP, no API can handle this. VFP is different from other languange. It treated all in string/chars. There is another technique but I guess not much different for speed.

[COLOR=0000D0]
val('0x' + ;
right(transform(asc(right(lcRecCnt,1)), '@0'),2) + ;
right(transform(asc(substr(lcRecCnt,3)), '@0'),2) + ;
right(transform(asc(substr(lcRecCnt,2)), '@0'),2) + ;
right(transform(asc(left(lcRecCnt,1)), '@0'),2))
[/color]

wgcs
Multiplication is fastest (val * 256*256*256),
Val * 256^3 comes in second,
and BITLSHIFT(Val,24) is slowest

I'm a bit surprise with your test, cause SHIFT is the fastest for machine code. Well, maybe VFP is not optimize for this.

-- AirCon --

 

[baltman]

This saves the screen dir info into a text file that can be parsed. I can't figure out how to supress the screen while not killing the file output too...

dir to file dir.txt

modi comm dir.txt

Brian

 

[ramani]

Hi

Just a quickie..

To get the number of records, you need the header info and the file opening in low level or in some way. SO I would go with VFPs native way.
****************************************************
* Get list of files and the number of records
CREATE CURSOR filename (cfilename c(128), nRecords I)
*
myDir = GETDIR()
myDir = ADDBS(myDir)
nFiles = ADIR(aFiles,myDir+"*.DBF&quot

FOR I=1 TO nFiles
USE myDir+(aFiles(i,1)) IN 0 ALIAS cTable
APPEND BLANK
REPLACE cfilename WITH aFiles(i,1), ;
nRecords WITH RECCOUNT("cTable&quot
USE IN cTable
ENDFOR
BROW
**********************************************************
You can probably hard code the myDir instead of GETDIR().

And if you still want to avoid the USE table way..

DIR myDir TO FILE test.txt

This will send the dbf of the myDir to a text file with information .. file name, no of records, last update and size to test.txt

You can display the test.txt as it is.. or get it back in an array... =ALINES(aFiles,FILETOSTR("test.txt&quot
Then aLines(3) onwards you will get the file names with data. You can work on that to get an acceptable format.




ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[agit72]

Hi all experts,
How about deleted record?? Are they counted??
how if we don't want to include the deleted records?

Agit Permana (08561052915)
Mitra Solusi Pratama, PT

http://www.msp-works.com