Android Question MySQL via JDBC and Special Charter (UTF8)

[mw71]

Hi,

i connect to a MySQL DB via JDBC.

unfortunately the special characters are not transferred correctly.
(i dont no if this the rigth description)

The Database is Unicode UTF8 general.


the Code (a little bit simplified):

Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.
    
    Public mysql As JdbcSQL
    Private driver As String = "com.mysql.jdbc.Driver"

    Private jdbcUrl As String = "jdbc:mysql://xxx.xxx.xxx.xxx/ABC" 
    Private Username As String = "xxx"
    Private Password As String = "xxx"

End Sub

Sub Service_Create
    DisableStrictMode
End Sub


Sub DisableStrictMode

    Dim jo As JavaObject
    jo.InitializeStatic("android.os.Build.VERSION")
    If jo.GetField("SDK_INT") > 9 Then
        Dim policy As JavaObject
        policy = policy.InitializeNewInstance("android.os.StrictMode.ThreadPolicy.Builder", Null)
        policy = policy.RunMethodJO("permitNetwork", Null).RunMethodJO("build", Null)
        Dim sm As JavaObject
        sm.InitializeStatic("android.os.StrictMode").RunMethod("setThreadPolicy", Array(policy))
    End If
End Sub

Sub Connect As ResumableSub    
    mysql.InitializeAsync("mysql", driver, jdbcUrl, Username, Password)
    Wait For MySQL_Ready (Success As Boolean)
    If Success = False Then
        Log("Check unfiltered logs for JDBC errors.")
    End If
    Return Success
End Sub

Sub CloseConnection
    mysql.Close
End Sub

Sub GetDatenDB (Referenz As String, Tabelle As String) As ResumableSub


    Dim lon As String
    Dim lat As String
    Dim name As String
    Dim cmt As String
    Dim ele As String
    Dim loc As String

    Wait For (Connect) Complete (Success As Boolean)

    If Success Then

        Dim sf As Object = mysql.ExecQueryAsync("mysql", $"SELECT * FROM `Tabelle` WHERE ref LIKE 'Referenz%'"$, Null)  
                    
        LogColor("Wait Query",Colors.Red)
        Wait For (sf) mysql_QueryComplete (Success As Boolean, Crsr As JdbcResultSet)
                
        If Success Then
            Starter.cl.lg("Query: sugcess")
        
            Do While Crsr.NextRow

                name = sb.ToString
                lon= Crsr.GetString("longitude")
                lat= Crsr.GetString("latitude")

                cmt= Crsr.GetString("name")         '<< here is the problem       
                LogColor($"${cmt} "$,Colors.Cyan)

                ele= Crsr.GetString("height")

            CloseConnection
        Else
            CloseConnection
        End If
    Else
        CloseConnection
    End If
End Sub

the problem is, i get "Höhe" from the DB, but correct (with the correct Coding at boot sides) "Höhe"

what can i do to read or convert correct?

 

[Erel]

Try to add to the url: ?characterEncoding=utf8

 

[makis_best]

If really this is your problem then....
You can simple set on your connection string "CharSet=utf8".
For example

Server=myServer;Database=myDBase;Uid=myUser;Pwd=myPass;CharSet=utf8;

 

[mw71]

hi,

Erel, i have test ist (again), unfortunately no change

jdbc:mysql://xxx.xxx.xxx.xxx/ABC?characterEncoding=utf8

if it usefull, i use this:
#AdditionalJar: mysql-connector-java-5.1.34-bin

 

[Erel]

The problem is somewhere else. Make sure that the table and database are indeed encoded with UTF8.

 

[mw71]

i am sure
the Database:
- Server: Localhost via UNIX socket
- Server-Typ: MySQL
- Server-Version: 5.7.31-0ubuntu0.18.04.1 - (Ubuntu)
- Protokoll-Version: 10
- Server-Zeichensatz: UTF-8 Unicode (utf8) <<= the Encoding of the Database

The Table (and Column) is encode in utf8_unicode_ci, type InnoDB


If there is no way to get it rigth from the Database, it there a (easy) way to change/Translate it in B4A?

 

[Erel]

Make sure that the table charset is also utf8.

It should work if the server is configured properly.

You cannot translate back string that was decoded with the wrong encoding.

 

[mw71]

the table, and the column also, charset is utf8_unicode_ci