Hi All, Can you help me, when I 2

[ghiehotamares]

Hi All,

Can you help me, when I run my program from visual foxpro and updating the data records from browse windows unable to send update to sql table(backend). my requirements should be automatic update the records to sql table/backend.
below is my program. can you assist me what I missed the commands/scripts.

hConn=("Driver=SQL Server;DSN=GHIESOURCE;Server=DESKTOP-KAH179Q;UID=;PWD=;DATABASE=MASTER")
nHandle=SQLSTRINGCONNECT(hConn)
?nHandle
SQLEXEC(nHandle,"SELECT * FROM DBO.MSreplication_options","tblMScursor")
set multilocks on
BROWSE && from this Im doing update/editing data records that should be sent to sql table backend.
=CURSORSETPROP("Buffering", 5, "tblMScursor")
=CURSORSETPROP("tables","dbo.MSreplication_options","tblMScursor")
=CURSORSETPROP("KeyFieldList","Id","tblMScursor") && Id is the name of the primary key
=CURSORSETPROP('UpdateFieldList', 'optname, major_version, minor_version, revision, install_failures, Id', 'tblMScursor')
=CURSORSETPROP("UpdateNameList", "optname optname","value value","major_version major_version","minor_version","revision revision","install_failures install_failures","Id Id", "tblMScursor")
=CURSORSETPROP("sendupdates",.t., "tblMScursor")
SELECT tblMScursor
TABLEUPDATE(2,.T.,"tblMScursor")
BROWSE
CLOSE ALL

 

[Mike Lewis]

You seem to be setting the correct properties, but where are you actually doing the updates? Are you doing them interactively in the first Browse? If so, that's too early. You need to set the properties before you do the updates.

Or are you doing them interactively in the second Browse? If so, you need to do the TABLEUPDATE() after you do the updates.

You might also want to consider a simpler way of updating the back-end table. Instead of making the cursor updatable, you could construct an UPDATE statement, then send it to the back-end via SQLEXEC().

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[keisha1]

yes mike im doing update/append on the first browse and I want the records send upate to sql table/backend but didn't affect on the sql table.

 

[Mike Lewis]

Then you should do the updates after your calls to CURSORSETPROP() and before your call to TABLEUPDATE().

It's also useful to test the value returned from TABLEUPDATE(). If it reurns .F., call AERROR() to determine the error.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[ghiehotamares]

is this possible to use simple 1st browse command to edit/insert records then to follow setcursor.

 

[Mike Lewis]

No. You must set the cursor properties before you do any updates. In most cases, you would set the cursor properties once only, once you have rerieved the cursor from the back end. You then do the updates, as many as you need, either interactively or programmatically. And finally you call TABLEUPDATE() to send the updates to the back end.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[ghiehotamares]

can you give me sample cursor properties that needs to put on the script before doing updates and send new updated data records into the backend/sql table.
can you correct the sequence of the ff. script below.

=CURSORSETPROP("Buffering", 5, "tblMScursor")
=CURSORSETPROP("tables","dbo.MSreplication_options","tblMScursor")
=CURSORSETPROP("KeyFieldList","Id","tblMScursor") && Id is the name of the primary key
BROWSE && doing update/editing data records

=CURSORSETPROP('UpdateFieldList', 'optname, major_version, minor_version, revision, install_failures, Id', 'tblMScursor')
=CURSORSETPROP("UpdateNameList", "optname optname","value value","major_version major_version","minor_version","revision revision","install_failures install_failures","Id Id", "tblMScursor")
=CURSORSETPROP("sendupdates",.t., "tblMScursor")
SELECT tblMScursor
TABLEUPDATE(.T.,"tblMScursor")

thanks your help response is highly appreciated. thanks

 

[Mike Lewis]

At first glance, it looks like you've got all the correct settings, but in the wrong order. This is what you need:

