How to know a table is empty ? 1

[andreateh]

How do i know that a table is empty ? "Empty" i mean here is no record in tha table or the table contain only deleted record. Does line below work ?

nRecno = IIF(NOT EOF("table") OR;
         BOF("table"),RECNO("table"),.F.)

IF TYPE("nRecNo") = "L"
     ?" no record"
ElSE
      ?"Contain Record"
endif

 

[ChrisRChamberlain]

[COLOR=blue]SELE TABLENAME
COUNT TO lnCount FOR !DELETED()[/color]

will give the total of deleted records.

[COLOR=blue]? RECCOUNT([TABLENAME])[/color]

will give the total of records including deleted records.

FAQ184-2483 - answering getting answered.​

Chris

http://www.pdfcommander.com

http://www.pdfcommander.co.uk

 

[JackTheC]

Why not simply use:

USE (TABLE)
IF EOF() && TRUE when empty or contains only deleted records
? "Empty"
ELSE
? "Not empty"
ENDIF
USE

Greetings,
Jack

 

[ChrisRChamberlain]

andreateh

Apologies - fingers faster than brain

[COLOR=blue]SELE TABLENAME
COUNT TO lnCount FOR DELETED()
? lnCount[/color]

FAQ184-2483 - answering getting answered.​

Chris

http://www.pdfcommander.com

http://www.pdfcommander.co.uk

 

[mejiaks]

hey, how do I poset the code in that fancy littlew window???

 

[TheRambler]

mejiaks

see thread184-782875

 

[andreateh]

Hi Jack if i use your code mean that i need to reload again my table right, if i already load the table? How about if i use a cursor ?

 

[ChrisRChamberlain]

andreateh

[COLOR=blue]IF EOF([TABLE])
   ? "Empty"
ELSE
   ? "Not empty"
ENDIF[/color]

It's not necessary to select a table to determine if the table is at EOF()

If you COUNT FOR expression and do not return to the record from which you started or issue LOCATE or GO TOP, you will put the table into an EOF() state, so JackTheC's code can give a false result under those circumstances.

So the comment && TRUE when empty or contains only deleted records is not necessarily the case.

FAQ184-2483 - answering getting answered.​

Chris

http://www.pdfcommander.com

http://www.pdfcommander.co.uk

 

[ChrisRChamberlain]

andreateh

To answer your original question :-

[COLOR=blue]IF RECC([TABLENAME]) > 0
[tab]SELE TABLENAME
[tab]COUNT FOR DELETED() TO lnCount
[tab]LOCATE
[tab]IF lnCount = RECC([TABLENAME]) 
[tab][tab]? [All ] ;
[tab][tab][tab]+ TRANS(RECC([TABLENAME]) ;
[tab][tab][tab]+ [ records are deleted]
[tab]ELSE
[tab][tab]? [There are ] ;
[tab][tab][tab]+ TRANS(RECC([TABLENAME]) ;
[tab][tab][tab]+ [records, of which ] ;
[tab][tab][tab]+ TRANS(lnCount) ;
[tab][tab][tab]+ [ are deleted]
[tab]ENDI
ELSE
[tab]? [Table is empty]
ENDI[/color]

FAQ184-2483 - answering getting answered.​

Chris

http://www.pdfcommander.com

http://www.pdfcommander.co.uk

 

[andreateh]

Thanks lot ChrisRChamberlain. One more qustion...


COUNT FOR DELETED() TO lnCount

Will line above slow down my program ?Because i have read before that COUNT is RushMore Optimizeable. So if don't have a index on DELETED() will it slow down ?

 

[ChrisRChamberlain]

andreateh

In theory, yes - in practice, probably not.

Chris

FAQ184-2483 - answering getting answered.​

Chris

 

[Stella740pl]

andreateh,

JackTheC's solution depends on SET DELETED setting.
To make it work, you should first

[blue]
SET DELETED ON
GO TOP IN MyTable
[/blue]
IF EOF('MyTable')
   ? "Empty"
ELSE
   ? "Not empty"
ENDIF

If you have SET DELETED OFF and the table has deleted records, you will get EOF() return .F. when on TOP of the table.

Stella

 

[Stella740pl]

Also, slight variations of Chris' solution:

SELECT MyTable
COUNT FOR !DELETED() TO nDel
IF nDel=0
   ? "Empty"
ELSE
   ? "Not empty"
ENDIF

or

SELECT MyTable
COUNT FOR !DELETED()
IF _TALLY=0
   ? "Empty"
ELSE
   ? "Not empty"
ENDIF

 

[GriffMG]

The count technique might take a long time on a large table.

I usually use BOF() and EOF() at the same time - if they are both true, there are no usable records!

HTH



Regards

Griff
Keep [Smile]ing

 

[OhioBill]

I'm with GriffMG. Nothing is known about the table, so eof() bof() seems best to me.

 

[Stella740pl]

GriffMG,

The count technique might take a long time on a large table.
Agree.

I usually use BOF() and EOF() at the same time - if they are both true, there are no usable records!
If table has only deleted records, as in original question, this will also depend on SET DELETED setting.

set deleted off
go top
?eof() AND bof()  && returns .T.
go bottom
?eof() AND bof()  && returns .F.
set deleted on
go top
?eof() AND bof()  && returns .F.
go bottom
?eof() AND bof()  && returns .T.

 

[AirCon]

This is another problem using BOF() and EOF()

Create cursor curTest (Test C(10))
Insert into curTest values ('Testing')

Set deleted on
? recno(), bof() and eof(), bof(), eof()
[COLOR=green]** return 1, .F., .F., .F.[/color]

Delete
? recno(), bof() and eof(), bof(), eof()
[COLOR=green]** return 1, .F., .F., .F.[/color]

Skip
? recno(), bof() and eof(), bof(), eof()
[COLOR=green]** return 2, .F., .F., .T.[/color]

Go bottom
? recno(), bof() and eof(), bof(), eof()
[COLOR=green]** return 2, .T., .T., .T.[/color]

-- AirCon --

 

[GriffMG]

Well, how weird!

I've been using the EOF() .and. BOF() for (cripes) about 15yrs and never noticed that!

Mind you, I stick with the deleted option set and just work with tables (as opposed to cursors) and never set out to try and break it (as a scheme)

Martin

Regards

Griff
Keep [Smile]ing

 

[FrenchFox]

select mytable
x = set('dele')
set dele on
locate for .T.
if eof()
? "empty"
else
? "not empty"
endif
set dele &x

 

[ChrisRChamberlain]

How do i know that a table is empty ? "Empty" i mean here is no record in tha table or the table contain only deleted record.

The combinations of GO TOP, BOF() and EOF() have been enumerated as an answer to the first part of the question, their reliability being somewhat suspect.

To answer the second part of andreateh's question and include a reliable answer to the first part requires a count of the deleted records, unless there is another technique?

FAQ184-2483 - answering getting answered.​

Chris
PDFcommander.com
PDFcommander.co.uk