QSqlQuery text encoding change MySQL4 -> MySQL5

[theophilus]

Hi,

We've just updated our software platform from CentOS 4 to CentOS 5, which entails an upgrade of MySQL from 4.1 to 5.0. After this upgrade I am unable to convert text retrieved from the database back into utf-8. The problem occurs in both Qt 4.3.5 and 4.6.0.

Details:
* The database column is varchar(100) and database is MyISAM with default charset = latin1.
* The text data is encoded in utf-8 before it's ingested into the database with the MySQL CLI.
* Querying the text in the MySQL CLI displays the correct text (arabic, hindi, etc).

On CentOS 4, with MySQL 4.1 and Qt4.x, the following code retrieves the text correctly:

QTextCodec* codec = QTextCodec::codecForName("utf-8");
QString name  = codec->toUnicode(query.value(1).toByteArray());

QTextCodec* codec = QTextCodec::codecForName("utf-8");
QString name  = codec->toUnicode(query.value(1).toByteArray());

To copy to clipboard, switch view to plain text mode 

The same code on CentOS 5, with MySQL 5 and Qt 4.x returns garbled text for all non-latin1 characters. Please help.

Best regards,
John

[theophilus]

Hi guys,

The problem is not solved, but I've made a test db, a test program and a perl script for testing which gives some interesting results. All test programs and test results are available in the attached zip file.

1) When querying the database with the perl script, I get the correct results both on CentOS4 with MySQL 4.1 and CentOS5 with MySQL 5:

% ./testquery.pl -db testquery -query "select * from station_info"
Host:     localhost
Database: testquery
Query:    select * from station_info
 
Answer: 80821 2833 35.43 -5.54 -9999 10 60101  MO  طنجة
Answer: 80822 2833 35.88 -5.31 -9999 10 60320  MO  سبتة
Answer: 80823 2379 17.09 -20.83 -9999 10 99999  MO  الكويرة
Answer: 1608 3108 49.0333 13.2333 612 1 10796  Germany  Plzeň
Answer: 1808 3198 50.6833 14.0333 377 1 11502  Czech Republic  ÚstÃ* n. L.
Answer: 1814 3108 48.9333 14.45 432 1 11541  Czech Republic  Č. Budějovice

% ./testquery.pl -db testquery -query "select * from station_info"
Host:     localhost
Database: testquery
Query:    select * from station_info

Answer: 80821 2833 35.43 -5.54 -9999 10 60101  MO  طنجة
Answer: 80822 2833 35.88 -5.31 -9999 10 60320  MO  سبتة
Answer: 80823 2379 17.09 -20.83 -9999 10 99999  MO  الكويرة
Answer: 1608 3108 49.0333 13.2333 612 1 10796  Germany  Plzeň
Answer: 1808 3198 50.6833 14.0333 377 1 11502  Czech Republic  ÚstÃ* n. L.
Answer: 1814 3108 48.9333 14.45 432 1 11541  Czech Republic  Č. Budějovice

To copy to clipboard, switch view to plain text mode 

Hence, I don't think there is anything wrong with the database.

2) When running the qt program I get different results in the last column. When running the test program on CentOS4 w/ MySQL 4.1, I get:
But when running the program on CentOS5 w/MySQL 5, I get

Both platforms are using Qt 4.3.5. I get the same faulty last colum on CentOS5 w/MySQL 5 when using Qt 4.6.

Here's the code of the the test program:

#include <QtGui>
#include <QtSql>
 
bool createConnection()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("localhost");
    db.setDatabaseName("testquery");
    db.setUserName("guest");
    db.setPassword("guest");
 
    if (!db.open()) {
        QMessageBox::critical(0, qApp->tr("Cannot open database"),
                              qApp->tr("Unable to establish a database connection.\n"
                                       "This example needs MySQL support. Please read "
                                       "the Qt SQL driver documentation for information how "
                                       "to build it.\n\n"
                                       "Click Cancel to exit."), QMessageBox::Cancel);
        return false;
    }
    return true;
}
 
