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

 

[rgbean]

Agit,
There are no built in summary counts for deleted records. For that count (or just non-deleted records), you'll need to open the table and count them! You may even want to lock the file or open it exclusively if you want an accurate count in a multi-user application.

Rick

 

[darrellblackhawk]

It can be done, but it'll take a lot longer than just
opening the table and getting the info.

I did a number of tests and it took on average 4-5 times
as long to use low-level file functions to retrive the
deleted record count VS just opening the table.

Sorry, but the facts are the facts.

Following is the code I used for the comparison tests:


clear
clear memo
clear prog


* Using low-level file functions
lnStop = 0
lnStart = seconds()

LOCAL lnFlHand, lnRecCnt, ln1stRec, lnLenOfRecs, i, lnDelCnt
store 0 to lnRecCnt, lnDelCnt
lnFlHand = FOPEN("FileToTest.dbf&quot
IF lnFlHand <> -1

* Get table specs
=FSEEK(lnFlHand,4,0)
lnRecCnt = BinToDecimal(FREAD(lnFlHand,4))
ln1stRec = BinToDecimal(FREAD(lnFlHand,2))
lnLenOfRecs = BinToDecimal(FREAD(lnFlHand,2))
lnDelCnt = 0

* Count number of deleted records
FOR i = 1 TO lnRecCnt
=FSEEK(lnFlHand,ln1stRec,0)
lnDelCnt = lnDelCnt + IIF(FREAD(lnFlHand,1)==&quot; &quot;,0,1)
ln1stRec = ln1stRec + lnLenOfRecs
NEXT

ENDIF
=FCLOSE(lnFlHand)

lnStop = seconds()
* End of test sample 1


? &quot;The table has &quot;+allt(str(lnRecCnt))+&quot; Records and &quot;+allt(str(lnDelCnt))+&quot; records.&quot;
? &quot;Elapsed time: &quot;,lnStop-lnStart
?



* Using standard table functions
lnStart = seconds()

use filetotest.dbf
count to lnDelCnt for deleted()
? &quot;The table has &quot;+allt(str(reccount()))+&quot; Records and &quot;+allt(str(lnDelCnt))+&quot; records.&quot;
use

lnStop = seconds()
* End of test sample 2

? &quot;The table has &quot;+allt(str(lnRecCnt))+&quot; Records and &quot;+allt(str(lnDelCnt))+&quot; records.&quot;
? &quot;Elapsed time: &quot;,lnStop-lnStart


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



'We all must do the hard bits so when we get bit we know where to bite'

 

[DanEvansJr]

So, without opening the table, I guess the answer is NO.

 

[darrellblackhawk]

I did some addtional test and found that if the record
count is low, the low-level function can sometimes be
faster. This makes sense. Possibly a combination of the
two methods could give an increase in performance.

That is, open the table low-level, and if the record count
is below a certain count, just count the deleted records
using low-level functions. Otherwise, close it and open it
using table functions and then get the deleted() record count.

Darrell

P.S. I noticed I display the results of the second test twice.


'We all must do the hard bits so when we get bit we know where to bite'

 

[wgcs]

My main concern/reason for wanting to get the RecCount without opening a table is to avoid all the conversions/errors that VFP can throw if the table header is damaged, or the CDX index file is missing. Until we convert over to VFP8's try-catch error handling, these errors have been very persnikity to try to keep from plaguing the user during a reindex routine.

I haven't done anything with the rec count yet, but I'd think it'd make sense to not import from (or even open) DBF's whose reccount=0.

I haven't implemented in our s/w yet, but plan to, to remove the VCX references before opening them. That's a feature I wish was built into VFP to be capable programmatically (without all the Error problems... if you have an object in scope with an error method, it's practically impossible to suppress this error... at least our code had been unstable because of it...)
But the UDF RemVCX() does it nice and quick.