First this is a continuation from a problem in another thread https://forum.xojo.com/47787-app-json-exception/p1#p387838
I have a list box that gets populated from a MySql table. Done this a thousand times it seems. Anyway the first time running it in debug the LB does not get populated. I finally started removing fields. Turns out when I add the Title field the problem re-appears.
Looked closely at the field definition and its a varchar 200 length, the character set if utf8 and the collation is utf8_general_ci.
I tried duplicating the table with a create from select, changing the type to text, and to medium text, nothing worked.
Does anybody have an idea of what the problem is?
You dont provide your code but I had similar problems with MySQL because even if the DB is defined utf-8 you have to explicitly define the encoding when reading string values from the recordset.
I tried that with an extends I git from @Greg O’Lone .
function UTF8(extends s as String) as String
If s.encoding = Encodings.UTF8 then
If s.Encoding is Nil then
Return DefineEncoding(s, Encodings.UTF8)
Return ConvertEncoding(s, Encodings.UTF8)
Here’s the code:
[code]lsSql = "SELECT system_company.
Company Name, system_company.
Contact Person, system_company.EMail, system_manuceu.Title " + _
"FROM system_company JOIN system_manuceu ON system_company.CompanyID = system_manuceu.CompanyID " + _
"Where StatusExpires > Now() ORDER BY system_company.
Company Name ASC "
rs = Session.sesWebDB.sqlSelect(lsSql)
if Session.SesWebDB.CheckDBError(“DB Error 1225”) then return
Dim lbFirst as Boolean = True
Dim lsCompany As String = “”
dim lnCnt As Integer = 0
While not rs.eof
App.WriteLog(rs.Field(“Company Name”).StringValue.DefineEncoding( Encodings.UTF8 ))
if lsCompany <> rs.Field(“Company Name”).StringValue then
lbFirst = True
lstProviders.AddRow rs.Field(“Company Name”).StringValue.DefineEncoding( Encodings.UTF8 )
lsCompany = rs.Field(“Company Name”).StringValue.UTF8
lstProviders.AddRow( " -----")
lstProviders.Cell(lstProviders.LastIndex, 1) = rs.Field(“Title”).StringValue.UTF8
lstProviders.Cell(lstProviders.LastIndex, 2) = rs.Field(“Contact Person”).StringValue.UTF8
lstProviders.Cell(lstProviders.LastIndex, 3) = rs.Field(“Email”).StringValue.UTF8
Have you tried to check if the UTF8 is valid?
if Encodings.UTF8.IsValidData(rs.Field("Title")) then
lstProviders.Cell(lstProviders.LastIndex, 1) = rs.Field("Title").StringValue.UTF8
Thank you! It found a value “How to Read a Centrifugal Pump Curve ? Part 2 ? Variable Speed”
Now obviously the .UTF Doesn’t work. How can I guard against this in future?
You may try to defineEncoding().
How do you get the data into your database? Normally, you should clean up your data. And when showing data add the check for valid data nevertheless.
Another question is if json should be able to handle the diamond. I don’t see at first glance why Web should barf on this. And obviously, Xojo doesn’t do the sanitation, too.
I did defineEncoding.
It was only 2 Records, they were pasted in.
Thanks all Project complete!