SqlQuery one named placeholders multiple 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 idVal will be bound twice, but when this request is executed, only rows with id_parent = idVal are deleted, while id_col = idVal remains undeleted. Thus, only the second idVal was bound to the request.

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 possible to use the same placeholder name multiple times in QSqlQuery?

Answer 1

From the QSqlQuery :: bindValue () documentation :

Values ​​cannot be bound to multiple locations in a query , for example:

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

A binding to a name will be bound to the first: name, but not to the second.

The last sentence seems a little wrong, since it looks like it is tied to the second: the name, but in any case it clearly indicates that what you are trying to achieve is not supported by Qt.

Your options should follow a workaround that you already have, or use the solution provided by Mahmoud Gamal in the commentary on your question.

Answer 2

To find out which query was actually executed, you can use QSqlQuery::executedQuery() .

And you must explicitly set the values ​​for the 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(); 

It will also be useful if you need refactoring in the future.

Answer 3

Try this first:

 select * from names where :ID in ((select id_col FROM names WHERE id_col = :ID) OR (select id_parent FROM names WHERE id_parent = :ID) ); 

if the above selection returns the correct data, use the following as a query:

 delete from names where :ID in ((select id_col FROM names WHERE id_col = :ID) OR (select id_parent FROM names WHERE id_parent = :ID) );