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]

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.

I've tried it with both free tables and DBC-based tables, and don't see any difference. I've only tried it in SP1 so far. Will try it under SP2 when I get a chance.

As per dbf("crsTest"), it always shows a file but the file is not always created.

That's interesting. I'll take a closer look at that -- although I'm still not sure that it explains the problem.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[Olaf Doschke]

Cetin,

I used your code, so it's the customer.dbf of the testdata.dbc.

Right now, I'm at a computer where your repro shows that behavior, though and there DBF('crsTest') is showing ...CUSTOMER.DBF

Both here and on other computers it didn't worked I have Vista and VFP9 SP2, so both OS and SP version are unlikely the reason. I'll store the environment settings of here and compare that at home and see what's different. Maybe I'll find out the secret of what prevents a filter cursor.

It's no news that if DBF() shows a TMP name, that file not necessarily is on disk and FILE(DBF()) could return .F., VFP will hold cursors in memory as long as it can before writing out to disk. But the TMP file extension is a sure sign VFP is having a 'real' NOFILTER cursor.

That's also a reasoin NOFILTER cursors are not necessarily an overhead in writing a file to disk, VFP caches cursors and therefore a filter cursor isn't that much of an advantage.

Bye, Olaf.

 

[Olaf Doschke]

I second everyone recommending to not store pipctures into a general field. Be it the size limitation and the bloating of the fpt file alone, be it the outdated OLE 1.0 working in general fields, be it the instability of showing pictures with Olecontrols, when the application associated with BMP changes, be it the hard way to get at the pictures again, once they are in the general fields.

On the other hand, the only thing missing besides the full file name with file extension maybe is, that you need to know: APPEND GENERAL does not create a new record but fills the current record only, so before you can do an APPEND GENERAL you need to be on a record.

Bye, Olaf.

 

[Mike Lewis]

Olaf,

While you're looking at the value returned by DBF(), I wonder if it would also be worth checking the %TEMP% directory, to see if any new file has arrived there.

I would like to experiment further with this myself, but won't have access to my development system for the next few days.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[Mike Lewis]

Olaf,

You accidentally posted your message about general fields here, instead of in thread184-1574325.

Why don't you copy and paste it into the other thread. Then you get get the one posted here deleted (along with this message, if you like).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[Olaf Doschke]

Hi Mike,

thanks for pointing out my posting mistake, I'll copy my post over to the other thread.

Regarding looking into %TEMP% (or Getenv('TEMP')), I can assure you with such small cursors as a single or no record cursor I've never seen the actual file, neither with FILE(), nor when looking into the temp dir with Windows Explorer. The file will appear there though and have that name, if you just insert enough records to that readwritable cursor, that it will not fit into memory.

You can have an easy repro on that with a cursor created by CREATE CURSOR instead of SQL:

CREATE CURSOR curTest (iid I autoinc, cText1 C(254), cText2 C(254))
Do While NOT File(DBF())
   Append Blank
   Append Blank
   Append Blank
   doevents
Enddo 

? DBF(), FILE(DBF())
ADIR(laFile,DBF())
? laFile(1),'size:',laFile(2)
? iid

This took about a minute at my 2GB laptop creating about 700000 records, which resulted in a filesize of about 300MB. Do that while on a coffee/tea break, also VFP will be very sluggish and unresponsive afterwards, even the simple USE (close) of that cursor will take long. After that and a CLEAR you'll be back to normal.

When not pushing cursors to these extremes them being in-memory despite of DBF() showing some file name is what's making them very fast. Also you can eg APPEND FROM DBF('crsAlias'), even though that file does not exist, that's kind of a virtual file system VFP manages, it's not OS based caching of the cursors DBF/TMP file.

Bye, Olaf.

 

[Nro]

I think I found a way to reproduce the behaviour and why not all of you have the same results.

If I try this code, from a table inside a DBC, with all the indexes (key fields and one for DELETED())

OPEN DATABASE p:\objitech\apps\lesfabpo\data\lesfabpo01.dbc SHARED
SYS(3054,11)
SELECT * FROM tblMain WHERE Tb_Code = -999 INTO CURSOR crsTb
DISPLAY STATUS
?RECCOUNT()
?_TALLY
?Tb_Desc

RECCOUNT() = 32, _TALLY = 0 and Tb_Desc = Admin. Rushmore optimization is full. If I “DISPLAY STATUS”, the cursor is only an alias of tblMain and is not a separate table.
Now, if I copy the table, run the same code, the cursor is effectively a temporary table ;

SELECT tblMain
COPY TO C:\TEMP\tblMain
CLEAR ALL
CLOSE ALL
USE c:\temp\tblMain IN 0 EXCLUSIVE
SYS(3054,11)
SELECT * FROM tblMain WHERE Tb_Code = -999 INTO CURSOR crsTb
DISPLAY STATUS
?RECCOUNT()
?_TALLY
?Tb_Desc

RECCOUNT() = 0, _TALLY = 0 and Tb_Desc =. Rushmore optimization is none. Now, if I create 2 indexes, and SET DELETED ON, FOX does not create a temp table.

USE c:\temp\tblMain IN 0 EXCLUSIVE
INDEX ON TB_Code TAG Tb_Code
INDEX ON DELETED() TAG DELETED
SYS(3054,11)
SELECT * FROM tblMain WHERE Tb_Code = -999 INTO CURSOR crsTb
?RECCOUNT()
?_TALLY
?Tb_Desc
DISPLAY STATUS

RECCOUNT() = 32, _TALLY = 0 and Tb_Desc = Admin. Rushmore optimization is full. If I “DISPLAY STATUS”, the cursor is only an alias of tblMain and is not a separate table.

So, when the optimization if full, VFP does not create a cursor, but instead, just filter the results.

Thanks

Nro

 

[Olaf Doschke]

Still no success in reproducing the filtered cursor behavior here. There were some differences in the settings: MULTILOCKS, SAFETY, REPROCESS, ANSI and PATH. But setting these the same as on the computer the repro works does not make the repro working here at home. And in fact both VFP versions are 5815, the offical SP2 without any of the last three fixes of SP2.

Nevertheless I've seen it on the computer of a customer and so the same could have happend to Nro. Before a LOCATE the filter of a filter cursor actually is not at work and accessing alias.field of the cursor alias actually shows the active record of the table.

Bye, Olaf.

 

[Nro]

Salut Olaf.

I’d just installed a fresh copy of VFP (thanks to Virtual PC), no SP, and I did not modify any settings. I have the same behaviour as I describe. If Rushmore is full, VFP put a filter to the table with the criteria, witch it make sense, because VFP can process a large amount of data, without creating/copying anything on the temp directory. It’s just surprise me, because I never, I mean, never use filters, but, from the beginning, I’m using cursors.

Thanks
Nro