stringvalue to text conversion

General
1

I’m getting a runtime error when converting a string value to text.

Example:
DIM EMPName as text
DIM sql as text
DIM rs as recordset
sql = “SELECT emp_name FROM emp_basic”
EMPName = rs.field(“emp_name”).stringvalue.totext
rs.close

using SQLite, this works fine since the datatype from the database is Text.
The error comes when using MySQL and the database type is VARCHAR(45)

“Unhandled xojo.Core.BadDataException
Message: String must have a known encoding”

I have not been able to find an example that shows a way to correct the encoding issue.
Please Help. Thanks

2 
EMPName = rs.field("emp_name").stringvalue.defineEncoding(Encodings.WhatEverMySQLDelivers).totext

If you want to convert the incoming data to UTF8:

EMPName = rs.field("emp_name").stringvalue.defineEncoding(Encodings.WhatEverMySQLDelivers).convertEncoding(Encodings.UTF8).totext

If you know that your MySQL server delivers UTF8:

EMPName = rs.field("emp_name").stringvalue.defineEncoding(Encodings.UTF8).totext
3

StringValue basically provides BYTES with no defined encoding
So you need to define the encoding before trying to convert it to text as Eli points out

4

I’ve tried multiple different UTF8 and Latin1 database formats on MySQL using the examples above and keep getting the same error message

5

The error is not about the database, it is a Xojo String to Text conversion issue. So the problem lies here:

... = rs.Field("emp_name").StringValue   <– this Xojo String has no encoding

So append .DefineEncoding(Encodings.UTF8).ToText to it and the error will go away:

EMPName = rs.Field("emp_name").StringValue.DefineEncoding(Encodings.UTF8).ToText
6

I understood that and I should have expanded on what I tried…

EMPName = rs.Field(“emp_name”).StringValue.DefineEncoding(Encodings.UTF8).ToText
EMPName = rs.Field(“emp_name”).StringValue.DefineEncoding(Encodings.ISOLatin1).ToText
EMPName = rs.field(“emp_name”).stringvalue.defineEncoding(Encodings.UTF8).convertEncoding(Encodings.UTF8).ToText
EMPName = rs.field(“emp_name”).stringvalue.defineEncoding(Encodings.ISOLatin1).convertEncoding(Encodings.ISOLatin1).ToText
EMPName = rs.field(“emp_name”).stringvalue.defineEncoding(Encodings.ISOLatin1).convertEncoding(Encodings.UTF8).totext
EMPName = rs.field(“emp_name”).stringvalue.defineEncoding(Encodings.UTF8).convertEncoding(Encodings.ISOLatin1).ToText

7

If you’re getting this from mysql make sure it is returning utf8
Have you ever executed the mysql “SET NAMES ‘utf-8’” directive when you first connect ?

Then this should be fine EMPName = rs.field(“emp_name”).stringvalue.defineEncoding(Encodings.UTF8).ToText

8

The question is what kind of error do you have – I understood from your answer that it was:

… as you wrote in your answer:

Hence me insisting in using DefineEncoding.

9

After doing some additional investigation, of the actual code I was using the error was coming from not coming from the location I was expecting. I had been doing my coding and debugging on a windows machine. After running "SET NAMES ‘utf-8’ on the MySQL server, and getting the same error, I ran the code on OS X and the error message I received was different and gave me the line number in the code that needed changed. The error in the windows debugger was generic and coming from an event where as the OS X debugger was more specific to the exact line of code in a method.

I’m guessing that I need to report this in the feedback…