MySQL TABLEUPDATE from VFP Cursor 2

[David Higgs]

I am trying to update a MySQL Table from changes made in a VFP Cursor.

The following code updates the field "Call_160" in Cursor "curLarge_Squares" with "Test".

SQLEXEC(SQLCONN,'SELECT * FROM &TBL_WAB_Large_sq ','curLarge_Squares')
INDEX on LG_SQUARE TAG lg_square

CURSORSETPROP("AutoIncError", .F. , "curLarge_Squares")

SET MULTILOCKS ON

=CURSORSETPROP("Buffering", 5, "curLarge_Squares")

GOTO TOP 

Replace Call_160 with "TEST" 

BROWSE 

=TABLEUPDATE()

I would now like to update the MySQL Database to match the VFP Cursor but I am not too sure how to proceed.

Any help would be much appreciated.


Regards,

David.

Recreational Developer / End User of VFP.

 

[Chris Miller]

There you go. VFP does need to know more. Did you do an updatable view, as I suggested to start with?

You need more CURSORSETPROPs, for "SendUpdates", "Database", "Tables", "KeyfieldList", "UpdatableFieldList", "UpdateNameList", and "UpdateType". If I haven't forgotten one. Read the help on each one and you'll get there.

In short, it's all you would also need to know when writing an UPDATE-SQL: What is the key to reidentify the original record. Which fields should be updated? Where is the original table? (even though the setting is called "Tables", usually it's just one, just like there only is one key field and not a list of them, but you see how this could be used in case for multi column keys or updates of multiple tables)
And then what about prevention of conflicts? Look into the details about UpdateType. The simplest is by key field only, of course.


Chriss

 

[Mike Lewis]

The simple way of doing this particular update would be:

[tt]SQLEXEC(SQLCONN,"UPDATE MySQLTable SET Call_160 TO 'TEST'")[/tt]

where MySQLTable is the name of your table on the server.

