MariaDB /MySQL returns ? instead of ë In PureBasic

[RBart]

Hi

I have a connection with my local MariaDB database. One of the columns returns "Belg?" instead of "België" in PureBasic.
The collation is "utf8_bin" already changed from "utf_general_ci".
I'm on Linux Ubuntu 22.04
In phpMyAdmin the value is "België".

result of the debugging, the value is already corrupted before using it in the application:

Belgi?
Type= 2 Naam= Land

Connection is made with:
UseMySQLDatabase()
If OpenDatabase(#Database, "host=localhost port=3306 dbname='NameDB'", "root", "",#PB_Database_MySQL)
piece of the code:

Code: Select all

 While NextDatabaseRow(#Database) ; as long as there are records
          Record$=""
          For i = 0 To DatabaseColumns(#Database) ;get columns 
             KolomType$ = Str(DatabaseColumnType(#Database, i)) 
             KolomNaam$ = DatabaseColumnName(#Database,i)
            RawValue$ = GetDatabaseString(#Database,i)
            If KolomType$= "2"  ; type = text          
                EscapedValue$ = ReplaceString(RawValue$, "ë", "ë") ; Voor voorbeeld, escape 'ë' ; this does not work because the value is already "?"                
                Debug EscapedValue$
            Else
                EscapedValue$ = RawValue$    
            EndIf
            Record$ =Record$ + Chr(10) + EscapedValue$;GetDatabaseString(#Database, i)   ;gegevens ophalen  
            
            Debug "Type= " + KolomType$ + " Naam= " + KolomNaam$            
           ; Break stops after first column
          Next         
          AddGadgetItem(#frmKlanten_lstKlanten,-1,Record$) ;lijn met gegevens toevoegen
          RecordCount.i = RecordCount.i + 1 
          Break ; stops after first record
        Wend

What am I doing wrong here?

Many thanks in advance,

Rudi

[Fred]

PB should work fine with utf8 column, did you change the column before or after the insert ?

[RBart]

It was utf8_general_c and it gave Belgi?.
PureBasic requires utf8
Couldn't select utf8 choise for utf8_bin instead. No solution.

[RBart]

It's via XAMPP 8.2.4-0
Config:
socket =/opt/lampp/var/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
default-character-set=utf8mb4
[mysqld]
user=mysql
port=3306
socket =/opt/lampp/var/mysql/mysql.sock
key_buffer=16M
max_allowed_packet=1M
table_open_cache=64
sort_buffer_size=512K
net_buffer_length=8K
read_buffer_size=256K
read_rnd_buffer_size=512K
myisam_sort_buffer_size=8M

[mk-soft]

I think (as I have read) umlauts are not allowed in the columns.

[RBart]

I think (as I have read) umlauts are not allowed in the columns.

Which columns do you mean? MariaDB or PureBasic. Where did you read it? In phpMyAdmin the columns show the correct value.

[RBart]

Temporaly solved it with:

Code: Select all

EscapedValue$ = ReplaceString(RawValue$, "?", "ë")

But this will also change any other character that is not supported. So I could make it more specific like

Code: Select all

EscapedValue$ = ReplaceString(RawValue$, "Belgi?", "België")

So this not a real solution yet.

[charvista]

Hi
Like RBart, I get the symbol ? in a black diamond instead of 'ë' for any character with an ascii value <=127 wrongly interpreted when extracting from MariaDB in localhost in PureBasic. When the characters are Japanese, Hebrew, Arabic, Emoticons,.... then they are showed as '?' in PureBasic (v6.30)
The DBeaver engine is set to charset=utf8mb4, collation=utf8mb4_unicode_520_ci
PureBasic program was launched with File Format: Encoding:Utf8
Within DBeavers, all characters are fine.
Fred asked: PB should work fine with utf8 column, did you change the column before or after the insert ?
The columns were defined with charset=utf8mb4, collation=utf8mb4_unicode_520_ci in DBeaver when creating a New Database, so the answer is: Before the insert of the columns.
I'm on Windows 11 Home, x64, PB 6.30 Final Release.
Like RBart, I open in PureBasic the database with:
UseMySQLDatabase()
If OpenDatabase(#Database, "host=localhost port=3306 dbname='NameDB'", "root", "(myPassword)",#PB_Database_MySQL)

[charvista]

ΕΥΡΗΚΑ ! (=Eurêka)

Code: Select all

UseMySQLDatabase()
MDB = OpenDatabase(#PB_Any, "host=localhost port=3306 dbname='NameDB' ","root","(myPassword)",#PB_Database_MySQL)
If MDB
    DatabaseQuery(MDB, "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_520_ci;")
    If DatabaseQuery(MDB, "SELECT * FROM mytable")

DatabaseQuery(MDB, "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_520_ci;")
is all what was needed: set the charset and collate before the first Query and you're all set up.

[RBart]

Hi Charvista

Great!

First I got Belgié but that was probabaly because I changed the charset a few times.
After changing the value in phpMyAdmin to België it fell into place.

I use:

Code: Select all

DatabaseQuery(#Database, "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_520_ci    ;") 

this would give the most acurate results.

Thank you again charvista
Greetings

[Fred]

I will see if it can be done automatically because we always expect UTF-8 in PureBasic anyway.