Contact US

Question

Updating SQL From VFP Cursor

Gary Sutherland (Programmer)

(OP)

29 Aug 22 11:50

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 (Programmer) · Answer 1 · 2022-08-29 12:05:18

Quote:
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 (Programmer) · Answer 2 · 2022-08-29 12:42:49

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 (Programmer) · Answer 3 · 2022-08-29 13:50:12

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 (Programmer) · Answer 4 · 2022-08-29 15:07:14

Quote (Mike Lewis)
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 (Programmer) · Answer 5 · 2022-08-29 21:11:26

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 (Programmer) · Answer 6 · 2022-08-29 21:26:48

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

CODE

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

Chriss

Answer 7

[Post Deleted]

Chris Miller (Programmer) · Answer 8 · 2022-08-30 10:33:16

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 (Programmer) · Answer 9 · 2022-08-30 10:52:23

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 (Programmer) · Answer 10 · 2022-08-30 11:07:37

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 (Programmer) · Answer 11 · 2022-08-30 11:44:52

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 (Programmer) · Answer 12 · 2022-08-30 11:46:42

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

Chriss

mjcmkrsr (TechnicalUser) · Answer 13 · 2022-08-30 12:34:24

Hi

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

CODE -->

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 (Programmer) · Answer 14 · 2022-08-30 14:15:37

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 (Programmer) · Answer 15 · 2022-08-30 14:27:25

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 (Programmer) · Answer 16 · 2022-08-30 14:37:06

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 (Programmer) · Answer 17 · 2022-08-30 14:45:05

Quote (Gary Sutherland)
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 (Programmer) · Answer 18 · 2022-08-30 15:11:00

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 (Programmer) · Answer 19 · 2022-08-30 15:23:23

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 (Programmer) · Answer 20 · 2022-08-30 18:09:13

Here's a self-contained example:

CODE

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

Mike Lewis (Programmer) · Answer 21 · 2022-08-31 07:44:09

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 (Programmer) · Answer 22 · 2022-08-31 08:14:10

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 (Programmer) · Answer 23 · 2022-08-31 11:16:30

Hi,

Quote (Mike Lewis)
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:

CODE

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 (Programmer) · Answer 24 · 2022-08-31 12:16:20

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 (Programmer) · Answer 25 · 2022-08-31 13:40:14

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 (Programmer) · Answer 26 · 2022-09-01 10:46:15

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 (Programmer) · Answer 27 · 2022-09-04 23:48:30

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

Contact US

Come Join Us!

Posting Guidelines

Updating SQL From VFP Cursor

Updating SQL From VFP Cursor

Updating SQL From VFP Cursor

RE: Updating SQL From VFP Cursor

Quote:

RE: Updating SQL From VFP Cursor

RE: Updating SQL From VFP Cursor

RE: Updating SQL From VFP Cursor

Quote (Mike Lewis)

RE: Updating SQL From VFP Cursor

RE: Updating SQL From VFP Cursor

CODE

RE: Updating SQL From VFP Cursor

RE: Updating SQL From VFP Cursor

RE: Updating SQL From VFP Cursor

RE: Updating SQL From VFP Cursor

RE: Updating SQL From VFP Cursor

RE: Updating SQL From VFP Cursor

CODE -->

RE: Updating SQL From VFP Cursor

RE: Updating SQL From VFP Cursor

RE: Updating SQL From VFP Cursor

RE: Updating SQL From VFP Cursor

Quote (Gary Sutherland)

RE: Updating SQL From VFP Cursor

RE: Updating SQL From VFP Cursor

RE: Updating SQL From VFP Cursor

CODE

RE: Updating SQL From VFP Cursor

RE: Updating SQL From VFP Cursor

RE: Updating SQL From VFP Cursor

Quote (Mike Lewis)

CODE

RE: Updating SQL From VFP Cursor

RE: Updating SQL From VFP Cursor

RE: Updating SQL From VFP Cursor

RE: Updating SQL From VFP Cursor

Red Flag This Post

Red Flag Submitted

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Join Tek-Tips® Today!

Join Tek-Tips^® Today!