Updating SQL From VFP Cursor
Updating SQL From VFP Cursor
(OP)
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.
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.
RE: Updating SQL From VFP Cursor
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
RE: Updating SQL From VFP Cursor
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
RE: Updating SQL From VFP Cursor
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
RE: Updating SQL From VFP Cursor
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
RE: Updating SQL From VFP 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
RE: Updating SQL From VFP Cursor
CODE
Chriss
RE: Updating SQL From VFP Cursor
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
RE: Updating SQL From VFP Cursor
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
RE: Updating SQL From VFP Cursor
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
RE: Updating SQL From VFP Cursor
RE: Updating SQL From VFP Cursor
Chriss
RE: Updating SQL From VFP Cursor
Below a sample code form Hacker's Guide to VFP 7. Maybe it gives some hints
CODE -->
hth
MarK
RE: Updating SQL From VFP Cursor
In the updatenamelist you should specify all fields of the stock cursor, also fields not in the updatablefieldlist.
Chriss
RE: Updating SQL From VFP Cursor
Chriss
RE: Updating SQL From VFP Cursor
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
RE: Updating SQL From VFP Cursor
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
RE: Updating SQL From VFP Cursor
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
RE: Updating SQL From VFP Cursor
You could generate the UpdateNamelist via FOR loop on all fields given by AFIELDS() on the cursor.
Chriss
RE: Updating SQL From VFP Cursor
CODE
Chriss
RE: Updating SQL From VFP Cursor
Can I jump back in here. I have followed this thread, but didn't intervene again because I saw Chris has been giving you detailed advice.
That said, if you are still unsure how to set the various properties, I suggest you set up a remote view into the relevant data - not as part of your production code, but to use as a way of generating code that you could then use as a model.
You would use the view designer to set up the remote view interactively (it works a bit like a wizard). In particular, you would use the Update Criteria page to make the various settings; these correspond to the properties that you set with CURSORSETPROP(). You can then run the view to check that it is retrieving the data and correctly updating it.
You can then go to View SQL (on the Query menu in the view designer) and you will see all the properties with their appropriate settings. You can then either use these as a model for your code, or simply paste then from the View SQL window into your own code (in which case will need to change DBSETPROP() to CURSORSETPROP()).
This is just a suggestion. It could be that you are already close to a solution with Chris's help, in which case feel free to ignore this.
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
Visual FoxPro articles, tips and downloads
RE: Updating SQL From VFP Cursor
I agree with Mike.
We checked different ways to switch from VFP DBF to MS-SQL. We choose Remoteviews and still use them even on big systems with MUCH data.
They are easy to build and maintain.
You can use and index them like a dbf, so there are not much changes in code for start.
We open them on startup with a DSNless connection string (easy to adapt for different customers) with NODATA clause and index them , then requery them when filters are set.
For each table we have one RV to present data to user for selection (with filters or QBE) and one with a filter on primary key for update / insert / delete.
(Ok, I am lazy , on smallbase data tables I just have one view). We also use SPT and use the cursor for report.
Best regards
tom
We include the dbc holding the view definitions in the exe and copy them on startup to users tempfolder, so no problem with 1709 in big networks. And dbc always matches program without deploying problems.
RE: Updating SQL From VFP Cursor
Well, that's partly true, but you don't get all the CURSORSETPROPS you need. You get the following DBSETPROPs:
CODE
A view then continues with a lot more DBSETPROP per view field. And that's determining which VFP field data types to apply for the different fields. You have no such thing in SQL Passthrough. And on the other side the view SQL definition also won't give you the UpdateNamelist and UpdatableFieldList list.
What gives you more comparable information about what to do in CURSORSETPROP is actually given, if you design a cursoradapter class. which has a visual Builder. At first you just CREATE CLASS ?, then in the upcoming dialog pick Cursoradapter as baseclass, give your class a name and the vcx it should be stored to (can also be generated at the same time). Then once you have the class designer and mainly a little grey square open, right click on it and pick Builder from the context menu.
Now you'll be guided in several pageframes what information to provide starting with the choice Data Soruce type, which must be ODBC for MSSQL. Then for the connection, then pick the table or tables and create the SQL by picking fields, etc. and finally you'll find things in the cursoradapter class in properties, look into the property window when you have the designer (still) open, and you'll see.
Chriss
RE: Updating SQL From VFP Cursor
I changed the SELECT statement to specify just the fields I needed to change and the key identifying field. I put these into the UpdateableFieldList and UpdateNameList properties and it's now working like a charm. When I make changes to the cursor in a BROWSE window they're automatically being passed through to the SQL server to update the the parent table.
Mike, also thanks. I'm going to have a look at the view designer as this might make things easier in future.
Thanks everyone for all the help and suggestions. Very much appreciated.
Gary
RE: Updating SQL From VFP Cursor
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
Visual FoxPro articles, tips and downloads
RE: Updating SQL From VFP Cursor
Gary
RE: Updating SQL From VFP Cursor
Did not want to jump in with all the discussions about RV and SETPROP, which is way over my head, until Gary mentioned 'This is so old that it still uses @SAY and @GET'.
I'm close to a go-live date in a year-long project of converting a DOS-era xBase accounting package (SBT, all @SAY and @GET) to SQL.
Love to share my experience.
Steve Yu