Wiki Home

Editable Cursors


Namespace: VB
How to make read-only SQL-SELECT cursors writeable

(Note that the following advice only applies to SQL-SELECT cursors, not cursors created by other means, such as the CREATE CURSOR command or by the opening of Remote Views, nor does it apply to the broader definition of "cursor" that includes tables opened by the USE command.)

Starting with VFP 7, there is a new syntax, SELECT ... INTO CURSOR READWRITE, which will create a read/write SQL-SELECT cursor.

Prior to VFP 7, however:

The help says, "After SELECT is executed, the temporary cursor remains open and is active but is read-only."
Here's a way around this: USE cursor AGAIN, and VFP gives it to you read/write

Internally when you create a temporary cursor, VFP creates a table on disk. By using the cursor again, really you USE that temporary table AGAIN. Like this:
SELECT * FROM Customer INTO CURSOR RatFace
SELE 0
USE DBF("RatFace") AGAIN
*-- Now read and write to your heart's content in this work area

However, VFP doesn't ALWAYS create the temp file on disk: While it has named it (so DBF("RatFace") returns what appears to be a valid name), the file may not exist. There are two ways to solve this:
* Post-VFP6 SP3 ? (I think that SP3 added the NOFILTER clause):
SELECT * FROM Customer INTO CURSOR RatFace NOFILTER

* Pre-VFP6 SP3 ? (I think that SP3 added the NOFILTER clause):
SELECT *,0 as DummyField FROM Customer INTO CURSOR RatFace

SELECT 0
USE DBF("RatFace") AGAIN
*-- Now read and write to your heart's content in this work area

Note: Remote Views, which are cursors created with SELECT statements against a back end, are read/write out of the box, of course.
Here are more examples showing this behavour.
clear
close all

* #1 Make a table to work with:
create table foo (cFid1 c(200))
insert into foo values ("")
? "#1", alias(), dbf(), file(dbf())

* #2 ok, but make sure that 2 users
* (including 2 sessions of the same app on the same workstation)
* don't try and create the same foo1
* also, leaves a foo1.dbf laying around, unless you clean it up.
select * from foo into table foo2a
append blank
? "#2", alias(), dbf(), file(dbf())

* #3 bad: adds a record to foo.dbf
* because it is really just doing a USE FOO.DBF READONLY; SET FILTER TO .F.
select * from foo into cursor foo3a
use (dbf('foo3a')) again in 0 alias foo3b
select foo3b
append blank
? "#3", alias(), dbf(), file(dbf())

* #4 Best: the NOFILTER clause forces a .tmp file to be used
* it will be deleted when the last instance of it is closed
* Note - there may not actually be a file on disk. - see #5
select * from foo into cursor foo4a nofilter
use (dbf('foo4a')) again in 0 alias foo4b
select foo4b
append blank
? "#4", alias(), dbf(), file(dbf())

* #5 There may not actually be a file on disk untill VFP needs it.
select * from foo into cursor foo5a nofilter
use (dbf('foo5a')) again in 0 alias foo5b
select foo5b
? "#5.1", alias(), dbf(), file(dbf())
* Add records untill it needs to use the .tmp file
dime a(65000,1)
do while not file(dbf())
	appe from array a
enddo
? "#5.2", alias(), dbf(), file(dbf())

* #6 - the name of the file may change!!!
* same thing except for the StrToFile()
select * from foo into cursor foo6a nofilter
use (dbf('foo6a')) again in 0 alias foo6b
select foo6b
? "#6.1", alias(), dbf(), file(dbf())
strtofile( "junk", dbf() )
? "#6.2", alias(), dbf(), file(dbf())
* Add records untill dbf() changes
dime a(65000,1)
lcDbf = dbf()
do while lcDbf = dbf()
	?? justfname( dbf() )
	appe from array a
enddo
? "#6.3", alias(), dbf(), file(dbf())

return

There is also a program by LuisGoco, introduced in print in 1994 and still floating around the Web, called "EditCurs", which figures out which of the various USE ... AGAIN and SELECT ... NOFILTER workarounds is appropriate for the situation, in order to force a given cursor to be read-write.
Contributors: Carl Karsten, Cindy Winegarden, Mike Helland
Category Code Samples Category Data
( Topic last updated: 2007.04.04 01:19:47 PM )