Daniel John Debrunner wrote:
Dag H. Wanvik wrote:

  
Hi,


    
"Daniel" == Daniel John Debrunner <djd@debrunners.com> wrote:
              
Daniel> 

Daniel> I think there was a long discussion on this about six months ago, lead
Daniel> by Phil Wilder. He was trying to clarify autocommit mode and held cursor
Daniel> behaviour in the JDBC spec. It's a little unclear, as for example,
Daniel> section 10.1 was not updated for held cursors. That was a case where the
Daniel> wiki would have been useful, to present a summary of the current discussion.
Daniel> 
Daniel> And as you say, I think that discussion was driven by differences in the
Daniel> client and embedded drivers in this area.

I talked to Lance about the spec being vague on this issue, and the
new draft for JDBC 4.0 
(http://www.jcp.org/aboutJava/communityprocess/edr/jsr221/index2.html)
has a new and clearer wording:


    
16.2.5 Closing a ResultSet Object

A ResultSet object is explicitly closed when
 - The close method on the ResultSet is executed, thereby releasing any
   external resources
 - The Statement or Connection object that produced the ResultSet is
   explictly closed

A ResultSet object is implicitly closed when
 - The associated Statement object is re-executed
 - The ResultSet is created with a Holdability of
   CLOSE_CURSORS_AT_COMMIT and an implicit or explicit commit occurs

Note: Some JDBC driver implementations may also implicitly close the
ResultSet when the ResultSet type is TYPE_FORWARD_ONLY and the next
method of ResultSet returns false.
      
Note that executing a statement on *another* statement object in the
same connection no longer closes a result set, 

This has never been the intent in JDBC since its inception,  From the 1.0.2 spec:

Multi-threading
We require that all operations on all the java.sql objects be multi-thread safe and able to cope
correctly with having several threads simultaneously calling the same object.
Some drivers may allow more concurrent execution than others. Developers can assume fully
concurrent execution; if the driver requires some form of synchronization, it will provide it.
The only difference visible to the developer will be that applications will run with reduced concurrency.
For example, two Statements on the same Connection can be executed concurrently and their
ResultSets can be processed concurrently (from the perspective of the developer). Some drivers
will provide this full concurrency. Others may execute one statement and wait until it completes
before sending the next.


HTH

-lance
 I find the new wording more logical,
and I think we should try to move the implementation of Derby towards
this behavior.

¹Auto-commit is triggered for select statements when the result set is
closed, making that statement complete (Section 9.1)
    

Interesting on the 4.0 stuff, that's a change in behaviour from 3.0, I
wonder if any applications would rely on the 3.0 executing on another
statement behaviour. Of course we shouldn't change until 4.0 is
official, and then do we support two behaviours, one for 3.0 and one for
4.0? Probably a future discussion on the dev list.

The 4.0 changes will allow multiple open statements per Connection in
auto commit mode, as opposed to a single one in 3.0. That's an
interesting change, I wonder if this will break any assumption in Derby
in autocommit mode. I'm also not sure that the changes help in the area
driving this discussion, allowing multiple threads to share a single
connection. It seems that the same basic problem exist, without
application synchronization, activity on one thread will modify the
state of the objects being used by the other thread.

Dan.