=CURSORSETPROP("Buffering", 5, "tblMScursor")
=CURSORSETPROP("tables","dbo.MSreplication_options","tblMScursor")
=CURSORSETPROP("KeyFieldList","Id","tblMScursor") && Id is the name of the primary key
=CURSORSETPROP('UpdateFieldList', 'optname, major_version, minor_version,;
   revision, install_failures, Id', 'tblMScursor')
=CURSORSETPROP("UpdateNameList", "optname optname","value value","major_version major_version",;
  "minor_version","revision revision","install_failures install_failures","Id Id", "tblMScursor")
=CURSORSETPROP("sendupdates",.t., "tblMScursor")

SELECT tblMScursor
BROWSE && doing update/editing data records
TABLEUPDATE(.T.,"tblMScursor")

Also, you only need to do the CURSORSETPROPs once - any time after you have obtained tblMScursor). You don't need to do them every time you do the Browse.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[keisha1]

=CURSORSETPROP("Buffering", 5, "tblMScursor")
=CURSORSETPROP("tables","dbo.MSreplication_options","tblMScursor")
=CURSORSETPROP("KeyFieldList","Id","tblMScursor") && Id is the name of the primary key
=CURSORSETPROP('UpdateFieldList', 'optname, major_version, minor_version,;
revision, install_failures, Id', 'tblMScursor')
=CURSORSETPROP("UpdateNameList", "optname optname","value value","major_version major_version",;
"minor_version","revision revision","install_failures install_failures","Id Id", "tblMScursor")
=CURSORSETPROP("sendupdates",.t., "tblMScursor")

SELECT tblMScursor
BROWSE && doing update/editing data records
TABLEUPDATE(.T.)


I tried executed but unable to send the update records to sql backend.

 

[Chris Miller]

=CURSORSETPROP("UpdateNameList", "optname optname","value value","major_version major_version",;
"minor_version","revision revision","install_failures install_failures","Id Id", "tblMScursor")

This should get you an error. Are you ignoring errors?

The updatenamelist must be one string, not one string per field.

Chriss

 

[keisha1]

Im not encountering errors. the problem is unable to send update records to sql backend.

 

[Chris Miller]

Look, CURSORSETPROP has 3 parameters, some optional, you pass in 9 arguments and that will give you the error "Too many arguments".

If you don't get that error, then you either suppress all errors, ignore them, don't realize this message is telling you about an error, or you execute something else than you think you execute.

You have to make this one string with the updatenamelist, then it could work.

Chriss

 

[Chris Miller]

Hi keisha1, hi ghiehotomares,

it seems hard for you to understand English, I also don't know if you're actually both work on the same team, but your code is using the same table and field names, so that's likely.

Let's start form scratch about what you need:
1. It all start with an MSSQL Server that must be present and accessible to you. It could also be other databases, but your use of Driver=SQL Server points to MS SQL Server.

It depends on the version of the server, which drivers are available and here is an overview chart for the currently actively maintained family of "ODBC Driver X for SQL Server":

https://docs.microsoft.com/en-us/sq...lation-and-driver-files?view=sql-server-ver16

The next oldest family of ODBC drivers for MS SQL Server are "Native Client" drivers and people used drivers of this family with VFP.

Your driver "SQL Server" is the oldest one you could use.

There have been gotchas for certain field types in VFP, for example Varchar(MAX) not being fetched into a VFP Memo field type but into C(0) fields, which theoretically are impossible, you can't actually create a C(0) field with CREATE TABLE or CREATE CURSOR in VFP, but several ODBC drivers managed to do that or managed it to let VFP think it needs to declare such fields. It's nonsense it's a 0 byte field which could only ever contain an empty string or be NULL.

So, overall there are some considerations about which driver to use, once you get a connection and can make your first SQLEXECs to bring over results, that's already fine, but still not a prove it's the right driver for you. The problems drivers have is with correct mapping of data types only, so far. I haven't seen drivers fail to execute INSERT, UPDATE or DELETE, so there's no threat of TABLEUPDATE() to not work generally.

Mike Lewis surely was and is on the right track that you had order of things to do wrong, but as he said, besides pointing out what order things must be done with, he said:

You [highlight #FCE94F]seem[/highlight] to be setting the correct properties

and

[highlight #FCE94F]At first glance[/highlight], it looks like you've got all the correct settings, but in the wrong order.

I now see that the wrong Updatenamelist already was posted in the original question by ghiehotamares:

=CURSORSETPROP("UpdateNameList", "optname optname","value value","major_version major_version","minor_version","revision revision","install_failures install_failures","Id Id", "tblMScursor")

This already used all the separated single field strings, from "optname optname" up to "Id Id", he already specified the updatenmelist as a list of single field name pairs. That's wrong. It's only one cursor property, it's a list, but it's one property, sou specify a list of naim pairs within one string, not 7 strings and not 9 overall arguments.

It may be your only remaining error, but to let you see the full picture and finally perhaps get this going you have to:

1. Have SET MULTILOCKS ON. ghiehotamares has done this as the 5th step. That's just okay, just barely okay as the last chance to do this before putting a cursor into a buffered state. I'll recommend to do this one at the start, as it's a setting you need for any work with buffers, you don't need to redo it, once multilocks are on they are on for anything you do after that.

2. Connect to MSSQL (or be connected already with a valid SQL connection handle, ASQLhandles() would create an array with at least one element that contains that handle and you might have it stored into a variable or property. You can use SQLStringConnect or SQLConnect for this, depending on what you have to connect, a connection string or a DSN name.

3. Quarey data into a cursor. That needs an SQLEXEC of a SELECT SQL query. You can only make a cursor updatable towards a remote backend, when it came from the remote backend.

4. Set up that query result cursor to buffer changes. That's not done just with SET MULTILOCKS ON, that's done bu making all your CURSOSETPROPS, and the props you need to do and their best order from the most important thing to set to the last details are:
4.1 SENDUPDATES, start with "telling" VFP you want this cursor to be able to send buffered changes back to the SQL Server, details follow, but this is the major setting determining whether to enable sending updates or not, this should come first. As a human you might think this is the least important one, as setting up all things that follow, from buffering the name lists, already implicitly tell VFP you want to send updates. Well, VFP would not imply sendupdates to be .T., you explicitly set it. And no criticism here, you have that CURSORSETPROP, you just did it last and did give it the least importance. It actually feels like an annoyance to need to set this, but set it first.
4.2 BUFFERING, you do use buffermode 5, that's optimistic table buffering and okay, but notice unlike the SET MULTILOCKS ON this is something you need to repeatedly do for every SQLEXEC result cursor. It's the next most general decision to make, all further details become a mute point, if you don't first specify how to buffer.
4.3 TABLES. Yes, again now the topmost and most general property that you can tell VFP is the best thing to tell it next, which table to update. And it's not by error a plural, in theory you can give VFP "directions" how to update multiple tables that you queried with joins into one VFP cursor, the usual case is the one table.
Also notice, how it actually scips one bigger scope than which table, one step up in hierarchy is which database, that's actually also a cursor property, it's already set as that data came from an SQLEXEC and while VFP does not automatically read in all available fields, their meaning as primary or foreign key or just any other normal field of the tables you query, it remembers which database of the connection this cursor came from.
4.4 KEYFIELDLIST. The next most important feature of a table is its keyfield (or in case of multiple tables their keyfields, therefore KEYYFIELDLIST, usually just one, Id, for example.
4.5 UPDATEFIELDLIST. This is special about SQL PAssthrough, one you have multilocks and buffering set for a cursor, any field of it can change and all the changes get buffered, but SQL PAssthrough will not update any of this, unless it's a field of the UpdateFieldList. Some fields could be readonly of have other constraints not allowing to set or update them, so that's necessary. Also remember, VFP doesn't automatically get this info from the remote database. It will just fetch too much information if you just want to get the SQLEXEC result without using it as a basis for TABLEUPDATEs.
4.5 UPDATENAMELIST. That's where you clearly have an error in your CURSORSETPROPS, it's just one property, although it's a list. Well, the UPDATEFILEDLIST also already was a list and you had that right.
5. you can now act on the cursor. For example BROWSE.
Notice, this is important. While buffering already is activated by MULTILOCKS and BUFFERING, VFP needs to know everything for the later TABLEUPDATE in advance, before changes are buffered.
6. After you did changes, TABLEUPDATE will forward these changes to MS SQL Server, it does so by automatically creating a list of INSERTs, UPDATEs, and DELETEs, for which all the information about key and updatable fields etc is needed.

Take a look into the parameterization of TABLEUPDATE, there's more to it than using the first parameter as .T. for all rows (that have changed).

And take into account what Mike LEwis already said, you can and should determine whether TABLEUPDATE returned .T. for reporting success or .F. for stating there was one or several errors and then look at errors via AERROR.

You can still easily have errors in your property values, we can't see your database and whether your specificated lists are okay.

I think Mike already gave good advice about the order in which to do things and you didn't listen to him or didn't understand. Some things can be done in slightly other orders, but only start editing data once you have specified anything VFP needs to know about it as all those properties of the cursor. Also don'T forget you can't CREATE CURSOR and apply properties to make changes in it go to a database. So the SQLEXEC of a SELECT SQL query in ghiehotamares also is a non-optional step, you must get your data you want to make into an updatable cursor in a cursor that was populated from that remote database in the first place.

If you want to insert new data into a remote database only, you can and must still first query something, you can chose to not query any data itself by using WHERE 0=1, for example, so the result cursor is empty and your changes are only INSERTS, still also this empty cursor has to come from the remote backend.

You can of course also create an empty cursor and finally do INSERTS based on the cursor data, but you can't use TABLEUPDATE in that case, just direct usage of SQLEXEC of INSERTs. That should perhaps also be said to make it absolutely clear you can't just make any cursor a source of TABLEUPDATEs towards a remote backend.

Chriss

 

[Chris Miller]

There are some more properties you can set, the major additional cursorproperty related to TABLEUPDATE is the WHREETYPE setting. You could make this step 4.3 b) or 4.6, and it defaults to something that's okay, it's not necessary to make a decision about it, but it can help to set it differently. The default setting will only allow updates to be done, if the modified fields, that will e updated by an UPDATE-SQL generated by TABLEUPDATE still have their old values in the database. So this is the fulfillment of conflict management on the side of VFP, it'll only update a record, if no one changed it between your fetching of it and your modifications. You can decide to let the updates only depend on finding a record via it's primary key, which by definition would never change also by other changes of data by other users.

And overall, take a look around, there are more CURSORSETPROP properties and the help of VFP describes all of them. The settings you make with CURSORSETPROP to nWorkare=0 will be made to all future cursors, not only in conjunction with SQLEXEC, but some offer a way to setup how the SQLEXEC acts before you actually have a cursor to do further CURSORSETPROPs.

Chriss

 

[ghiehotamares]

hConn=("Driver=SQL Server;DSN=GHIESOURCE;Server=DESKTOP-KAH179Q;UID=;PWD=;DATABASE=MASTER")
nHandle=SQLSTRINGCONNECT(hConn)
?SQLEXEC(nHandle,"SELECT * FROM DBO.replications_opt","tblMScursor")
set multilocks on
=CURSORSETPROP("Buffering", 5, "tblMScursor")
=CURSORSETPROP("tables","dbo.replications_opt","tblMScursor")
=CURSORSETPROP("KeyFieldList","Id","tblMScursor") && Id is the name of the primary key
=CURSORSETPROP("UpdatableFieldList","optname, major_version, minor_version, revision, install_failures,",'tblMScursor')
=CURSORSETPROP("UpdateNameList","Id replications_opt.Id, optname replications_opt.optname, value replications_opt.value,major_version replications_opt.major_version, minor_version replications.minor_version, revision minor_version.revision, install_failures minor_version.install_failures", "tblMScursor")
=CURSORSETPROP("SendUpdates", .t., "tblMScursor")&&Specify that updates should be sent to the backend
SELECT tblMScursor
BROWSE
=TABLEUPDATE()

I did not encounter any errors but unable to send records to sql backend
what is the incorrect from UpdateNamelist ?

 

[Chris Miller]

...where <remote table name> matches the name from the Tables property

In the table property you specified [highlight #FCE94F]dbo.[/highlight]MSreplication_options, in your UpdateNameList you only specified replications_opt.

TABLEUPDATE has parameters, if you use it without any parameterization, read up what it does. Spoiler: It would only update the current row, if there's a change in it.


Chriss

 

[Chris Miller]

No guarantee, but here is your code amended, there were still a lot of errors in your updatenamelist:

revision minor_version.revision, install_failures minor_version.install_failures

Not at all good.


Some questions: You connect to database master, are you aware this way you will easily get an error even from the SQLEXEC selecting from DBO.replications_opt, because it's not a table in the master database?
Connect to your database, not to master. If you absolutely want to connect to master, addressing your tables needs yourdatabase.dbo.yourtablename as fully qualified name.

Also, as you have a DSN, that's fine, that qualifies for using SQLConnect instead of SQLStringConnect in a simpler way. All usage of driver and user/password etc. will then depend on the DSN only.

nHandle=SQLSTRINGCONNECT('GIESOURCCE')
If nHandle<0
   AERROR(laError)
   Suspend && an error happened
Endif

If SQLEXEC(nHandle,"SELECT * FROM DBO.replications_opt","curReplications")<0
   AERROR(laError)
   Suspend && an error happened
Endif

Set Multilocks On
=CURSORSETPROP("Buffering", 5, "curReplications")
=CURSORSETPROP("Tables","replications_opt","curReplications")
=CURSORSETPROP("KeyFieldList","Id","curReplications") && Id is the name of the primary key
=CURSORSETPROP("UpdatableFieldList","optname, major_version, minor_version, revision, install_failures,","curReplications")
=CURSORSETPROP("UpdateNameList","Id replications_opt.Id,optname replications_opt.optname,value replications_opt.value,major_version replications_opt.major_version,minor_version replications_opt.minor_version,revision replications_opt.revision,install_failures replications_opt.install_failures", "curReplications")
=CURSORSETPROP("SendUpdates", .t., "curReplications") && Specify that updates should be sent to the backend
SELECT curReplications
BROWSE && edit data
IF NOT TableUpdate(2,.t., "curReplications",'laSQLErrors')
   Aerror(laError)
   Suspend && an error or errors happened, see message in laError[1,2] and conflicts denoted in the laSQLErrors array
Endif

Chriss

 

[ghiehotamares]

many thanks Chris and mike your help/response most highly appreciated.

 

[ghiehotamares]

Hi Chriss/ Mike

Here is another question why I encountered error if I add/put install replications_opt.install on the updateNamelist, I encountered "command contains unrecognized phrase/keyword" for the remarks field, so I need to mark asterisk for install field to avoid the error but the problem is unable to update the records for install field, can you help for the updateNamelist


=CURSORSETPROP("UpdatableFieldList","optname, major_version, minor_version, revision, install, remarks","curReplications")

=CURSORSETPROP("UpdateNameList",;
"Id replications_opt.Id,;
optname replications_opt.optname,;
value replications_opt.value,;
major_version replications_opt.major_version,;
minor_version replications_opt.minor_version,;
revision replications_opt.revision,;
**install replications_opt.install,;
remarks replications_opt.remarks","curReplications")


Thanks

 

[Mike Lewis]

You can't comment out one a line inside a multi-line statement.

...
revision replications_opt.revision,[highlight #FCE94F];[/highlight]
**install replications_opt.install,;
remarks replications_opt.remarks","curReplications")

The semi-colon at the end of the first line above indicates that the statement will continue on the next line. But the next line is a comment, so that won't work.

Furthermore, the last of the above lines will then be treated as a stand-alone line, beginning with the word "remarks". But this is not a valid command.

The obvious solution is to figure out why replications_opt.install was causing an error. If you can't do that, remove the entire line rather than commenting it out. In other words, like this:

....
revision replications_opt.revision,;
remarks replications_opt.remarks","curReplications")

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads