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]

Gary,

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

 

[tomk3]

Hello,

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.

 

[Chris Miller]

Hi,

will need to change DBSETPROP() to CURSORSETPROP()

Well, that's partly true, but you don't get all the CURSORSETPROPS you need. You get the following DBSETPROPs:

DBSetProp(ThisView,"View","SendUpdates",.F.)
DBSetProp(ThisView,"View","BatchUpdateCount",1)
DBSetProp(ThisView,"View","CompareMemo",.T.)
DBSetProp(ThisView,"View","FetchAsNeeded",.F.)
DBSetProp(ThisView,"View","FetchMemo",.T.)
DBSetProp(ThisView,"View","FetchSize",100)
DBSetProp(ThisView,"View","MaxRecords",-1)
DBSetProp(ThisView,"View","Prepared",.F.)
DBSetProp(ThisView,"View","ShareConnection",.T.)
DBSetProp(ThisView,"View","AllowSimultaneousFetch",.F.)
DBSetProp(ThisView,"View","UpdateType",1)
DBSetProp(ThisView,"View","UseMemoSize",255)
DBSetProp(ThisView,"View","Tables","stock")
DBSetProp(ThisView,"View","WhereType",1)

And most of them, if not all (I didn't check) can also be set for cursors. Here it's a DB property as a view is a DB object and not a workare, it's a workarea when using it, of course. But that's the reason it's stored there to be applied to the view workarea at runtime.

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

 

[Gary Sutherland]

Chris, spot on! Thanks.

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

 

[Mike Lewis]

Delighted to see it is now working, Gary. Good luck with the rest of your project.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Gary Sutherland]

Thanks Mike. I've begun implementing the changes to the existing program. This is so old that it still uses @SAY and @GET. I did suggest s re-write in VFP but the budget isn't there.

Gary

 

[Steve Yu]

Gary and guys,

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