QSqlQuery::prepare + MySQL ODBC Connector

Question

I was using Qt's MySQL driver with 32bit MinGW Qt. This was working:

QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
db.setDatabaseName("MyDatabase");
//SETUP
if (db.open) {
    QSqlQuery q;
    if (q.prepare("SELECT id FROM Things WHERE parent_id = :pid")) {
        q.bindValue(":pid", 1);
        qDebug() << boundValues();
        if (q.exec) {
            //DO STUFF
}   }   }

But now that I'm using 64bit MSVS Qt, I need to use MySQL ODBC Connector. I've set it up and changed the code to reflect that:

QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
db.setDatabaseName("Driver={MySQL ODBC 8.0 Unicode Driver};DATABASE=MyDatabase;");

That's all I did. SELECT statements without WHERE clause are working as expected and I can manipulate the database via QSqlTableModel like before.

It's just that the binding stopped working... I mean the bound value is there and qDebug returns that:

QMap((":pid", QVariant(int, 1)))

but now the query returns no rows after the exec; but also no errors... this also works:

q.prepare(QString("SELECT id FROM Things WHERE parent_id = '%1'").arg(1))

Any help?

Answer 1

MySQL 5 introduces stored procedure support at the SQL level, but no API to control IN, OUT, and INOUT parameters. Therefore, parameters have to be set and read using SQL commands instead of QSqlQuery::bindValue().

Try to avoid binding http://doc.qt.io/qt-5/sql-driver.html, use procedures or add your params dynamically:

  public void mtd(int param)
   {
   if (q.prepare("SELECT id FROM Things WHERE 
        parent_id ='"+param+"'")) {
       if (q.exec) {
        //DO STUFF
   }
   }}

Answer 2

For me always works unnamed parameters in QSqlQuery. For example:

if (db.open) {
    QSqlQuery q;
    if (q.prepare("SELECT id FROM Things WHERE parent_id = ?")) {
        q.bindValue(0, 1);
        if (q.exec) {
            //DO STUFF
}   }   }

Tested with MySql (Linux), ODBC (mingw), QSqlite.

Answer 3

The last time I had a similar issue of prepared queries not working, it was because of an old database driver.

Basically some 3rd-party program put an old mysql.dll in my PATH. When my Qt application was running the old DLL was loaded instead of the newer one. The difference between the old and the new version was enough to make Qt fail to prepare queries.

So I recommend you check your software is loading the correct versions of your database related DLLs.

Also not all Qt drivers support prepared queries, so you should check if QSqlDriver::hasFeature(QSqlDriver::PreparedQueries) returns true for the driver you use.

Note that in my case QSqlDriver::hasFeature(QSqlDriver::PreparedQueries) did return true, because the expected version of the DLL was supposed to support prepared queries.