How to Determine If A Table/Cursor Is Empty? 5

[Rock4J]

As per thread184-785663

I'm looking for a code that can Determine if a table is empty.

Using ADO-Recordset in VB I usually use ".Recordcount", as simple as that.

I believe there is a right way to do it in Foxpro. and hopefully it support "VPF6".

Please help.

Thanks.

Regards,
Rocky

 

[Mike Lewis]

Hi Rocky,

The easiest way is like this:

IF RECCOUNT("TheTable") = 0
  * The table is empty
ELSE
  * It's not empty
ENDIF

A problem with that occurs if all the records in the table are deleted. RECCOUNT() returns the number of physical records, deleted or otherwise, which might not be what you want. If the table might contain deleted records, then do this instead:

LOCAL ARRAY laCount(1)
SELECT COUNT(*) FROM TheTable INTO ARRAY laCount
IF laCount(1) = 0
  * The table is empty
ELSE
  * It's not empty
ENDIF

Hope this helps.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[bborissov]

 RECCOUNT("AliasNameHere") = 0

Borislav Borissov
VFP9 SP2, SQL Server

 

[mm0000]

select table

count for not deleted() to mcount
if mcount = 0
**Table empty code
else
**Table not empty code
endif

If there are no deleted records in your table or you can issue a pack command before checking for status, you could check for an empty table using

select table
if reccount() = 0
**Table empty code
else
**Table not empty code
endif

 

[Mike Lewis]

Rocky, As you can see, there are several ways of doing it (all posted within three minutes of each other).

I noticed you asked for a method that works in VFP 6.0. All the solutions shown here are OK for that.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Rock4J]

Thanks MikeLewis, bborissov and mm0000 !!

I really appreciates it.

I will try it out okay..

Thanks so much..



Regards,
Rocky

 

[Olaf Doschke]

Just to explain, why some solutions here count records: In DBFs you can have RECCOUNT()>0, while there are no records. Because if you delete records, they are only marked deleted. This is done, because removing the bytes of the record physically means you'd need to rewrite all records after this one and in the mean case this means writing half the file size, just to remove the bytes of one record. Therefore there only is a byte in each record, that's not part of the record data and is set to 42 for deleted records.

RECCOUNT() simply is reading the reccount from the DBF header (it's structure is explained in the VFP help) and that counter is only incremented, when new records are added, not decremented, if records are deleted. So it also counts deleted records and you can compute the dbf file size via 1+HEADER()+RECCOUNT()*RECSIZE().

RECCOUNT() is fine anyway, because you would rather only have an empty table after creating it or as a result table, and then RECCOUNT() will be 0, it's seldom you delete all records of a table, if you would want that, you'd rather ZAP than DELETE ALL and thereby really physically reset the DBF to be just the header and EOF byte without any records.

But to be sure about that and nevertheless not need to count, you could query just the first undeleted record you find into a cursor, this will give you an unusual approach:

SELECT TOP 1 RECNO() FROM theTable ORDER BY 1 INTO CURSOR curEmpty
If RECCOUNT("curEmpty")=0
   * the table is empty
EndIf

As you only want to see, if the table is empty, you don't need the count, neither the count including deleted records nor the count of undeleted records. The query will instead query the top 1 record and stop there. If there is one you know the table is not empty, if there is none, you know the table is empty.

The advantage of that solution is, it will be fast and only query a single record, even if there are many. It won't retrieve the row itself, just the recno of the first undeleted record, but the resultset doesn't matter. If it's RECCOUNT() is 0, the table is empty.

On the other side RECCOUNT() is just reading 4 bytes of the DBF header, as already said, that's even faster, and can be relevant if you apply it to a resultset cursor or table, directly after creating it. That's what I use and why I use it. There can't be a deleted record in curEmpty.

Even if you SET DELETED OFF and allow VFP to query deleted records, the result curEmpty would never have deleted records, instead it would have the deleted record from the source table(s) as undeleted records of the result. The deletion mark byte is not inherited by querying data, resultsets are new records and therefore get no deletion mark byte set. That's the danger of SET DELETED OFF, you need to be sure you want that, eg when you want to find a record to RECALL it. The normal case is SET DELETED ON anyway.

Oh, and there is another simple solution:

set deleted off && if it isn't anyway
use sometable
if eof()
   * table is empty, because
   * VFP autolocates the first undeleted record and goes to eof(), if there are only deleted records.
endif

Bye, Olaf.

 

[Olaf Doschke]

Ouch, in my last solution it of course must be SET DELETED ON, if it isn't anyway.

I always get this confused, even just a moment after correctly explaining it ;^)

