B4J Question B4J Sinhala Unicode issue

Kasun Kanchana · Sep 4, 2023

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:

MYSQL:

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:

jdbc url:

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?

teddybear · Sep 7, 2023

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

B4X:

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 · Sep 5, 2023

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 · Sep 5, 2023

Erel · Sep 6, 2023

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 · Sep 6, 2023

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

B4X:

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

B4X:

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

Database structure,

B4X:

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 · Sep 6, 2023

B4X:

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

Upload the file. Don't modify it.

Kasun Kanchana · Sep 6, 2023

Output

Erel · Sep 6, 2023

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

Kasun Kanchana · Sep 6, 2023

Thank you for pointing it out.

I have changed it to

B4X:

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

but still no changes.

Anyway. Thank you for your help

Erel · Sep 7, 2023

The problem is with the data extraction.

Try it with:

B4X:

   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 · Sep 7, 2023

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

B4X:

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 · Sep 7, 2023

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 · Sep 10, 2023

Thank you @Erel and @teddybear

It's working now!

B4J Question B4J Sinhala Unicode issue

Kasun Kanchana

Member

teddybear

Erel

B4X founder

Kasun Kanchana

Member

Erel

B4X founder

Kasun Kanchana

Member

Erel

B4X founder

Kasun Kanchana

Member

Attachments

Erel

B4X founder

Kasun Kanchana

Member

Erel

B4X founder

teddybear

Well-Known Member

Erel

B4X founder

Kasun Kanchana

Member

Similar Threads

B4J Question B4J Sinhala Unicode issue

Member

B4X founder

Member

B4X founder

Member

B4X founder

Member

Attachments

B4X founder

Member

B4X founder

Well-Known Member

B4X founder

Member

Similar Threads

Privacy & Transparency

Privacy & Transparency