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 14:26:41 GMT


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

Mime
View raw message