Updating a Table with SQLEXEC 1

[David Higgs]

I am trying to update a record in a MySQL Database using the following code:

SQLEXEC(SQLCONN,"Update &TBL_Logbook SET " + ;
"COL_BAND = '&Band'," + ;			&&	  9 COL_BAND
"Col_CALL = '&Callsign'," + ;			&&	 12 COL_CALL
"Col_CHECK = '&Check'," + ;			&&	 13 COL_CHECK	(Contest)
"Col_CLASS = '&Class'," + ;			&&	 14 COL_CLASS	(Contest)
"Col_CNTY = '&county'," + ;			&&	 15 COL_CNTY
"Col_COMMENT = '&comments'," + ;		&&	 16 COL_COMMENT
"Col_CONT = '&Cont'," + ;			&&	 17 COL_CONT
"Col_CONTACTED_OP = '&Contacted_OP'," + ;	&&	 18 COL_CONTACTED_OP
"Col_CONTEST_ID = '&CONTEST_ID'," + ;		&&	 19 COL_CONTEST_ID	(Contest)
"Col_COUNTRY = '&Country'," + ;			&&	 20 COL_COUNTRY
"Col_CQZ = '&CQZ'" + ;				&&	 21 COL_CQZ	
       " Where Col_Primary_Key = '&Pri_key'")

A couple of questions,
(1) is there a limit to the number of fields (I have over 100 fields to update) that can be updated using this code? I am getting a errors when I add further fields.

(2) Should I be looking at another way of Updating the MySQL Database?

Regards,

David.

Recreational user of VFP.

 

[Olaf Doschke]

On the VFP side you have ~255 chars per string literal, but that's not a limit of your case, as you put your query together with many much shorter string literals. And that's already your first error. You should use TEXT...ENDTEXT. Besides that you can use VFPs mechanism of query parameterization (that works independent of the databases own parameterization mechanisms).

LOCAL lcSQLUpdate

TEXT TO lcSQLUpdate TEXTMERGE NOSHOW
Update <<TBL_Logbook>> SET 
COL_BAND = ?Band,
Col_CALL = ?Callsign
...
Where Col_Primary_Key = ?Pri_key
ENDTEXT
If SQLEXEC(SQLCONN, lcSQLUpdate)<0
   Aerror(laError)
   Suspend
Endif

In case of an error, you get info in an array laError, you can view in the locals window of the debugger.
For this query to work that way you need variables of fields of the current workarea with the names given. You can be more specific addressing variables with m. prefix: ?m.Band, ?m.Callsign,... And when addressing fields you might do with the full qualifier ?workareaalias.Band, ?workareaalias.Callsign,...

If you want to SCAN a workarea and do multiple updates, one step more direct would be preparing a statement like this with SQLPrepare, after which you only call SQLEXEC(SQLCONN), then VFP executes the last prepared statement and to go through all records you scan through your workarealias.

And even better is making the cursor updatable, that you got from a MySQL-SELECT via SQLEXEC(). That means more than writable, any SQLEXEC generated cursor is writable, but you don't write back to the remote database source tables. I can point you to posts I already did on that topic, but details of the configuration are often even troubling me, causing misleading errors. The pro side is, once all SQLSETPROP() is established, your update code is TABLEUPDATE(). It's still not a beginner topic.

So rather start with using TEXT..ENDTEXT, making it much easier to establish the SQL Update. In the end a TABLEUPDATEalso sends SQLUpdate and one advantage of creating the queries yourself is more control and use of SQL specifics automatically created queries don't allow, like MySQLs INSERT ... ON DUPLICATE UPDATE mechanism allowing to process any record you iterate in the same way instead of needing to decide for INSERT of new records and UPDATE of old.

Bye, Olaf.

 

[David Higgs]

Olaf,

Many thanks for your informative reply. After evaluating your code in my Test Program I copied it into my main aplication and all is working well.

I did a little research on the Double << >> and found that this is telling "Textmerge" to Stop evaluating expressions within << and >>, so I learnt something new.

I will follow up some of your suggestions of using alternative (albeit more complicated) methods of Inserting / Updating the Logbook.


