Re: Proposal for a configurable ResultSet implementation

Peter,

This is already listed on the todo list.  I agree that this
functionality is useful and necessary under some circumstances, and I
don't think it will be too hard to implement.  I was planning on doing
this for 7.3, but if you want to work on this, I can work on other todo
items.

There are some interesting cases to look at.  For example how would you
handle the following:

ResultSet l_rset = l_stmt.executeQuery(
    "insert into foo values(1,2,3); " +
    "select * from foo; ");

You can do this sort of bunching of multiple sql statements into one
call today and it will work (in fact I use this often to reduce network
roundtrips).  The result set returned will be for the last statement
executed in the bunch.  How do you implicitly turn this into something
that usese cursors, without needing to implement a parser in the jdbc code?

The complexity of this feature isn't in making the functionality work,
but in understanding when it can or should be used and when it can't or
shouldn't be used.

thanks,
--Barry

Kovács Péter wrote:

> Hallo,
>
> 1.)
> I think that the implementation of the class ResultSet is not consistent
> with the intention of the JDBC2 API designer. The intention of the API
> designer was clearly to use a server side cursor for the ResultSet, this is
> why the ResultSet.getCursorName() exists. Also, look at methods like
> java.sql.ResultSet.absolute(int) for example. The API Spec. says: "Moves the
> cursor to the given row number in the result set." and similar. (The current
> implementation caches the whole result set on the client side.)
>
> 2.)
> The PostgreSQL backend has the very nice feature that the cursor can be
> moved back and forth. (With Oracle and Sybase you can move the cursor only
> forward [I was told that ORA9i supports backward cursor movement, but I did
> not check].)
>
> 3.)
> There may exist a number of applications relying on the current behaviour
> (full client side caching by the jdbc-driver). Based on mails on this list I
> know of at least one implementation that keeps the result set returned from
> Statement.execute() and uses it later, while the connection itself is
> already being used by some other thread.
>
> Proposal:
> Let's provide two implementations for the ResultSet: the default would be
> the current implementation, and another optional (activated through
> properties or something) using server side cursor. I have not yet looked
> very closely at the feasibility of my proposal, but on the face of it, it
> can be implemented. First I'd like to monitor people's opinion about this.
>
>
> Peter
>
> PS:
> I have to go off line for a few days next week, but I will (I hope) return.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>