void populateTableFromDatabase(QTableWidget* table)
{
    QTextCodec* codec = QTextCodec::codecForName("utf-8");
    QString cmd("SELECT id, country, placename FROM station_info");
    int i = 0;
 
    QSqlQuery query(cmd);
    if( query.isActive() )
    {
        table->setRowCount(query.size());
        table->setColumnCount(4);
        QStringList labels;
        labels << "ID" << "Country" << "From Latin1" << "From UTF-8";
        table->setHorizontalHeaderLabels(labels);
 
        while ( query.next() )
        {
            double id = query.value(0).toInt();
            QString country = query.value(1).toString();
            QString placename1 = query.value(2).toString();
            QString placename2 = codec->toUnicode(query.value(2).toByteArray());
 
            table->setItem(i,0,new QTableWidgetItem(QString::number(id)));
            table->setItem(i,1,new QTableWidgetItem(country));
            table->setItem(i,2,new QTableWidgetItem(placename1));
            table->setItem(i,3,new QTableWidgetItem(placename2));
 
            i++;
        }
    }
}
 
 
 int main(int argc, char *argv[])
 {
     QApplication app(argc, argv);
     if (!createConnection())
         return 1;
 
     QHBoxLayout *hbox = new QHBoxLayout();
     QTableWidget *table = new QTableWidget();
     populateTableFromDatabase(table);
     hbox->addWidget(table);
 
     QWidget widget;
     widget.setLayout(hbox);
     widget.setWindowTitle("testquery: MySQL 5");
     widget.resize(450,240);
     widget.show();
 
     return app.exec();
 }

#include <QtGui>
#include <QtSql>

bool createConnection()
{
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("localhost");
    db.setDatabaseName("testquery");
    db.setUserName("guest");
    db.setPassword("guest");

    if (!db.open()) {
        QMessageBox::critical(0, qApp->tr("Cannot open database"),
                              qApp->tr("Unable to establish a database connection.\n"
                                       "This example needs MySQL support. Please read "
                                       "the Qt SQL driver documentation for information how "
                                       "to build it.\n\n"
                                       "Click Cancel to exit."), QMessageBox::Cancel);
        return false;
    }
    return true;
}

void populateTableFromDatabase(QTableWidget* table)
{
    QTextCodec* codec = QTextCodec::codecForName("utf-8");
    QString cmd("SELECT id, country, placename FROM station_info");
    int i = 0;

    QSqlQuery query(cmd);
    if( query.isActive() )
    {
        table->setRowCount(query.size());
        table->setColumnCount(4);
        QStringList labels;
        labels << "ID" << "Country" << "From Latin1" << "From UTF-8";
        table->setHorizontalHeaderLabels(labels);

        while ( query.next() )
        {
            double id = query.value(0).toInt();
            QString country = query.value(1).toString();
            QString placename1 = query.value(2).toString();
            QString placename2 = codec->toUnicode(query.value(2).toByteArray());

            table->setItem(i,0,new QTableWidgetItem(QString::number(id)));
            table->setItem(i,1,new QTableWidgetItem(country));
            table->setItem(i,2,new QTableWidgetItem(placename1));
            table->setItem(i,3,new QTableWidgetItem(placename2));

            i++;
        }
    }
}


 int main(int argc, char *argv[])
 {
     QApplication app(argc, argv);
     if (!createConnection())
         return 1;

     QHBoxLayout *hbox = new QHBoxLayout();
     QTableWidget *table = new QTableWidget();
     populateTableFromDatabase(table);
     hbox->addWidget(table);

     QWidget widget;
     widget.setLayout(hbox);
     widget.setWindowTitle("testquery: MySQL 5");
     widget.resize(450,240);
     widget.show();

     return app.exec();
 }

To copy to clipboard, switch view to plain text mode 

Thanks in advance for any help or tips

Best regards,
John