Regards,

David.

Recreational user of VFP.

 

[Olaf Doschke]

<<>> is doing the opposite, it merges the constant text outside of such delimiters with the evaluated expression inside it, eg the name of the table stored in a variable TBL_Logbook is inserted at that place <<TBL_Logbook>>. Your usage of that variable name via &TBL_Logbook suggests that (it does the similar thing with macro substitution). You could also use <<>> for all the column values, to merge in the values as the code itself, but using parameterized queries with "?var" helps with problematic cases (and even security=. You can be agnostic of the column types instead of needing to care whether you need string delimiters or not and other literal specifics, a variable has a type just like a column has and that way column=?variable works without further thinking about how which type has to be written in source code. The variable just has to be present and has to have the correct type of value when you SQLEXEC.

So in the end, you can do a very flexible (parameterized query with static query code. That way Textmerge is even obsolete, but one thing you can't parameterize is the table name. Here Textmerge is the valid option. Therefore I also used it that way. If you indeed have a variable called TBL_Logbook with the value "TBL_Logbook", you could also rethink your original code, it's a bit like having a variable called "one" to store 1 to it and only 1 and use it in calculations, you use constants for that, but constants won't macro substitute. You just incidentally circumvent a TEXT...ENDTEXT problem with it, too: TEXT...ENDTEXT is not translating constant names to their #DEFINEd values, also not in textmerge delimiters. That means you did something right by doing it wrong.

Anyway, good it works for now. Maybe other tasks are more pressing than bringing this to updatable SQL passthrough cursors.

Bye, Olaf.

 

[David Higgs]

Olaf Doschke (Programmer)12 May 18 10:31

<<>> is doing the opposite, inside it merges the constant text with the evaluated expression, eg the name of the table stored in a variable TBL_Logbook. Your usage of that variable name via &TBL_Logbook suggests that.

I misread the contents of “Textmerge Off” for << >>. I should have known that because during my evaluation of your code I temporarily disabled “NOSHOW” so that I could see what was happening. As you surmised the Table Name is stored in variable TBL_Logbook. The Table name is dependent on which Logbook has been selected at run time.

Anyway, good it works for now. Maybe other tasks are more pressing than bringing this to updatable sql passthrough cursors.

Having followed the various threads about (Not) declaring PUBLIC Variables I have been spending some time looking at my code with a view of removing as many PUBLIC Variables as I could. When I moved from dBASE IV to FoxPro if I had an issue with “Variable not found” I would take the easy option and declare it PUBLIC. I am embarrassed to admit the total number of variables I had used but I pleased to say that I have reduced them to less than a handful by mainly using CURSORS. So maybe the way to further develop my update code would be to use cursors.

I am also looking at other additions to my application which will involve some Excel Automation so I will be kept busy!

Regards,

David.

Recreational user of VFP.

 

[Olaf Doschke]

Just by the way the whole thing the TEXT...ENDTEXT construct is ding is called textmerge, therefore it is weird you need to specify the TEXTMERGE as option quite like the NOSHOW switch should rather be opposite, you'd rather expect switches NOTEXTMERGE and SHOW.

Well, originally it was intended for screen output, perhaps, rather like \ and \\ commands. There also is a Textmerge() function, which is useful for smaller cases but also, if your inner TEXT...ENDTEXT template text is in a memo.

From today's perspective not using the SCREEN as legacy VFP did and using Winforms, the more natural ways of TEXT...ENDTEXT is into a variable not showing on screen and typically including textmerging, which in detail as said is merging in data into the text at the placeholders between textmerge delimiters. The ability to use TEXT...ENDTEXT without Textmerge would only make use of easily writing out a multi-line string, and I prefer that to writing it out in normal string delimiters, which actually also works.

The ADDITIVE switch also is fine, imagine a TEXT...ENDTEXT block merging data into a HTML table <tr>...</tr> row. Of course with a simple assignment that's also possible vy var = var + Textmerge(...), but overall I consider TEXT...ENDTEXT better in the source code readability.

Okay, enough said.

Bye, Olaf.