Wrong Encoding?

[Jürgen_Wagner]

Hello,

I have a lot of experience with SQLLIGHT databases.

Now I’m trying to deal with a MariaDB database.

When I overwrite an existing data record with rs.Column(“Description”).StringValue = “Hülltext”
and then look directly into the database, the text is entered like this: “Hülltext”

So I probably have an encoding problem. All attempts in the last few hours haven’t gotten me anywhere.

If I set the text to “Hülltext” with a database tool and then read it out, I correctly get the text “Hülltext”
(text = rs.Column(“Description”).StringValue)

So now the text “Hülltext” is in the database. If I edit the RowSet and save it without changing anything, the database will say “Hülltext” again.

rs.edit
rs.SaveRow
rs.Close

Where is my mistake?

[Ian_Kennedy]

Make sure your database is defined using UTF8. Also make sure all string assigned are Defined as UTF8 before being passed to the database. Also make sure that all strings retrieved are defined as UTF8 before you do anything with them. Use DefineEncoding if they are not set correctly. For example:

var MyString as String
MyString = rs.Column(“Description”).StringValue.DefineEncoding( Encodings.UTF8 )

[Tim_Hare]

I never worry what the data looks like in some external database tool. If you define the encoding when you read it back into Xojo, you’ll get the correct results.

[Jürgen_Wagner]

@Ian_Kennedy

Make sure your database is defined using UTF8

You mean this setting?

or this ??

[Thomas_Roemert]

@Jürgen_Wagner

Now I’m trying to deal with a MariaDB database.

When working with MariaDB or MySQL I always add a “.DefineEncoding(Encodings.UTF8)” when reading or writing from/to the database.

It looks like collation settings on the server side are not always respected - maybe something in the mysql community driver…

[Emile_Schwarz]

@Jürgen_Wagner

and then look directly into the database, the text is entered like this: “Hülltext”

How do you do that ?

with a database tool
The problem doing that is… you do not know what the database tool do.

Code the write by yourself, so you know what the encoding is.

If I edit the RowSet and save it without changing anything, the database will say “Hülltext” again.
As others says: you have to set an encoding at read time (the one that you used at write time, of course).

[Jürgen_Wagner]

@Emile_Schwarz

@Jürgen_Wagner

and then look directly into the database, the text is entered like this: “Hülltext”

How do you do that ?

With phpMyAdmin or HeidiSQL

@Emile_Schwarz

Code the write by yourself, so you know what the encoding is.

I’ll try this, but this is a SQL Database, data can manipulated outside XOJO ???

I do some tests now to understand this. and report here…

[Jürgen_Wagner]

Now, when read/writes are encoded to UTF8, the Data inside XOJO is allways “Hülltext”.

A look inside with phpmyAdmin in this case looks like

Now, change the Description in myphpAdmin to “Hülltext”, Xojo read this as

with UTF8 Encoding.

If i change for a simple Test the ReadData Encoding to ISOLatin1 the XOJO read this as

I’m really confused about this…

[kevin_g]

Are you executing SET NAMES 'utf8'; after connecting to MySQL?

[Emile_Schwarz]

@Jürgen_Wagner

myphpAdmin

If this is your developent environment go ahead.

If you want to use Xojo, stop using other disturbing tools.

You will always see garbage if you do not use the correct code. I cannot advice on a development environment I do not know/use. On the other side, with Xojo, define the encoding for Read and Write to UTF8 and the text will be displayed correctly on the user screen. It may be displayed otherwise if you use, say, an hex editor. 'an hex editor who do not know UTF8…).

Is it clear now ?

[Jürgen_Wagner]

@kevin_g

Are you executing SET NAMES 'utf8'; after connecting to MySQL?

Kevin, thank you very much! Now everything works as expected. The data is now read correctly by XOJO.
That was the missing trick! YEAH