SqlQuery one named placeholders several times

Question

I tried

  QSqlQuery query;
  query.prepare("DELETE FROM names WHERE id_col = :ID OR id_parent = :ID");
  query.bindValue(":ID", idVal);
  query.exec();

assuming that idVal will be binded two times, but executing this query only rows with id_parent = idVal is deleted, with id_col = idVal remains undeleted. So only second time idVal was binded to the query.

When I rewrite it to

  QSqlQuery query;
  query.prepare("DELETE FROM names WHERE id_col = ? OR id_parent = ?");
  query.bindValue(0, idVal);
  query.bindValue(1, idVal);
  query.exec();

everything worked as expected.

Is it a way to use one named placeholders several time in QSqlQuery?

Answer 1

From the QSqlQuery::bindValue() documentation:

Values cannot be bound to multiple locations in the query, eg:

INSERT INTO testtable (id, name, samename) VALUES (:id, :name, :name)

Binding to name will bind to the first :name, but not the second.

The last sentence appears to be slightly wrong as it looks like it binds to the second :name, but either way, this clearly states what you are trying to achieve is not supported by Qt.

Your options are to stick with the workaround you already have, or use the solution provided by Mahmoud Gamal in the comment to your question.

Answer 2

To see what query was actually executed, you can use QSqlQuery::executedQuery().

And you should explicitly set values for placeholders:

QSqlQuery query;
query.prepare("DELETE FROM names WHERE id_col = :ID_COL OR id_parent = :ID_PAR");
query.bindValue(":ID_COL", idVal);
query.bindValue(":ID_PAR", idVal);
query.exec();

Also it will be useful if you will need refactoring in future.