MySQL UTF-8 encoding issue

[yazwas]

Hello Everyone

I have another mysql UTF-8 problem, I have a table that has some records stored in it in UTF-8 format (some records are in English, and some of them are Arabic)

my problem is that if I use php to retried the entries, they are encoded correctly and I can see both the Arabic and the English words. but I also need to use Qt to read the same entries and display them in a Qt table.

The entry in the database looks like this in UTF-8 encoding (in php)
دبي

I'm doing it and I'm getting these values.
??دب

the above line, has partially correct value, it gets 2 out of 3 characters correct, but I have NO idea why its NOT showing this correctly.

Below is my code, and I would appreciate any suggestion you have.

bool ret = query.exec("select  name_searchword from searchword where id_company = 1125");
    while(query.next())
    {
        QByteArray ba = query.value(0).toByteArray();
        QString st = QString::fromUtf8( ba.data() );
        qDebug() << "Str is :" << st;
    }

bool ret = query.exec("select  name_searchword from searchword where id_company = 1125");
    while(query.next())
    {
        QByteArray ba = query.value(0).toByteArray();
        QString st = QString::fromUtf8( ba.data() );
        qDebug() << "Str is :" << st;
    }

To copy to clipboard, switch view to plain text mode 

The output is:

Str is : دب??


I've tried to use QTextCodec::setCodecForCStrings and set the value to UTF-8, but thats ONLY made it worse.

I've also tried those 2 lines, together and one on its own, and still no results.

QTextCodec::setCodecForCStrings(QTextCodec::codecForName("UTF-8") );

QTextCodec::setCodecForCStrings(QTextCodec::codecForName("UTF-8") );

To copy to clipboard, switch view to plain text mode 

QTextCodec *codec  = QTextCodec::codecForName("UTF8");

QTextCodec *codec  = QTextCodec::codecForName("UTF8");

To copy to clipboard, switch view to plain text mode 

Thanks a lot for your help

[Lykurg]

why do you convert your result to an byte array? there you skip the informations. Just do

bool ret = query.exec("select  name_searchword from searchword where id_company = 1125");
    while(query.next())
    {
        QString st = query.value(0).toString();
        qDebug() << "Str is :" << st;
    }

bool ret = query.exec("select  name_searchword from searchword where id_company = 1125");
    while(query.next())
    {
        QString st = query.value(0).toString();
        qDebug() << "Str is :" << st;
    }

To copy to clipboard, switch view to plain text mode 

and all should work fine.

[yazwas]

Hey Lykurg

I tried this, but it gave me the following result
Str is : "?¯?¨U‰"

thankx, but what do you think is the reason?

[Lykurg]

Are you sure that your data in the database is encoded in utf8 correctly? Do you use any encoding function in php before printing it?

You can try to set utf8 explicitly after establishing the connection to your database using:

SET NAMES 'utf8';
SET CHARACTER SET 'utf8';

SET NAMES 'utf8';
SET CHARACTER SET 'utf8';

To copy to clipboard, switch view to plain text mode 

(Not sure right now if it has to be utf8 or utf-8...)

[yazwas]

I'm sure of the text is encoded correctly in UTF8, in PHP it shows if I use the following line at the beginning of the page
<?php header("Content-type: text/html; charset=utf-8");

and then I use code directly, and everything works out fine.

This is how my Qt code looks like, and I still dont have the correct result, if you can see something that I'm missing, please point it out to me.
Thanks

QSqlQuery query(m_masterDatabase);
 
    bool ret = query.exec("SET NAMES 'utf8'");
    if(ret)
        qDebug() << "true1";
    else
        qDebug() << query.lastError().text();
 
    ret = query.exec("SET CHARACTER SET 'utf8'");
    if(ret)
        qDebug() << "true2";
    else
        qDebug() << query.lastError().text();
 
    ret = query.exec("select  name_searchword from searchword where id_company = 1125");
    while(query.next())
    {
        QString st = query.value(0).toString();
        qDebug() << "Str is :" << st;
    }

QSqlQuery query(m_masterDatabase);

    bool ret = query.exec("SET NAMES 'utf8'");
    if(ret)
        qDebug() << "true1";
    else
        qDebug() << query.lastError().text();

    ret = query.exec("SET CHARACTER SET 'utf8'");
    if(ret)
        qDebug() << "true2";
    else
        qDebug() << query.lastError().text();

    ret = query.exec("select  name_searchword from searchword where id_company = 1125");
    while(query.next())
    {
        QString st = query.value(0).toString();
        qDebug() << "Str is :" << st;
    }

To copy to clipboard, switch view to plain text mode 

[Lykurg]

Well right now it seems to me, that your database in not right encoded. Your Qt is fine.

You could make a test: insert a new row to your database by using Qt. Make sure all is utf8. (use QString::fromUtf8() etc.) then querry that row and see if it is displayed correct.