Qt-interest Archive, February 2007
QODBC driver harms QSqlQuery objects
Message 1 in thread
Dear Qt Community,
during the development of a database application I recently encountered
the following problem with Ot's ODBC driver on Windows when connecting to
a PostgreSQL server on Linux using the official psqlODBC driver: If the
database connection is closed with QSqlDatabase::close() and subsequently
reopened with QSqlDatabase::open() this close-open-cycle renders QSqlQuery
objects relating to the freshly reestablished database connection useless.
The application crashes due to an access violation when QSqlQuery::exec()
is called on a query object after the second QSqlDatabase::open(). Here's
a short example:
#include <QtGui>
#include <QtSql>
#include <iostream>
int main(int argc, char *argv[])
{
QApplication app(argc,argv);
QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
db.setDatabaseName("...YOUR DATABASE HERE...");
if (!db.open()) {
std::cerr << "ERROR OPENING DATABASE\n";
exit(1);
}
std::cerr << "FIRST QUERY... ";
QSqlQuery query("select 0");
std::cerr << (query.isActive() ? "DONE\n" : "FAILED\n");
db.close();
db.open(); // <-- This harms the QSqlQuery object above.
std::cerr << "SECOND QUERY... ";
query.exec(query.lastQuery());
std::cerr << (query.isActive() ? "DONE\n" : "FAILED\n");
}
The program never returns from the call of query.exec(). This seems to be
faulty behaviour because, IMHO, QSqlQuery objects should not be harmed by
closing and reopening the database connection they are related to - as
opposed to the latter's removal with QSqlDatabase::removeDatabase().
Moreover, one can observe that just closing the database connection, i.e.
without the second call to db.open(), the call of query.exec() doesn't
make the application crash, but complains correctly that the query is
executed on a closed database connection.
Because closing and immediate reopening of a database connection is a
simple but effective means of handling a connection's temporary loss this
behaviour is quite annoying. A possible workaround is to create the query
objects on the heap, destroy and recreate them in case of a failure and
delete them finally, but this approach is much less elegant than having
them exist on the stack like it is shown throughout Qt's documentation.
The abovementioned behaviour does not show up on Linux when using the
PostgreSQL driver QPSQL. Therefore, I'd like to ask for opinions and
experiences, especially:
- Has anybody encountered the same phenonemon or can reproduce it?
- Does anybody know if this behaviour is caused by Qt or psqlODBC?
- As the case may be, should this be filed as a bug report to TT?
The versions of the involved software are:
PostgreSQL 8.2.3 on Debian Etch 20061111
psqlODBC-08_02_0200 on Windows Server 2003 / XP
Qt 4.2.2 Open Source Edition on Linux and Windows
Any comments and advice will be greatly appreciated.
Thanks and Regards
Michael Hertling
--
[ signature omitted ]
Message 2 in thread
I am not expert in QODBC, but I know a little about ODBC itself. Hope it
will help.
You go against ODBC principles here. In pure ODBC you create handle for
database connection, and later you create handle for SQL statement on top of
database connection. Closing database connection effectively kills all
statements based on this connection.
So, in your example query.exec() SHOULD fail with exception on the second
run... or it should silently 'recreate' itself, reattaching inner statement
handle to a new database handle. I am not sure which way would be more
"QT-way".
And yes, recreating QSqlQuery object or objects each time you reconnect to
database and destroying them each time you lose connection to database is a
correct way to go from ODBC-driver point of view.
"Michael Hertling" <hertling@xxxxxxxxxxxxxxxxxxx> wrote in message
news:Pine.LNX.4.64.0702191646520.17551@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> Dear Qt Community,
>
> during the development of a database application I recently encountered
> the following problem with Ot's ODBC driver on Windows when connecting to
> a PostgreSQL server on Linux using the official psqlODBC driver: If the
> database connection is closed with QSqlDatabase::close() and subsequently
> reopened with QSqlDatabase::open() this close-open-cycle renders QSqlQuery
> objects relating to the freshly reestablished database connection useless.
> The application crashes due to an access violation when QSqlQuery::exec()
> is called on a query object after the second QSqlDatabase::open(). Here's
> a short example:
>
> #include <QtGui>
> #include <QtSql>
>
> #include <iostream>
>
> int main(int argc, char *argv[])
> {
> QApplication app(argc,argv);
> QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
> db.setDatabaseName("...YOUR DATABASE HERE...");
> if (!db.open()) {
> std::cerr << "ERROR OPENING DATABASE\n";
> exit(1);
> }
>
> std::cerr << "FIRST QUERY... ";
> QSqlQuery query("select 0");
> std::cerr << (query.isActive() ? "DONE\n" : "FAILED\n");
>
> db.close();
> db.open(); // <-- This harms the QSqlQuery object above.
>
> std::cerr << "SECOND QUERY... ";
> query.exec(query.lastQuery());
> std::cerr << (query.isActive() ? "DONE\n" : "FAILED\n");
> }
>
> The program never returns from the call of query.exec(). This seems to be
> faulty behaviour because, IMHO, QSqlQuery objects should not be harmed by
> closing and reopening the database connection they are related to - as
> opposed to the latter's removal with QSqlDatabase::removeDatabase().
>
> Moreover, one can observe that just closing the database connection, i.e.
> without the second call to db.open(), the call of query.exec() doesn't
> make the application crash, but complains correctly that the query is
> executed on a closed database connection.
>
> Because closing and immediate reopening of a database connection is a
> simple but effective means of handling a connection's temporary loss this
> behaviour is quite annoying. A possible workaround is to create the query
> objects on the heap, destroy and recreate them in case of a failure and
> delete them finally, but this approach is much less elegant than having
> them exist on the stack like it is shown throughout Qt's documentation.
>
> The abovementioned behaviour does not show up on Linux when using the
> PostgreSQL driver QPSQL. Therefore, I'd like to ask for opinions and
> experiences, especially:
>
> - Has anybody encountered the same phenonemon or can reproduce it?
> - Does anybody know if this behaviour is caused by Qt or psqlODBC?
> - As the case may be, should this be filed as a bug report to TT?
>
> The versions of the involved software are:
>
> PostgreSQL 8.2.3 on Debian Etch 20061111
> psqlODBC-08_02_0200 on Windows Server 2003 / XP
> Qt 4.2.2 Open Source Edition on Linux and Windows
>
> Any comments and advice will be greatly appreciated.
>
> Thanks and Regards
>
> Michael Hertling
>
> --
> To unsubscribe - send a mail to qt-interest-request@xxxxxxxxxxxxx with
"unsubscribe" in the subject or the body.
> List archive and information: http://lists.trolltech.com/qt-interest/
--
[ signature omitted ]
Message 3 in thread
Hi,
> So, in your example query.exec() SHOULD fail with exception on the second
> run... or it should silently 'recreate' itself, reattaching inner statement
> handle to a new database handle. I am not sure which way would be more
> "QT-way".
Silently 'recreating' itself would be "Qt-ish" indeed, but what if there
are multiple active database connections? Which one should be used?
--
[ signature omitted ]
Message 4 in thread
Hi Dimitri!
On Thu, 22 Feb 2007, Dimitri wrote:
>> So, in your example query.exec() SHOULD fail with exception on the second
>> run... or it should silently 'recreate' itself, reattaching inner statement
>> handle to a new database handle. I am not sure which way would be more
>> "QT-way".
>
> Silently 'recreating' itself would be "Qt-ish" indeed, but what if there are
> multiple active database connections? Which one should be used?
Obviously, this should be the connection the QSqlQuery was created for
initially, i.e. the one mentioned in the constructor and, casually, still
persisting even after it has been closed. For this purpose, the QSqlQuery
has to be aware of "its" QSqlDatabase, but a glance into the sources shows
that, apparently, QSqlQuery only contains data necessary for the execution
of its SQL statement while not having a link to the affected QSqlDatabase.
This issue has, IMHO, already given rise to several postings and also to
task #131011. Perhaps, the latter's resolution will clarify the somewhat
subtle relationship between QSqlDatabase and QSqlQuery.
Regards
Michael Hertling
--
[ signature omitted ]
Message 5 in thread
Hi,
> Obviously, this should be the connection the QSqlQuery was created for
> initially, i.e. the one mentioned in the constructor and, casually,
> still persisting even after it has been closed.
Indeed, but how do you identify a connection? For example there could be
two connections to the same database in a program (at least that's my
understanding, I don't know much about this part of Qt). If one of the
connections is killed, should you re-use the other connection or create
another one?
What if a password is needed for the connection?
What if the database connection has been killed on purpose elsewhere in
the program? Then it should not be silently recreated.
What if... OK, just playing devil's advocate :-)
--
[ signature omitted ]
Message 6 in thread
I wrote:
> Indeed, but how do you identify a connection? For example there could be
> two connections to the same database in a program (at least that's my
> understanding, I don't know much about this part of Qt). If one of the
> connections is killed, should you re-use the other connection or create
> another one?
Hmmm... QSqlQuery remembers the QSqlDatabse passed as a parameter to its
constructor. I should have thought before posting this one...
--
[ signature omitted ]
Message 7 in thread
Hi Dimitri!
On Sun, 25 Feb 2007, Dimitri wrote:
> I wrote:
>> Indeed, but how do you identify a connection? For example there could be
>> two connections to the same database in a program (at least that's my
>> understanding, I don't know much about this part of Qt). If one of the
>> connections is killed, should you re-use the other connection or create
>> another one?
>
> Hmmm... QSqlQuery remembers the QSqlDatabse passed as a parameter to its
> constructor. I should have thought before posting this one...
This is a point about which I'm increasingly in doubt. Having thrown a
glance at the sources, I can't see if QSqlQuery stores the QSqlDatabase
mentioned in its constructor. Possibly, I have overlooked the concerning
lines of code, but, up to now, it seems to me as if QSqlQuery only stores
the data needed to handle its SQL statement, e.g. the PGconn and PGresult
pointers when using PostgreSQL or at least the statement handle when using
ODBC. Again, I'd like to refer to task #131011 which, IMHO, relates right
to this issue. Perhaps, someone out in QUniverse can throw light on this
question.
Regards
Michael Hertling
--
[ signature omitted ]
Message 8 in thread
Hi Dimitri!
On Sun, 25 Feb 2007, Dimitri wrote:
>> Obviously, this should be the connection the QSqlQuery was created for
>> initially, i.e. the one mentioned in the constructor and, casually, still
>> persisting even after it has been closed.
>
> Indeed, but how do you identify a connection? For example there could be two
> connections to the same database in a program [...]
Do you mean
- two different connections, each created by QSqlDatabase::addDatabase()
with different connection names, but addressing the same database, or
- two different instances of QSqlDatabase, e.g. each retrieved using
QSqlDatabase::database() with the same connection name each time?
In both cases, one will have two different QSqlDatabase objects, but in
the first case, each of them refers to a different database connection
whereas, in the latter case, both represent the same single connection.
To my knowledge, this will make a fundamental difference in some ways.
> [..] If one of the
> connections is killed, should you re-use the other connection or create
> another one?
What do you mean exactly with "killing" a connection?
- Closing with QSqlDatabase::close(), or
- Removing with QSqlDatabase::removeDatabase() ?
> What if a password is needed for the connection?
If the connection was just closed it still exists and can be retrieved
with QSqlDatabase::database(). The password is either stored within the
returned QSqlDatabase object - accessed by QSqlDatabase::password() - or
it must be provided in the call of QSqlDatabase::open(). However, if the
connection was removed one wouldn't expect QSqlQuery having a chance for
automatic reconnecting, but this is reasonable and documented behaviour.
> What if the database connection has been killed on purpose elsewhere in the
> program? Then it should not be silently recreated.
If killing means removing here, I absolutely agree with you, but what if
killing means closing? So, we return to the thread's original question.
> What if... OK, just playing devil's advocate :-)
The devil is using Qt, too? Hellish... ;)
Regards
Michael Hertling
--
[ signature omitted ]
Message 9 in thread
Hi George!
On Tue, 20 Feb 2007, George Brink wrote:
> You go against ODBC principles here. In pure ODBC you create handle for
> database connection, and later you create handle for SQL statement on top of
> database connection. Closing database connection effectively kills all
> statements based on this connection.
You're absolutely right. After reception of your reply I read the ODBC
reference more closely and learned that SQLDisconnect() frees remaining
statements allocated on the affected connection. An inspection of Qt's
relating source code turns out that, ultimately, QSqlQuery allocates an
ODBC statement handle which isn't reallocated by QSqlQuery:exec() while
QSqlDatabase::close() invokes SQLDisconnect() without being aware of the
connection's still pending QSqlQuery objects. Thus, QSqlQuery::exec() on
any closed and reopened ODBC connection most certainly leads to an access
violation.
> So, in your example query.exec() SHOULD fail with exception on the second
> run... or it should silently 'recreate' itself, reattaching inner statement
> handle to a new database handle. I am not sure which way would be more
> "QT-way".
Of course, such a kind of self-healing would be most elegant, but as far
as I can see, this requires a tighter interconnection of QSqlDatabase and
QSqlQuery. Perhaps it can be addressed in the course of task #131011 which
relates to this subject, too, and, regarding its history, reveals that the
relation between QSqlDatabase and QSqlQuery is a rather subtle one... ;-)
> And yes, recreating QSqlQuery object or objects each time you reconnect to
> database and destroying them each time you lose connection to database is a
> correct way to go from ODBC-driver point of view.
Indeed, for the time being, this appears to be the preferable way to have
applications recover lost database connections independent of the actual
backend and without subsequent crashing.
> [...] Hope it will help.
It did. Thank you very much for sharing your knowledge.
Regards
Michael Hertling
--
[ signature omitted ]
Message 10 in thread
Hi,
Michael Hertling wrote:
> QApplication app(argc,argv);
> QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
> db.setDatabaseName("...YOUR DATABASE HERE...");
> if (!db.open()) {
> std::cerr << "ERROR OPENING DATABASE\n";
> exit(1);
> }
>
> std::cerr << "FIRST QUERY... ";
> QSqlQuery query("select 0");
Here you are using this constructor:
http://doc.trolltech.com/4.2/qsqlquery.html#QSqlQuery-2
If db is not specified, the application's default
database is used.
What if you specify the database? It shouldn't make a difference but you
never know:
QSqlQuery query("select 0", db);
Otherwise I tend to agree with George Brink, it doesn't sound like a
good idea to kill a database connection and expect queries to survive
that. Still, the documentation could maybe warn against that.
--
[ signature omitted ]
Message 11 in thread
Hi Dimitri!
On Thu, 22 Feb 2007, Dimitri wrote:
>> QApplication app(argc,argv);
>> QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
>> db.setDatabaseName("...YOUR DATABASE HERE...");
>> if (!db.open()) {
>> std::cerr << "ERROR OPENING DATABASE\n";
>> exit(1);
>> }
>>
>> std::cerr << "FIRST QUERY... ";
>> QSqlQuery query("select 0");
>
> Here you are using this constructor:
> http://doc.trolltech.com/4.2/qsqlquery.html#QSqlQuery-2
> If db is not specified, the application's default
> database is used.
>
> What if you specify the database? It shouldn't make a difference but you
> never know:
> QSqlQuery query("select 0", db);
Lately, I gave it a try, but it didn't bring a change: Crash happens...
> Otherwise I tend to agree with George Brink, it doesn't sound like a good
> idea to kill a database connection and expect queries to survive that. Still,
> the documentation could maybe warn against that.
Yes, I agree, especially because the workaround isn't difficult: Simply
create the queries on the heap, but don't forget to delete them finally.
Nevertheless, having them exist on the stack would mean to be with Qt's
elegance, and, as I mentioned in the thread's initial posting, the QPSQL
driver is capable for this: A QSqlQuery object remains usable after its
QSqlDatabase was closed and reopened. For the time being, this shouldn't
be considered as assured behaviour, but perhaps the future will bring an
improvement (task #131011).
Thank you very much for your reply.
Regards
Michael Hertling
--
[ signature omitted ]