ON means the deleteion flag works, therefore suppressing deleted records as you would expect from a normal table anyway. OFF means the flag doesn't work and has no effect, so VFP handles deleted records as if they never were deleted.

Bye, Olaf.

 

[Olaf Doschke]

Me again,

just to state the obvious:

EOF() is only indicating an empty table right after USE table, EOF() can of course also be .T. for a table with records, eg after a SCAN..ENDSCAN loop you are at EOF(), also after a LOCATE or SEEK without a matching record.

And while I am at a third post in series again, I'll add another tricky thing: If you SELECT * FROM someatable WHERE .F. INTO CURSOR curTemp NOFITLER that cursor of course is empty and has the structure of the table. In VFP you can make that cursor readwrite then, by USE("curTemp") IN 0 AGAIN ALIAS curReadWrite. Then USE curTemp and work with curReadWrite.

Bye, Olaf.

 

[Mike Lewis]

in my last solution it of course must be SET DELETED ON, if it isn't anyway .... I always get this confused,

I'm really pleased to hear that, Olaf. I never rememember which way round SET DELETED goes. I always have to try it both ways to see the effect. Neither setting seems intuitive.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[GriffMG]

I might suggest that if you open a table and go top, and find EOF() and BOF() both to be true... you have an empty table.

use mytable
go top
if eof() .and. bof()
  ** empty
else
  ** not empty
endif

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Olaf Doschke]

Griff, that's right, but that's also only covering the case RECCOUNT()=0.

Bye, Olaf.

 

[GriffMG]

I don't quite follow Olaf

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Mike Lewis]

I don't quite follow Olaf

Griff, I think the point is that your suggestion will work only if the table has no deleted records. But if that's the case, just testing for RECCOUNT() = 0 would be just as effective.

Rocky, if you're still here, can you tell us if the presence of deleted records is likely to be an issue? If you know that your table has no deleted records, then testing for RECCOUNT() = 0 is the simplest and fastest way to do it. All the other suggestions are intended for the (unusual) case where all the records might be deleted.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[GriffMG]

Ah, in that case - so long as deleted records are hidden:

set deleted on
use mytable
go top
if eof() 
  ** empty
else
  ** not empty
endif

and the test for BOF() is actually redundant too...

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Olaf Doschke]

Griff, your final code is almost my last suggestion, but you don't need to GO TOP right after USE MYTABLE. When VFP opens a table, it locates the first undeleted record anyway, if there is one.

Bye, Olaf.

 

[GriffMG]

Quite right Olaf, I needed it to test for BOF(), but if you are just checking EOF() then the table will be 'go topped' already

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Olaf Doschke]

Now I don't quite follow your logic.

If you USE a table the GO TOP is included already, it's not needed, therefore.

Bye, Olaf.

 

[GriffMG]

If you USE a table that is completely empty, no deleted records, then EOF() will be true, and BOF() false - doing a GO TOP makes them BOTH true.
But, as someone pointed out earlier - if there are deleted records (or perhaps a filter) BOF() will not go true even with a GO TOP

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Olaf Doschke]

OK, but I was talking about your final code and your final code has no BOF() at all. And in your final code, the GO TOP therefore also isn't needed at all.

Besides, if table is empty, BOF() and EOF() is true, even if you don't GO TOP. So even in that case you won't neither GO TOP, nor BOF().

Bye, Olaf.