Goalposts Moved on VFP Cursors?
I appear to have a deep misunderstanding of how VFP cursors work
My code is littered with examples like this as the quickest way to establish the presence of a record meeting arbitrary conditions where a simple INDEXSEEK isn't appropriate
SELECT * FROM (ANYTABLE) WHERE (ANYCONDITION) INTO CURSOR 'JUNK'
IF RECCOUNT('JUNK')>0
DO SOMETHING
ELSE
DO SOMETHING_ELSE
ENDIF
This has worked for years.
Suddenly it doesn't.
I was so shocked, I assumed that either my data or my VFP installation had become corrupt.
I eliminated the former by trying it on various different tables from different sources
I eliminated the latter by first, thoroughly uninstalling all traces of VFP, then reinstalling it.
Then repeating the experiment on two other workstations where I had VFP installed.
The result is consistent.
What actually happens is that the Cursor is created with a filter matching (ANYCONDITION) and, if you're running this from a command line, for a few seconds you see the result you expect (eg 0 Records if you know that no records match the condition) but then the status bar displays the name of the Cursor, its table of origin and the result count followed by RECCOUNT() of the original table (and now Cursor) (eg "Junk (path to table) Record 1/(RECCOUNT('ANYTABLE')") . The cursor is displaying the result count because it is now in a filter which matches ANYCONDITION. But if I clear the filter (SET FILTER TO) what I have is a full copy of (ANYTABLE) in cursor JUNK.
In contrast, if, instead of INTO CURSOR 'JUNK' I use INTO DBF 'JUNK' the results are exactly what I expect.
So
1 am I deluded in my expectation that the contents of CURSOR should be identical to the contents of the DBF equivalent?
2 If so, how on earth have I been getting away with it up to now? My software has often depended on this simple test and up till about a week ago neither I nor my clients have ever encountered a problem resulting from the use of such code
3 If not (i.e. we all agree that CURSORS should behave in accord with my prior expectations) how on earth could this behaviour change, simultaneously, on 3 different VFP workstations? (which don't even have the same version of Windows 10 running)
My code is littered with examples like this as the quickest way to establish the presence of a record meeting arbitrary conditions where a simple INDEXSEEK isn't appropriate
SELECT * FROM (ANYTABLE) WHERE (ANYCONDITION) INTO CURSOR 'JUNK'
IF RECCOUNT('JUNK')>0
DO SOMETHING
ELSE
DO SOMETHING_ELSE
ENDIF
This has worked for years.
Suddenly it doesn't.
I was so shocked, I assumed that either my data or my VFP installation had become corrupt.
I eliminated the former by trying it on various different tables from different sources
I eliminated the latter by first, thoroughly uninstalling all traces of VFP, then reinstalling it.
Then repeating the experiment on two other workstations where I had VFP installed.
The result is consistent.
What actually happens is that the Cursor is created with a filter matching (ANYCONDITION) and, if you're running this from a command line, for a few seconds you see the result you expect (eg 0 Records if you know that no records match the condition) but then the status bar displays the name of the Cursor, its table of origin and the result count followed by RECCOUNT() of the original table (and now Cursor) (eg "Junk (path to table) Record 1/(RECCOUNT('ANYTABLE')") . The cursor is displaying the result count because it is now in a filter which matches ANYCONDITION. But if I clear the filter (SET FILTER TO) what I have is a full copy of (ANYTABLE) in cursor JUNK.
In contrast, if, instead of INTO CURSOR 'JUNK' I use INTO DBF 'JUNK' the results are exactly what I expect.
So
1 am I deluded in my expectation that the contents of CURSOR should be identical to the contents of the DBF equivalent?
2 If so, how on earth have I been getting away with it up to now? My software has often depended on this simple test and up till about a week ago neither I nor my clients have ever encountered a problem resulting from the use of such code
3 If not (i.e. we all agree that CURSORS should behave in accord with my prior expectations) how on earth could this behaviour change, simultaneously, on 3 different VFP workstations? (which don't even have the same version of Windows 10 running)
This is standard VFP behavior since the far past...
VFP can decide to create the CURSOR as a filter on the underlying table open in a separate workarea. This is possible namely when the filter (ANYCONDITION) is fully optimized (appropriate indexes exist), the query is based just on one table, and no calculated fields are involved in the query...
If VFP creates the cursor as a filtered table then it has certain drawbacks. One of them is the RECCOUNT(), another one is the impossibility to use such cursor in a subquery and we could find more.
To avoid filtered table opening you may add NOFILTER keyword to your query:
SELECT * FROM (ANYTABLE) WHERE (ANYCONDITION) INTO CURSOR 'JUNK' NOFILTER
Or you may test the _TALLY system variable instead of the RECCOUNT().
If you don't need data from the underlying table for further processing then you may just retrieve COUNT(*):
VFP can decide to create the CURSOR as a filter on the underlying table open in a separate workarea. This is possible namely when the filter (ANYCONDITION) is fully optimized (appropriate indexes exist), the query is based just on one table, and no calculated fields are involved in the query...
If VFP creates the cursor as a filtered table then it has certain drawbacks. One of them is the RECCOUNT(), another one is the impossibility to use such cursor in a subquery and we could find more.
To avoid filtered table opening you may add NOFILTER keyword to your query:
SELECT * FROM (ANYTABLE) WHERE (ANYCONDITION) INTO CURSOR 'JUNK' NOFILTER
Or you may test the _TALLY system variable instead of the RECCOUNT().
If you don't need data from the underlying table for further processing then you may just retrieve COUNT(*):
SELECT COUNT(*) cnt FROM (ANYTABLE) WHERE (ANYCONDITION) INTO CURSOR 'JUNK'
IF JUNK.cnt > 0
DO SOMETHING
ELSE
DO SOMETHING_ELSE
ENDIFSELECT (ANYTABLE)
LOCATE FOR (ANYCONDITION)
IF FOUND()
DO SOMETHING
ELSE
DO SOMETHING_ELSE
ENDIFASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
greetings pcelba
I take it for granted that you're right, and I'll re-code to use your suggestions.
But it leaves me flummoxed! How on earth have I got away with my version for so long?
In about 50% of my use cases, I do need the data and for 50% I don't. So I'll try the SELECT COUNT(*)
option for the latter.
and I'll thorougly retest the other. But I can tell you now that there is one particular example where I deliberately use a cursor to create a non amendable table (because its about 10 times faster than creating the equivalent dbf) and it most definitely ONLY displays the selected records and has no filter active. Given what you say above, that shouldn't be possible, so I'm going to crawl particularly carefully over the code for that little trick to see what's really happening. I'll feed back the results when I understand them.
Meanwhile I'll accept your contribution as the valid solution.
I take it for granted that you're right, and I'll re-code to use your suggestions.
But it leaves me flummoxed! How on earth have I got away with my version for so long?
In about 50% of my use cases, I do need the data and for 50% I don't. So I'll try the SELECT COUNT(*)
option for the latter.
and I'll thorougly retest the other. But I can tell you now that there is one particular example where I deliberately use a cursor to create a non amendable table (because its about 10 times faster than creating the equivalent dbf) and it most definitely ONLY displays the selected records and has no filter active. Given what you say above, that shouldn't be possible, so I'm going to crawl particularly carefully over the code for that little trick to see what's really happening. I'll feed back the results when I understand them.
Meanwhile I'll accept your contribution as the valid solution.
ASKER
The key concept is "fully optimised". After reviewing my code, where in all except the most recent cases, the Cursor behaves exactly as I expect and want, the reason is that, (by luck rather than judgement) they ARE fully optimized. So I only hit a problem when writing code which permits unoptimised conditions. I have to challenge your additional condition: "the query is based just on one table, and no calculated fields are involved in the query..."
as this line of code also works perfectly:
SELECT * FROM STOKTRAN WHERE NOT DELETED() AND STPRIME IN(SELECT STPRIME FROM STOCK) INTO CURSOR BALCHECK ORDER BY CODE
which suggests that the IN(SELECT (tied to an indexed key in the foreign table) gets around the single table limitation.
as this line of code also works perfectly:
SELECT * FROM STOKTRAN WHERE NOT DELETED() AND STPRIME IN(SELECT STPRIME FROM STOCK) INTO CURSOR BALCHECK ORDER BY CODE
which suggests that the IN(SELECT (tied to an indexed key in the foreign table) gets around the single table limitation.
Unoptimized conditions are OK for you.
The problem is when the condition is fully optimized. In such case the filtered table is opened instead of cursor creation. VFP uses this approach because fully optimized filter is created quickly w/o additional disk space requirement and thanks to the optimization the data access speed is not degraded.
It is technically possible to create a filter which is not fully optimized but such filter would slow everything down.
The IN (SELECT ...) cannot be used in filter so it also disqualifies the filtered table opening (good for you).
ORDER BY avoids filtered table when no appropriate index key exists.
And that's not all... VFP can create a filter which has incorrect syntax when you would use it in SET FILTER command but it works internally.
The problem is when the condition is fully optimized. In such case the filtered table is opened instead of cursor creation. VFP uses this approach because fully optimized filter is created quickly w/o additional disk space requirement and thanks to the optimization the data access speed is not degraded.
It is technically possible to create a filter which is not fully optimized but such filter would slow everything down.
The IN (SELECT ...) cannot be used in filter so it also disqualifies the filtered table opening (good for you).
ORDER BY avoids filtered table when no appropriate index key exists.
And that's not all... VFP can create a filter which has incorrect syntax when you would use it in SET FILTER command but it works internally.
ASKER
Can you clarify
"ORDER BY avoids filtered table when no appropriate index key exists."
That reads as though your saying adding "ORDER BY" will FORCE the kind of cursor I'm after (containing only the selected records). But its also ambiguous because of your "when" clause - which implies the filter version is ONLY avoided when no index key exists (which contradicts what you've said before)
If we can eliminate the filtered version by adding an ORDER BY clause, it would solve the problem entirely as I'm quite happy to add that to my arbitrary queries which may or may not be "fully optimised"
"ORDER BY avoids filtered table when no appropriate index key exists."
That reads as though your saying adding "ORDER BY" will FORCE the kind of cursor I'm after (containing only the selected records). But its also ambiguous because of your "when" clause - which implies the filter version is ONLY avoided when no index key exists (which contradicts what you've said before)
If we can eliminate the filtered version by adding an ORDER BY clause, it would solve the problem entirely as I'm quite happy to add that to my arbitrary queries which may or may not be "fully optimised"
Imagine the table T1 with columns C1 and C2 where C1 is indexed (index tag C1) and SET DELETED is OFF and collation is Machine.
Following queries will behave this way
SELECT * FROM T1 WHERE C2 = SomeValue INTO CURSOR cT1 … not optimized creates cursor in temp table
SELECT * FROM T1 WHERE C1 = SomeValue INTO CURSOR cT2 … fully optimized opens filtered table
SELECT * FROM T1 WHERE C1 = SomeValue ORDER BY C1 INTO CURSOR cT3 … fully optimized and index matching ORDER BY on C1 exists … opens filtered table where order is set to C1 index tag
SELECT * FROM T1 WHERE C1 = SomeValue ORDER BY C2 INTO CURSOR cT4 … fully optimized WHERE but index on C2 does not exist so it creates cursor in temp table
Of course, we could make it more foggy when considering various collations...
But you may ignore all these rules and use NOFILTER keyword when you need the cursor created as a temp table.
Following queries will behave this way
SELECT * FROM T1 WHERE C2 = SomeValue INTO CURSOR cT1 … not optimized creates cursor in temp table
SELECT * FROM T1 WHERE C1 = SomeValue INTO CURSOR cT2 … fully optimized opens filtered table
SELECT * FROM T1 WHERE C1 = SomeValue ORDER BY C1 INTO CURSOR cT3 … fully optimized and index matching ORDER BY on C1 exists … opens filtered table where order is set to C1 index tag
SELECT * FROM T1 WHERE C1 = SomeValue ORDER BY C2 INTO CURSOR cT4 … fully optimized WHERE but index on C2 does not exist so it creates cursor in temp table
Of course, we could make it more foggy when considering various collations...
But you may ignore all these rules and use NOFILTER keyword when you need the cursor created as a temp table.
ASKER
(It's only because it failed in some code I'm working on at the moment, that I discovered this aberrant behaviour from the command line)