Since I installed the last release of XOJO, 2014r2, I encounter encoding issues.
I’m working on Mac OS 10.9.4, in french.
It seems that XOJO builds the application with another encoding. I tested my app with 2014r1.1 and there is no issue…
My app saves data in a MySQL database. With the previous release of XOJO, the app writes in the MySQL database with no any encoding issue, unlike the current release.
Any idea? Can I set the default encoding somewhere in XOJO?!?
I am admittedly out of my comfort zone here, but, if that were true, wouldn’t it break nearly everyone’s code who tried to build with 2014r2?
may I ask, what behavior are you seeing that leads you to believe it is an encoding issue?
Before, with the previous version of XOJO, data were saved with accents, in MySQL DB.
Now, data are saved in my DB with the encoding problem. I want to clarify that I changed «nothing» in the read/write process in the MySQL DB.
Here is how I process for read and write data:
[code]Write data:
sql = “UPDATE MyTable SET MyString=‘myStringWithAccents’ WHERE CONDITION”
db_server.SQLExecute(sql.DefineEncoding(DBEncoding))
Read data:
dim rs As RecordSet = db_server.SQLSelect(“SELECT MyString FROM MyTable WHERE CONDITION”)
myStringWithAccents = ConvertEncoding(DefineEncoding(rs.Field(“MyString”).StringValue, DBEncoding), Encodings.UTF8)
[/code]
What is DBEncoding set to ?
What is the default encoding for your mySQL database ?
Have you ever executed the mysql “SET NAMES ‘utf-8’” directive (this lets you use UTF-8 regardless of what encoding the DB is configured to use) ?
Have you recently updated the mysql engine ?
Write data:
sql = "UPDATE MyTable SET MyString='myStringWithAccents' WHERE CONDITION"
db_server.SQLExecute(sql.DefineEncoding(DBEncoding))
This is definitely wrong it should only be
sql = "UPDATE MyTable SET MyString='myStringWithAccents' WHERE CONDITION"
db_server.SQLExecute( sql )
The code you have is telling the runtime that this string you just created is in some encoding (DBEncoding) when in fact it probably is UTF-8. And that can cause problems all by itself since now the bytes will be interpreted as being in one encoding when they are in fact in another.
I would
make sure I execute “set names ‘utf-8’” immediately after connecting
never use anything but UTF-8
make sure all my reads are like
myStringWithAccents = DefineEncoding(rs.Field(“MyString”).StringValue, Encodings.UTF8)
This way regardless of what mysql is set it will ALWAYS send you data in UTF-8 and accept UTF-8 from your application.
DBEncoding is set to Encodings.ISOLatin9 and the encoding of my MySQL DB is latin1_general_ci.
I didn’t update the MySQL engine and what I don’t understant is that when I build my app with the 2014r1.1, there is no encoding issue, accents are correctly saved in the DB…
I’m not aware of any bug fixes in the mySQL plugin but there may have been fixes in string encoding code
However, I’m quite certain your code is not correct either.
Unless SQL is NIL encoded (which you can check in the debugger) this line is for sure wrong. It should just be
sql = "UPDATE MyTable SET MyString='myStringWithAccents' WHERE CONDITION"
db_server.SQLExecute( sql )
[quote=111740:@SbastienAngot]Write data:
sql = “UPDATE MyTable SET MyString=‘myStringWithAccents’ WHERE CONDITION”
db_server.SQLExecute(sql.DefineEncoding(DBEncoding))
Read data:
dim rs As RecordSet = db_server.SQLSelect(“SELECT MyString FROM MyTable WHERE CONDITION”)
myStringWithAccents = ConvertEncoding(DefineEncoding(rs.Field(“MyString”).StringValue, DBEncoding), Encodings.UTF8)[/quote]
I think when writing data to the db server, you should use ConvertEncoding, not DefineEncoding.
In addition you should use “SET NAMES …;” after opening the connection to the MySQL server. For example “SET NAMES utf8;”.
heres what I just did on my mySQL server (version 5.6.12)
create new database named ‘test’ with default encoding set to latin1
added new table (myTable) with a single column (myString as long text)
put this code in the Action event of a button (since its really simple)
[code] dim db as new MySQLCommunityServer
db.DatabaseName = “test”
db.host = “localhost”
db.Port = 3306
db.UserName = “” // note this actually has my user id which I removed
db.Password = “” // note this actually has my user password which I removed
if db.connect() then
db.SQLExecute( “set names ‘utf-8’” )
dim sql as string = "insert into MyTable( MyString ) values ('') "
db.SQLExecute( sql )
break
dim rs as recordset = db.SQLSelect( "select * from myTable" )
dim s as string = DefineEncoding( rs.Field("MyString").StringValue, Encodings.UTF8 )
break
end if
[/code]
When I run this I get the correct data in the application
Navicat however display it as àü
EVEN when I set the table & database to BE UTF-8 Navicat makes this display error
But the code works perfectly and I get back exactly what I expect
The fact is that with the previous release, 2014r1.1, my code worked perfectly and Navicat displayed the text with correct accents.
Now, impossible to save data in MySQL DB without breaking accents…
If I add the command SQLExecute(“set names ‘utf8’”), it’s worse because all data of my DB have accents and I get the error : “Illegal mix of collations”…
The main issue is that all data already saved in MySQL have accents, even in Navicat. It’s complicated to change the method now…