Updateable cursor from SQL Passthrough 1

[wlhbill]

I am trying to create an updateable local cursor to SQL Server using this:

ol=SQLCONNECT('RDB') && RDB is an ODBC datasource

SQLEXEC(ol,"select cat,type,descr,data from types",'t1')

CURSORSETPROP("Tables",'types','T1')

CURSORSETPROP("UpdatableFieldList",'cat,type,descr,data','T1')

CURSORSETPROP("KeyFieldList",'cat, type','T1')

CURSORSETPROP("SendUpdates",.t.,'T1')

SELECT T1

BROWSE

When I change a field in the cursor T1 I get the message
'No update tables are specified. Use the Tables property of the cursor.'

I know I can create a remote updateable view but it has to be stored in a local db and for other reasons I do not want to have any local fox db, even temporary.

I am using VFP7 and SQL Server 2000 Service pack 3.

 

[Mike Gagnon]

wlhbill

Unfortunately in order to send back data to an SQL server, you need a view, you cannot do it with a cursor. Just create and View and a temporary database, and delete the database after.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[wlhbill]

Mike,

Thanks for the quick reply. I was afraid that's what the answer was. Unfortunatly I have other reasons for not wanting to create even a temporary database in this application so it looks like I will have to do it the hard way with logic and explicit updates.

Thanks again for the timely reply.

Bill

 

[Mike Lewis]

Mike,

Unfortunately in order to send back data to an SQL server, you need a view, you cannot do it with a cursor

Sorry, but I disagree. Bill's approach is perfectly valid. There must be something wrong with his property settings. It's difficult to see what it is without knowing all the table and field names, but the basic approach looks OK to me.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[mcoupal]

This is what I'm doing. The only problem is when the query contains more than one record that is field-for-field exactly the same, whatever changes made are made to both records. That is, you delete one both are deleted ..

SQLExec(gnHandle, cSQL, lcCursor)
IF !USED(lcCursor)
MESSAGEBOX('Error! Select statement could not be executed!')
RETU
ENDIF

* - Build a list of fields
lcFields=''
FOR lnCnt = 1 to fcoun()
lcFields = lcFields + IIF(lnCnt=1, '', ",") + FIELD(lnCnt)
ENDFOR

CursorSetProp("KeyFieldList",lcFields,lcCursor)
CursorSetProp("WhereType",4,lcCursor)
CursorSetProp("Tables",lcTable,lcCursor)

FOR lnCnt = 1 TO FCOUN() && Make all field updateable for MIS's convenience
lcFox = 'CursorSetProp("UpdateNameList","' + FIEL(lnCnt) + ' ' + lcTable + '.' + FIEL(lnCnt) + '", "' + lcCursor + '")'
&lcFox
ENDFOR

* - Build X number of strings (macros) to create to set the UpdatableFieldList property of the cursor
lnLoop = 1
lcText = ''
lcText2 = ''
FOR lnCnt = 1 TO FCOUNT()
lcText = lcText + FIELD(lnCnt) + IIF(lnCnt = FCOUNT(), '', ',')

IF MOD(lnCnt,20) = 0 OR lnCnt = FCOUNT()
STORE lcText TO ('lcList' + ALLTRIM(STR(lnLoop)))
lcText2 = lcText2 + 'lcList' + ALLTRIM(STR(lnLoop)) + IIF(lnCnt = FCOUNT(),'','+')
lcText = ''
lnLoop = lnLoop + 1
ENDIF
ENDFOR

* - Set the cursor properties
lcText2 = 'CursorSetProp("UpdatableFieldList","' + &lcText2 + '", "' + lcCursor + '")'
&lcText2

CursorSetProp("SendUpdates",.T.,lcCursor)
CursorSetProp("Buffering",5,lcCursor)

 

[Mike Lewis]

mcoupal,

whatever changes made are made to both records. That is, you delete one both are deleted

Yes, that's exactly what I would expect. The reason is that VFP generates an UPDATE or DELETE statement behind the scenes, and that statement has a WHERE clause that tells the server which records to delete. Of course, the server will happily delete all records that meet the condition in the WHERE clause.

It is for that reason that your tables should have a primary key.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[wgcs]

The fundemental problem you have is that the source of the cursor: SQLEXEC(ol,"select cat,type,descr,data from types",'t1')

..is gone the moment the cursor is returned... VFP has no idea where the cursor came from, or what connection to send updates on, or anything... it is essentially just a temporary DBF file.

The relationship between the cursor and the RDBMS source is stored in the assocated 'View' data in the Database... so you need a database to store that associated view data.

The help on SETCURSORPROP('SendUpdates') says:
.T. – Specifies that a SQL update query is sent to update tables when an update is made using the view.
.F. – Specifies that a SQL update query is not sent to update tables.

 

[Mike Lewis]

Wgcs,

The fundemental problem you have is that the source of the cursor: SQLEXEC(ol,"select cat,type,descr,data from types",'t1')

Sorry, but I still disagree. As I said to Mike Gagnon (above), you can make a cursor returned from SQLEXEC() updatable by setting properties, exactly as you can with a view. (It's not necessarily the best way to send updates to the server, but that doesn't change the fact that it's possible.)


wlhbill,

If you are still here, I would make two suggestions:

- One property that you are not settng is UpdateNameList (which is not the same as UpdateFieldList). I suggest you check that out.

- If that still doesn't work, I suggest you check the reply from each of the CURSORSETPROP() calls. If one returns .F., that is the one that didn't work.

Mike



Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[wlhbill]

Mike,
You get the gold star. Adding this made the cursor updateable without a local database:

CURSORSETPROP("UpdateNameList","cat types.cat, type types.type, descr types.descr, data types.data",'t1')

KUDOS and thanks,
Bill

 

[Mike Lewis]

Bill,

Delighted it worked. I'm only sorry I didn't spot it earlier.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[wgcs]

Ok, Mike: I stand corrected, I never expected that to be possible (maybe I should start using a similar method in my own DB's... I have deliberate update functions that essentially build the SQL update commands!)

 

[Mike Lewis]

Wgcs,

I have deliberate update functions that essentially build the SQL update commands

That's a good approach too. I've used both methods.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com