Prepared QSqlQuery doesn't execute as expected.

trumperycurl

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

jsulm

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

trumperycurl

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.