How to correctly insert non-ascii characters in a MySQL database

[hliljegren]

Is there any possibility in this universe to insert non-ascii data correctly into a field in a MySQL database?

If I have a table mytable in "myDB" with a field "content" The field is set to utf8mb4_general_ci and try the following code:

Code: Select all

   put revOpenDatabase("mysql", "localhost","MyDB", "myUser", "mypassword") into tDBid
   put "How can I insert ÅÄÖ correctly??" into tStr
   put "INSERT into mytable (content) VALUES (:1)" into tSQL
   revExecuteSQL tDBid, tSQL, "tStr"
   revCloseDatabase tDBid

The data will not show up correctly in the database (when inspecting via mysql or phpmyadmin or any other database tool)
I think I have tried every permutation of textEncode or textDecode, like
put textEncode(tStr, "uff-8") into tStr
before calling revExecuteSQL but whatever combination I try the data in the database is NOT correct.

Any suggestions!

[paul@researchware.com]

The encoding of the SQL database has to match what encoding you are using with textEncode before storing in the database. See https://dev.mysql.com/doc/refman/8.0/en/charset.html for the database side.

[hliljegren]

Thanks for the reply!

The encoding of the SQL database has to match what encoding you are using with textEncode before storing in the database.

Yes, but according to that document, utf8mb4_general_ci is using the charset utf8mb4, thus my (obviously faulty!) idea was to do a

Code: Select all

textEncode(myStr, "uff-8")

before saving into the database. But this doesn't work! They still doesn't save correctly in the database:



First line saved from within phpmyadmin
second line saved using the code:

Code: Select all

   put "ÅÄÖ är större än åäö" into tStr
   put textEncode(tStr, "utf-8") into tStr
   put "INSERT into utftest (content) VALUES (:1)" into tSQL
   revExecuteSQL tDBid, tSQL, "tStr"

Doing the following in PHP works perfectly:

Code: Select all

// Establish database connection
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $content = "ÅÄÖ är större än åäö";

    // Prepare and execute the SQL statement
    $stmt = $conn->prepare("INSERT INTO utftest (content) VALUES (:content)");
    $stmt->bindParam(':content', $content);
    $stmt->execute();

[paul@researchware.com]

I will speculate that there may be a difference between "utf8" in Livecode and "utf8mb4_general_ci" and a collation for a character field in mySQL?

I set my collations for all varchar or text fields as "utf8_general_ci" and use textEncode with "utf8" before posting to the database. When I read data back into Livecode, I perform a textDecode from "utf8" to get it back into LC "native" (16 bit unicode) text.

This works for me. I have not even tried "utf8mb4_general_ci" and, I'm sorry to say, don't even know what the "mb4" part of the name means. Sorry I could not be of more help.

[hliljegren]

the mb4 says that it uses up to four bytes for a character and if I try to set my collation to utf8_general_ci my database won't allow that as that is deprecated. It was the same as utf8mb3 if I remember correctly, and that used up to three bytes for a character which is not how utf8 works anymore. So this starts to look like a bug in my opinion...
I'll file a bug report…