How to check that my query is empty ? or not empty ? 4

[A_Radiolog_inFoxPro]

Dear All ,

I am having an issue with some high-level queries,

 Select * from *sometable* INTO ARRAY aMyArray WHERE arguments

I need to be able to know if this query is empty so that I won't go down the line to check if my array even exists ( because since the query is empty the array won't be created at all.)

Thanks

The Doc

 

[mjcmkrsr]

Hi Doc,

You may want to have a look at the _TALLY system variable or check with VARTYPE() e.g.

IF VARTYPE(aMyArray) != "U"
continue
ELSE
do something else
ENDIF

hth

MK

 

[Mike Lewis]

Another approach:

IF _TALLY = 0
  * no results returned from SELECT
ENDIF

But be sure to do this immediately after the SELECT, as other commands can change value of _TALLY.

Mike





__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Indeed, as Mike mentioned, _TALLY is the number of result records, also when you query into an array.

I used it in the coode to your last question, I wonder why you didn't seee it there and if you don't know what _tally means look it up or ask. Where is your head?

Chriss

 

[Gerrit Broekhuis]

Hi,

Why not use a cursor instead of an array? _tally will stil tell the number of records processed, but you can also use reccount(), browse, etc..

Regards, Gerrit

 

[A_Radiolog_inFoxPro]

Dear All ,

Thank you very much for your valuable input ,

After 10 days of hard coding I managed to get my subroutine done , thanks to you ,

Thank you for your valuable input.

I went with the

IF VARTYPE(aMyArray) != "U"

Since it was simple and easy to manipulate with cases.

Thank you again.

The Doc.

 

[Chris Miller]

Doc,

there's a clear reason why I popinted out _TALLY two times. First time in providing code for validation, second time in pointing back at that. And now for a third time.

You can easily fool yourself with checking VARTYPE(aMyArray). When you run two queries and aMyArray was built by the first one, but the last query did have no result, then VARTYPE(aMyArray) != "U" even though _TALLY is 0.

_TALLY is always best, and also works , no matter if you query into a cursor, table or array.

And I don't see how _TALLY is hard to use or "manipulate" with cass. What do you even mean by "manipulating" it, you don't want to manipulate what it is, do you?

Chriss

 

[Mike Lewis]

I agree with Chris. _TALLY is simple to use: it is just like a variable, in that you can test its value and take action accordingly. It's also easy to understand by anyone looking at your code. I'd guess that the majority of developers in this forum would use _TALLY when they need to know if a query has produced results, rather than the more convoluted method of testing the vartype of the array.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

_tally is also more universal, it also counts the number of updates done by an SQL update or by a replace or,.... welll take a look at the list the Hacker's guide founbd out (even trying whether _tally is changed by undocumented things)

https://hackfox.github.io/section4/s4g097.html

The point is, _tally is available not only in case of queries and so better get used to it than different functions and vartype checks. There also is nothing else to tell you the count of updated records or deleted records. So, better know your _tally, man.

Chriss

 

[Steve Meyerson]

FWIW
I can't explain this, but when SELECTing into a cursor resulting in XX records, my debug window showed _TALLY=0 and RECCOUNT('ThisTable')=XX.

Since then I use RECCOUNT() when I can because I feel it's more reliable. I may have done something wrong and haven't retested, but it's worked for me til now.

Steve

 

[Chris Miller]

Simple reason, you expect the debugger window (watch window or variables) to update immdiately, when something changes. _Tally is reliable, when you would have clicked on watch window, it surely would have updated the display of _tally to XX.

It's true to say that reccount() of result cursors is reliable for cursors, but it doesn't cover the case of counting updates, for example.

Okay, I went to it and tried, but the watch window updated _tally immediately. Not sure whether that proves or disproves anything, but if you don't rely on _tally I can't help you, I guess.

_Tally obviouusly changes with the next query, so you have to check against _tally before doing something else that changes _tally, therein could be another reason you didn't see XX. Reccount("cursor") will reliably tell you the reccount of that cursor, if you know and accept this count also includes deleted rows, but it also increses if you add a record that wasn't part of the query result. So anything is always a moving target. It's still under your control to store away _tally right after the query to later use it for a CASE statement or anything else.

Chriss

 

[Mike Lewis]

Steve, I've just spent a few minutes trying to reproduce the behaviour you described. But both _TALLY and RECCOUNT() behaved exactly as expected in all cases, and with no delay for updating the Watch window.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Just keep a few things in mind, when you work with a function or alo a query that has an array as its result, you see phrasing like this in all documentation.

a) VFP creates an array, if it doesn't exist and fills it with the data depending on query or function (ALINES, AUSED, etc.)
b) VFP extends an array, if it exists but is too small, so the result data can fit int

But that's it. VFP does NOT release an array if there is no result, so cehcking whether the array is not "U", which means checking whether the array is not undefined, means have the prerequisite the array is not existing before the query, if it did already exist it still exists when the result is empty, because VFP does not destroy an array just because there was no result, the old array is then kept. And that's where you can easily fail.

