Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
Message-ID: <43721273.9090502@comcast.net>
Date: Wed, 09 Nov 2005 09:14:59 -0600
From: Will Senn <will_senn@comcast.net>
User-Agent: Thunderbird 1.4.1 (Windows/20051006)
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: I need some advice to choose database for an upcomming job
References: <87ee9b170510290729o58425b8fh4f3d7f03cce0ac29@mail.gmail.com>
 <43712186.7090406@debrunners.com> <4371292E.6020908@sun.com>
 <200511082318.36490.msegel@segel.com> <43720721.7000306@sun.com>
In-Reply-To: <43720721.7000306@sun.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit


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

Total and complete insanity on the vendors part, but a truthful 
assessment nonetheless. Gone are the days of bullet proof specs, or are 
they? Could it be that they were never here? Sadly not, although it may 
have seemed so, back in the day. The exponential increase in complexity 
has exposed a great number of flaws in what might have seemed solid back 
when.

However, that being said, my 2 cents is that it's totally nuts to commit 
anything after an error, unless you don't care that your datastore is in 
an unknown state (you being the database vendor). The argument that the 
app developer might want it that way... sheesh harkens back to the days 
of writing linear incongruent pseudo random number generators using 
BASIC's integer overflow characteristics... Cool, but infinitely 
frightening (non-portable, monster-unmaintainable) at the same time.

-Will