[Qt-interest] problem with bindValue() in nested queries

[Tony Rietwyk]

> Sent: Tuesday, 3 January 2012 8:03 PM
> 
> Hi all,
> i have a piece of code like:
> 
> QSqlQuery query,q2;
> query.prepare("select * from table1");
> query.exec();
> while (query.next()) {
> int myid = query.record().value("id").toInt();
> 
> q2.prepare ("select * from table2 where id=:myid");
> q2.bindValue(":myid",myid); q2.exec(); // <--- this fail
> 
> }
> 
> the error i get is:
> QSqlError(-1, "QPSQL: Unable to create query", "ERROR:  operator does not
> exist: integer =?
> LINE 1: SELECT * FROM table2 WHERE id=?
>                                               ^
> HINT:  No operator matches the given name and argument type(s). You
> might need to add explicit type casts.
> ")  for query ( "SELECT * FROM table2 WHERE id=?" )
> 
> a few more points:
> *) i am sure that myid is correct: qDebug()  prints it out correctly;
> *) if i change the query like: q2.prepare("select * from table2 where
> id="+QString::number(myid)); everything works
> *) if i execute the query outside the while loop it works (test done
setting
> myid to some sensible number)
> *) "external" query does not use any kind of bound values
> *) qt is 4.7.4 on linux gentoo (amd64), db is postgresql 9.1.2 (same
platform)
> 
> any ideas?

Hi Francesco, 

I think the problem may be due to repeating the prepare unnecessarily - try
this: 

QSqlQuery query,q2;
if (!query.prepare("select * from table1"))
  // fail

if (!query.exec())
  // fail

if (!q2.prepare ("select * from table2 where id=:myid"))
  // fail

while (query.next())
{
  int myid = query.record().value("id").toInt();
  q2.bindValue(":myid",myid);
  if (!q2.exec())
     // fail
}

Note how the query shown in the error message has replaced the :myid with ?.
So it seems positional is the native form for PostGres, though the other
form should be OK. 

Hope that helps, 

Tony.