[highlight #FCE94F]EDIT:[/highlight] Oops. Forget to specify which record you want to update on the server. The code above would update every record, which is definitely not what you want. You need something like this:

[tt]SQLEXEC(SQLCONN,"UPDATE MySQLTable SET Call_160 TO 'TEST' WHERE ID = 100 ")[/tt]

where ID is the name of a key field; in this example you want to update the record whose ID is 100.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

One more detail, without working out the full code (I'm on the verge of leaving) - These cursorssetprop settings need to be done before you REPLACE anything (or use any other method to change the Cursor data). Plus, for TABLEUPDATE() to work the cursor needs to be buffered, but you already set "Buffering". And no matter wether you use optimisitc/pessimistic row or table buffering, you should SET MJULTILOCKS ON, too.



Chriss

 

[Mike Lewis]

David, I'm not sure if this will help, but here is some code which I have pasted from one of my projects:

SQLEXEC(lnConn,'select * from guides')
SELECT sqlresult
CURSORSETPROP('Tables','Guides')
CURSORSETPROP('UpdateNameList', :
  'guide_Id guides.guide_id, name guides.name ;
  address guides.address')
CURSORSETPROP('KeyFieldList','Guide_id')
CURSORSETPROP('UpdatableFieldList','Name,Address')
CURSORSETPROP('SendUpdates',.T.)

This sets the required properties in the cursoe named sqlresult (which is the default name from SQLEXEC()). If you read this in conjunction with the Help file for CURSORSETPROP(), it should give you a good idea of what's needed. (Note that this an alternative to sending an UPDATE command, as per my previous post.)

You also need set the buffering, which you are already doing.

The above code makes the Name and the Address fields updatable. Once that's done, any edits you make to those fields in the cursor will automatically get sent to the server when you do your TABLEUPDATE().

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[David Higgs]

Mike, I started this thread as a follow on from my previous thread [link ]

https://www.tek-tips.com/viewthread.cfm?qid=1811174

[/url] where I mentioned that I am in the process of rebuilding my "Logbook" application from scratch and taking the opportunity to improve the efficiency / look of my code based on experience gained (albeit little) since I wrote the original application.

During the initialisation of my application I copy the entire contents of the MySQL "Logbook" Database into a VFP CURSOR which is then displayed in a Grid. When I add or modify an entry in the "Logbook" it is sent direct to the MySQL Database and at the same time I ZAP the Logbook CURSOR and reload the contents of the MySQL Database back into it, so that it remains in-sync.

Now that I am re-writing my application, I thought it would probably be more efficient to update the MySQL Database and then update CURSOR using the REPLACE Command instead of having to reload the entire MySQL Database back into the CURSOR.

In the previous thread, Chris Millar mentioned using the Cursor to update the MySQL Data which was the reason for starting this thread.

The code I posted above was just a quick test to try to update a single field of a single record identified by a unique Primary_Key. Once I got that to work I would have then inserted into my application and expanded it to cover all 123 fields.

I naievly thought that the code I produced was nearly sufficient to synchronise the Cursor with the MySQL Database; your sample code shows that I am very wide of the mark. I didn't realise that I would need multiple CURSORSETPROP which shows I've not grasped the principle of using CURSORSETPROP and TABLEUPDATE.

At this moment in time I feel I can't see the wood for the trees and don't know if I should progress with using using CURSORSETPROP and TABLEUPDATE or stick with my original code (Zapping the Cursor and re-loading the revised data) or stick with my revised code of just updating the record in the MySQL Database and Cursor, both of which work ok.

Chris, I have been dabbling with DBMS (GWBASIC, dBaseIII+, dBaseIV, FoxPro, VFP) for over 40 Years BUT only as an "Hobbyist" so when you say "it's not Rocket Science", it may not be to you, but at times like this it feels that way with me. I have been retired for a few years now having spent most of my working life as a Service Engineer in Induction Heating (Melting Furnaces etc) so I guess using SCR's and IGBT's will probably be a black art to you?

I use VFP to assist me with my Hobbies of "British Railways" and "Amateur Radio" and I'm am very proud of what I have achieved with VFP (albeit that I probably only use less than 20% of VFP Capabilities). I even control my Transceiver with my VFP Logbook application.

I owe deep gratitude in particular to the likes of Mike Lewis, Olaf Doschke and ATLOPES of this parish, plus of coarse, I appreciate the help of you and other members of this forum.

I followed your advice from the other thread and printed numerous pages from the VFP Help File, but I've always struggled to "learn by reading" and much prefer to "learn by examples" and that's what I did with the code I posted above.










Regards,

David.

Recreational Developer / End User of VFP.

 

[Chris Miller]

Well, my point is, that while Mikes code examples will push you a step further, you'll not know why he set the 'UpdateNameList' this way and what is important. I am also an autodidact most of the time, but not reluctant to also read a few pages of things not revealing themselves after a few tries which errored.

I also first used code of others, but failed to adapt it, and the reason is, there are a few thigs to consider and it's best to know the behind the scenes details to know what adjustments can be made and make the best of it. There's more to learn about modes of fetching the data, for example. I really won't do you a good favor in giving another example of using it. More pointers to read about to understand how VFP detects which data needs to be synced back to the remote backend is GETFLDSTATE(), you don't need to use it in your code, finally you only need TABLEUPDATE with a few parameters to tell whether one record or all changes, whether to get info about failed records and either stop at the first fail or continue to sync as much as works, and last not least whether to force the sync or not.

You see this has vast options, so one example of code does not do justice to what the method of buffering and locking can do for you, and I even just mentioned locking without going into the details of that.

You only get to the end when you struggle a bit through this, but it's worth it in the end.

Chriss

 

[Mike Lewis]

David,

Having read what you wrote about the background to this question, I am wondering if a better route for your would be to use remote views. This is not to send you down a completely new path. A remote view is essentially a wrapper for the cursor that you would otherwise get from SQLEXEC(). It has pros and cons, but an important point is that you can create and manage them interactively rather than writing some quite tricky code.

If you want to follow this up, I suggest you start by exploring the Connection Manager and the View Designer. To use these tools, you will need a VFP database (a DBC). If you already have a DBC to hold your local tables, that is, your DBFs, you can use that one. You launch each of the tools by drilling down into the database within the Project Manager. Click in turn on Connections and Remote Views, and then click New.

There are Help pages for these tools. In the Help, drill down to Reference / User Interface Reference / Designers.

When creating a remote view, you might start by ignoring the Update Criteria tab. You will still be able to create a view that retrieves the data from the server. This will be the equivalent of your curLarge_Squares cursor.

The important point here is that once you have the remote view in place, you can treat it almost exactly like an ordinary DBF or cursor. You can use it to populate a grid, or to provide a control source for controls in a form, or to drive a report, or almost anything else. All the table-related VFP commands and functions - with just a couple of minor exceptions - are available, including USE, BROWSE, SCAN / ENDSCAN / SEEK, etc.

When you are ready to go further, by setting the options in the Update Criteria tab of the view designer, you can arrange things so that any updates to the view are automatically sent back to the server.

I appreciate that this might be too much of a new approach for you, so I won't give any more information here. But if you do start down that road, come back with any details questions you might have.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[David Higgs]

If you read this in conjunction with the Help file for CURSORSETPROP(), it should give you a good idea of what's needed.

Mike, Thank you for your reply and suggestion of an alternative option if I chose not to pursue the CURSORSETPROP / Tableupdate() route.

When I first saw your example code I was fairly overwhelmed by it all, but having followed your advice above, I can see now what your code is all about and how I might adapt it to suit my application. This has encouraged me to continue down this route as I can see the benefits of doing so. e.g Cursor updating MySQL Database with Tableupdate().

Due to the number of fields in the MySQL Database I will have to make use of TEXT...ENDTEXT when I update the MySQL Database; I do this anyway with my current application so shouldn't be a problem.

I will spend some time now going through the help files again and continue with my Test Program just to prove the system before working on my application.





Regards,

David.

Recreational Developer / End User of VFP.

 

[Mike Lewis]

That sounds like a good plan, David. Once you get into it, I'm sure you won't find all that CURSORSETPROP() stuff so intimidating.

Keep in mind that you only need to set those properties when you first create the cursor, not every time you update it. If it's any help, I found another example here:

https://devzone.advantagedatabase.c...ge11.1/index.html?master_sql_pass_through.htm

This explains the properties in a little more detail than my earlier example. (Don't be put off by the fact that it is intended to run against Advantage Database Server rather than MySQL. The code is the same.)

Let us know how you get on, and if you have any more questions.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[David Higgs]

A little progress, although the MySQL Database is still not being updated. Also I need a "WHERE PRIMARY_KEY = 1" statement but not sure where to put it at the moment.

SET MULTILOCKS ON
	
SQLEXEC(SQLCONN,'SELECT * FROM WAB_Large_sq','curLarge_Squares')
INDEX on LG_SQUARE TAG lg_square

SELECT curLarge_Squares	

GOTO TOP 

Replace Call_160 with "TEST" && Update Cursor Field Call_160 with "TEST"

BROWSE && Check Cursor is being updated.

CURSORSETPROP("AutoIncError", .F. , "curLarge_Squares")

CURSORSETPROP("Buffering", 5, "curLarge_Squares")

CURSORSETPROP('Tables','WAB_Large_sq')					&& MySQL Table Name 

CURSORSETPROP('UpdateNameList','call_160 curLarge_Squares.call_160')	&&	Name List 

CURSORSETPROP('KeyFieldList','Call_160')				&& CSV 

CURSORSETPROP('UpdatableFieldList','Call_160') 			&& Field List 

CURSORSETPROP('SendUpdates',.T.) 						&& Send SQL update 

TABLEUPDATE()

Regards,

David.

Recreational Developer / End User of VFP.

 

[David Higgs]

Mike,

Thank you for the link. Just a had a quick look at it and I think it answers a few questions for me. I can see I've missed out one of the Table Names. I've done enough today, but will take a look at the additional code and apply some of it to my Test Code another day.

Regards,

David.

Recreational Developer / End User of VFP.

 

[Mike Lewis]

David,

So when you are ready to come back to it ...

Your problem is:

[tt]
CURSORSETPROP('KeyFieldList','Call_160')[/tt]

This is supposed to specify the name of the primary key on the server. Since Call_160 is the field you are updating, I assume that is not the primary key. The primary key must hold a value that uniquely identifies the record, typically something like a customer ID or a product code.

Once you've specified that, then MySQL will look at the value of the primary key in the record you are updating, then locate and update the record with that same primary key on the server.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Mike has it spot on. You mention what's missing is "PRIMARY_KEY = 1." Well, if that's the name of your primary key, then it belongs into the keyfield list.
The other thing to look at is the UpdateType. When you want it to reidentify the record by key field only, that's one of the update types you can choose. There are more complex ways to reidentify a record that inlclude testing whether the record has been modified by some other remote user.

Chriss

 

[Chris Miller]

Another thing I see now:

CURSORSETPROP("AutoIncError", .F. , "curLarge_Squares")

That's not your problem, if you read records into the VFP cursor and update them, you never generate keys in the VFP cursor. If you add a record, the primary_key field can remain 0, as the server sets that. The problem you face with this is how to get back the key created serverside.

I'd relay that problem to later. A simple solution is to not use Autoinc integers but GUIDs.


Chriss

 

[David Higgs]

I couldn't resist taking another look. Still not updating MySQL Database though.

SET MULTILOCKS ON
	
SQLEXEC(SQLCONN,'SELECT * FROM WAB_Large_sq','curLarge_Squares')
INDEX on LG_SQUARE TAG lg_square

SELECT curLarge_Squares	

GOTO TOP 

**	Update First Row of Cursor Field Call_160 with "TEST" 
Replace Call_160 with "TEST" && Update 

**	Check First Row of Cursor Field Call_160 has been updated. 
BROWSE 

** 
CURSORSETPROP("AutoIncError", .F. , "curLarge_Squares")

**	Set optimistic Table Buffering on. 
CURSORSETPROP("Buffering", 5, "curLarge_Squares")

** Specify Table name 
CURSORSETPROP('Tables','WAB_Large_sq','curLarge_Squares')	

** Provide mapping of view names to table names for the update 
CURSORSETPROP('UpdateNameList','Call_160 WAB_Large_sq.Call_160')
	
** Specify which field is the primary key 
CURSORSETPROP('KeyFieldList','Primary_Key','curLarge_Squares')	

** specify which fields can be updated 
CURSORSETPROP('UpdatableFieldList','Call_160') 			

** indicate that the WHERE clause will use only the primary key
CURSORSETPROP("WhereType", Primary_Key=1, "curLarge_Squares" )

** Specify that updates should be sent to the backend
CURSORSETPROP('SendUpdates',.T.,'curLarge_Squares') 						

TABLEUPDATE()

Regards,

David.

Recreational Developer / End User of VFP.

 

[Chris Miller]

The cursorsetprops need to be done first. Obviously after the SQLEXEC creating the cursor, but before any data change.

Chriss

 

[Chris Miller]

And this should give you an error:

CURSORSETPROP("WhereType", Primary_Key=1, "curLarge_Squares" )

It has to be a number, just a number.

Chriss

 

[David Higgs]

The cursorsetprops need to be done first.

I should have realised that especially after Mike had pointed it out earlier.

I also realised that I missed out "Primary_Key WAB_Large_sq.Primary_Key" in the UpdateNameList.

I finally have a working example that I can now expand upon.

SQLEXEC(SQLCONN,'SELECT * FROM WAB_Large_sq','curLarge_Squares')
INDEX on LG_SQUARE TAG lg_square

SET MULTILOCKS ON	&& Must be SET to ON to enable Row or Table buffering. 

** 
*CURSORSETPROP("AutoIncError", .F. , "curLarge_Squares")

**	Set optimistic Table Buffering on. 
CURSORSETPROP("Buffering", 5, "curLarge_Squares")

** Specify Table Name. 
CURSORSETPROP('Tables','WAB_Large_sq','curLarge_Squares')	

** Provide mapping of view names to table names for the update. 
CURSORSETPROP('UpdateNameList','Primary_Key WAB_Large_sq.Primary_Key, Call_160 WAB_Large_sq.Call_160')
	
** Specify which field is the primary key. 
CURSORSETPROP('KeyFieldList','Primary_Key')	

** specify which fields can be updated. 
CURSORSETPROP('UpdatableFieldList','Call_160') 			

** indicate that the WHERE clause will use only the primary key. 
CURSORSETPROP("WhereType", Primary_Key, "curLarge_Squares")

** Specify that updates should be sent to the backend. 
CURSORSETPROP('SendUpdates',.T. , "curLarge_Squares") 						

SELECT curLarge_Squares	

GOTO TOP 

**	Update First Row of Cursor Field Call_160 with "TEST". 
Replace Call_160 with "TEST" 

**	Check First Row of Cursor Field Call_160 has been updated. 
*BROWSE 

TABLEUPDATE()

Just one final thing, what do I need to do with AUTOINC ?

CURSORSETPROP("AutoIncError", .F. , "curLarge_Squares")

I would like to thank you for suggesting CURSORSETPROP/ TABLEUPDATE() and for your assistance in getting things working. It wasn't that difficult after I got my head around things. In the end, it was the Example Code that Mike shared and also the link that Mike provided (and I thank him for that) that gave me a nudge in the right direction. I then used the VFP Help files to understand a bit more about how things work.








Regards,

David.

Recreational Developer / End User of VFP.

 

[Chris Miller]

It wasn't that difficult after I got my head around things.

That was exactly what I wanted to achieve. I'm very glad it turned out as imagined.


The point about autoinc is a bit delicate. It's not the cursor but the database that has the autoinc field. The cursor has just an int, because it can't "inherit" the autoinc property from the server. It would need to stay in sync with the serverside counter for IDs, that's not poassible when you think about a multi user scenario, it would bind all the clients and server together to sync each other node.

So autoinc counting is just the job of the server. You can leave the primary key column 0 and a TABLEUPDATE will make the server set it. The catch is, this new key does not get reflected back to your cursor, so you need a query for it: SQLEXEC(SQLCONN,'SELECT LAST_INSERT_ID() as ID','curKey') and then REPLACE primary_key with curKey.ID.

To keep this simple just add one row at a time, so have a routine for adding a new row to the cursor by 1. APPEND or INSRT it with ID=0, do a TABLEUPDATE() of that single new cursor record and fetch it's ID to set it. This way you stay in sync with what the ddatabase has as IDs.



Chriss