Updating SQL From VFP Cursor 1

[Gary Sutherland]

I've been tasked with updating an existing FoxPro program so that it can be used with data stored in an SQL database.

Essentially what I'm doing is bringing the data through from SQL to a VFP cursor. Some of the records will have their field values changed and then the data written back to the SQL database table.

I've successfully brought the data through to a VFP cursor where I can view and modify it in a Browse window but I'm having problems when writing the data back to SQL.

I can update specific fields in the SQL table by specifying them with a SET clause to the UPDATE command but this seems a bit long-winded.

What I'd like to be able to do is write the whole record back to SQL but can't seem to find a way of doing this.

Am I asking too much of VFP's support for SQL Server? I just want to be sure I'm not barking up the wrong tree and wasting my time.

 

[Mike Lewis]

Am I asking too much of VFP's support for SQL Server?

Not at all. This sort of thing s meat and drink to VFP.

But the answer depends in part on how you are retrieving data from the server. I'll assume for now that you are using SQL Pass-Through (SPT), that is, with functions such as SQLCONNECT() and SQLEXEC(). The key is to set a number of properties of your cursor (the one containing the data that you are updating). You do this with the CURSORSETPROP() function.

The Help for that function will show you which properties you need to set. In summary, look at the following:

Tables
UpdatNamList
KeyFieldList
UpdatableFieldList
SetUpdates

(The last of these is the one that many people forget.)

In general, you would set these properties after retrieving the cursor from the server, but before you do any updates to it. Once you've done that, any changes you make to the cursor will be sent back to the server.

A similar approach will apply if you are using remote views or a cursoradapter rather than SPT. If so, let us know and I,or someone else, will talk you through the details.

Having said all this, I personally favour sending INSERT and UPDATE commands to the server, but that's my personal choice.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Gary Sutherland]

Hi Mike, thanks for getting back to me.

This is what I'm using to connect to the SQL server an bring the data back to view and modify it.

lcServerName="orac\sqlexpress"
lcDatabase="comp_z"
lcConnectString="driver={SQL SERVER};server="+lcServerName+";database="+lcDatabase+";Application Name=VFP;Trusted_Connection=yes;"
lnSqlHandle=SQLSTRINGCONNECT(lcConnectString)
IF lnSqlHandle>0 AND SQLEXEC(lnSQLHandle,"SELECT * FROM stock","stock")>0
SELECT stock
BROWSE NORMAL NOCAPTION
ENDIF

I'll read through those properties and give them a go.

Thanks.
Gary

 

[Mike Lewis]

That looks right, Gary. You just need to place the CURSORSETPROPs before the Browse

Also, keep in mind that you don't need to do the first four lines in the above code every time you perform this procedure. Provided lnSqlHandle stays in scope, you only need to do the SQLSTRINGCONNECT() once.

Come back if you need more detailed information.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

This sort of thing s meat and drink to VFP.

