Encoding troubles with 2014r2

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?!?

Perhaps it is the same like here:
https://forum.xojo.com/13896-xojo-2014-r2-mac-version-umlaute-defekt/?quote=111655#reply

Oh… German…

I understand that he saved the project in an older version of XOJO and then, opens it with the new release, with no any encoding trouble.

So I tried this trick but with no result… With the 2014r2, my encoding issue reappears.

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?

With the previous release, 2014r1.1, I build my app and then, edit a record saved in a MySQL database : no trouble.

Then, I build my app with the new release 2014r2, edit the same record and get an encoding problem : « é » instead of « é » etc.

So, I gather that this error is probably due to a change in the new release of XOJO?

I made some new tests :

  • With another app, exactly the same issue (with 2014r2, not with 2014r1.1)
  • It seems that this issue doesn’t exist with files, only with a MySQL DB.

[quote=111707:@SbastienAngot]With the previous release, 2014r1.1, I build my app and then, edit a record saved in a MySQL database : no trouble.

Then, I build my app with the new release 2014r2, edit the same record and get an encoding problem : é instead of etc.

So, I gather that this error is probably due to a change in the new release of XOJO?[/quote]

Sounds more like you read the data out and do not define the encoding
Thats the most common cause

ANY data that comes from outside your application (like from a serial port, database, file, etc) SHOULD have its encoding DEFINED (see http://documentation.xojo.com/index.php/DefineEncoding)

Looks like the string is in Unicode format but the app does not know it. You may want to go :

StringWithAccents = DefineEncoding(WrongString, Encodings.UTF8 )

This will translate “é” into “”.

My app already reads data with a defineEncoding.

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]

I hope that it’s clear…

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

  1. make sure I execute “set names ‘utf-8’” immediately after connecting
  2. never use anything but UTF-8
  3. 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.

Thanks for your help everybody!

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 )

I already tested that and no change… :frowning:

New test with a break point on the db_server.SQLExecute function.

The following command works correctly:

sql = "UPDATE MyTable SET MyString='myStringWithAccents' WHERE CONDITION" db_server.SQLExecute(sql.DefineEncoding(DBEncoding))
This one not:

sql = "UPDATE MyTable SET MyString='myStringWithAccents' WHERE CONDITION" db_server.SQLExecute(sql)

And with the newest release, both doesn’t work…

[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)

  1. create new database named ‘test’ with default encoding set to latin1
  2. added new table (myTable) with a single column (myString as long text)
  3. 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…

I’m also using Navicat and regardless of how I set the table it displays it wrong

Did you try with the previous release of XOJO?

yes
2014r1.1 and navicat gives me the exact same results

I’m using Navicat 10.1.1 Enterprise