Updating a MySQL Database after Text Fields are Changed

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
montymay
Posts: 145
Joined: Thu Jul 18, 2013 5:23 am

Updating a MySQL Database after Text Fields are Changed

Post by montymay » Sun Mar 24, 2019 9:12 am

Hello

My problem is similar to the original problem in this thread viewtopic.php?f=9&t=10022&p=178062#p178062, so I posted a reply there, but the thread didn't appear at the top of the forum, so I am reposting here.

Rather than a datagrid, I am having no success in updating the MySQL database that populates the first text field of each group of six groups within a containing group named "digest." My database is a MySQL database accessed via WAMP. I definitely made a connection to my MySQL database. The following code works when performing a single updating operation on my MySQL database, for example:

Code: Select all

  
on closecard
   	put fld "id" into tID
   	put fld "judge" into tJudge
   	put "UPDATE MyTable SET judge = '"&tJudge&"' WHERE id = '"&tID&"'" into tSQL
   	revExecuteSQL gCourts_DatabaseID, tSQL
end closecard
The following repeat loop worked when the database was sqlite, but not on the MySQL database. It has no effect when I change a text field and close the card. (Explanation: the name of the field, the name of the owning group, and the name of the database field are the same.)

Code: Select all

   
on closecard
   	repeat with x = 1 to 6
      	put the name of grp x of grp "digest" into tField[x]
      	put fld 1 of grp x of grp "digest" into tNewText[x]
      	put "UPDATE MyTable SET '"&tField[x]&"' = '"&tNewText[x]&"' WHERE id='"&tID&"'" into tSQL
      	revExecuteSQL gCourts_DatabaseID, tSQL
	end repeat
end closecard
 
Could it be the single quote/double quote syntax? I noticed that the query is written differently from the example in the other thread:
AccountManager=" & "'" & (item 2 of line y of tUpgrade) & "'" & "
Changing the syntax to this pattern did not work for me either. Is the SQL code the same for both SQLite and MySQL in looping routines? Thanks for any replies. Sorry for the double posting, but it seems that my first post would never be noticed because the topic does not appear at the top of the list.

Monty

bangkok
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 937
Joined: Fri Aug 15, 2008 7:15 am

Re: Updating a MySQL Database after Text Fields are Changed

Post by bangkok » Sun Mar 24, 2019 9:33 am

First : tID is not defined in your closecard script with the loop.

on closecard
repeat with x = 1 to 6
put the name of grp x of grp "digest" into tField[x]
put fld 1 of grp x of grp "digest" into tNewText[x]
put "UPDATE MyTable SET '"&tField[x]&"' = '"&tNewText[x]&"' WHERE id='"&tID&"'" into tSQL
revExecuteSQL gCourts_DatabaseID, tSQL
end repeat
end closecard
Second : it's always handy to "see" the final SQL query you're sending to the server.

And third : check the result variable for any SQL problem.


Code: Select all

on closecard
   	repeat with x = 1 to 6

 ---- define tID

      	put the name of grp x of grp "digest" into tField[x]
      	put fld 1 of grp x of grp "digest" into tNewText[x]
      	put "UPDATE MyTable SET '"&tField[x]&"' = '"&tNewText[x]&"' WHERE id='"&[b]tID[/b]&"'" into tSQL

answer tSQL

      	revExecuteSQL gCourts_DatabaseID, tSQL

put the result into tResult
if tResult is not a number then
answer error "SQL error :"&tResult
exit to top
end if
	end repeat
end closecard

AxWald
Posts: 578
Joined: Thu Mar 06, 2014 2:57 pm

Re: Updating a MySQL Database after Text Fields are Changed

Post by AxWald » Sun Mar 24, 2019 9:46 am

Hi,

besides the above:

Setting the field name in quotes makes MySQL think it's not a field name, but a text ...

Code: Select all

UPDATE MyTable SET fieldName = 'fieldValue' WHERE id='1'
will work. But:

Code: Select all

UPDATE MyTable SET 'fieldName' = 'fieldValue' WHERE id='1'
will return an error.

If you need to designate table- or field names (because there's spaces in it), use ` (accent grave, ASCII 96):

Code: Select all

UPDATE `MyTable` SET `fieldName` = 'fieldValue' WHERE id=1
And you need quotes only for text, not for numerics (id should be an integer ...).

Have fun!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!

montymay
Posts: 145
Joined: Thu Jul 18, 2013 5:23 am

Re: Updating a MySQL Database after Text Fields are Changed

Post by montymay » Sun Mar 24, 2019 10:22 am

Thank you, bangkok and AxWald, for your replies.

The tID variable was populated earlier in the same handler, so I assume that it need not be put in the loop.

The problem seems be with the use of quotes around the field name. Removing both enclosing single and double quotes around the tField[x] array variable threw an error for me, but removing the enclosing single quotes around the array variable so that it read "&tFIeld[x]&" worked! I don't yet understand why but I will go with this solution. Maybe the particular version of my MySQL database that came with WAMP has something to do with it.)

The tID variable is indeed an integer but removing the enclosing single and double quotes threw an error, but leaving as is let the script work.

Checking tSQL and tResult was very helpful and I should have remembered to try it because I used the technique before.

So thanks again for your very prompt replies.

Best wishes

Monty

Post Reply

Return to “Databases”