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

[Scott Aron Bloom]

Here is my suggestion, first, this could be a postgres issue, not a Qt
issue, so lets try to solve that..

The truth is, each SQL engine, has different requirements for nested
commands.. 

Rewrite the code so that the outer query creates a QList< int> of the
ids.  Then loop over them and get the new sub table information..

If that doesn't work, figure out why :)

If it does, then we know the inner query is good, so try using by
position binding rather than named binding, 

The issue could be, and I have seen this with some back ends, there are
settings to how many open commands per connection are allowed.. if its
set wrong or too low, you may need to finish accessing the outer tble.

Also, check the return value of the prepare statement on the inner
query... Sometimes it fails, and gives a MUCH better error message then
the exec failing..

Scott


-----Original Message-----
From: qt-interest-bounces+scott.bloom=onshorecs.com at qt.nokia.com
[mailto:qt-interest-bounces+scott.bloom=onshorecs.com at qt.nokia.com] On
Behalf Of Francesco Lamonica
Sent: Tuesday, January 03, 2012 1:03 AM
To: Qt Interest List
Subject: [Qt-interest] problem with bindValue() in nested queries

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?
_______________________________________________
Qt-interest mailing list
Qt-interest at qt.nokia.com
http://lists.qt.nokia.com/mailman/listinfo/qt-interest