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
   }
   }}