db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Lance J. Andersen" <Lance.Ander...@Sun.COM>
Subject Re: I need some advice to choose database for an upcomming job
Date Wed, 09 Nov 2005 15:39:27 GMT


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.


>  
>

Mime
View raw message