Problem with accentuated chars when updating MySQL table

[GoneToSail]

Good evening,
In a card script, I have to send the following simple query to a MySQL database (version 5.7, recently upgraded from 5.6 on my test server):

Code: Select all

put fld "AreaID" into tAreaID
put fld "RevisedData" into tRevisedData
put "UPDATE Areas SET AreaName = '"&tRevisedData&"' WHERE AreaID = '"&tAreaID&"'" into tSQL
revExecuteSQL gConnectionID, tSQL

This instruction works fine as long as I don't have any accentuated char in the field "AreaID". If field "AreaID" contains a word with an "é", I receive the following error msg:
the result=Incorrect string value: '\x8Ee' for column 'AreaName' at row 1
I guess that this error is in connection with a discrepancy in character sets, but I cannot find how to solve it !
I never had this problem when the server was running the previous version 5.6.27 !

The query is sent from LiveCode Community 7.0.6 running on an Apple Powerbook with OS X 10.10.5
The version of MySQL server is 5.7.9 - MySQL Community Server (GPL) using UTF-8 Unicode (utf8) character set.

Any idea please ?

[ghettocottage]

You might try textEncode the field contents first:

Code: Select all

put textEncode(field "AreaID","UTF-8") into tAreaID

[GoneToSail]

It works !
And I found also that I have also to use textDecode for the data retrieved from the database.

Many thanks. it was a bright light at the end of a sad week-end...