Re: Proposal for a configurable ResultSet implementation

Barry,

> -----Original Message-----
> From: Barry Lind [mailto:barry@xythos.com]
> Sent: Friday, January 11, 2002 6:46 PM
> To: Kovács Péter
> Cc: pgsql-jdbc@postgresql.org
> Subject: 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.

As long as I do not have an estimation about the amount of work involved in
the (design and) implementation of this item, I would be very cautious to
make any commitments. (If I happen to decide that I can implement something
reasonable within a reasonable time frame, I will contact you before I start
any sizeable implementation work.)

>
> 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?

Is the parameter passed to Statement.executeQuery(String) in your example
one statement or two. Instinctively, I would say that it's two statements
(but I am not sure). The JDBC API doc says this about the method
Statement.executeQuery(String): "Executes a SQL statement that returns a
single ResultSet." If my assumption is correct and one "insert ..." plus one
"select ..." are two statements, then the API is not correctly used in your
example and as such I would be inclined to dismiss the problem.

A related (but not necessarily equivalent) question is: Is the syntax used
in your example to concatenate several SQL statements (or --if the parameter
can be considered as one SQL statement-- to concatenate several components
of the same SQL statement) portable across the JDBC drivers of the currently
popular RDBMSs? (In my view, one of the advantages of PosgtreSQL over MySQL
is that [to my knowledge] PostgreSQL implements significantly more of the
functionality offered by the SQL spec. Though our views over the importance
of portability may differ.)

Instinctively, in most cases I would prefer to use functions (aka stored
procedures) over concatenation to reduce network roundtrips. Functions make
database product specificities more transparent to the application (at
practically no cost).

> 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.

I am afraid I can only guess your point here: Do you mean that the gains
using a cursor for a query do not always justify the overhead of creating
the cursor?

Thank you

Peter