Please nominate your Qt Champions for 2021! https://forum.qt.io/topic/132134/looking-for-the-2021-qt-champions

Prepared QSqlQuery doesn't execute as expected.



  • Hello everyone,

    I'm trying to do a few things with Qt and Sql in C++ and while most things are functioning as intended one is not. I tried searching the internet for information but no matter what I tried it didn't work, so I figured I'd ask here.

    To explain the idea, I have a GUI, which is supposed to get some data from a database table, create a new column, if it doesn't exist, and finally update the values of this column accordingly.

    I managed to create the column successfully, but the query I'm using to update the values seems to fail:

    QString tmp;
    QSqlQuery que;
    float percent = 12;
    
    que.prepare("UPDATE tbl_1 SET Percent= :percent WHERE ID= :id");
    que.bindValue(":percent", QString::number(percent));
    tmp.append("'");
    tmp.append("6212080BR0000090");
    tmp.append("'");
    que.bindValue(":id", tmp);
    

    I've used similar code successfully but in this case when I check the table after execution I see no change.

    I tried using the function below to retrieve the last executed message just for debugging purposes but everything seems fine and the output is exacly what I expected it to be.

    Function:

    QString Traffic::getLastExecutedQuery(const QSqlQuery& query)
    {
    	QString str = query.lastQuery();
    	QMapIterator<QString, QVariant> it(query.boundValues());
    	while (it.hasNext())
    	{
    		it.next();
    		str.replace(it.key(), it.value().toString());
    	}
    	return str;
    }
    

    I tried using the same query without binding the values and instead subsituted the placeholders with the values they're supposed to receive and it worked immeadiately. I also tried removing the where statement and it also worked. Therefore I would conclude that the problems lies with the ID placeholder. The problem, I think, is that the value is supposed to be encapsulated in quotation marks, which I can't place in the prepared statement, since then the value is not binded at all. Instead I tried just putting them into the string of the value itself, but that obviously didn't yield the result I was hoping for.

    I'm currently a bit lost, so any help is appreciated.

    Cheers


  • Lifetime Qt Champion

    @trumperycurl What does https://doc.qt.io/qt-5/qsqlquery.html#lastError tell you?
    Also, why do you put the ID in ''?


  • Lifetime Qt Champion

    @trumperycurl What does https://doc.qt.io/qt-5/qsqlquery.html#lastError tell you?
    Also, why do you put the ID in ''?



  • Hi @jsulm,

    thanks for the reply.

    I used the quotes because on my other tool, which I use to check my database and try some new stuff (DB Browser for SQLite) the query only worked with quotes so I presumed I had to include them, but actually removing them solved the problem.

    Kind of a stupid mistake on my side, but it happens I suppose.


Log in to reply