SQL Select into cursor problem?

[Nro]

Hi all. I don’t know if it’s a feature, but it’s the first time I notice that.
I have a table called “tblMain” with 100 records in it. Here is the structure;

Tb_Code  Tb_Desc
1		Admin
2		Users 
3		Other
4		Guest

If I do;

Select * from tblMain where Tb_Code = -999 into cursor crsTbl

VFP create an empty cursor : _TALLY = 0, but EOF() = .F.
If I try

?Tb_Desc

It returns “Admin”

If I select the tblMain table, browse and select let’s say, the third record, “?Tb_Desc” will return “Other”. I select back “crsTbl, “?Tb_Desc”, returns “Admin”

It’s not really a problem for me because I always verify _TALLY >0 after a cursor creation, but is it normal behaviour for cursors?

Thanks

Nro

 

[Mike Lewis]

Nro,

No, this is not normal behaviour, and not what I would expect. There must be some other factor at work here.

What happens if you browse csrTbl? Do you see any data in it?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Olaf Doschke]

could you pleasy verify what you see, if you add NOFILTER to the SQL Select * from tblMain where Tb_Code = -999 into cursor crsTbl NOFILTER?

That cursor then really should be empty, have a RecCount() 0 (which is more important than _tally) and you'd be at EOF() there.

Bye, Olaf.

 

[cbasoz]

That is normal expected behavior. With simple queries where VFP is not forced to create a non filtered cursor, it simply filters on source (if source is not open first opens it) rather than creating a TRUE cursor. Original cursor is not eof() and its current record is Admin it means. You can create a true cursor either by adding NoFilter, ReadWrite clause or adding a non existant field, or a .t. in where clause.

Select * from tblMain where Tb_Code = -999 into cursor crsTb nofilter

* or another way
Select * from tblMain where Tb_Code = -999 and .T. into cursor crsTb

Cetin Basoz
MS Foxpro MVP, MCP

 

[Mike Lewis]

Cetin,

I'm not sure I agree with you. The fact that the cursor is in fact a filtered table is an important point, but it shouldn't affect the data that appears to be in the cursor.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Nro]

Hello. Thanks for your replies.

Mike, I see no data in the browse.

Olaf and Cetin, you’re right. If I add NOFILTER to the select statement, the select is working as expected: _TALLY = 0 and I have EOF() = .T. The RECCOUNT() = 0 and the cursor is not pointing on the first record of the original table.
I did notice something else. My “tblMain” was a part of a DBC. If I free the table ;

SELECT Tblmain
COPY TO pipo
USE pipo
SELECT * FROM pipo WHERE tb_code = -999 INTO CURSOR crspip

It’s working as expected (_TALLY = 0 ,EOF() = .T. , RECCOUNT() = 0)

Maybe I never notice this behaviour because some of my system does not use DBC.

Thanks for you lights.

Nro

 

[EzLogic]

so this:

select * from TblMain where tb_code = -999 and 1 = 1 ;
into cursor crspip

if its in the DBC.. that happened to me in Sage Pro ERP system.. when i added 1 = 1 it broke the link from the cursor to the dbc/table.

Ali Koumaiha
TeknoPCS Inc.
Dearborn heights, MI 48127

 

[Mike Lewis]

Nro, Olaf and Cetin,

I know I'm in a minority of one here, but this still doesn't sound right to me.

What you are saying is that when you have a "behind-the-scenes" filtered table, you don't see the data in the cursor. That can't be right.

Just to be clear about this. When I say a "behind-the-scenes" filter, I mean a cursor produced by a SQL SELECT which is really a filtered version of the underlying table, opened with a different alias in a different work area. In other words, the type of "cursor" you get in certain circumstances if you omit NOFILTER.

I simply don't believe that such a cursor doesn't show the data that meets the filter.

It's true that it will behave in unexpected ways. For example, RECCOUNT() won't reflect the number of records in the cursor. But surely you don't mean to tell me that it will not contain the data that was produced by the original SELECT? That can't possibly be right.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[alvechurchdata]

I agree with Mike. If crsTbl is the current work area and I ask to see the value of a field then I should see the value of that field from the current work area.

