Philip Wilder wrote:
As part of the EG meeting I was requested to send him an email which
could in turn be forwarded on to other EG members. A few points before
I begin:
- I wish to apologize in advance as I did a poor job keeping track of
organizations everyone was associated with and an even worse job with
names. If I make any mistakes with either I apologize.
- There were two versions of questions in use during the meeting. We
eventually established that the majority of people had the Kathey's
version of questions upon which mine were based. These are the
questions which I will post here. As Kathey pointed out in an earlier
email my questions can be found at:
http://mail-archives.apache.org/mod_mbox/db-derby-dev/200507.mbox/[EMAIL PROTECTED]
- There was some talk on auto-generated numbers for tables that I was
unprepared for which I will not discuss here as I believe the issue
was outside the scope of the questions asked by either Kathey or I.
1) The spec says that the result set is closed when invocation of its
next method returns
false. Does this mean an additional call to next should throw an
exception that the result set is closed or should it just continue to
return false?
The feedback I got on this issue was mixed. I believe the Oracle
representative told me that oracle differentiates between traversing
past the end of the ResultSet and an explicit call to the
ResultSet#close method by the user. In the former case continued calls
to ResultSet.next() would always return false whereas in the latter
case Oracle would throw an Exception. In many cases (the majority?)
JDBC drivers would return false on calls to ResultSet#next() after the
completion of the ResultSet. It was generally agreed that the wording
of section 9.1 of the JDBC 4.0 specification could be improved but
people were divided on how exactly to improve it. I eagerly await
further opinions on this issue.
5) How should DatabaseMetaData calls affect commit? Since the statement
itself is not exposed, should execution of a metadata call send a
commit or have special handling to avoid this?
Feedback in this area was a bit more firm. It seems there exist JDBC
drivers where DatabaseMetaData methods that return ResultSets have no
associated Statement. This means that there is no Statement to
complete when the ResultSet closes which in turn means that an
Auto-Commit is not necessary. However, there are also instances where
JDBC drivers *must* query the database and by the nature of the
connection this query *must* close other open Statements. It is my
understanding that these two sides are irreconcilable. Thus the
behavior of the DatabaseMetaData object is driver and database
dependant, so it is open to the Derby community to implement
DatabaseMetaData Objects in a manner suitable to Derby. However it was
agreed that changes were needed to the specification to alert
developers that DatabaseMetaData objects could be implemented in
either fashion and they should not program in such a way that their
code was dependant upon either implementation.
3) For callable statements the spec says "the statement is complete
when all of the associated result sets have been closed"
but the setAutoCommit javadoc also seems to require that other
results such as results which are update counts and output parameters
be retrieved. How do these play into statement completion?
It was agreed that the documentation from the JDBC specification and
Java Documentation were conflicting. At the same time it was also
agreed that neither wording was completely right. I proposed
(something close to) the following amendment to the wording of these
documents:
"For CallableStatement objects or for statements that return multiple
results, the statement is complete when:
- All of the associated result sets have been closed.
- All of the results (update counts) have been retrieved
- All output parameters have been retrieved.
"
There are two problems with this wording:
1) Potential ambiguity with the word 'retrieved'.
2) Special handling of more advanced data types. The two cases raised
were XML data types and (B?)LOBs.
While the first problem could likely be resolved quickly, the second
is more problematic.
4) (Bonus Question not asked in email by Kathey or I) In the case of
conflict between Java Documentation and JDBC 4.0. specification which
document should be assumed to be correct?
I believe Lance's answer was neither. Neither document can be taken
over the other 100% of the time. It is necessary to weigh both inputs
and make the evaluation as to which is correct. At the same time
another member of the EG team suggested that if a decision needed to
be made and there was no clear winner that developers should follow
documents in the following order:
1) JDBC Specification
2) Java Documentation
3) JDBC Tutorial
I would also encourage Lance to provide his two cents anywhere his
interpretation of the meeting does not run parallel to his own.
Philip
I think it is about time that I took another shot at attempting to
resolve some of these issues that were unearthed during the extensive
investigations to uncover the best way in which to resolve DERBY-213.
In particular there are two things I wish to address:
1) ResultSet.next() after last row of FORWARD_ONLY cursor throws an SQL
Exception with Network Server
I started working on this issue approximately 2 months ago and while it
has brought about a great deal of discussion there have been no changes
in the code to bring about a resolution to this issue. As I allude to in
my summary of the meeting with the Expert Group it would seem that the
most common approach taken by other JDBC vendors is to return false
after the last Row of the ResultSet for a FORWARD_ONLY cursor. The exact
state of the ResultSet when this value is returned may differ from
vendor to vendor but the output is the same. I propose, at least for the
moment, the Client be changed to match Embedded in this regard. In this
way we succeed in bringing the two drivers more in line with one another
and if we later find that this is not the correct approach then both
client and Embedded can be changed. While it could be argued that this
change is too hasty and that we should wait and do it “the right way”
the change is sufficiently small that I believe it is worth the effort
to enact. Sometimes a good fix today, is better then a perfect fix tomorrow.
2) Client DatabaseMetaData
At the moment it would appear that DatabaseMetaData calls that return
ResultSets can be used in both client and embedded without affecting
other Statements. My research into this area leads me to believe that
this is a partial truth. From examining the client code it would appear
that the ResultSets generated by DatabaseMetaData come from prepared
statements which are treated like CallableStatements. These
PreparedStatements produce a ResultSetList and a Section object that
prevents auto-commits within the code. This lack of auto-commit insures
that ResultSets from DatabaseMetaData objects never interfere with the
operation of other statements. In fact this finding implies that
CallableStatements that return ResultSets will never interfere with one
another. So if changes are made to the way auto-commits are handled it
may also be necessary to implement some sort of 'forMetaData' marker
like that which is used in embedded. If someone can validate this as a
bug I can pursue the issue further.
To illustrate this problem I have included a number of tests at the end
of this email. If someone else can validate this as a bug then I will
pursue it further.
Philip
/**
* Tests to see if the execution of a second statement will close the
* ResultSet of the first
*
* @param conn
* @throws SQLException
*/
public void test1(Connection conn) throws SQLException {
System.out.println("Begin Test 1");
Statement s1 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT);
ResultSet rs1 = s1.executeQuery("select * from sys.systables");
Statement s2 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT);
ResultSet rs2 = s2.executeQuery("select * from sys.sysschemas");
rs2.close();
rs1.next(); //SQLException thrown here
rs1.close();
System.out.println("End Test 1");
}
/**
* Test to show that DatabaseMetaData Statements behave differently
* then normal Statements.
*
* @param conn
* @throws SQLException
*/
public void test1_1(Connection conn) throws SQLException {
//No SQLexception thrown
System.out.println("Begin test 1_1");
DatabaseMetaData dbmd1 = conn.getMetaData();
ResultSet rs1 = dbmd1.getCatalogs();
DatabaseMetaData dbmd2 = conn.getMetaData();
ResultSet rs2 = dbmd2.getSchemas();
rs2.close();
rs1.next();
rs1.close();
System.out.println("End test 1_1");
}
/**
* Test to show that Statements executed using the call command
* (as DatabaseMetaData internal statements do)
* behave differently then normal statements.
* @param conn
* @throws SQLException
*/
public void test1_2(Connection conn) throws SQLException {
//No SQLException thrown
System.out.println("Begin Test 1_2");
Statement s1 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT);
ResultSet rs1 = s1.executeQuery("CALL SYSIBM.SQLTABLES('', '', '', '',
'GETSCHEMAS=1')");
Statement s2 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT);
ResultSet rs2 = s2.executeQuery("CALL SYSIBM.SQLTABLES('', '', '', '',
'GETCATALOGS=1')");
rs2.close();
rs1.next();
rs1.close();
System.out.println("End Test 1_2");
}