I second that and simply also point out that besides SQL Passthrough (That's the name of what you use with SQLConnect, SQLExec and many more SQL functions (all bunching up at SQL... in the help language reference) you have two more alternatives. So you have

1. SQL Passthrough
2. Remote Views.
3. CursorAdapters.

There are chapters in the VFP help that describe them in detail.

Chriss

 

[Gary Sutherland]

I've implemented the CURSORSETPROP commands but am getting an error message when I try to move off of the first line of the Browse: "No update tables are specified. Use the Tables property of the cursor."

IF lnSqlHandle>0 AND SQLEXEC(lnSQLHandle,"SELECT * FROM stock","stock")=1
=CURSORSETPROP("Tables","stock","stock")
=CURSORSETPROP("UpdatableFieldList","cn_cat,cn_cat2","stock")
=CURSORSETPROP("KeyFieldList","ID,ID","stock")
=CURSORSETPROP("UpdateNameList","cn_cat,cn_cat,cn_cat2,cn_cat2","stock")
=CURSORSETPROP("SendUpdates",.T.,"stock")
SELECT stock
BROWSE NORMAL NOCAPTION
ENDIF

Any ideas what it's unhappy about?

Gary

 

[Chris Miller]

Take a closer look at what the updatenamelist has to be. It has to have fieldsnames as in the VFP cursor paired with sqltablename.fieldname in the SQL backend. In your case this would be

CURSORSETPROP("UpdateNameList","cn_cat stock.cn_cat, cn_cat2 stock.cn_cat2","stock")

Chriss

 

[Chris Miller]

Have you debugged what's excuted?

In async mode SSQLEXEC can return 0 without an error, just still fetching the data.
Your tables definition seems okay, that's likely NOT the reason, though VFP claims you didn't specify it.
You could double check that with CURSORGETPROP, of course.

I remember VFP flags this, even if something else is wrong, and if it's not the updatenamelist, then how about twice specifying the ID as key field? Why? The keyfieldlist can be a single field, it would only be a list, if you'd join tables and still want to enable updating both related records. It should be "ID" only.

I see something else missing, you have no explicit buffering of stock. You may have once and for all set buffering for all future cursor by setting it for workarea 0, though.

I would also specify WhereType with CURSORSETPROP.

Chriss

 

[Gary Sutherland]

Thanks for the suggestion, Chris. I've applied those changes and they've worked in so much as I'm now getting a different error message when moving off a row after having changed a field value:

My program now looks like this:

IF lnSqlHandle>0 AND SQLEXEC(lnSQLHandle,"SELECT * FROM stock","stock")=1
=CURSORSETPROP("Tables","stock","stock")
=CURSORSETPROP("UpdatableFieldList","cn_cat,cn_cat2","stock")
=CURSORSETPROP("KeyFieldList","id","stock")
=CURSORSETPROP("UpdateNameList","cn_cat stock.cn_cat, cn_cat2 stock.cn_cat2","stock")
=CURSORSETPROP("SendUpdates",.T.,"stock")
SELECT stock
BROWSE NORMAL NOCAPTION
ENDIF

Have I understood the use of the KeyFieldList property correctly? There is an ID field in both the SQL table and the VFP cursor.

Gary

 

[Chris Miller]

In theory the names of VFP fields could differ from SQL Server, as SQL Server allows spaces in names, for example. That could apply to the ID field name as to any other, which would suggest you specify "ID stock.ID", just like in the updatenamelist property to enable differences in the VFP vs SQL Server name. But indeed you only specify "ID".

The error is about another table "cname", are you sure it's for the same stock example? I guess that's just for another table.
Or do you use a variable for the table name? Well, then just use cName, not "cName" to pass the variable value and not the variable name.

Chriss

 

[Gary Sutherland]

Sorry for the confusion, Chris; I'm was trying it with two different SQL databases but I get the same error with either. I've tried "id id", "id stock.id, and just "id". There's another field that could be used as a key field but trying that doesn't help, either. If I look at the table in the SQL Management Studio I can see that it has no keys defined although there is an index for the fields I've tried in KeyFieldList.

 

[Chris Miller]

Well, you have to have primary key fields...

Chriss

 

[mjcmkrsr]

Hi

Below a sample code form Hacker's Guide to VFP 7. Maybe it gives some hints

CREATE DATA Test
CREATE SQL VIEW MyEmps AS ;
    SELECT EmployeeId, First_Name, Last_Name ;
       FROM Employee WHERE Region = ?Region
USE MyEmps
? CURSORGETPROP("Buffering")    && Returns 3
* Set updating properties
* Employee_Id is primary key
? CURSORSETPROP("KeyFieldList", "Employee_Id") 
? CURSORSETPROP("UpdateNameList", ;
 "first_name Employee.first_name,last_name Employee.last_name")
? CURSORSETPROP("SendUpdates", .T.)
? CURSORSETPROP("Tables", "Employee")
? CURSORSETPROP("UpdatableFieldList", "First_Name,Last_Name")
* Now the view is updatable

hth

MarK

 

[Chris Miller]

I looked into what could cause this and found out one thing where VFP is sensitive:
In the updatenamelist you should specify all fields of the stock cursor, also fields not in the updatablefieldlist.

Chriss

 

[Chris Miller]

Regarding primary key. VFP believes what you tell it. It won't verify whether a field is technically in a primary key constraint in the server. If the id is not unique, the UPDATE-SQL VFP generates will just update all records with the same id value and that's usually not what you want. So you have to have unique values in some column.

Chriss

 

[Gary Sutherland]

Thanks Mark.

Chris, both the ID field (Int) and the cn_ref field (Char) contain key values and have indexes defined for them in the SQL table. Is there something else that needs setting?

Gary

 

[Chris Miller]

both the ID field (Int) and the cn_ref field (Char) contain key values

The SQL VFP creates can use Where ID=value, but not Where ID=value and cn_ref=value to reidentify the record for updates. And so, no, you have to have a single unique field. Indees or not is just a matter of performance and obviously a primary key constraint will not allow NULL or double values. That's why such keys are a must-have anyway.

Regarding other things, see my last posts.

Chriss

 

[Gary Sutherland]

Thanks Chris. So it can use either one or the other to uniquely identify the record in the SQL table. They both hold unique (but different) identifying data.

The database the program I'm working on updating is supplied by a third party so have to work with what I have to hand. I'll change the 'SELECT *' to a more conservative group of fields and try modifying the properties.

Thanks
Gary

 

[Chris Miller]

It's a very good idea to use an explicit field list instead of *, you only query dead weight, if you query what you don't want to update. It may be data you need to see as the user to know what you want to modify, though.

You could generate the UpdateNamelist via FOR loop on all fields given by AFIELDS() on the cursor.

Chriss

 

[Chris Miller]

Here's a self-contained example:

Clear
Set Multilocks On
CursorSetProp("Buffering",5,0) 

Local lcConnectionString, lnHandle
lcConnectionString = "Driver={ODBC Driver 17 for SQL Server};Server=localhost\SQLEXPRESS;Database=tempdb;Trusted_Connection=yes;"
lnHandle = SQLStringConnect(lcConnectionString)

If lnHandle<0
   AError(laError)
   Suspend
EndIf
SQLExec(lnHandle,"Create Table ##test (ID uniqueidentifier Default newid() NOT NULL, Name VarChar(50), Constraint pk_stock Primary Key (ID))")
SQLExec(lnHandle,"Insert Into ##test (Name) values ('Gary Sutherland'),('Mike Lewis'),('mjcmkrsr'),('Chris Miller')")

If SQLExec(lnHandle,"Select ID,Name From ##test","tmpTest")<0
   AError(laError)
   Suspend
EndIf 

CURSORSETPROP("SendUpdates",.T.,"tmpTest")
CURSORSETPROP("Tables","##test","tmpTest")
CURSORSETPROP("KeyFieldList","ID","tmpTest")
CURSORSETPROP("UpdatableFieldList","Name","tmpTest")
CURSORSETPROP("UpdateNameList","ID ##Test.ID,Name ##Test.Name","tmpTest")

Select tmpTest
Browse
Local Array laErrors[1]
If TableUpdate(2,.f.,'tmpTest','laErrors') and GetNextModified(0)=0
   ? "Tableupdate succeeded."
Else
   For each lnRecno in laErrors
      ? "record", lnRecno, "couldn't be saved."
   EndFor
EndIf

If SQLExec(lnHandle,"Select ID,Name From ##test","tmpTestAfterTableupdate")<0
   AError(laError)
   Suspend
EndIf 

* cautious when you replace ##test with a real table of a database, don't DROP it.
SQLExec(lnHandle,"Drop Table ##test")

SQLDisconnect(lnHandle)

Select tmpTestAfterTableupdate
Browse

Chriss