Retrieving ♭ and ♯ characters from MariaDB in Xojo

General
1

Hello All

I have a MariaDB database containing key signatures from pieces of music - letter plus sharp or flat as in E♭, F♯, etc. My database tool (DBForge Studio) displays these just fine, and tells me that the collation is ‘utf8mb3_general_ci’. Exporting the database to a CSV and opening it in Excel shows ♭ and ♯ as expected. Excel’s UNICODE function shows 9837 for ♭ and 9839 for ♯.

I’m using the MySQLCommunityServer connector to connect to the DB, and what I assume is the ‘standard’ method of db.selectSQL into a rowset, then stepping through each row, assigning the fields to a temporary string variable, then listbox(name).addrow to populate an on-screen list box.

When I do this, both ♭ and ♯ come out as ?

What should I be checking next? Suggestions on how to get Xojo to display these characters correctly in a list box are welcome!

Thanks in advance

Steve

2

Check in the Xojo debugger to see if your temporary string variable has a UTF-8 encoding.

If it is not UTF-8 then there is possibly a bug in the Xojo plugin and you may have to use DefineEncoding to tell Xojo the string does contain UTF-8 characters.

3

Yeah, that’s correct. U+2669 through U+266F are musical symbols. That’s E2 99 A9 through E2 99 AF in UTF-8.

4

Looking at the debugger, I get original(nil) rather than UTF8. The column type is 5 - text.

Any pointers on what to do next would be appreciated!

Best regards

S

5

convertencoding your string to utf8 should be the one to go

https://documentation.xojo.com/api/text/encoding_text/convertencoding.html#convertencoding

6

After connection send this cmd in a ExecuteSQL:

SET NAMES utf8mb4;

Then retrieve some content and check if the encoding is ok

2 Likes
7

You need to use DefineEncoding when encoding is nil to make sure the encoding is right.

See the helper function described in this blog post:

Tip of the day: StringValueX for database access

Or you’d use MBS Xojo SQL Plugin, which does provide the strings with the correct encoding automatically.

The missing encoding in the MySQLCommunityServer class is a known problem for about 20 years now.

1 Like
8

As I mentioned in my original reply, you can use DefineEncoding to tell Xojo the string is UTF8.

You could also try Rick’s suggestion which is to execute the SET NAMES SQL after connecting to the database.

9

Maybe you really just need to tell Xojo the Encoding of the DB/Table, like so:

yourTempString = theData.Column("a_body").StringValue.DefineEncoding(Encodings.ISOLatin1)

or

yourTempString = theData.Column("a_body").StringValue.DefineEncoding(Encodings.UTF8)

AFAIK, if you don’t tell Xojo which encoding the RowSet/RecordSet is using, it will pull VarChar/Text/etc… with a NIL encoding.

BTW: You should ALWAYS define the encoding of Strings while reading from a 3rd party Database. SQlite might be an exception and automatically use UTF8?

10

Thanks all. A combination of

theData.Column("a_body").StringValue.DefineEncoding(Encodings.UTF8)

and

SET NAMES utf8mb4;

together produce the desired result and the sharps and flats now render correctly.

Best regards
Steve

2 Likes