To query INTO CURSOR differs in that aspect, as VFP then always first creates an empty cursor, it even overrides and thus destroys a cursor of the same name. So that differs from querying into an array and one thing is completely right about Steves usage of reccount() in that case. It will just also be a number of some workarea when you query into an array and will have no connection to the result size. That's where _TALLY is always working, as already explained.

One more thing VFP does not do: When an array is sufficiently large it's used to store the query result and any additional array elements stay untouched, so you can't even use ALEN to determine the _tally of the query. An array as a result of a query has one major use case: When your query should produce exactly one field result, when the result array size is 1 only. Then both array[1] and array without the element index will be that one single value. such queries are things like SELECT COUNT(*) or other aggregation functions.

I won't dictate you what to use, if the array reuslt is convenient for some reason, I'm fine, but you need to know that in this case _TALLY can differ from ALEN, because the array was already larger than necessary for the result and the arrray can exist even though _tally is 0, so you are in the danger zone.

There are obviously two easy ways out of the danger zone: RELEASE the array before the query, or just use _tally. I'd neglect the report of a single case where _tally supposedly didn't give the result record count. It's really deep in the foundation of all commands having a result count.

Even not considering all these specialties, I'd find using _TALLY far easier and more elegant than checking the existance of the result array with VARTYPE.

Chriss

 

[Mike Lewis]

One other point in the VARTYPE() vs. _TALLY discussion:

_TALLY works in all versions of VFP. VARTYPE() is only available in VFP 6.0 and later.

Of course, this is unlikely to affect any of us here. But it might be relevant to other people who find this thread.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

you are in the danger zone.

What danger zone am I talking about, you might think. When you even come back and still read how the thread continues.

Well, in generl the danger zone of code that depends on previous execution or not and can give false positives. In the concrete example of what you do in thread184-1828552, searching a barcode, for example.

You likely will do so with a series of barcodes. And in case you scan and then search a barcode that finds nothing, but the previous barcode was found, just checking VARTYPE() will mean your code assumes the barcode found a product, whil _tally is 0. Because VFP does not delete arrays before creating a new result array from an SQL SELECT...INTO ARRAY query.

So the vartype check is not clean.

To demonstrate that in an easy case:

Create Cursor sampleproducts (barcode c(12), product c(20))

Insert into sampleproducts values ('123456789012','product1')

cBarcode = '111111111111'
Select * from sampleproducts into array aFoundproduct where barcode=m.cBarcode

? _tally, 'result(s) found'
If VARTYPE(aFoundproduct) != "U"
   ? 'found '+aFoundproduct[2]
Else
   ? 'found nothing for barcode '+m.cBarcode
EndIf


cBarcode = '123456789012'
Select * from sampleproducts into array aFoundproduct where barcode=m.cBarcode
   
? _tally, 'result(s) found'
If VARTYPE(aFoundproduct) != "U"
   ? 'found '+aFoundproduct[2]
Else
   ? 'found nothing for barcode '+m.cBarcode
EndIf
   
cBarcode = '111111111111'
Select * from sampleproducts into array aFoundproduct where barcode=m.cBarcode
   
? _tally, 'result(s) found'
If VARTYPE(aFoundproduct) != "U"
   ? 'found '+aFoundproduct[2]
Else
   ? 'found nothing for barcode '+m.cBarcode
EndIf

If you run this you'll see the third result has _tally=0 but VARTYPE(aFoundproduct) != "U" suggests there is a found product. It's just the result of the previous query that found something, so what does that mean? Once you find a product by a barcode you'll also always find the last found product when scanning a non existant barcode. There's the danger zone. No error happens, but you have a false result which you assume came from the last query.

So see for yourself, _tally is the "higher truth".


Chriss

 

[Steve Meyerson]

Mike, Chris,

Ok, you guys convinced me. I must have done one of the "wrong" things you suggested and made a rash decision to switch to RECCOUNT() when I could. Ignorance is bliss (plagerized).

Steve

 

[Chris Miller]

Greate, Steve

and for those convinced by Steve _tally isn't reliable, you get far easier into trouble trusintg anything elwse but _tally, just one example, even using INTO CURSOR, that faily with RECCOUN() due to the concept of filter cursors:

Clear
Open Database _samples+"tastrade\data\tastrade.dbc"
Select * from customer where customer_id LIKE 'A%' into cursor alfki
? _tally
? Reccount()
Scan
   ? customer_id
endscan

You see a _tally of 4, a reccount of 52, and the scan loop just outputs the 4 result customers with IDs starting with 'A'.

So, _TALLY is the most reliable count of results, no matter if you belive it may fail at some rare circumstances or not. I'd be eager to see such a case happening and how it could even be explained.

Chriss

 

[A_Radiolog_inFoxPro]

Did I just started a war?
The_Doc

 

[Steve Meyerson]

You see a _tally of 4, a reccount of 52

With your code, I got a RECCOUNT() of 4. I still agree though - _TALLY wins the "war" (per Radio).

Steve

 

[Mike Lewis]

Let's forget the war. Both approaches are valid. Each has its pros and cons.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads