Encoding problem on MySQL


I’m having a bit of a problem with dealing with encoding with a MySQL database. My first language is Spanish as my name probably gives away and I need to save and retrieve data with tildes and the letter.

At the current time my app is actually writing and reading the tildes and the correctly but the thing is that I’m getting funky characters when I inspect the tables in MySQLWorkbench or DBeaver where instead of an or characters with tilde some funky characters are being displayed, for instance:

Instead of Mara Jos I get María José

The DB charset is utf8mb4 and the Collation is utf8mb4_spanish2_ci

This is some of the code I’m using to save to the DB:

var pv() as Variant // I'm trying to be api 2.0 compliant and use var as a good boy :-P
pv.Append(self.nombres) // This code is in a class and nombres is type Text
Session.db.ExecuteSQL(sql, pv)

For reading the data from the DB I’m doing this:

self.nombres = rs.Column("NOMBRES").StringValue.DefineEncoding(Encodings.UTF8).ToText

This code is actually working OK within my app, but I would like to be able to do some queries from within the DB GUI manager and also be able to export the data without it having funky characters instead of the correct characters or to rephrase what I’m saying: I would like to be able to save, read and be able to work with the proper characters from both within my app and MySQLWorkbench.

Any help is much appreciated!

To avoid problems you should allways use
charset: UTF-8 Unicode (utf8)
collation: utf8_unicode_ci

You can try to convert:

alter table TABLE_NAME convert to character set utf8 collate utf8_unicode_ci;

Allways use Define Names when connecting to the server.

db.SQLExecute("SET NAMES utf8;")

And finally, old Workbench only can use Latin1, so, be sure to use a recent version

Thanks Ivan!

That did the trick. This issue had been pounding my head for various days. Cyber high five to you!

You might want to have a look at this article just for some background info.

Thanks Wayne!