Updating a CURSOR using REPLACE Text....Endtext 2

[David Higgs]

I would like to use REPLACE (TEXT...ENDTEXT) to update a Cursor.

I have about 30 Fields to Update, this is a shortened version for test purposes.

Select curLogbook
TEXT TO lcSQLUpdate TEXTMERGE NOSHOW
Replace COL_Band with UPD_Band, COL_Call with UPD_Callsign, COL_CNTY with UPD_CNTY, COL_Comment with UPD_comment, 
COL_CONT with UPD_Cont, COL_CONTACTED_OP with UPD_Contacted_OP, COL_CONTEST_ID with UPD_CONTEST_ID, COL_Country with UPD_Country
FOR COL_Primary_Key = (UPD_Primary_Key) 
ENDTEXT

I raised a similar question for MySQL Database here [link ]

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

[/url] which works ok. I am stuck on how to modify the code to use it to update my cursor. i.e. How do I execute the Code in the TEXT..ENDTEXT String?

Regards,

David.

Recreational Developer / End User of VFP.

 

[Mike Lewis]

David,

After you execute the TEXT ... ENDTEXT, the actual text of the command will be in your variable named lcSQLUpdate. To execute that command, you use macro expansion. Quite simply:

&lcSQLUpdate

Just that one short string, on a line by itself.

A couple of other (minor) points:

- You refer to a SQL update, but your command is using pure VFP syntax, not SQL, that is, REPLACE rather than UPDATE. This doesn't really matter - the command will work just as well - but it is something to keep in mind.

- The whole point of TEXT .. ENDTEXT is make the code readable. To that end, I suggest you break it up into shorter lines, so that you can see the whole thing at once without horizontal scrolling. Again, this doesn't affect the functioning of the code in any way, but it will make it easier to read and maintain.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

One other point:

You need to be sure that you are updating the correct cursor. To that end, you should add an IN clause to the end if the REPLACE.

You might think that it would be enough to SELECT the relevant work area before you do the REPLACE. That tells VFP where to find the fields used in the FOR clause, but it doesn't tell it which cursor to update. This won't make any difference in this case, but again it is something to keep in mind.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[David Higgs]

Hello Mike,

Thank you for your reply.

After you execute the TEXT ... ENDTEXT, the actual text of the command will be in your variable named lcSQLUpdate. To execute that command, you use macro expansion.

I was so near yet so far as I was thinking along the lines of the ampersand but I thought there should be something in front of &lcSQLUpdate, now I know.

The reference to MySQL Update was that I use the TEXT ... ENDTEXT when I update a MySQL Database and that I wanted to use something similar with VFP Cursor. Ah, I see what you mean; I should have used lcCurUpdate in my post. In my haste in posting I forgot to change it.

I will reformat the code to make it more readable. I do use the format you suggest when UPDATING MySQL Databases.


Regards,

David.

Recreational Developer / End User of VFP.

 

[David Higgs]

Mike,

I can't seem to get the multiple line SYNTAX right.

TEXT TO lcCurUpdate TEXTMERGE NOSHOW
Replace COL_Band with UPD_Band, COL_Call with UPD_Callsign
FOR COL_Primary_Key = (UPD_Primary_Key) 
ENDTEXT

TEXT TO lcCurUpdate TEXTMERGE NOSHOW
Replace COL_Band with UPD_Band, 
COL_Call with UPD_Callsign
FOR COL_Primary_Key = (UPD_Primary_Key) 
ENDTEXT

TEXT TO lcCurUpdate TEXTMERGE NOSHOW
Replace COL_Band with UPD_Band, ;
COL_Call with UPD_Callsign
FOR COL_Primary_Key = (UPD_Primary_Key) 
ENDTEXT

Regards,

David.

Recreational Developer / End User of VFP.

 

[Chris Miller]

The reason to use TEXT ENDTEXT for preparing SQL for remote databases is that you only have this way of executing SQL by sending an SQL command via SQLExec.

It's not necessary to put a replace in a TEXT ENDTEXXT, as VFP can natively execute it. The only reason I could think of is to use the textmerge functionality, but anything you have in variables or expresions or cursor fields or object properties also can be used directly in VFP SQL or xBase commands like REPLACE.#

When you still want to use this for FoxPro you need to use semicolons for multi line commands, SQL Servers don't need these, but VFP does. Also when you put the multiline command in TEXT..ENDTEXT.

In the end you have something like this:

TEXT TO variable TEXTMERGE NOSOW
some VFP command or query ;
in VFP syntax ;
the way it oould also work directly
ENDTEXT
&variable

instead of

some VFP command or query ;
in VFP syntax ;
the way it oould also work directly

