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]

Another idea is to execute a script that can make the LAST_UPDATE_ID() as fast as possible and in the same session. You can execute SQLEXEC(SQLConn,"INSERT INTO MYSQLTABLE (fields) VALUES (values); SELECT * FROM MYSQLTABLE Where primary_key=LAST_INSERT_ID() LIMIT 1","curNewRow")

And then append that to the cursor. This is just giving the new record a wrong fieldstate as new cursor record, while it actually came from the server. So you'd need to use SETFIELDSTATE(). Just btw, LIMIT 1 is of course unnecessary, but makes clear to MySQL you only fetch one row.

Otherwise you end up doing what you wanted to prevent: REQUERY() all data. It's not an unusual solution to do that, but usually you also don't query all data into a cursor, just say one employee, one order, one product, (and detail data with maybe 10 rows) etc. Then a REQUERY of the list of detail data doesn't hurt much.

Chriss

 

[David Higgs]

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

Whilst I understood your rationale, it was slightly at odds with my thinking. I printed out the Help File and read through it. At the end, I did glean some information, but I didn’t realise that I would probably need more than one CURSORSETPROP and therefore was somewhat overwhelmed when confronted with Mike’s example. When I started to dissect Mike’s example, I noticed some familiar Names e.g 'UpdateNameList', where have I seen that name before, the VFP Help File. So, using the Help File I could then understand a bit more of what’s going on, but without the example files I would probably still be scratching my head. Anyway, we are beginning to achieve what we both set out to achieve, that’s the main thing.

Thankyou for the information on how to deal with the Primary_Key, I will experiment and see which option works for me. VFP Help file to hand!


Regards,

David.

Recreational Developer / End User of VFP.

 

[David Higgs]

I'm trying to get the Syntax right for a large list (123) of Table Names over multiple lines, this single line works.

	TEXT TO Logbook_Field_List TEXTMERGE NOSHOW
	'Col_Primary_Key &TBL_Logbook..Col_Primary_Key,Col_Band &TBL_Logbook..Col_Band'
	ENDTEXT

** Provide mapping of "View Names" to "Table Names" for the update. 
CURSORSETPROP('UpdateNameList',&Logbook_Field_List)

But this multiple list doesn't, I get a Command contains unrecognized phrases/keyword error.

	TEXT TO Logbook_Field_List TEXTMERGE NOSHOW
	'Col_Primary_Key &TBL_Logbook..Col_Primary_Key,;
	Col_Band &TBL_Logbook..Col_Band'
	ENDTEXT

** Provide mapping of "View Names" to "Table Names" for the update. 
CURSORSETPROP('UpdateNameList',&Logbook_Field_List)

I've tried a few combinations at the end of the first line without success.



Regards,

David.

Recreational Developer / End User of VFP.

 

[Mike Lewis]

The problem here is the semi-colon at the end of the first line within the TEXT / ENDTEXT. Unlike in normal VFP code, the text within a TEXT / ENDTEXT does not use semi-colons to break up the lines. It relies instead on the physical line breaks. So, by inserting that semi-colon, the semi-colon will be embedded in your Logbook_Field_List variable, which is not what you want.

By the same token, the string delimiters (that is, the single quote marks) will also be embedded in the string. That's not a problem, as you are macro-expanding the string. But it would be simpler to omit the quote marks, and then to remove the ampersand in front of Logbook_Field_List in your CURSORSETPROP().

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[David Higgs]

Thanks Mike, I had my "over complicate things" Hat on today!

I looked at various TEXT..ENDTEXT that I've used successfully in the past, but couldn't crack this one.

Regards,

David.

Recreational Developer / End User of VFP.

 

[David Higgs]

In my original Application, on the MainPage / Pageframe / Logbook I have a Grid (gdLogbook) which displays the contents of (curLogbook). During initialisation I Load the contents of a MySQL Database into a Temporary Cursor (curLogbook_tmp) and then create an empty cursor (curLogbook).

SELECT * from curLogbook_tmp WHERE .F. INTO CURSOR curLogbook READWRITE

In the Init of Mainpage / Pageframe / Logbook I then Append the contents of curLogbook_tmp into curLogbook and close the curLogbook_tmp this is to avoid CurLogbook not being found by the Grid gdLogbook at initialisation.

Having completed my initial tests with CURSORSETPROP/ TABLEUPDATE() I've started to look at integrating my new code into my original application and I'm struggling to find a way to make it work.

