Error: Cannot update cursor 2

[Koen Piller]

Hi,

I havve create with

m.lcCursor = 'curRit'
Select Permission
liPermission = Permission.Id
lcWhere = 'WHERE Permission.ID =' + Transform(m.liPermission)
Set Textmerge On
TEXT TO m.lcSQL NOSHOW TEXTMERGE PRETEXT 1+2+4+8
SELECT ;
   Rit.ID as Rit_ID , ;
   Rit.*, ;
   Permission.ID, ;
   Docter.*, ;
   Locatie.Latlan, ;
   DocterBridgeLocation.ID ;
   FROM Rit ;
   JOIN DocterBridgeLocation ON Rit.iDocterBridgeLocation = DocterBridgeLocation.ID ;
   join Locatie on Locatie.ID = DocterBridgeLocation.iLocation ;
   join Docter on Docter.ID = DocterBridgeLocation.iDocter ;
   JOIN Permission ON Permission.id = Rit.iPermission ;
   INTO CURSOR <<LCCURSOR>> NOFILTER <<LCWHERE>>

ENDTEXT

m.lcSql = getSQLText(m.lcSql,'VFP', .T.)
&lcSql

a fairly simple cursor. Now I need to update that file and I encounter the errormessage 111 and indeed

A cursor created with the SELECT – SQL command is always read-only.

How to solve this?

Stay healthy,
Koen

 

[Mike Lewis]

A simple cursor, and a simple solution to the problem. Add [tt]READWRITE[/tt] to the end of your SELECT statement.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Koen Piller]

Mike,
I feel ashamed.
Txs
Stay healthy.
Koen

 

[mjcmkrsr]

Hi Mike

Add READWRITE to the end of your SELECT statement.

This is true for Koen's specific SQL statement but you can't just put it at the end of any SQL-statement. READWRITE is an optional parameter for the INTO CURSOR instruction (see also the Help File and the HG to VFP 7)

SELECT 
... 
INTO CURSOR csrTest READWRITE
...

Please apologize for bothering you

MarK

 

[Mike Lewis]

I feel ashamed.

You shouldn't. The error wording that you quoted is misleading:

A cursor created with the SELECT – SQL command is always read-only.

It is not true that the cursor is always read-only, but rather that it is read-only by default. There is a good reason for this. The ability to optionally make it read-write was added in a fairly recent version.

This is true for Koen's specific SQL statement but you can't just put it at the end of any SQL-statement.

That's right. I never said otherwise.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Better yet use READWRITE instead of NOFILTER.

NOFILTER only ensures the created cursor really is a new cursor and not the original DBF in the form of a so called FILTER cursor, which is a DBF used wit a SET FILTER and a nw alias name. Those then are readwrite, but doing changes on them means editing the DBF itself, so NOFILTER isn't bad to avoid that, but it creates a readonly cursor.

Chriss

 

[Koen Piller]

Chriss,
are you sure?

but doing changes on them means editing the DBF itself

If you create a cursor with

select myFile.* from myFile into cursor curMyfile readwrite

and than change the value of one of the fields of curMyfile, the content of the equivalent field in myFile is not changed.
For that you shall need to have an update().
The parameter NoFilter ensures a complete new cursor is built, which is convenient when you have changed in the "from File". Without the NoFilter parameter VFP could just compose/show the old cursor again and not a cursor with that changed value(s).
Stay healthy,
Koen

 

[Chris Miller]

No, just with neither NOFILTER nor READWRITE, you can get filter cursors.

I said NOFILTER is to ensure NO filter cursor. Same for READWRITE, on top of that it just also enables writing, but you don't write to the DBF, yes. So it's a good thing to use either of them unless you're sure about wanting a filter cursor and acting on it cautiously.

Here you have an example of how these clauses work out:

* remember deleted setting
lcDeleted = Set('Deleted')
Set Deleted Off
On Error ? Message()
Select uniqueid From Foxcode Into Cursor nofiltercursor NOFILTER
? Dbf('nofiltercursor')
Replace uniqueid With 'x' && causes error, as NOFILTER cursors are a) .tmp (real cursor, no filter cursor) and b) not readwrite
? "can't update"
Select uniqueid From Foxcode Into Cursor readwritecursor Readwrite
? Dbf('readwritecursor')
Replace uniqueid With 'x' && causes no error, but the foxcode.dbf isn't changed, changes go into the cursor.
? _Tally, ' rows updated'
? "can update"

Select uniqueid From Foxcode Into Cursor filtercursor
? Dbf('filtercursor')
? "cursor is the same as the dbf"
* Cautious now, you will change the foxcode.dbf when changing data in the filtercursor

* reset deleted setting
Set Deleted &lcDeleted

To conclude: There is no problem with using NOFILTER or READWRITE, you only can get into trouble without either of them. Using both clauses causes no error, but I'm not sure whether RADWRITE then is prioritized or the first or the last option, you only need to use NOFILTER or READWRITE, both together make no real sense. If you use one of them you always ensure the cursor you create is independent of the original DBF. You can skip both clauses if the query is about two or more tables, filter cursors only result from queries on a single DBF that are fully optimizable.

Chriss

 

[Mike Lewis]

Or, to put it another way ...

You will get a filtered version of the physical table (as opposed to a completely new cursor), if all of the following conditions are true:

1. There is only one table involved in the query.

2. There are no calculated fields or aggregate functions.

3. The query can be Rushmore-optimised.

4. There is no NOFILTER clause present.

5. There is no READWRITE clause present.

So if either NOFILTER or READWRITE is present (or both), you get a new cursor. That cursor might or might not be updatable, but, if it is, the updates only affect the cursor itself, not the underlying table. Once the cursor has been created, it no longer has a link with the underlying table.

If that's not what you want - in other words, if you want a new cursor which is updatable and where the updates go through to the underlying table - you can do that by creating an updatable local view.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads