8

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?

smsware
  • 429
  • 1
  • 13
  • 41
  • I'm not familiar with `QODBC`, but have you read http://doc.qt.io/qt-5/sql-driver.html#qodbc? – TrebledJ Nov 22 '18 at 13:46
  • @TrebuchetMS do you have something specific in mind? I can see no helpful information there... ;c – smsware Nov 22 '18 at 14:27
  • Mmm... maybe just a typo in the question, but: `QMap((":vid", QVariant(int, 1)))` reads `:vip` instead of `:pid` as in the first code snippet... is that correct? – cbuchart Nov 25 '18 at 21:37
  • @cbuchart yeah, just a typo, it's a shortcut of "parent_id" and in my app the parent is named "venue" so "venue_id" is "vid", that's all. It's not the problem, the app is working if connected with QMySQL driver. – smsware Nov 26 '18 at 14:20
  • With MSVC /64 Qt kit, using MySQL ODBC 64 bit driver, both _named_ and _positional_ placeholders work just fine. – Mohammad Kanan Nov 26 '18 at 15:52
  • @smsware Have you tried to use `QODBC3` instead of `QODBC`? Or you can try this: `q.bindValue(":pid", QVariant(1));` – Shtol Krakov Nov 26 '18 at 17:04

3 Answers3

0

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.

Serhiy Kulish
  • 1,057
  • 1
  • 6
  • 8
  • it changed the output of boundValues() to QMap((":a", QVariant(int, 1))) but it still doesn't work... I can see it was prepared and executed on my server's general log but I got no rows on my app's side. Actually, using ?s was the first thing I did. ;c – smsware Nov 21 '18 at 19:27
0

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.

Benjamin T
  • 8,120
  • 20
  • 37
  • hasFeature returns true and all the DLLs (checked with ListDLLs) used by the app comes from C:\Qt\5.11.2\msvc2017_64, C:\Windows\System32 and C:\Program Files\MySQL\Connector ODBC 8.0 so it should be okay... – smsware Nov 29 '18 at 17:08
0

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
   }
   }}
EngTiyeb
  • 38
  • 8