The issue I have is that my initial tests were carried out using a Cursor curLogbook generated from a MySQL Database. In view of the way my original Application works curLogbook would be empty at initialisation. So if I were to apply the CURSORSETPROP/ TABLEUPDATE() to curLogbook it would be empty and when I Append the contents of curLogbook_tmp into curLogbook and do a Tableupdate() this would then, I think, try to update the MySQL Database?







Regards,

David.

Recreational Developer / End User of VFP.

 

[Mike Lewis]

Yes, that's right. If curLogbook is the cursor to which you have applied your CURSORSETPROP()s and TABLEUPDATE(), then appending records to that cursor will indeed update the server.

However, there is a simnple switch that you can flick to turn that behaviour on or off:

CURSORSETPROP('SendUpdates', .F., "curLogbook")   && disables updating the server
...
CURSORSETPROP('SendUpdates', .T., "curLogbook")   && re-enables updating the server

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[David Higgs]

Thanks Mike, I will experiment with those settings by initially switching Updates off until the curLogbook has been populated.



Regards,

David.

Recreational Developer / End User of VFP.

 

[David Higgs]

I am seeing an issue, "Property is invalid for table cursors".

** Specify Table Name. 
CURSORSETPROP('Tables','&TBL_Logbook','curLogbook')

I believe the issue is maybe due to the MySQL Data being loaded into 'curLogbook_tmp' and then subsequently loaded into 'curLogbook' which is created from 'curLogbook_tmp'.

SQLEXEC(SQLCONN,lcRename_Fields,'curLogbook_tmp')

SELECT * from curLogbook_tmp WHERE .F. INTO CURSOR curLogbook READWRITE			&&	Creates Empty curLogbook

The CURSORSETPROP is looking for a pair '&TBL_Logbook','curLogbook' which doesn't exist as the SQLCONN is / was '&TBL_Logbook','curLogbook_tmp'.


Regards,

David.

Recreational Developer / End User of VFP.

 

[Mike Lewis]

I think I can see what's happening here.

All those CURSORSETPROP()s work with cursors obtained directly from SQLEXEC(). So they would work with curLogbook_tmp. But they don't work with cursors that you create yourself in VFP, including those created with SELECT ... INTO CURSOR ... So not with curLogbook_tmp.

So it looks like you will have to take some other approach. Can you do away with the temporary cursor, and get the results directly into curLogbook?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[David Higgs]

All those CURSORSETPROP()s work with cursors obtained directly from SQLEXEC(). So they would work with curLogbook_tmp. But they don't work with cursors that you create yourself in VFP, including those created with SELECT ... INTO CURSOR ... So not with curLogbook_tmp.

That's exactly what I was finding.

So it looks like you will have to take some other approach. Can you do away with the temporary cursor, and get the results directly into curLogbook?

The only reason I use "curLogbook_tmp" and "curLogbook" is so that the Grid on the Mainform is able to find the "curLogbook" at initialisation.

Regards,

David.

Recreational Developer / End User of VFP.

 

[Mike Lewis]

That should be easy to get round. For example, you could defer setting the grid's RecordSource until after you have downloaded the data.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

You can load the data in the form load, for example, that's before any controls exist.

If you pass in parameter values for the where clause to form init, the solution to that are temporary private or even poublic variables set be the caller by convention before starting the form. Instead of passing the data both caller and form load share the "secret" to use the same variables, the form.load can then release them again, when you choose public vars or they get out of scope in the caller when they are private.

You don't need a secondary cursor for the grid in this case, also later when you would requery by SSQLEXEC, I think. This works like VFP can deal with view requeries without the two cursor idea.

