Re: Connections/Statements/ResultSets (Was: Re: Pooling

Dave Cramer wrote:

> The spec probably doesn't say anything but; with the way pg's driver is
> currently written you can have as many resultsets/statements open as you
> want. This is because the driver will read them all in when you do an
> executeXXX. There is a plan however to move to a cursor backed result
> set which will change the above statement. This is because a cursor must
> be executed within a transaction, and you can only have one transaction
> open per resultset. I suppose it's possible to still open a non-cursor
> based resultset while the transaction is open?
>
> Dave

Did you really want to say
> ... you can only have one transaction open per resultset
?
Shouldn't it be per connection?
If so, there would be several scenarios, if I am correct.

1. connection.getAutoCommit is false

Since it's possible to have several cursors within one transaction, one
could also have more than one cursor based result set per connection.
I don't know if this is a good idea, because if

2. connection.getAutoCommit is true

then everything looks differnt. You would have to open a transaction for
a cursor based result set. What happens to the auto commit state of the
connection? Calling setAutoCommit while having a result set open would
break the result set, since:
"NOTE: If this method is called during a transaction, the transaction is
committed."
(J2SE 1.4 API on java.sql.Connection)

> I suppose it's possible to still open a non-cursor
> based resultset while the transaction is open?

That would be nice! At least the non-cursor based resultset should not
be removed from the driver. Have a look at createStatement
(J2SE 1.4 API, don't have any older).

This is the most specific prototype of createStatement:
createStatement(int resultSetType, int resultSetConcurrency, int
resultSetHoldability)

I suppost the decision about cursor/non-cursor result set should be made
based on these attributes (resultSetConcurrency, resultSetHoldability).

Correct me, if I'm wrong.

Best Regards,
Michael Paesold