I've just tried it and I get what I expected to see from ?Tb_Desc - nothing. This works the same whether the table is free or part of a database. Has anyone else managed to replicate Nro's original behaviour?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Olaf Doschke]

Mike, you're right, a filtered cursor does not show less data in a browse. But please take a close look at Nro's problem: He expects an empty result but can see 'Admin' when selecting the result cursor alias/workarea and doing ?tb_desc.

This suggests VFP is showing MORE than it should show, not LESS.

But since Nro only does ?fieldname and not ?alias.fieldname I assume somehow VFP does show the current record of the underlying table instead of the result cursor. The result cursor, even though being just a FILTER and even though RECCOUNT() of it being the RECCOUNT() of the table can be considered empty, still VFP shows some value from the table. The browse of crsTbl will show empty nevertheless.

What's in effect here may even be a flaw of FILTER, as you know a filter really only comes into effect if you LOCATE after a SET FILTER and analog to this the result alias could behave unexpected, giving a peek to the underlying table even though the filter would not be fullfilled at the 'Admin' record, where Tb_code is 1 and not -999 like in the Where clause.

There is something wrong with that and I can't reproduce THAT part of the bahaviour, still the whole problem smelled like a FILTER problem and it looks like NOFILTER, READONLY or Cetins trick to add a ".T. AND" to the where clause all help to make that problem vanish.

Bye, Olaf.

 

[Mike Lewis]

Olaf,

Your analysis sounds plausible -- and especially your conclusion that the behaviour seen by Nro might be caused some quirk in the FILTER. Like you, I thought it was because of the need to move the record pointer in order for the filter take effect. But like you, I couldn't reproduce Nro's behaviour.

My point is that it's a mistake to blindly add NOFILTER to a SELECT without understanding the reasons. I accept that it appears to have solved the problem in this case, but I would want to know why it was happening before relying on it in the future.

Geoff,

Thank you for your confirmation. It's a relief to know that it wasn't a minority of one after all.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[cbasoz]

It doesn't sound right to me either but it is how VFP has been doing it ever. It is well known that unless you create a true cursor VFP simply uses a filter on source. And when you accept that it is a filter on source then all start to sound right.

A filter doesn't work as expected until there is an operation that would move the pointer to the first record, right? Here that is what happens. Nothing to move the pointer in source (remember filtered on source) cause eof() = .F. and you see the current record of source if you ask for it. Using NOFILTER is not something done blindly but something that should be done always to eliminate such weirdnesses. I find it more effective to use NOFILTER rather then to test for it each time.

Here is a reproducing code:

Clear All
Clear
Select * From (_samples+'data\customer') ;
   Where cust_id = '-1' Into Cursor crsTest
? Eof(), Eof('crsTest'), cust_id, crsTest.cust_id

* Move pointer
Locate
? Eof(), Eof('crsTest'), cust_id, crsTest.cust_id

You can use a lot of commands to move the pointer like locate, browse ... All would then show the correct Eof() result. IMHO adding NOFILTER clause is the simple reliable solution (if I was sure of the version wouldn't even suggest "where .T. and" - in old versions NOFILTER didn't exist).

Cetin Basoz
MS Foxpro MVP, MCP

 

[cbasoz]

BTW 'set filter to ...' is on my list of "Never to use commands".

Cetin Basoz
MS Foxpro MVP, MCP

 

[Nro]

As I said: it’s the first time I notice this behaviour for a cursor and I’m using cursors for a long time. In all my systems, I always look for _TALLY = 0 to see if anything is selected. I never saw a cursor as a filter (and I never use a filter, anyway). I see it as a temporary table created on the fly.

It’s great you take the time to explain what’s behind a cursor (even if I’m working with Fox since Foxbase, I still have something to learn).

To reproduce the behaviour, maybe try SET EXACT OFF .This setting is off by default in all of my programs.

BTW, I try it in Fox 2.6, and it’s the same as in VFP, so it’s the same behaviour with free tables.

Thanks again.

Nro

 

[Olaf Doschke]

Cetin,

thanks for trying a repro, but it doesn't display that quirk to me, it may depend on some setting we have different.