More generally you can load all data of single tables into SQLEXEC cursors, query (join, union, filter etc. data from these base cursors into cursors for the grid or other UI controls and later go the reverse route. But then you're also back to double-entry bookkeeping. This can pay for a GUI which makes complex queries, your initial SQLEXEC cursors then are like a local database and the gateway towards the real backend server. This atually works best with Views, because you can define views on views. and this have the special updatable table views and the query views.

It's still advisable to not load the whole database, but you could use this principle to once load everything into the updatable simple singe-table cursors and then work with views on them for thee rest of the application. Provided your database fits into memory this will work very fast for single user applications. That situation somewhat removes the need for a backend, but a few users could still be handled or you can use the application from several places and the MySQL server just as the cloud backend.

Others are doing this principle by fetching the central backend to a local. Local with DBFs, or MySQL, or MS SQL Express, you see you can imagine all kinds of routes, if this helps with the performance and separation, though it could get quite messy.

So back to the simplest case you only query to cursors you directly use in the UI and don't use any local gateway database or "model". One reason to use the concept is bad wifi or bad or slow internet or often power outages. As long as you can keep the client up and running (for example on a good charged notebook) you can later reconnect and TABLEUPDATE().

Chriss

 

[David Higgs]

I've removed all reference to 'curLogbook_tmp' and now just use 'curLogbook' and found that the Grid 'gdLogbook' was being populated. I can only put this down to the way that I now select which Logbook to use. I used to run the program and select the Database from a 'Menu' on a 'Form'. Now that I've started to rebuild the application I now pass a parameter from a 'Batch File' which selects which Logbook to use. When running from the IDE I use a 'Test Logbook' so as not to corrupt my actual Logbooks.

The only thing now is that I have noticed when I use the following code it takes an age to execute.

*                        
*  Alternate Row Colours 
*  --------------------- 

PROCEDURE Alt_Row_Colours

LPARAMETERS alt_col_selection	

SELECT (alt_col_selection)		
tot_rec = Reccount()

Go Top

CURSORSETPROP('SendUpdates', .F., "curLogbook")   && disables updating the server

For A = 1 To tot_rec

	Replace alt_col With A
	
	Skip

	If Eof()
		Exit
	Endif

NEXT

CURSORSETPROP('SendUpdates', .T., "curLogbook")   && re-enables updating the server

'Alt_Col' is a Field in curLogbook.

The code 'Replaces' the value in 'Alt_Col' after a change of 'Data Selection' i.e a change of 'SET Filiter To' this is to enable alternate Row Colours.

I placed the 'Sendupdates' commands in, but it still takes an age.

Regards,

David.

Recreational Developer / End User of VFP.

 

[Chris Miller]

How about SQL?

update curLogbook Set alt_col=recno()

Besides you could set the dynamic color property to an expression using RECNO()%2

If you need to update by current set order you need scan...endscan, this will not run much faster. The only reason changing the record would cause a MYSQL update is when you use the single record buffering mode, just use table buffering and optimisitc locking (=buffermode 5) and nothing happens in the server until you TABLEUPDATE(), also when SendUpdates is .T.

Chriss

 

[David Higgs]

Besides you could set the dynamic color property to an expression using RECNO()%2

I use that method, the Alt_Col is used to consecutively number the records to give alternate Row Colours.

Function LogBackColor
  Parameter alt_col				&&  Assigns data from a calling program to Private Variables. 
  Private alt_col,m.LogRGBValue
 
  m.LogRGBValue = RGB(255,255,255)		&&  White Background 
 
  IF ALT_COL % 2 <> 0
    m.LogRGBValue = RGB(255,255,178)	        &&  Yellow Background 
  ENDIF  
  
  RETURN(m.LogRGBValue) 			&&  Return RGB(Values) 
ENDFUNC

If you need to update by current set order you need scan...endscan, this will not run much faster. The only reason changing the record would cause a MYSQL update is when you use the single record buffering mode, just use table buffering and optimisitc locking (=buffermode 5) and nothing happens in the server until you TABLEUPDATE(), also when SendUpdates is .T.

I have CURSORSETPROP("Buffering", 5, "curLogbook").


Regards,

David.

Recreational Developer / End User of VFP.

 

[Chris Miller]

Does it help when you SET NOTIFY CURSOR OFF?

Chriss

 

[Mike Lewis]

David,

The code in your previous post but one is not good. In particular, the [tt]For A = 1 To tot_rec / NEXT [/tt] loop.

First, it would be neater, more readable and more efficient to replace this with a SCAN / ENDSCAN loop.

It is also a bad idea to use A as a variable name (or any letter between A and J). That's because those letters also serve as default table aliases. It won't matter in this case, but in general it is something to keep in mind.

And finally, the whole loop is unnecessary, because you can do the whole thing with a single UPDATE (or REPLACE) statement, as Chris has demonstrated.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[David Higgs]

The code in your previous post but one is not good.

Mike, Thank you for your suggestions for a more readable / efficient code. As I'm re-writing my original code these tips are most welcome, I will incorporate them in my revised code.

I don't know why I chose 'A' as the variable for the FOR/NEXT Loop, that must have been a temporary flash back to the Z80 Days!

Although I'm aware of SCAN / ENDSCAN I've only used it once in my application, I'll re-visit it.

At the moment, I can't see how the whole Loop can be replaced with a single UPDATE/REPLACE statement as the whole table needs to be consecutively re-numbered.

Regards,

David.

Recreational Developer / End User of VFP.

 

[David Higgs]

Does it help when you SET NOTIFY CURSOR OFF?

No difference.

Regards,

David.

Recreational Developer / End User of VFP.