B4J Question B4J Sinhala Unicode issue

[Kasun Kanchana]

I have a MySQL database in which some columns contain "Sinhala Unicode" texts. I'm using the JDBCSQL Library to connect this MySQL database with my B4J application.

Here is the table structure:

CREATE TABLE `tbl_tax_arpa_acres` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `arpa` text CHARACTER SET utf8 COLLATE utf8_sinhala_ci DEFAULT NULL,
  `arpa_agent` text CHARACTER SET utf8 COLLATE utf8_sinhala_ci DEFAULT NULL,
  `paddy_acres` double(10,2) DEFAULT NULL,
  `high_land_acres` double(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I have changed the database character set to 'utf8' and the collation to 'utf8_unicode_ci'. My jdbcURL is configured as follows:

Private jdbcUrl As String = "jdbc:mysql://serverIP/mydatabase?characterEncoding=utf8&autoReconnect=true"

When I run the app, the log displays the Unicode characters correctly, but the labels, TextFields, etc., show characters like "හපුà¶à¶½à·š."

Why is this happening?

 

[Erel]

Set the label text with label1.Text = "text here with Sinhala"
Does it show properly? If not then you need to change to a different font.

If it does then store the db result in a text file and check its content with Notepad++.

 

[Kasun Kanchana]

 

[Erel]

This means that the problem is in the data stored in the db or the extraction of this data. Check the db content with PhpMyAdmin or a similar tool.

 

[Kasun Kanchana]

I have checked the database using Navicat, and it looks okay to me. You can see that the log is showing the Unicode correctly, but the label is different.

This is the code I've used

Sub getARPAacres As ResumableSub
    Dim SenderFilter As Object = connection.mysql.ExecQueryAsync("SQL", "SELECT * FROM tbl_tax_arpa_acres", Null)
    Wait For (SenderFilter) SQL_QueryComplete (Success As Boolean, rs As JdbcResultSet)
    If Success Then
        Do While rs.NextRow
            lbl_high_land.Text=rs.GetString("arpa_agent")
            Log(rs.GetString("arpa_agent"))
        Loop
        rs.Close
        Return True
    Else
        Log(LastException)
        Return False
    End If
End Sub

and the connection

    Private jdbcUrl As String = "jdbc:mysql://serverIP/database?useUnicode=yes&haracterEncoding=utf-8&autoReconnect=true"

Database structure,

CREATE TABLE `tbl_tax_arpa_acres` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `arpa` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
  `arpa_agent` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
  `paddy_acres` double(10,2) DEFAULT NULL,
  `high_land_acres` double(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

 

[Erel]

Dim s As String = rs.GetString("arpa_agent")
File.WriteString(File.DirApp, "test.txt", s)

Upload the file. Don't modify it.

 

[Kasun Kanchana]

Output

 

[Erel]

You are missing a 'c': Private jdbcUrl As String = "jdbc:mysql://serverIP/database?useUnicode=yes&haracterEncoding=utf-8&autoReconnect=true"

 

[Kasun Kanchana]

Thank you for pointing it out.

I have changed it to

    Private jdbcUrl As String = "jdbc:mysql://serverIP/Database?characterEncoding=utf8&useUnicode=yes"

but still no changes.

Anyway. Thank you for your help

 

[Erel]

The problem is with the data extraction.

Try it with:

   Private jdbcUrl As String = "jdbc:mysql://serverIP/Database?characterEncoding=UTF-8

And make sure that the table and db encoding are also UTF-8

 

[teddybear]

I think perhaps you need to recreate the table tbl_tax_arpa_acres.
Try this sql:

CREATE TABLE `tbl_tax_arpa_acres` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `arpa` varchar(100) DEFAULT NULL,
  `arpa_agent` varchar(100) DEFAULT NULL,
  `paddy_acres` double(10,2) DEFAULT NULL,
  `high_land_acres` double(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

 

[Erel]

Good catch @teddybear

utf8_unicode_ci = https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8.html
Deprecated and should never be used. It doesn't support code points larger than 65K.

 

[Kasun Kanchana]

Thank you @Erel and @teddybear

It's working now!