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]

Okay, that's one more thing as expected.

Now for the coverage log. You just need to add the two lines to start and end the log. It'll show what code lines run and/or whether there is something unusual about the timing. The result is just a CSV file of data. It should contain about 15000-20000 lines when you put the start and end of logging as me.

Notepad could open that, I don't know if you prefer another editor, Notepad++ is surely the better notepad, for example by having a line number display left of the "Editbox".

If the log is as long as expected Id look for timing data in the log, that's the first column of the CSV data.


Chriss

 

[David Higgs]

Looking at the "Logging" it seems that "LogbackColor" being repeatedly called, but why that should take more time to execute with CURSORSETPROP, I don't know.

*                                           
*  Alternate Back Colours (Logbook Look Up) 
*                                           

Function LookupBackColor
  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(179,255,255)	&&	Blue Background 
  ENDIF  
  
  RETURN(m.LogRGBValue) 				&&	Return RGB(Values) 
 ENDFUNC

Regards,

David.

Recreational Developer / End User of VFP.

 

[Chris Miller]

So what if you set Dynamicbackcolor to a single constant color while you set the alt_col column? I assume that's what's calling LookupBackColor.

This has the same overhead of graphics painting, even more than status bar notifications as whole rows of data are colored, so this should not happen during the cursor update. You could also try to set _screen.LockScreen = .t. during the loop, just be sure no error happens and you therefore _screen.LockScreen stays .t.

Set it back to .f. after the replaces, of course.

Chriss

 

[David Higgs]

My Grid is located at Logbook.pfMain.Logbook.gdLogbook so I used "logbook.LockScreen = .t." which had no affect.

This code is in the Grid Refresh:

this.SetAll("DynamicBackColor", "LogBackColor(alt_col)", "Column")

How / where do I set Dynamicbackcolor to a single constant color ?

Regards,

David.

Recreational Developer / End User of VFP.

 

[Mike Lewis]

How / where do I set Dynamicbackcolor to a single constant color ?

I missed the last few posts in this thread, but let me try to answer the above question.

If you want every row to have the same BackColor, there is no point in using DynamicBackColor. Just set the BackColor property to the desired colour.

However, in this case, I think your main concern is to check that you are using DynamicBackColor correctly. The setting of DynamicBackColor is an expression that evaluates to a colur value, for example, "RGB(128, 128, 128)". Another (simpler) example would be "255" (which the RGB code for red).

As you aer using SetAll(), you want the second parameter of that method to be the colour expression. So if "LogBackColor(alt_col)" is a field that contains a colour code, that should work. More usually, the second parameter would contain an expression that determines the colour code, such as : [tt]THIS.SetAll("DynamicBackColor", "IIF(alt_col % 2 <> 0, RGB(255,255,255, RGB(179,255,255)", "Column")[/tt].

Bonus tip: Instead of[tt] RGB(255, 255, 255)[/tt], you can simply write[tt] -1[/tt].

Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[David Higgs]

Mike, it was Chris asking me to to temporary change the Dynamicbackcolor three posts above.

"So what if you set Dynamicbackcolor to a single constant color while you set the alt_col column?"

THIS.SetAll("DynamicBackColor", "IIF(alt_col % 2 <> 0, RGB(255,255,255, RGB(179,255,255)", "Column")

you kindly gave me that code in 2016 for my application and has been in use ever since. I remember querying the % 2 at the time when you explained how it is used.



Regards,

David.

Recreational Developer / End User of VFP.

 

[Mike Lewis]

Good heavens, David. You've got a much better memory than me. Never mind 2016, I sometimes look back at stuff I posted three or four weeks ago, and don't remember posting it.

As I said, I missed the last few posts in this thread. I'll look over them now and try to catch up with the discussion. (This must be one of the longest threads I've participated in for quite a while.)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Mike, of course there's not point in setting DynamicBackcolor to a single color, but that way the replacements in the cursor don't trigger the grid to update.

The Lockscreen solution will prevent even more than that, but when it was set to call the LookupBackColor function or method and that happens about 6000 times, that likely makes up most of the 13 seconds. Not because any line of the function takes long, but because it triggers the grid to redraw its rows, even if the color doesn't really switch.

David, if you have set DynamicBackcolor like Mike said (even already in 2016) - THIS.SetAll("DynamicBackColor", "IIF(alt_col % 2 <> 0, RGB(255,255,255, RGB(179,255,255)", "Column"). - then this is not calling LookupBackColor. So where does that call come from?



Chriss

 

[David Higgs]

Chris,

The only call for "DynamicBackColor" is in the GRID REFRESH that displays the contents of the Logbook.

this.SetAll("DynamicBackColor", "LogBackColor(alt_col)", "Column")

The code that Mike refers to is used in my original code and other applications. I'm using the above code in the re-build of my original application.

What I don't understand is why adding the CURSORSETPROP code is causing additional work with alt_col.

Regards,

David.

Recreational Developer / End User of VFP.

 

[Mike Lewis]

The only call for "DynamicBackColor" is in the GRID REFRESH

