Michael Segel wrote:
On Tuesday 08 November 2005 16:39, Lance J. Andersen wrote:
  
Daniel John Debrunner wrote:
    
Lance J. Andersen wrote:
      
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,
        
Well, it sure had a funny way of showing it was not the intent :-)

JDBC 1

"New JDBC connections are initially in “auto-commit” mode. This means
that each statement is executed as a separate transaction at the
database. In order to execute several statements within a single
transaction, you must first disable auto-commit by calling
Connection.setAutoCommit(false)."
      
I assume you are refering to the JDBC 102 spec , i am aware of this
verbage.

The above wording does not specify what happens to the Statement that
was active.  Is it commited or rolled back?  I am sure your milage
varies as it does if you do a Connection.close() and there is an active
transaction (The SQL standard differs from the reality of vendors.  Some
commit, some rollback some just give an Error and expect the user to
address it as the standard suggests)

    

A connection.close() is a method on the connection object. Since the result 
sets and statements are all relative to the connection object, clearly there 
will be problems and this is not the intention as per the earlier spec.

I think the bigger question is how do you account for transactions within a 
connection?  That appears to be the issue.
If all of your SQL are atomic, then you don't have a problem.  ;-)
  
The SQL Standard indicates that if you disconnect while a transaction is active, that an error will be raised.  Nothing more, nothing less.

some vendors Commit, some rollback, some give the error and expect the end user to do the right thing.

Unfortunately vendors are not eager to change their semantics once their technologies have been in the field.  So unfortunately the specs (any of them will not
provide a silver bullet from poor design of an application)
  
JDBC 3

"For Select statements, the statement is complete when the associated
result set is
closed. The result set is closed as soon as one of the following occurs:
[...]
- another Statement object is executed on the same connection"

Pretty explicit in both cases, only a single statement can be active in
auto-commit mode. JDBC 4.0 is proposing a change that multiple
statements, possibly including updates, can be active when in
auto-commit mode. I wonder if that was really the intent of EG for these
changes?
      
The JDBC 3 spec intermixed when a ResultSet was closed with auto-commit
semantics and these both are seperate scenarios.  These should have been
seperated.


The verbage in JDBC 3 WRT to  having another Statement executed on the
same connection was not explict WRT auto commit as it was written and
led to confusion.

    

You can interpret this as follows:

Those who wrote the JDBC spec did not conceive that someone would want to have 
multiple separate sets of transactions occurring within the same connection. 
(If you're going to run transactions then you should design your system to 
create a separate connection per thread.)

Simply put GIGO....

Or 

Those who wrote the spec left the design open, allowing individuals to 
implement the spec as they saw fit. Allowing programmers enough room to hang 
themselves. If you consider the C programming language, its fairly open and 
doesn't protect the novice programmer from him/her self. Its a double edged 
sword.

  
Perhaps 1 more bullet could be added to clarify this *specific* scenario
as to the state of the ResultSet, i will see.

    
Try and consider your audience.
Sometimes more is less. Or is it that less is more?

But hey, what do I know?
I just saw that CA was bowing out of the Opensource  DB world.... ;-)
  
 This to me is a corner case that a good programmer
would not get themselves into.

I may not get agreement from the EG on this scenario, outside of your milage may vary.
-G