Page 1 of 1
How to correctly insert non-ascii characters in a MySQL database
Posted: Mon Mar 03, 2025 10:37 pm
by 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!
Re: How to correctly insert non-ascii characters in a MySQL database
Posted: Tue Mar 04, 2025 2:18 pm
by 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.
Re: How to correctly insert non-ascii characters in a MySQL database
Posted: Wed Mar 05, 2025 3:24 pm
by 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
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();
Re: How to correctly insert non-ascii characters in a MySQL database
Posted: Wed Mar 05, 2025 4:01 pm
by 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.
Re: How to correctly insert non-ascii characters in a MySQL database
Posted: Thu Mar 06, 2025 8:50 am
by 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…