If you mean you are actually calling [tt]this.SetAll("DynamicBackColor",[/tt] etc. from the grid's Refresh, that is definitely wrong. You should put that code in the grid's Init, or possibly the form's Init, but not in the Refresh.

It is true that the IIF() condition is evaluated and the BackColor is set during the refresh, but the code itself should not be executed at that point.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[David Higgs]

You should put that code in the grid's Init, or possibly the form's Init, but not in the Refresh.

Well, I "Cut" the Code from the Grid's Refresh and went to "Paste" it in the Grid's Init and found the code was already in there but was starred out of use with "*" . For some reason I must have moved the code to the "Refresh" but can't recall why.

All is working as before.

Regards,

David.

Recreational Developer / End User of VFP.

 

[Chris Miller]

All is working as before.

Does that include updating alt_col in under a second?

What I don't understand is why adding the CURSORSETPROP code is causing additional work with alt_col.

That's a good question, as updating alt_col also without the CURSORSETPROP will trigger a refesh cascading to more refresh and this should fail also for a non updatable not bound to a remote backend. You could check whether the buffering already starts it. Anyway the reason is, the refresh surely is a bad place for setting dynamicbackcolor. Even not knowing anything about how and when the grid refreshes, it should be intuitively clear this is bound to cause cascading feedback to itself. I think it's kind of a wonder VFP does not choke on this and decides to not cuase that an endless loopback effect.

Seems there are some timeouts working in the native grid code.

Chriss

 

[David Higgs]

Does that include updating alt_col in under a second?

No, it's still taking 11 Secs. It's strange though that the first time I cause Alt_Col to run it's 0.172 Secs and thereafter in the order of 10.767 secs.

Anyway the reason is, the refresh surely is a bad place for setting dynamicbackcolor.

I did move the call for "DynamicBackColor" to the Grid Init.



Regards,

David.

Recreational Developer / End User of VFP.

 

[Chris Miller]

Did you set DynamicBackcolor to a constant color or used Screen.Lockscreen?

Because we still found out there are lots of calls to LogBackColor, because that's in the DanymicBackcolor expression and the records change, thus the grid refreshes, thus this fuction is called.
This doesn't just happen once you set DynamicBackcolor. Also if you put that into INIT() the expression still runs for every grid row refreshed.

And I also find it a good explanation why the first time is fast. You likely do the first run of filling alt_col before the DynamicBackcolr is in effect. It might already be set as the Init is early, but the grid isn't yet visible while you initialize evrything, that takes until the Show happens, and that's after all inits, even after form init.

PS: Before you wonder about what best to do, just try to not set Dynamicbackcolor at all to see if it is the reason this all takes so long.

Chriss

 

[David Higgs]

PS: Before you wonder about what best to do, just try to not set Dynamicbackcolor at all to see if it is the reason this all takes so long.

I've just removed SET Dynamicbackcolor from Grid Init. No change with times taken, just plain white Grid Background as expected.

Regards,

David.

Recreational Developer / End User of VFP.

 

[Chris Miller]

Okay, what else is in the coverage log file. When you determine the line number of the [tt]Replace alt_col With ninc[/tt], then how many logged lines are there and what's their time - again, that's in the first CSV column of the log.

Chriss

 

[David Higgs]

The following starts at Line No. 407 to 410

SCAN 
	ninc = ninc + 1
	Replace alt_col With ninc
ENDSCAN

This code is repeated approx 5686 Times.

  0.000020,,alt_row_colours,408,d:\amateur radio logbook - development_tests\amateur radio logbook.fxp,3
   0.000023,,alt_row_colours,409,d:\amateur radio logbook - development_tests\amateur radio logbook.fxp,3
   0.000042,,alt_row_colours,410,d:\amateur radio logbook - development_tests\amateur radio logbook.fxp,3

Regards,

David.

Recreational Developer / End User of VFP.

 

[Chris Miller]

Okay, the first column numbers mean seconds, so this loop takes 5700*(0.000020+0.000023+0.000042) seconds - ca. 0.5 seconds, unless you find lines in the log with a much higher execution time.

So you have to find another thing that consumes the rest of the 13 seconds. If summing all times from the log does not sum to 13 seconds then time goes into events that have no VFP code, like native refresh behavior or anything in other processes, which would include OLE controls that react to the cursor changes. Very unlikely, though, as OLE controls bind to record sets, not VFP cursors, and even less likely, if you don't use OLE controls ;o).

In the simplest case you suspect the wrong code to be slow and you should then perhaps log from start of the loop and end with a button having SET COVERAGE TO a its click event code.

Chriss

 

[Chris Miller]

Does your application have a READ EVENTS or do you use a modal main form?

Because I just made sure coverage logging even logs idle wait time in the read events line:

ON KEY LABEL F3 CLEAR EVENTS

SET COVERAGE TO timing.log
READ EVENTS
SET COVERAGE TO

The timing log looks like this when I wait a while before I hit F3:

8.131208,,timing,4,c:\...\timing.fxp,1
0.000342,,on key,,,2
0.000148,,timing,5,c:\...\timing.fxp,1

I point this out because there is a slight chance your replacement is fast but then the display only refreshes when you move the mouse or click or use a key. And that idle time would show up as a log entry of your READ EVENTS somewhere.

In my simple case the first line in the log is about line 4 of timing.prg (or fxp) and that's the READ EVENTS, the ON KEY is logged without line number as it happens out of context of the timing.prg and then finally turning logging off is the last thing logged.

The more you log, the more you know. I'd still suspect anything from the start of the loop onwards so a stop button for the log would be a way to stop the recording of what code gets executed after you see the new coloring in effect.

Chriss

 

[David Higgs]

My application uses READ EVENTS and the MainForm is Modeless.

I use OLE for the MyCOM Serial Port which controls my Transceiver. My tests have been carried out with the Transceiver switched off, so there is no Serial Communication taking place. In any event, communication only occurs when I request it.

I also use OLE for a Timer, but again, that's only when I request it.

The more you log, the more you know. I'd still suspect anything from the start of the loop onwards so a stop button for the log would be a way to stop the recording of what code gets executed after you see the new coloring in effect.

I've temporarily suspended the use of DYNAMICBACKCOLOR.

I will try a few more SET Coverage's.

I still can't get my head around why including CURSORSETPROP should cause this issue.





Regards,

David.

Recreational Developer / End User of VFP.