Search code examples
c++mysqlqtqsqlquery

QSqlQuery is not binding values

I am performing queries against a MySQL database, and use code similar to below throughout my app. But for some reason the update below says 0 rows affected, when it should be 1. On digging deeper I discovered my bindValue commands don't seem to have any effect.

        QSqlQuery* query = new QSqlQuery(m_db)
        query->prepare(QString("UPDATE companies SET "
                               "NAME=:name, "
                               "ISUSER=:isuser, "
                               "ISVAR=:isvar, "
                               "ISOEM=:isoem, "
                               "CONTACT=:contact, "
                               "EMAIL=:email, "
                               "COMMENTS=:comments "
                               "WHERE ID=:id "
                               "LIMIT 1"));
        query->bindValue(":name",rowData.name);
        query->bindValue(":isuser",rowData.isEndUser);
        query->bindValue(":isvar",rowData.isVAR);
        query->bindValue(":isoem",rowData.isOEM);
        query->bindValue(":contact",rowData.contact);
        query->bindValue(":email",rowData.email);
        query->bindValue(":comments",rowData.comments);
        query->bindValue(":id",id);
        bool queryOk = query->exec();
        if (queryOk) {
            qDebug() << query->executedQuery();
            qDebug() << query->lastQuery();
            qDebug() << query->lastError().text();
            qDebug() << rowsAffected;

There must be something different/wrong in the code above causing the output below:

"UPDATE companies SET NAME=:name, ISUSER=:isuser, ISVAR=:isvar, ISOEM=:iSOEM, CONTACT=:contact, EMAIL=:email, COMMENTS=:comments WHERE ID=:id LIMIT 1"
"UPDATE companies SET NAME=:name, ISUSER=:isuser, ISVAR=:isvar, ISOEM=:iSOEM, CONTACT=:contact, EMAIL=:email, COMMENTS=:comments WHERE ID=:id LIMIT 1"
""
0

But I can't see the problem, and the query returns no errors. Yet the query string seems to contain the variable names not substituted.

Solution

  • QSqlQuery::executedQuery() won't show you the bound values, because the idea of bound values is that they never become part of the query itself (which completely eliminates the problem of escaping them). What you see is the actual query submitted to the database. The bound values are submitted to the database alongside the query string (very much same like with QSqlQuery).

    As for the rowsAffected being zero, I don't see it being initialized or updated by the code in your example, which is likely why it says 0. you probably want to use query->numRowsAffected() instead.

    Finally (not related to any of your questions), you don't need to allocate the QSqlQuery on heap (unless you really need the query to outlive the scope in which it is created) and you can simply allocate it on stack. Fewer dynamic allocations == fewer chances of memory leaks :-)