Help with funny characters showing in a chat feature using MySQL

Hi. I have a desktop app where I am working on a simple chat feature. The conversation is saved to MySQL server, and the desktop apps read/write to the server.

I am noticing on Mac, when there is a line return either by entering this in with the text or when coded as EndOfLine, it is displaying as several of the black diamonds with the question mark in the middle. Windows looks ok with the return.

Also, with the use of an apostrophe, Mac again shows several black diamonds, and Windows shows this:
here’s the next line

When that should be “here’s the next line”

Is there a special trick to display these and any other special characters correctly?

Give this a try.

Before your database transactions, add the following line of code to tell the MySQL server to expect the content to be UTF-8 encoded.

db.SQLExecute("SET NAMES 'utf8'")

Check the text encodings.

Unless you use MBS SQL Plugin, you need to check encoding with MySQL.

e.g. with MySQLCommunityServer class you need to SQLExecute “SET NAMES utf8”.
Than all strings from MySQL need to use DefineEncoding to tell Xojo what encoding you use. Also all text going to MySQL need to go through ConvertEncoding to make sure it’s UTF-8.

Thanks for the replies! I am assuming “NAMES” is to be changed to the specific field. In this case, the field causing the issue is “Message”. Yes?

no.
And please read MySQL manual to learn what this command does.

This is the documentation page:

https://dev.mysql.com/doc/refman/5.7/en/charset-connection.html

And the relevant section:

Great thanks! I’ll give the page a read in a bit. That seemed to do the trick for the special characters, but the coded returns still show the diamonds on Mac. Is EndOfLine ok to use or should it be something like u&A

For example, I have the message area displaying with this:

txtMessageHistory.Text = txtMessageHistory.Text + "[" + data.Field("TimeStamp").StringValue + "] " + theName + " wrote:  " + data.Field("Message").StringValue + EndOfLine + EndOfLine

Since you’re now telling MySQL to give you UTF8 encoded results, try changing

data.Field("Message").StringValue

to

data.Field("Message").StringValue.DefineEncoding(Encodings.UTF8)

That will tell Xojo that the string returned by MySQL is UTF8 encoded.

If that doesn’t work, you might need to utilize ReplaceLineEndings to normalize the line endings between Windows and Mac.

Hi Jared. Couple questions

[quote=348032:@Jared Feder]Since you’re now telling MySQL to give you UTF8 encoded results, try changing

data.Field("Message").StringValue

to

data.Field("Message").StringValue.DefineEncoding(Encodings.UTF8)

That will tell Xojo that the string returned by MySQL is UTF8 encoded.[/quote]
Is this suggestion to add this during the save to the server of the message or during the display? It didn’t see to make any changes when made to the display into the TextArea. The user may enter in a carriage return in their message, so I’d like to have that covered if possible without showing the black diamonds

This seemed to work to remove the black diamond characters for the returns. However, I would still like to have some sort of carriage return. Below is the result using ReplaceLineEndings:
[2017-08-29 14:12:43] ino@vim wrote: hi
there[2017-08-29 13:56:40] You wrote: hello
again[2017-08-29 13:56:25] You wrote: hello

When I’d like it to look like:
[2017-08-29 14:12:43] ino@vim wrote: hi there
[2017-08-29 13:56:40] You wrote: hello again
[2017-08-29 13:56:25] You wrote: hello

And back to the special characters. On Windows, it seems to be acting well with the

db.SQLExecute("SET NAMES 'utf8'")

But here is the result on Mac:
[2017-08-29 14:22:29] You wrote: Ryan???s message

The data on the server is displaying the apostrophe correctly

when you GET data from the database the two bits of code Jared gave you will insure that

  1. the database returns UTF-8 data to you - db.SQLExecute("SET NAMES 'utf8'")
  2. that your running application knows the data it gets from the db is UTF-8
    txtMessageHistory.Text = txtMessageHistory.Text + "[" + data.Field("TimeStamp").StringValue.DefineEncoding(Encodings.UTF8) + "] " + theName + " wrote:  " + data.Field("Message").StringValue.DefineEncoding(Encodings.UTF8) + EndOfLine + EndOfLine

if you dont do these things you get diamonds
https://blog.xojo.com/2013/08/20/why-are-there-diamonds-in-my-user-interface/

Ahh. This made a difference! Thank you all. Really appreciate the assistance