I've attached a document which contains the entirety of this email
thread plus the latest auto-commit behavior document as assembled by
Kathey and I.
Philip
Lance Andersen wrote:
a recap would be good.
The wording that is in the spec was made based on the input from the
EG which includes IBM, Oracle and mysql.
Please put a writeup together and I will discuss it with my EG.
Thanks Lance
p.s. Yes it is hard to follow as there have been a slew of emails on
derby-dev and I am way behind due to JavaOne and the Sun shutdown.
Philip Wilder wrote:
I'm a little concerned that this issue seems to be withering on the
vine so to speak so I thought I would bring it back to the attention
of the dev list again.
In an effort to collect more information I have been running simple
tests such as the one I've attached to this email against db2, oracle
and mysql. The JDBC 4.0 specifications state that a ResultSet will
close "if the cursor is forward only, then when invocation of its
next method returns
false". Yet none of the Databases I've tested do this, including
Derby Embedded. The only test to follow this behavior I've found was
Derby Client and this way have played some part in the SQuirreL
malfunction mentioned earlier.
So will JDBC 4.0 "break" these drivers in this respect or is there
some other resolution?
On a related note would anyone find it beneficial if I were to
collect this thread into one document and posted it to the newsgroup?
This issue has gone on for some time now and I can understand if
anyone is having trouble following the thread.
Philip
Dan Debrunner wrote:
Kathey Marsden wrote:
Lance J. Andersen wrote:
I am just getting back from J1 and I have a quite a few emails
to wade
through. If/when you hace a cycle, if you can summarize the issues
outstanding, i can look at it and discuss with the EG. There
are sooo
many emails from derby-dev, it is going to take me quite some
time to
digest it all.
Hi Lance,
Yes, there is a lot of mail. At least this thread would be worth
reading in its entirety. The executive summary as Philip would
put it
is that
Regarding the spec, the biggest items resolve to me seem to me to be.
- When is a result set closed and when should next return
false vs
throw an exception?
I think it is well defined when a result set is closed, I think it's
more once it is closed, what should its methods do? Especially
rs.next(), return false or throw an exception. At least, for Lance &
the
EG, I think we need a better question than "when is a result set
closed'.
Dan.
------------------------------------------------------------------------
This document is a summary of the JDBC email thread. To anyone just getting
involved I would particularly encourage you to check out Kathey's July 1st
email which almost acts as a summary of this summary. If you are interested in
learning about how this thread came into being you can check out the IRC chat
transcripts attached to DERBY-213. Finally, attached to the bottom of this
document is the latest plain text draft of the Auto-Commit Behavior document
assembled by Kathey and I.
As always any questions, comments or concerns can be sent to me at [EMAIL
PROTECTED] or the derby-dev list.
Philip
#############
Subject: JDBC auto-commit semantics challenge
From: Philip Wilder <[EMAIL PROTECTED]>
Date: Thu, 23 Jun 2005 15:37:52 -0300
To: Derby Development <[email protected]>
Hello all,
In case you haven't been following the DERBY-213 chat transcripts for about a
week now Kathey Marsden and I have been working to establish a concrete outline
for the auto-commit behavior we wish to see implemented with Derby. Many of the
problems we have been having with DERBY-213 have stemmed from ambiguous
interpretations of the JDBC spec, so we felt it would be beneficial to both us
and the dev community at large if we took the time to get it right.
At the moment we are on the 4th draft of the document and it has reached a
stage that is satisfactory to both Kathey and I. As such, we felt that it was
time to get your input. Hopefully if enough people can agree upon a single
definition we can change this document to match the general consensus and
incorporate it into the
http://incubator.apache.org/derby/papers/JDBCImplementation.html paper as per
Dan's suggestion.
Anyway, I know many of you are busy preparing for JavaOne but any time you can
take to challenge any of our semantic assertions or just correct my grammar
would be appreciated.
Philip
##########
Subject: Re: JDBC auto-commit semantics challenge
From: Philip Wilder <[EMAIL PROTECTED]>
Date: Thu, 23 Jun 2005 16:47:38 -0300
To: Derby Development <[email protected]>
Philip Wilder wrote:
>> Hello all,
>>
>> In case you haven't been following the DERBY-213 chat transcripts for about
>> a week now Kathey Marsden and I have been working to establish a concrete
>> outline for the auto-commit behavior we wish to see implemented with Derby.
>> Many of the problems we have been having with DERBY-213 have stemmed from
>> ambiguous interpretations of the JDBC spec, so we felt it would be
>> beneficial to both us and the dev community at large if we took the time to
>> get it right.
>>
>> At the moment we are on the 4th draft of the document and it has reached a
>> stage that is satisfactory to both Kathey and I. As such, we felt that it
>> was time to get your input. Hopefully if enough people can agree upon a
>> single definition we can change this document to match the general consensus
>> and incorporate it into the
>>
>> http://incubator.apache.org/derby/papers/JDBCImplementation.html paper as
>> per Dan's suggestion.
>>
>> Anyway, I know many of you are busy preparing for JavaOne but any time you
>> can take to challenge any of our semantic assertions or just correct my
>> grammar would be appreciated.
>>
>> Philip
>>
Two notes regarding this issue:
a) Kathey and I are currently scheduled for a IRC meeting on irc.freenode.net
server, #derby channel at June 28, 5:00 a.m. PST. If you have an interest in
this issue but are unable to make it to this time we may be able to make
alternate arrangements.
b) Kathey has expressed an interest in having a wiki set up for this issue and
I can see the benefit of one. Does anyone out there know what channels we need
to go through to get our own wiki page?
Philip
##########
Subject: Re: JDBC auto-commit semantics challenge
From: Philip Wilder <[EMAIL PROTECTED]>
Date: Fri, 24 Jun 2005 10:36:09 -0300
To: Derby Development <[email protected]>
I'm submitted a few changes to the last document that might aid understanding,
particularly in the advanced section of the document. In addition, I've
switched from Word docs and PDFs to straight text to make is easier to add
inline comments.
Philip
##########
Subject: Re: JDBC auto-commit semantics challenge
From: Daniel John Debrunner <[EMAIL PROTECTED]>
Date: Fri, 24 Jun 2005 07:40:56 -0700
To: Derby Development <[email protected]>
Philip Wilder wrote:
>> I'm submitted a few changes to the last document that might aid
>> understanding, particularly in the advanced section of the document. In
>> addition, I've switched from Word docs and PDFs to straight text to make
>> is easier to add inline comments.
I think you dropped the footnotes that you had in the pdf document.
The note that talked about JDBC 3.0 spec did not mention output
parameters for auto commit on callable statements.
I would challenge that the text from JDBC 3.0 spec, section 10.1 is the
definitive behaviour, not the javadoc of setAutoCommit.
Dan.
##########
Subject: Re: JDBC auto-commit semantics challenge
From: Kathey Marsden <[EMAIL PROTECTED]>
Date: Fri, 24 Jun 2005 15:10:42 -0700
To: Derby Development <[email protected]>
Daniel John Debrunner wrote:
>>I think you dropped the footnotes that you had in the pdf document.
>>The note that talked about JDBC 3.0 spec did not mention output
>>parameters for auto commit on callable statements.
>>
>>I would challenge that the text from JDBC 3.0 spec, section 10.1 is the
>>definitive behaviour, not the javadoc of setAutoCommit.
>>
So does that make that a bug in the setAutoCommit javadoc? Lance do
you have an opinon here?
##########
Subject: Re: JDBC auto-commit semantics challenge
From: "Lance J. Andersen" <[EMAIL PROTECTED]>
Date: Fri, 24 Jun 2005 20:54:00 -0400
To: Derby Development <[email protected]>
i have not had the bandwidth to follow this thread due to J1 and a few other
fire drills. We have made changes to the spec and javadocs in JDBC 4 to
clarify things in these areas. Please take a look and see if there is still
something you feel needs clarified.
Kathey Marsden wrote:
>Daniel John Debrunner wrote:
>
>
>
>>I think you dropped the footnotes that you had in the pdf document.
>>The note that talked about JDBC 3.0 spec did not mention output
>>parameters for auto commit on callable statements.
>>
>>I would challenge that the text from JDBC 3.0 spec, section 10.1 is the
>>definitive behaviour, not the javadoc of setAutoCommit.
>>
>>
>>
>>
>>
>So does that make that a bug in the setAutoCommit javadoc? Lance do
>you have an opinon here?
##########
Subject: Re: JDBC auto-commit semantics challenge
From: Philip Wilder <[EMAIL PROTECTED]>
Date: Mon, 27 Jun 2005 09:21:13 -0300
To: Derby Development <[email protected]>
Lance, It would appear that the setAutoCommit javadoc for JDBC 4.0 and the
javadoc used for J2SE 1.4.2 (I assume JDBC 3.0) are identical so I'm afraid
they can shed no light on the subject.
Dan, the footnotes were removed as they do not translate particularly well to a
plain text format. I do apologize for zapping the footnote regarding your the
difference between the JDBC spec and the javadocs. That should have stayed.
For anyone just tuning into this email thread the comment went something like
this:
"[The advanced case] definition complies with the JDK 1.4.1 interpretation of
the JDBC implementation which differs from the JDBC 3.0 specifications. Thanks
to Daniel Debrunner for pointing this out."
Philip Wilder
Lance Anderson wrote:
> i have not had the bandwidth to follow this thread due to J1 and a few other
> fire drills. We have
> made changes to the spec and javadocs in JDBC 4 to clarify things in these
> areas. Please take a
> look and see if there is still something you feel needs clarified.
>
> Kathey Marsden wrote:
>
>> Daniel John Debrunner wrote:
>>
>>
>>
>>> I think you dropped the footnotes that you had in the pdf document.
>>> The note that talked about JDBC 3.0 spec did not mention output
>>> parameters for auto commit on callable statements.
>>>
>>> I would challenge that the text from JDBC 3.0 spec, section 10.1 is the
>>> definitive behaviour, not the javadoc of setAutoCommit.
>>>
>>>
>>>
>>>
>>
>> So does that make that a bug in the setAutoCommit javadoc? Lance do
>> you have an opinon here?
##########
Subject: Re: JDBC auto-commit semantics challenge
From: "Lance J. Andersen" <[EMAIL PROTECTED]>
Date: Fri, 01 Jul 2005 13:54:16 -0400
To: Derby Development <[email protected]>
I am just getting back from J1 and I have a quite a few emails to wade through.
If/when you hace a cycle, if you can summarize the issues outstanding, i can
look at it and discuss with the EG. There are sooo many emails from derby-dev,
it is going to take me quite some time to digest it all.
lance
Philip Wilder wrote:
> Lance, It would appear that the setAutoCommit javadoc for JDBC 4.0 and the
> javadoc used for J2SE 1.4.2 (I assume JDBC 3.0) are identical so I'm afraid
> they can shed no light on the subject.
>
> Dan, the footnotes were removed as they do not translate particularly well to
> a plain text format. I do apologize for zapping the footnote regarding your
> the difference between the JDBC spec and the javadocs. That should have
> stayed.
>
> For anyone just tuning into this email thread the comment went something like
> this:
>
> "[The advanced case] definition complies with the JDK 1.4.1 interpretation of
> the JDBC implementation which differs from the JDBC 3.0 specifications.
> Thanks to Daniel Debrunner for pointing this out."
>
> Philip Wilder
>
> Lance Anderson wrote:
>
>> i have not had the bandwidth to follow this thread due to J1 and a few other
>> fire drills. We have
>> made changes to the spec and javadocs in JDBC 4 to clarify things in these
>> areas. Please take a
>> look and see if there is still something you feel needs clarified.
>>
>> Kathey Marsden wrote:
>>
>> Daniel John Debrunner wrote:
>>
>>
>>> I think you dropped the footnotes that you had in the pdf document.
>>> The note that talked about JDBC 3.0 spec did not mention output
>>> parameters for auto commit on callable statements.
>>>
>>> I would challenge that the text from JDBC 3.0 spec, section 10.1 is the
>>> definitive behaviour, not the javadoc of setAutoCommit.
>>>
>>
>> So does that make that a bug in the setAutoCommit javadoc? Lance do
>> you have an opinon here?
>>
##########
Subject: Re: JDBC auto-commit semantics challenge
From: Kathey Marsden <[EMAIL PROTECTED]>
Date: Fri, 01 Jul 2005 17:28:18 -0700
To: Derby Development <[email protected]>
Lance J. Andersen wrote:
>> I am just getting back from J1 and I have a quite a few emails to wade
>> through. If/when you hace a cycle, if you can summarize the issues
>> outstanding, i can look at it and discuss with the EG. There are sooo
>> many emails from derby-dev, it is going to take me quite some time to
>> digest it all.
>>
Hi Lance,
Yes, there is a lot of mail. At least this thread would be worth
reading in its entirety. The executive summary as Philip would put it
is that
- Philip posted a proposed autocommit behaviour for Derby as a
challenge the community.
- Dan challenged that the definitive auto-commit behaviour should
be the JDBC3.0 spec section 10.1, not the setAutoCommit doc.
- I asked you a bunch of questions about the JDBC4.0 spec.
- Questions came up about inconsistencies between next()
behaviour on result sets closed by calling next past the last row vs
next() after being closed with the close method.
- We found out squirrel is broken for client because of DERBY-213.
ResultSet.next() after last row of FORWARD_ONLY cursor throws an SQL
Exception with Network Server.
Regarding the spec, the biggest items resolve to me seem to me to be.
- When is a result set closed and when should next return false vs
throw an exception?
- Should the additional information about auto-commit in the
setAutoCommit javadoc be fixed in the spec or fixed in the javadoc?
- Should there be special processing for DatabaseMetaData ?
But there is more. My specific questions about the JDBC40 spec section
9.1 are here.
http://mail-archives.apache.org/mod_mbox/db-derby-dev/200506.mbox/[EMAIL
PROTECTED]
Now it is my turn to be out for a week, so when I get back I expect you
folks to be able to tell me definitively, when a statement is completes,
when is a result set is closed, when next() should return false vs
throws an exception and when that commit should get sent!
Kathey
##########
Subject: Re: JDBC auto-commit semantics challenge
From: Daniel John Debrunner <[EMAIL PROTECTED]>
Date: Fri, 01 Jul 2005 17:53:28 -0700
To: Derby Development <[email protected]>
Kathey Marsden wrote:
>> Lance J. Andersen wrote:
>>
>>
>
>>>>I am just getting back from J1 and I have a quite a few emails to wade
>>>>through. If/when you hace a cycle, if you can summarize the issues
>>>>outstanding, i can look at it and discuss with the EG. There are sooo
>>>>many emails from derby-dev, it is going to take me quite some time to
>>>>digest it all.
>>>>
>
>>
>> Hi Lance,
>>
>> Yes, there is a lot of mail. At least this thread would be worth
>> reading in its entirety. The executive summary as Philip would put it
>> is that
>>
>> Regarding the spec, the biggest items resolve to me seem to me to be.
>>
>> - When is a result set closed and when should next return false vs
>> throw an exception?
I think it is well defined when a result set is closed, I think it's
more once it is closed, what should its methods do? Especially
rs.next(), return false or throw an exception. At least, for Lance & the
EG, I think we need a better question than "when is a result set closed'.
Dan.
##########
Subject: Re: JDBC auto-commit semantics challenge
From: Philip Wilder <[EMAIL PROTECTED]>
Date: Wed, 13 Jul 2005 10:33:59 -0300
To: Derby Development <[email protected]>
I'm a little concerned that this issue seems to be withering on the vine so to
speak so I thought I would bring it back to the attention of the dev list again.
In an effort to collect more information I have been running simple tests such
as the one I've attached to this email against db2, oracle and mysql. The JDBC
4.0 specifications state that a ResultSet will close "if the cursor is forward
only, then when invocation of its next method returns
false". Yet none of the Databases I've tested do this, including Derby
Embedded. The only test to follow this behavior I've found was Derby Client and
this may have played some part in the SQuirreL malfunction mentioned earlier.
So will JDBC 4.0 "break" these drivers in this respect or is there some other
resolution?
On a related note would anyone find it beneficial if I were to collect this
thread into one document and posted it to the newsgroup? This issue has gone on
for some time now and I can understand if anyone is having trouble following
the thread.
Philip
Dan Debrunner wrote:
> Kathey Marsden wrote:
>
>>> Lance J. Andersen wrote:
>>>
>>
>>>>> I am just getting back from J1 and I have a quite a few emails to wade
>>>>> through. If/when you hace a cycle, if you can summarize the issues
>>>>> outstanding, i can look at it and discuss with the EG. There are sooo
>>>>> many emails from derby-dev, it is going to take me quite some time to
>>>>> digest it all.
>>>>>
>>>
>>> Hi Lance,
>>>
>>> Yes, there is a lot of mail. At least this thread would be worth
>>> reading in its entirety. The executive summary as Philip would put it
>>> is that
>>
>>>
>>> Regarding the spec, the biggest items resolve to me seem to me to be.
>>>
>>> - When is a result set closed and when should next return false vs
>>> throw an exception?
>>
>
> I think it is well defined when a result set is closed, I think it's
> more once it is closed, what should its methods do? Especially
> rs.next(), return false or throw an exception. At least, for Lance & the
> EG, I think we need a better question than "when is a result set closed'.
>
> Dan.
>
##################################
############
Introduction
############
The following document is a proposed outline for Derby commit
functionality. It is hoped that this document can eliminate the ambiguity of
the JDBC documentation and centralize the information needed to provide an
implementation that matches JDBC expectations. It was not possible in all cases
to make these decisions based solely upon the supporting documentation, so
there may be instances where this document makes reference to external sources
or attempts to provides a reasonable interpretation based on the available data.
########
Analysis
########
An Auto-commit occurs when one of the following occurs:
1. When any Statement Completes
2. When any Statement Executes. This will implicitly close any ResultSets
associated with this Statement.
3. When a call to connection.setAutoCommit() is made during a transaction that
changes the value autoCommit.
A Statement completes when:
DDL: After execution.
DML
o Simple Cases
- Delete/Insert/Update: After execution.
- Query:
FORWARD_ONLY: After the final row of the ResultSet has been
retrieved or the ResultSet has been closed.
SCROLLABLE: After the ResultSet has been closed.
o Advanced Case
- Multiple Results (currently only applicable to CallableStatements): All
results have been retrieved. Results include
ResultSets: A ResultSet has been retrieved when getMoreResults has
been called and the query results retrieved as described in simple cases.
Update counts: An Update count is returned for ddl, inserts, updates
and deletes. Update counts have been retrieved after getUpdateCount has been
called or getMoreResults() has been called to get the next result for the
statement.
- Output parameters: Output parameters are associated with
CallableStatements. Output parameters have been retrieved when an appropriate
CallableStatement getter method has been called for each output parameter.
Note:
Since DatabaseMetaData does not make use of any Statement objects accessible to
the user ResultSets that come from DatabaseMetaData statements should be
committed only when no other Statements are currently open. Also, the advanced
case definition complies with the JDK 1.4.1 interpretation of the JDBC
implementation which differs from the JDBC 3.0 specifications. Thanks to
Daniel Debrunner for pointing this out.
#################
Related Questions
#################
What happens to other statements when the auto-commit occurs?
o Commits are Connection wide so a commit from one statement will
affect all statements.
Should special consideration be given the Distributed transactions?
o Auto-committing is not supported for distributed transactions.
What is the proper commit action of a call to executeBatch()?
o The JDBC specification does not specify when an auto-commit should
occur in an executeBatch Statement.
##########
References
##########
Javadocs
java.sql.Statement.executeBatch()
Submits a batch of commands to the database for execution and if all commands
execute successfully, returns an array of update counts. The int elements of
the array that is returned are ordered to correspond to the commands in the
batch, which are ordered according to the order in which they were added to the
batch. The elements in the array returned by the method executeBatch may be one
of the following:
A number greater than or equal to zero -- indicates that the command was
processed successfully and is an update count giving the number of rows in the
database that were affected by the command's execution
A value of SUCCESS_NO_INFO -- indicates that the command was processed
successfully but that the number of rows affected is unknown
If one of the commands in a batch update fails to execute properly, this method
throws a BatchUpdateException, and a JDBC driver may or may not continue to
process the remaining commands in the batch. However, the driver's behavior
must be consistent with a particular DBMS, either always continuing to process
commands or never continuing to process commands. If the driver continues
processing after a failure, the array returned by the method
BatchUpdateException.getUpdateCounts will contain as many elements as there are
commands in the batch, and at least one of the elements will be the following:
A value of EXECUTE_FAILED -- indicates that the command failed to execute
successfully and occurs only if a driver continues to process commands after a
command fails
A driver is not required to implement this method. The possible implementations
and return values have been modified in the Java 2 SDK, Standard Edition,
version 1.3 to accommodate the option of continuing to proccess commands in a
batch update after a BatchUpdateException obejct has been thrown.
Returns:
an array of update counts containing one element for each command in the batch.
The elements of the array are ordered according to the order in which commands
were added to the batch.
Throws:
SQLException - if a database access error occurs or the driver does not support
batch statements. Throws BatchUpdateException (a subclass of SQLException) if
one of the commands sent to the database fails to execute properly or attempts
to return a result set.
java.sql.Connection.setAutoCommit()
Sets this connection's auto-commit mode to the given state. If a connection is
in auto-commit mode, then all its SQL statements will be executed and committed
as individual transactions. Otherwise, its SQL statements are grouped into
transactions that are terminated by a call to either the method commit or the
method rollback. By default, new connections are in auto-commit mode.
The commit occurs when the statement completes or the next execute occurs,
whichever comes first. In the case of statements returning a ResultSet object,
the statement completes when the last row of the ResultSet object has been
retrieved or the ResultSet object has been closed. In advanced cases, a single
statement may return multiple results as well as output parameter values. In
these cases, the commit occurs when all results and output parameter values
have been retrieved.
NOTE: If this method is called during a transaction, the transaction is
committed.
Java.sql.Statement.getMoreResults()
Moves to this Statement object's next result, deals with any current ResultSet
object(s) according to the instructions specified by the given flag, and
returns true if the next result is a ResultSet object.
There are no more results when the following is true:
// stmt is a Statement object
((stmt.getMoreResults() == false) && (stmt.getUpdateCount() == -1))
Parameters:
current - one of the following Statement constants indicating what should
happen to current ResultSet objects obtained using the method getResultSet:
Statement.CLOSE_CURRENT_RESULT, Statement.KEEP_CURRENT_RESULT, or
Statement.CLOSE_ALL_RESULTS
Returns:
true if the next result is a ResultSet object; false if it is an update count
or there are no more results
JDBC API Tutorial and Reference, Second Edition
Universal Data Access of the Java 2 Platform
Page 347 setAutoCommit
The commit occurs when the statement completes or the next execute occurs,
whichever comes first. In the case of statements returning a ResultSet object,
the statement completes when the last row of a non-scrollable resultset has
been retrieved or the ResultSet object has been closed.
Derby Comment: Currently Embedded Derby considers the Statement complete when
the last row has been returned for both scrollable and non-scrollable
ResultSets.
Page 819, Section 40.1.3 Statement Completion
A Statement is considered Complete when it has been executed and all its
results have been returned. For the method executeQuery, which returns one
result set the statement is completed when all the rows of the ResultSet object
have been retrieved. For the method executeUpdate, a statement is completed
when it is executed. In rare cases where the method execute is called, however,
a statement is not complete until all the result sets or update counts it
generated have been retrieved.
Page 996 Glossary Transaction
A sequence of SQL/JDBC calls that constitute an atomic unit of work: Either all
of the commands in a transaction are committed as a unit, or all of the
commands are rolled back as a unit. Transactions provide ACID properties:
atomicity, consistency, integrity of data and durability of database changes.
See commit and rollback. A transaction in which commands are sent to more then
one DBMS server is a distributed transaction.