The way you can format the command in TEXT ENDTEXT is exactly the same as you can outside of it, so you don't gain better readability, you can format multiline commands outside of TEXT..ENDTEXT the same way. You donÄt need the command or script to pass it to something, VFP can execute it directly.


Chriss

 

[Mike Lewis]

David, I realised after posting my replies that you don't really need to use TEXT .. ENDTEXT at all in this case. Basically, TEXT ... ENDTEXT provides a more readable way of creating variables containing long strings of text. It is often used to create commands that are sent to a server - as you did in the case of your MySQL code - but not so much for code that is executed directly within VFP.

There is a feature of TEXT ... ENDTEXT that allows you to insert variables within the text, the variables being evaluated when the TEXT ... ENDTEXT is executed. This can be very useful in some case, but again it's not relevant in this case.

The bottom line is that, in this case, you can just code your REPLACE statement as usual, just like any other native VFP command. But don't abandon TEXT ... ENDTEXT completely, as it is a very useful construct in various circumstances.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[David Higgs]

Mike,
I have just tried the following code which errors due to, I think, exceeding 255 CHRS. I've not yet been successful in using TEXT..EndText.

Replace COL_Band with UPD_Band ;
COL_Call with UPD_Callsign ;
COL_CNTY with UPD_CNTY ; 
COL_Comment with UPD_comment ; 
COL_CONT with UPD_Cont ;
COL_CONTACTED_OP with UPD_Contacted_OP ; 
COL_CONTEST_ID with UPD_CONTEST_ID ; 
COL_Country with UPD_Country ; 
COL_CQZ with UPD_CQZ ; 
COL_DXCC with UPD_DXCC ; 
COL_EQ_CALL with UPD_EQ_CALL ; 
COL_EQSL_QSLRDATE with UPD_EQSL_QSLRDATE ; 
COL_EQSL_QSLSDATE with UPD_EQSL_QSLSDATE ;
COL_EQSL_QSL_RCVD with UPD_EQSL_QSL_RCVD ; 
COL_EQSL_QSL_SENT with UPD_EQSL_QSL_SENT ;  
COL_FREQ with UPD_Freq ;
COL_Gridsquare with UPD_Gridsquare ; 
COL_IOTA with UPD_IOTA ; 
COL_ITUZ with UPD_ITUZ ;     
COL_MODE with UPD_Mode ; 
COL_My_Antenna with UPD_My_Antenna ; 
COL_MY_City with UPD_My_City ; 
COL_MY_CNTY with UPD_My_Cnty ; 
COL_MY_COUNTRY with UPD_My_Country ; 
COL_MY_CQ_ZONE with UPD_My_CQ_Zone ; 
COL_MY_GRIDSQUARE with UPD_My_GridSquare ; 
COL_MY_IOTA with UPD_My_Iota ; 
COL_MY_ITU_ZONE with UPD_My_ITU_Zone ; 
COL_MY_LAT with UPD_My_Lat ; 
COL_MY_LON with UPD_My_Lon ;
COL_MY_NAME with UPD_My_Name ; 
COL_MY_POSTAL_CODE with UPD_My_Postal_Code ; 
COL_MY_RIG with UPD_My_Rig ; 
COL_MY_STREET with UPD_My_Street ;
COL_NAME with UPD_Name ; 
COL_Operator with UPD_Operator ; 
COL_Owner_Callsign with UPD_Owner_Callsign ; 
COL_PFX with UPD_PFX ;
COL_QSLRDATE with UPD_QSLRDATE ; 
COL_QSLSDATE with UPD_QSLSDATE ;  
COL_QSL_RCVD with UPD_QSL_RCVD ; 
COL_QSL_RCVD_VIA with UPD_QSL_RCVD_VIA ; 
COL_QSL_SENT with UPD_QSL_SENT ; 
COL_QSL_SENT_VIA with UPD_QSL_SENT_VIA ; 
COL_QSL_VIA with UPD_QSL_VIA ; 
COL_QTH with UPD_QTH ; 
COL_RIG with UPD_Rig ;
COL_RST_RCVD with UPD_rst_rcvd ; 
COL_RST_SENT with UPD_rst_sent ;  
COL_SRX with LDE_SRX ; 
COL_SRX_STRING with UPD_SRX_String ; 
COL_Station_Callsign with UPD_Station_Callsign ; 
COL_STX with LDE_STX ; 
COL_STX_STRING with UPD_STX_String ; 
COL_TIME_OFF with UPD_Time_Off ; 
COL_TIME_ON WITH UPD_Time_On ; 
COL_TX_PWR with UPD_Tx_PWR ;
WAB_Lg_Sq with wab_lg_sq_upd ; 
WAB_Sm_Sq with wab_area_upd ; 
Trig_Point with Trig_Point_upd ; 
SOTA with SOTA_upd ; 
WAI with W_A_I_upd ; 
FOR COL_Primary_Key = (UPD_Primary_Key)

