[Qt-interest] Using true database cursors with Qt

[John Moran]

Hi Bill,

> I think you think that means something other than what it actually
> means. Impedance mismatch in OO systems is the mismatch between tables
> and the object layout.

It refers to that too. I believe that the term broadly refers to
difficulties when mapping constructs in SQL to procedural languages.
Its use probably pre-dates ORM. For example, the book "SQL: A
Beginner's guide" mentions it without mentioning ORM at all.

> QSqlQuery is the class you want. Where the database supports scrollable
> cursors, they are used. Where they don't, we emulated them (by deriving
> from QSqlCachedResult). It's not an optimal solution, but it's a
> solution nonetheless.

> In this case however, data is fetched as it's requested. From
> QPSQLResult::data:     const char *val = PQgetvalue(d->result, at(), i);
> where at() is the record(tuple actually) number in the query.
> It looks like the problem is elsewhere (what are your connection
> options? etc).

That's good to know, but I'm afraid I don't see the relevance to me as
a client of QSqlQuery if I'm not in a position to take advantage of it
for *my own* purposes, as described - this is really just an
optimisation that is encapsulated from me, as I understand it. Are you
suggesting that I call QApplication::processEvents() at the end of
each iteration of my loop through the QSqlQuery?

Are you sure that PQgetvalue() actually lazily fetches from the
server? I doubt it - it just looks like it's for getting individual
fields of a tuple that has already been retrieved. I'm pretty sure
that cursors must be done in SQL, but it still seems reasonable to
have a C++ cursor API for scenarios just like this one - take a look
at libpqxx for an excellent example of one.

> If you can give us a simple example that replicates the
> issue, we'll add it to the autotest system and look at it as soon as we can.

I don't have an issue, or a bug as such for which a test case would
provide some insight. The only issue is that I think that I ought to
be able to fetch fixed size blocks (I'd probably settle on 50 - 100
records per fetch) of the result set, to make my UI update gradually
and to avoid painful blocking - I don't think that there's a practical
way to do this.

Perhaps I could increment an integer each iteration, and every
iteration in which I find the integer to be "i % 50 == 0" I could call
QApplication::processEvents(). However, that assumes that QPSQL is
internally using a cursor, which I have my doubts about. Does that
sound reasonable, assuming that QPSQL uses a cursor?


Regards,
John Moran