Michael Segel wrote:
On Wednesday 09 November 2005 08:26, Lance J. Andersen wrote:
  
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.

    
Right.
No argument there. And I think that you've missed the point I was trying to 
make. 

Its not how the database vendor handles transactions, but rather how JDBC 
handles them.
  
No that is not totally true as the database implementation is key in the functionality that a JDBC driver can provide.

Some databases start an implicit transaction for a given SQL statement and there is no way for the driver to know about this completely.

This issue was discussed at great lengths in the JDBC EG already and due to the various semantic differences between vendors, this is another scenario where your milage varies.
If you look at the API spec (http://java.sun.com/j2se/1.5.0/docs/api/) and 
review the java.sql Connection Interface, you will see that at the Connection 
object level, you control the transactions for the entire connection object.
(Read: ONE TRANSACTION CONTEXT FOR THE ENTIRE OBJECT)

  
not always, see above
If you look at the methods, clearly there is only one single transaction 
context per Connection object. This only becomes an issue when your 
statements are no longer atomic in nature... ;-)


So 
  
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)

    

Ahh, so true. A barrier to the adoption of new technology because their is an 
economic disincentive... (Nothing new there, just look at the phone companies 
in the US.)

But this is really irrelevant. Unless you're talking about changing the JDBC 
spec. 

You have really two ways to look at this.

1) The JDBC spec is fine, and the Connection object   should manage the 
transactions.
  
JDBC,  like ODBC is an API to assist with writing portable code, but they are only a plumbing layer.  Limitations
are set on top of these APIs based on the functionality provided by the backend.

These APIs do a good job in helping with portability, but it is not perfect, especially corner cases such as this.