Regards,

David.

Recreational Developer / End User of VFP.

 

[David Higgs]

The following (shortend) code produces a Unrecognised phrase / keyword error for &lcCurUpdate

*                           
*  Update curLogbook Cursor 
*                           
   
SELECT curLogbook	

LOCAL lcCurUpdate

TEXT TO lcCurUpdate TEXTMERGE NOSHOW
Replace COL_Band with UPD_Band ;
COL_Call with UPD_Callsign ;
FOR COL_Primary_Key = (UPD_Primary_Key)
ENDTEXT 

&lcCurUpdate

Regards,

David.

Recreational Developer / End User of VFP.

 

[Chris Miller]

There only is a 255 char limit to strings, not commands. But commands are limited to 8192 characters. I don't think you exceeded that. What's the error?

Chriss

 

[Mike Lewis]

No, it's nothing to do with a limit of 255 characters. The meximum length of a command is 8,192 characters. Your REPLACE is 2,104 characters, so that's not the problem.

The problem is that you need a comma after each of the individual parts (except the last). For example: [tt]COL_Call with UPD_Callsign[highlight #FCE94F],[/highlight] ;[/tt]

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[David Higgs]

What's the error?

Hello Chris,

Thank you for your input.

Error reported is "Data type mismatch"

Regards,

David.

Recreational Developer / End User of VFP.

 

[Mike Lewis]

The problem caused by the missing comma applies to both the TEXT ... ENDTEXT version and the direct version.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[David Higgs]

The problem is that you need a comma after each of the individual parts (except the last). For example: COL_Call with UPD_Callsign, ;

This also produces a Data Type mismatch error.

I suspect this is going to be a Date / Time related issue. I will carry further tests and report back.

Regards,

David.

Recreational Developer / End User of VFP.

 

[David Higgs]

I've found 3 off Integers and 2 off DATETIME Fields are causing the Data Type mismatch errors.

This is a very brief description of my application.

I am in the process of rebuilding my "Logbook" application and I'm applying alterations to improve the efficiency of my code based on experience gained (albeit little) since I wrote the original application.

The MySQL Data Files are stored on my Local MySQL Server. During the initialisation of my application I copy the contents of the MySQL "Logbook" into a VFP CURSOR which is then displayed in a Grid.

When I add an entry to the "Logbook" it is added to the MySQL Database and at the same time I reload the contents of the MySQL "Logbook" into the Logbook CURSOR so that it remains up-to-date.

Now that I am re-writing my application, I thought it would be efficient to update the CURSOR using the REPLACE Command instead of reloading the MySQL "Logbook" into the Logbook CURSOR.

So that's basically where I am at.






Regards,

David.

Recreational Developer / End User of VFP.

 

[Chris Miller]

The normal solution is to query data into an updatable remote view or make an cursor you got from SQLExec updatable with CURSORSETPROP settings like those needed for an updatable remote view. SQLExec gives you more freedom than a view, you also use it anway.

In all cases a TABLEUPDATE("updatablecursor") then updates the remote table. And with that construct the MySQL table will be in sync with what's in the cursor, without doing an UPDATE to MySQL and the same Update on the cursor, but by letting VFP forward all cursor changes.

It's really that simple from that end, the hurdle is setting up the cursor properties right, but it's not hard. For getting the concept I'd use a new DBC, create a connection object in it with CREATE CONNECTION and then a remote view. The view designer will allow you to look at the view code making settings with DBSetProp, that are similar to what you need for CurorSetProp.

You find what to set with the interactive view designer and the VFP help, I spare myself writing a tutorial for it. Try it yourself. I can guarantee you'll not get it to work immediately but it's much better to do this yourself than to just follow instructions. Because there's not just one straight valid way, there are several choices to make and so the best initial advice is, do it. You only get to this paradise of not needing to write out UPDATE, INSERT or DELETE statements but just use TABLEUPDATE(), if you go through this yourself. It's not rocket science.

Chriss

 

[David Higgs]

Hello Chris,

Thank you for your reply.

I will take a look at at the COMMANDS and Solutions you mention and hopefully incorporate them in my application. I've printed your message so that I can do some research. Re-writing the application has been very rewarding although I have a long way to go.

No doubt I will have a few more questions as I progress.

Regards,

David.

Recreational Developer / End User of VFP.