Updating a Table with SQLEXEC

[ggrewe]

I am having a problem updating a table in MS SQL SP3 WITH VFP7 AND VFP8. I have run the following code, but the final SQLEXEC statement always fails. I am sure I missed something in setting the properties, but I cannot find what it is. Here is the code, without the error trapping...

OPEN DATABASE EMail
SET DATABASE TO Email
STORE SQLCONNECT("EMail") TO rn_SQLConnect &&& Connection string in the DBC

=SQLEXEC(rn_SQLConnect,"SELECT * FROM GroupList", "qryGroupList")

SELECT("qryGroupList")
INDEX ON UserID TAG UserID
INDEX ON GroupLookupId TAG GroupID

ll_RetVal=CURSORSETPROP("Tables",'GroupList','qrygrouplist')
ll_RetVal=CURSORSETPROP("UpdateNameList","valueId",'qrygrouplist')
ll_RetVal=CURSORSETPROP("UpdatableFieldList","valueId",'qrygrouplist')
ll_RetVal=CURSORSETPROP("KeyFieldList","userid,valueId",'qrygrouplist')
ll_RetVal=CURSORSETPROP("SendUpdates",.t., 'qrygrouplist')
ll_RetVal=CURSORSETPROP("Buffering",5,'qrygrouplist')

ln_RetVal=SQLPREPARE(rn_SQLConnect, "update QRYGROUPLIST set valueid ='Post' where userid = 1 and grouplookupid = 8")
ln_RetVal=SQLEXEC(rn_SQLConnect)
RETURN ln_RetVal

 

[SqueakinSweep]

QryGrouplist is a disconnected table or cursor. It appears you are trying to fire updates back to this table, making assumptions that this in turn will update the SQL Database.

I may be wrong, but I thought this could only be done with Remote Views.

You need to fire your SQL Updates back to the SQL Table, ie Grouplist, and not QryGroupList

ln_RetVal=SQLPREPARE(rn_SQLConnect, "update GROUPLIST set valueid ='Post' where userid = 1 and grouplookupid = 8")

Sweep
...if it works dont mess with it

 

[ggrewe]

That was it, thanks. I should not be working when it is so late...

 

[Mike Lewis]

SqueakinSweep,

I may be wrong, but I thought this could only be done with Remote Views

Sorry, but you are wrong. ggrewe's approach is correct.

ggrewe,

The same problem was discussed recently in thread1252-812206

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

 

[ggrewe]

Mike,

Thanks for the repsonse. I found this thread reference after I posted mine and did realize I was trying to update a native cursor, not a view. When I changed the code last night, it did work. But that does bring me to another question. THe reason I stopped using an updatable view that I created in the DBC was because it took longer to retrieve and update the data than doing a SQL pass through. Is this normally the case?

 

[Mike Lewis]

Ggrewe,

THe reason I stopped using an updatable view that I created in the DBC was because it took longer to retrieve and update the data than doing a SQL pass through. Is this normally the case?

Not really. SQL pass-though might be slightly faster, but not significantly so. After all, a remote view is little more than a wrapper for SPT. The underlying mechanics are the same.

If you are seeing a signficant difference in performance, my guess would be it's because of the number of records being updated.

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

 

[SqueakinSweep]

Mike...I learn something new every day!!

I tried to experiment with your advice, and wrote a little code to try it out. All the CURSORSETPROPS work, and the only command to fail is the final SQLEXEC

Any ideas as to why this may be. Im working with VFP6.0 and SQL Server 2000.

CLOSE ALL

LOCAL nHandle

OPEN DATABASE SQL
STORE SQLCONNECT("Connect1") TO nHandle

?nHandle
=SQLEXEC(nHandle,"SELECT * FROM BSC_ACK", "T1")

?CURSORSETPROP("Tables",'BSC_ACK',"T1")
?CURSORSETPROP("UpdatableFieldList","ack_no","T1")
?CURSORSETPROP("UpdateNameList","ack_no","T1")
?CURSORSETPROP("KeyFieldList","bsc_id","T1")
?CURSORSETPROP("SendUpdates",.T.,"T1")
?CURSORSETPROP("Buffering",5,"T1")
?CursorSetProp("WhereType",1,"T1")

?SQLPREPARE(nHandle,"UPDATE T1 SET ack_no='AA' WHERE bsc_id = 5")
?SQLEXEC(nHandle)

Sweep
...if it works dont mess with it

 

[Mike Lewis]

Sweep,

You are trying to do the same thing twice.

You are correctly retrieving data into a cursor and making the cursor updateable. Once you have done that, there is no point in doing the SQLPREPARE() or sending an UPDATE command.

Instead, you should just update the cursor, which you can do with a REPLACE:

SELECT T1
LOCATE FOR bsc_id = 5
REPLACE ack_no WITH 'AA'

Then, just issue a TABLUPDATE() to commit the changes. That will send the update to the server.

Alternatively, skip all the CURSORSETPROP()s, and send the UPDATE instead. If you do that, you don't need to use SQLPREPARE(). Just use SQLEXEC() to send the UPDATE command.

Hope this is clear.

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

 

[SqueakinSweep]

Crystal clear Mike...many thanks.

Sweep
...if it works dont mess with it

 

[SqueakinSweep]

Mike

Ive amended the code as per your advice, but the TABLEUPDATE always returns False.

Is there anything I've missed here. Ive tried all kinds of combinations for the TABLEUPDATE, and for the CURSORSETPROP(WhereType)

Im really keen to resolve this. I'd like to build a class around this to control all the SQL Updates

LOCAL nHandle
SET MULTILOCKS ON

OPEN DATABASE SQL
STORE SQLCONNECT("Connect1") TO nHandle
=SQLEXEC(nHandle,"SELECT bsc_id,ack_no FROM BSC_ACK", "T1")

*--SQL Table has BSC_ID as Primary Key, ACK_NO is a CHAR Field
?CURSORSETPROP("Tables", "BSC_ACK" , "T1")
?CURSORSETPROP("UpdatableFieldList" , "ack_no" , "T1")
?CURSORSETPROP("UpdateNameList" , "ack_no bsc_ack.ack_no" , "T1")
?CURSORSETPROP("KeyFieldList" , "bsc_id" , "T1")
?CURSORSETPROP("SendUpdates", .T. , "T1")
?CURSORSETPROP("Buffering" , 5 , "T1")
?CursorSetProp("WhereType", 2 , "T1")

select t1
locate for bsc_id=5
replace ack_no with "TEST" 

?TABLEUPDATE() &&always returns False

Sweep
...if it works dont mess with it

 

[Mike Lewis]

Sweep,

There are all kinds of reasons for TABLUPDATE() to return .F. Your best bet is to call ARROR() immediately afterwards, and check the contents of the error array which that function creates.

Actually, this highlights one of the advantage of sending the update directly, via an UPDATE statement, as you were doing previously, rather than using an updateable cursor. If you send the update directly, and if SQLEXEC() returns a negative number (indicating an error), you still have to call ARRROR() to find the cause of the error, but in that case, it is SQL Server which is generating the error message, and this is likely to be more useful that a general VFP error.

Hope this isn't too confusing.

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