The first ? Eof(), Eof('crsTest'), cust_id, crsTest.cust_id
should show .F., .F. and the cust_id of the first record in customer.dbf, but it shows .T., .T. and nothing for me, even before the LOCATE.

Mike,

I'm mostly with you, but in this case... If you add NOFILTER to generate the cursor as a seperate file you can be very sure you won't have that quirk, no matter how and why that happens and while an explaination helps preventing a problem more throughly, a solution is a good start and the FILTER nature of some result cursors is at least a plausible explaination, even if we fail to reproduce this.

It would be nice for example to know which setting (like eg EXACT or ANSI) would cause Cetin to see a repro behavior while I still don't, but to me it's obvious enough, that it must be something about the FILTER + x and using NOFILTER makes x unimportant in this case, because I am very sure -I know - that a NOFILTER result cursor is it's own file and will never have any record in it from the table not fullfilling the where clause.

There would need to be somethig else totally wrong, if that would happen, eg rushmore finding a record in a corrupt index, that would not be long to the result by the field value, but is listed as a result in the index.

Bye, Olaf.

 

[Mike Lewis]

Olaf,

This is clearly a matter of personal preference and attitude, but my take on NOFILTER is to use when I know that I will need it, and not otherwise.

It only takes a moment's thought to make the decision. It is usually pretty obvious whether the SELECT will produce a new cursor or a filtered table. And provided I remember the very few limitations on using a filtered table (like not inputting it into another SELECT), I don't have any problem with that.

The advantage of omitting NOFILTER in cases where it would make a difference is performance. So I suppose it's a question of trading off that advantage against the need to think through the implications. If you don't mind the possible performance drop, NOFILTER does provide a valuable safety net.

But that's just my view, and I don't necessarily want to impose it on others.

Getting back to the question of why Nro and Cetin saw a different behaviour than we did, I wish I knew the answer to that. As you say, it's some sort of quirk somewhere.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[cbasoz]

set exact, set ansi both effect it. I have them at their defaults (OFF).

Nofilter doesn't suffer from performance and so I will continue to use Nofilter (well lately I don't use native VFP tables so it won't be a problem for me to use or not).

Cetin Basoz
MS Foxpro MVP, MCP

 

[Olaf Doschke]

Mike,

I see, I personally prefer the safety net. While Nofilter forces VFP to create a TMP file instead of only a filter, there are cases a scan through the result is faster with a nofilter cursor than through the filtered DBF.

Cetin, I had EXACT OFF and ANSI ON, wonder if ANSI OFF is the default. Anyway I tried all of the combinations of these two settings and would expect only ANSI having effect on the SQL anyway. No change. I don't get a peek to the customer.dbf, but in fact the SQL is not generating a filter cursor, even though there is an index on cust_id and OPTIMIZE also is ON. That's also quite weird I even get a TMP file for DBF('crsTest'), if I simply do SELECT * FROM customer Into Cursor crsTest.

Bye, Olaf.

 

[Mike Lewis]

I've just had a another shot at trying to reproduce this problem, but I still don't see the behaviour reported by Nro.

Like Olaf, I have tried all the combinations of EXACT and ANSI (although I wouldn't expect these to affect the outcome).

I also checked DBF(), which always seems to show a temporary file (not a filtered table), which is curious. Even with a completely straightforward fully-optimisable SELECT, I can't seem to get a filtered table. I don't understand that. Surely there can't be an implicit NOFILTER at work? Very strange.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[cbasoz]

I would suspect that you are using free tables where this phenomenon doesn't occur. I can invariably reproduce it using database based tables. Also I use SP1 and it might have been corrected in SP2.

As per dbf("crsTest"), it always shows a file but the file is not always created. Allocating a handle for it might occur or not based on version. For example under VF5-6 you were always getting an allocated handle to that file while in VFP9 a handle is not allocated if not needed and the cursor can be fully in memory despite dbf("crsTest") returns a filename. I have some demonstration code somewhere and I can find it if you want (or you can recreate - select into a cursor from a relatively small data then write something to the file returned by dbf(). You can write to it, still you can work with the cursor).

Cetin Basoz
MS Foxpro MVP, MCP