Select with NOFILTER and then creating index on 2nd index facing readonly error

[mstrcmtr]

Select Fld_Name,Fld_Code,CAST(Fld_bal As N(12,2)) As Fld_bal;
From Tbl_Acnt ;
Where &TTLWhrC INTO Cursor Tc_Acnt NOFILTER

e.g. TTLWhrC = '1=1'

INDEX ON Fld_Name TAG Fld_Name

INDEX ON Fld_Code TAG Fld_Code

On 2nd Index giving following Error

File d:\tmp\00005fyh0058.tmp is Readonly

 

[Tore Bleken]

That's very strange. What happens if you change the order of the two Index lines? Also try to replace Nofilter with Readwrite. It shouldn't make any difference for what I know, but it could be worth a try.

 

[Olaf Doschke]

NOFILTER is okay for the purpose of getting a new .tmp file and not work on the Tbl_Acnt.DBF and index that. permanently.

I can't reproduce the error, I can do

SELECT * FROM browser INTO CURSOR tc_acnt NOFILTER
INDEX on top TAG top
INDEX on left TAG left

So in principle, this should work ok. It is not necessary to query READWRITE, as INDEX ON creates a new CDX file and doesn't need to have write access to the .tmp cursor file. To explain ".tmp cursor file": That means DBF('tc_acnt') will tell you this exact file name as in the error message. FILE(DBF()) is .F. as this file isn't created for performance reasons, it is just an in-memory virtual file, VFPs cursors would be slow if files really were written to HDD.

The only unusual thing is your temp folder location. By default, it should be in your profile. Maybe try to not set TEMPDIR in a config.fpw file and just keep it at Windows defaults. I assume you don't have write permission into d:\tmp, which does not matter for the cursor itself, as the file will actually not be created as said, unless you exceed the RAM memory VFP can use to keep cursors. For CDX the write permissions will matter, the first INDEX ON will create the file CDX(1) and any further index will extend the file, which will be the same file name with CDX extension, like for any DBF: FORCEEXT(DBF("tc_acnt"),"cdx"). But that would suggest you get the error already on the first INDEX ON, I don't know what happens here, but try to keep VFP's temp dir at windows defaults and if you'd like other locations of tempwork, then configure %TEMP% for yourself or perhaps company, but don't do so for the general public, don't fiddle with users environment settings, that also includes assuming anyone will have a drive D:

Bye, Olaf.

 

[Mike Lewis]

I can't reproduce the error either - both with and without the NOFILTER. That said, I would normally always add READWRITE if I want to create an index, but as Olaf points out, that isn't strictly necessary.

In fact, as far as I can see, you don't need the NOFILTER, as the presence of the CAST() function should prevent the cursor from being "filtered".

By the way, why do you need to create the indexes? If you want the indexes in order to present the data in a specific sequence (e.g. for a report), you might find it easier and faster to use the ORDER BY clause in the SQL. If you want the indexes to speed up searches of the cursor, consider building that search condition in the original SELECT instead.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Tore Bleken]

Regarding the need for indexes for cursors, I agree that they are very often redundant. Since most cursors will remain in memory all the time (even though they appear to reside on disk since DBF() shows them as files), the process of building these indexes often outweighs the increase you expect that they will give.

 

[Olaf Doschke]

It's not always about performance, the creation of CDXes alone pays for the ease of sorting a grid by cursor indexes without needing to go back to query the DBF(s) with ORDER BY. a Grid.header click just needs to be SET ORDER TO .... ASC/DESC.

It's true your main indexes should be the permanent indexes of the database DBFs, so initial queries can make use of them, but once you have your data acting on it locally with indexes is never wrong, even just for sake of simpler code. Data in cursors not necessarily is just a few rows you can also work on with LOCATEs or can simply requery from the indexed DBFs. That solution to query the underlying DBFs repeatedly only becomes more important if you want the cursor to reflect latest data or the filter conditions change frequently and you need to go back to the DBF anyway, not just sort the data you already have queried.

Bye, Olaf.

 

[Olaf Doschke]

Just looking back at the error, it can't only be about write permission in d:\tmp, index creation only reads from that (virtual) tmp file, it never writes to it. That would need field rules or triggers, and even those would need to act with a write triggered by a read operation, but read operations don't trigger them. Only insert/update/delete operations trigger triggers, also only changes trigger table and field rules to be checked. And these things of tables don't get copied to a cursor anyway.

