Including field in listbox causes blank listbox

Targets Web
1

Hi,

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?

2

Hi,

You don’t 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.

3

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
Return s
End if

If s.Encoding is Nil then
Return DefineEncoding(s, Encodings.UTF8)
End if

Return ConvertEncoding(s, Encodings.UTF8)
End Function

4

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
’
'System.DebugLog
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
end
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
rs.MoveNext
’
’

wend
[/code]

5

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 else break end if

6

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?

7

You may try to defineEncoding().

8

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.

9

I did defineEncoding.

It was only 2 Records, they were pasted in.

10

Thanks all Project complete!