Select INTO Cursor opens table with alias

[DanWalter]

Hi:
I have worked around this problem, but I'm curious why it happens. This is in VFP 7.

When this code is run, there are 46 work areas used in the form's datasession.

SELECT var1, var2 FROM mytable INTO CURSOR myCursor

The result is area 47 is now the table mytable with the alias myCursor.

Running the statement from the command window generates the expected results, a temp table with the alias myCursor.

Workaround:

SELECT var1, var2 FROM mytable INTO CURSOR myCursor NOFILTER

The nofilter clause was added into vfp 5. vfp 3 had a bug (just one??!!??) that the SELECT INTO CURSOR statement would not generate a temp table unless a constant, logical field was added, e.g. "SELECT var1, .F. FROM myTable INTO CUROSOR myCursor" worked but SELECT var1 FROM myTable INTO CUROSOR myCursor" would throw an error.

Anyhow - does anyone have experience with this situation? Why does vfp not want to create a temp table unless forced to in this situation?

TIA,
Dan


Dan Walter
Daniel.Walter@uvm.edu
try { succeed(); } catch(E) { tryAgain(); } finally { rtfm(); }

 

[Mike Lewis]

Dan,

I'm not clear exactly what the problem is. You say that the statement works as expected. So what exactly do you need to know?

Are you asking for an explanation of the NOFILTER clause? If so, I'll give it a try.

By the way, this was not a bug in 3.0. It worked that way by design.

Basically, when you do a SELECT on a single table, with no calculated fields, which is Rushmore-optimisable, VFP doesn't really create a cursor. It simply opens the table in a new work area, and applies a filter to it. This is much fast than actually running the SELECT.

However, there are a few cases where a filered table doesn't behave exactly like a cursor. Inputting into another SELECT is one of them. For those cases, in 3.0 you had to trick VFP into creating a real cursor rather than applying the filter, and one way to do that was to add a calculated field. Just adding a constant to the field list would achieve that.

The NOFILTER clause was added in 5.0 to avoid the need for that sort of workaround. When you add NOFILTER, you get a genuine cursor, but at the cost of performance.

Does that answer your question?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[DanWalter]

Mike:

The problem is that selecting into a cursor lets me be lazy - I close the form and the cursor is closed and the underlying table is deleted. When VFP "helps" me (a MS trend that makes me nuts in other apps, especially Word) - the filtered view of the table is not closed, and the table is in use. I want to then do some exclusive operations on the table - which I can't when the table is in use.

Quoting your reply, my bold added:
"Basically, when you do a SELECT on a single table, with no calculated fields, which is Rushmore-optimisable, VFP doesn't really create a cursor. It simply opens the table in a new work area, and applies a filter to it. This is much fast than actually running the SELECT."

If the command has more than one behavior, it ought to be documented as such - this "feature" isn't mentioned in either the SQL-Select or the Rushmore topics (if I even could guess to look there!)

Okay, flame off for now. I did find another reference in this forum that noted the behavior - your addition of the Rushmore explanation is helpful.

Thanks!
Dan

Dan Walter
Daniel.Walter@uvm.edu
try { succeed(); } catch(E) { tryAgain(); } finally { rtfm(); }

 

[Mike Lewis]

Dan,

OK, I understand your point a bit better now. This is just one of those things that you have to know about. I agree it was badly documented -- but no-one can claim that the VFP docs are completely comprehensive.


Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[TamarGranor]

Dan - just want to point out that adding NOFILTER does give you a cursor with all the benefits of a cursor. It just ensures that VFP actually creates something new instead of filtering the original table.

Tamar

 

[DanWalter]

Tamar:
That (NOFILTER) was the workaround I found by piecing together research from this forum and the MSDN site. READWRITE would also do the trick. I hadn't run across this problem before (in 14+ years with Fox!) because all my other SELECTs have been either too complex for the Rushmore optimization to kick in, or have been READWRITE. In all that time, this was my first "simple" SELECT statement!

Thanks!

Dan

Dan Walter
Daniel.Walter@uvm.edu
try { succeed(); } catch(E) { tryAgain(); } finally { rtfm(); }

 

[Mike Lewis]

Dan,

At risk of labouring the point ... I wouldn't call NOFILTER a workaround. It's more like a feature that overrides certain default behaviour.

Also, are you saying that adding READWRITE would create a genuine cursor, in cases where you would otherwise get a filtered table? (I'm not disagreeing with you on that -- in fact, it sounds reasonable; it's just that I've not come across it before.)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[DanWalter]

Mike,
Okay, I'll call NOFILTER a feature that overrides an undocumented default behavior, at least in my own head.

And yes, READWRITE gives the same result, a true cursor (I just ran a quick test to check it). My take on it is that these two keywords (NOFILTER and READWRITE) directly address issues related to temporary tables, while the SELECT command affects both permanent and temporary tables and is designed to optimize for speed first. Any sort of join or transformation of data results in a true cursor if the output INTO CURSOR is designated. Even a simple STRTRAN() of a field can have the desired effect.

SELECT platform, type FROM browser;
 INTO CURSOR myCursor1 
&& Result = filtered browser table aliased as myCursor1
SELECT STRTRAN(platform,"WIN","MAC"), type ;
 FROM browser INTO CURSOR myCursor2 
&& Result = true cursor aliased myCursor2

Thanks for the help!
Dan


Dan Walter
Daniel.Walter@uvm.edu
try { succeed(); } catch(E) { tryAgain(); } finally { rtfm(); }

 

[Mike Lewis]

Dan,

I'll call NOFILTER a feature that overrides an undocumented default behavior

That makes me feel a lot more comfortable <g>.


READWRITE gives the same result

That's something I've learned. Thanks.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)