If it was about d:\tmp permissions the error should state not being able to write to the cdx file, not to the tmp file. Writing to the tmp file means writing to the cursor dbf file (tmp=dbf, cdx=cdx and any other dbf ralated file will have the normal file extension, also fpt, idx). I can only assume you don't show the real code and simplified it to show us what you think errors. Write errors to the tmp file must be related to inserts, appends, replaces, or updates into the cursor, then READWRITE instead of NOFILTER is a solution.

Bye, Olaf.

 

[Tore Bleken]

I know that it's not all about performance. However, if it's for showing the data in a grid and letting the user select the sort order, the index can be created on the fly if it doesn't exist. I do it that way in my own "sortable grid" class. Initially he data is sorted on one of the fields by the Select statement, and the icon in that field's header shows accordingly. If the user clicks on any header, an index tag is created if necessary.

 

[Olaf Doschke]

I remember this strategy of creating indexes on the fly is implemented in VFP ffc classes. Just in a very complicated and convoluted way of first detecting whether the necessary index already exists. I made it simple for myself, too, and index on main sort order and often don't offer to sort by any column anyway.

The major problem should be writing to a nofilter (=readonly) cursor, not creating the indexes. But we'll see.

Bye, Olaf.

 

[mstrcmtr]

Thanks All for giving advise

Problem Solved by Adding NoFilter ReadWrite

Help also says for more then 1 index of cursor need ReadWrite clause

 

[Tore Bleken]

May I ask you where this is mentioned in Help? I can't find it.

 

[mstrcmtr]

SELECT - SQL Command - INTO or TO Clause

READWRITE specifies that the temporary cursor is modifiable. If the source table or tables use auto-incrementing, the cursor created with READWRITE does not inherit those settings. You can create more than one structural index on a cursor using the READWRITE argument.

 

[Tore Bleken]

Ah, I find it. Strange that this isn't mentioned under Nofilter. Personally I never use Nofilter, only Readwrite since the latter brings more benefits without losing any.

 

[Mike Lewis]

I agree about using READWRITE. Much of the time, you need the cursor to be write-able. And when you don't, READWRITE does no harm. In fact, the only reason that the clause exists is because in earlier versions of VFP all cursors output by SELECT were always read-only, and still are by default to maintain backward compatiblity.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Still, you are able to create more than one index on a cursor also only using NOFILTER. READWRITE only solves your problem of modifying the DBF (TMP) file itself, the error is about this file and not the cdx file.
So I don't know what is the help referring to when it says, you can create more than one structural index on a cursor only when using the READWRITE argument. This is nonsense.

How big nonsense this specification is, becomes clear, when you read the index file and tag terminology in the topic Visual FoxPro Index Files:

Structural compound index (.cdx) files - Opens and closes with the table automatically. Uses same base name as the table file name.

By this definition there always only is one such file possible, because you can't have two CDXx files with the same name, but you can have as many index tags as are fitting in the 2GB limit within that CDX file.

And the help topic tells about secondary CDX and IDX files, too, secondary CDX files are called Nonstructural compound index files, and the difference just is, you need to open/specify this CDX file explicitly when SET ORDER TO TAG OF another.cdx than the structural compound index cdx.

I bet you don't get the error with the secondary INDEX ON, you get the error later at another part of your code. It's simple to verify that, just add a MessageBox after the first and after the second INDEX ON. If you get the error after the first message and before the second, it surely comes from the second INDEX ON, I'd be surprised about that and would like to know what VFP version and service pack level you're using. It clearly does not happen in Visual FoxPro 09.00.0000.7423 for Windows (SP2 with the latest hotfix).

Bye, Olaf.

 

[TamarGranor]

No reason ever to use both NOFILTER and READWRITE. READWRITE does everything NOFILTER does and more.

Tamar

 

[Olaf Doschke]

That's also not true. If it's important nothing ever accidentally changes data of the cursor I use NOFILTER. Since the grid has AllowCellSelection it is less important to have a read-only cursor as grid data source, but it was handy in older times and grids are not the only control to bind readonly data.

I am also not on the boat to say you only need table buffering as TABEUPDATE() can also update single rows. It makes a difference row buffering automatically stores data in the moment you leave the buffered row. So don't say anything is useless and can be disregarded.

Aside of all that, the help still has nonsense about this aspect, there is no advantage in indexing a READWRITE vs NOFILTER cursor.

Bye, Olaf.