I am curious as to what sort of dependencies exist between a ResultSet and
its corresponding Connection. Let's say I create a Statement on a
Connection and then execute a query. Before I am finished with the
corresponding ResultSet, I start a transaction on the same Connection and
execute a series of Statements. I've noticed that if I use the same
Statement object in the transaction (e.g. to execute an update), the first
ResultSet is closed. However, if I guarantee the use of
new Statement objects in my transaction, should the ResultSet be affected at
all by the operations that occur in the transaction? Short of the
Connection being closed due to a fatal error, does the ResultSet feel any
affects due to further operations on the Connection?

I ask because I am pooling Connections and want to be able to re-use a
Connection before a query on it has been fully completed (i.e. the query
execution has finished, but the results have not all been retrieved).
Originally, Connections were leased from the pool for exclusive use until
the query had been fully completed, but existing application code (too much
to change at this time) behaves improperly and does not always retrieve all
query results. With the existing pooling implementation, the Connections
leased for those queries are leased indefinitely and become unusable (a
lease timeout isn't appropriate, either, because some of the queries take a
very long time to complete). Thus, I would like to be able to re-use those
"zombied" Connections, including for transaction purposes.

Thanks for any help.

Nick

On Thu, 19 Feb 2004 10:09:53 -0800, Nicholas Laird wrote:

I'm not really up to speed on Java or JDBC, however I can say this about
the underlying protocol that Sybase uses: In *general* you can only have a
single *active* statement on any connection, where "active" means that the
statement still has results pending.

Michael
--
Michael Peppler Data Migrations, Inc.
mpeppler (AT) peppler (DOT) org http://www.peppler.org/
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.peppler.org/resume.html