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

[Francesco Lamonica]

Hi Aaron,

i'll try to rewrite the code later today, however if i "hardcode" the
id in the nested query (instead of using binding) i get the expected
result of a successful query, hence i think that both my query and the
number of open objects should be ok, don't you agree?

i'll try to use position binding and see if it makes any different.

On Tue, Jan 3, 2012 at 6:31 PM, Scott Aron Bloom
<Scott.Bloom at onshorecs.com> wrote:
> 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
> _______________________________________________
> Qt-interest mailing list
> Qt-interest at qt.nokia.com
> http://lists.qt.nokia.com/mailman/listinfo/qt-interest