Queries and Filtered Cursors

Submitted by Sergey on January 2, 2008 - 01:47 UTC
topic: 
sql
vfp
foxpro
cursor
nofilter
readwrite

The SELECT - SQL Command with INTO CURSOR

clause can create a filtered cursor under following conditions:

  • Only one table is referenced by the query
  • WHERE condition is fully optimizable and can be represented by SET FILTER TO command
  • In the Select list either '*' specified (all fields) or subset of the fields from the table
  • No additional clauses, like DISTINCT, ORDER BY, GROUP BY, etc., are present
  • SET EXACT and SET ANSI settings match
VFP creates a filtered cursor by opening the source table similar to USE AGAIN command, applying SET FILTER to it and limiting the list of fields similar to SET FIELDS, if necessary. It could be much faster than creating actual cursor because no data transfer takes place but it comes with a price. The filtered cursors have following limitations/side effects:
  • They cannot be used in subsequent queries.
  • A variable in the WHERE clause could cause error later if it goes out of scope.
  • The data is coming directly from the underlying table and as result
    • RECCOUNT() will return number of records in the underlying table not in the cursor. _TALLY will hold number of records in the cursor.
    • In general, RECNO() will not be correct relatively to the cursor content.
    • Changes in the table will be visible in the cursor.
It's better to avoid filtered cursors because of their limitations/side effects by adding NOFILTER or READWRITE clause to a query. Sample code demonstrates some of the points discussed. $SAMPLECODE$ 




 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70





CLEAR 

SET DELETE ON
SET EXACT OFF
SET ANSI OFF

CREATE TABLE TestTable (pk I Autoinc, Sometext C(20))
INSERT INTO TestTable (Sometext) VALUES ("One")
INSERT INTO TestTable (Sometext) VALUES ("Two")
INSERT INTO TestTable (Sometext) VALUES ("Three")
INDEX ON pk TAG pk
* Turn on query optimization display
= SYS(3054,1)

? "This query is only partially optimizable, real cursor is created"
SELECT Sometext, pk FROM TestTable WHERE pk > 0 INTO CURSOR crsResult
? CursorType()
?
* Add index on DELETED() to make query fully optimizable
SELECT TestTable
INDEX ON DELETED() TAG deleted	
	
? "This query is fully optimizable, filtered cursor is created"
SELECT Sometext, pk FROM TestTable WHERE pk > 0 INTO CURSOR crsResult
? CursorType()
? "Filter: " + FILTER()
?
* Turn off query optimization display 
= SYS(3054,0)

? "Real cursor is created because of ORDER BY clause"
SELECT Sometext, pk FROM TestTable WHERE pk > 0 INTO CURSOR crsResult ;
	ORDER BY 1
? CursorType()
?

? "Real cursor is created because of NOFILTER clause"
SELECT Sometext, pk FROM TestTable WHERE pk > 0 INTO CURSOR crsResult ;
	NOFILTER
? CursorType()
?

lcPk = 1
? "Real cursor is created. The variable in the WHERE clause is used during the query execution only"
SELECT Sometext, pk FROM TestTable WHERE pk = lcPk INTO CURSOR crsResult ;
	NOFILTER
? CursorType()
? "Releasing variable doesn't cause error later"
RELEASE lcPk
GO TOP
?

lcPk = 1
? "Filtered cursor is created with variable in the filter expression"
SELECT Sometext, pk FROM TestTable WHERE pk = lcPk INTO CURSOR crsResult
? CursorType()
? "Filter: " + FILTER()
? "Releasing variable causes error on the next command"
RELEASE lcPk
GO TOP

RETURN
	
FUNCTION CursorType
* There's no function in VFP which will tell a difference between a cursor and a table
* However a table file has extension 'DBF' vs cursor's 'TMP'
RETURN IIF(JUSTEXT(DBF()) = "DBF", "Filtered Cursor", "Real Cursor")

Comments

Submitted by Anonymous on June 12, 2010 - 11:49 UTC
Thank you so much! I'm lucky find this topic during i fight with this problem all day.