db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Philip Wilder <0505...@acadiau.ca>
Subject Re: EG meeting feedback - July 27, 2005
Date Mon, 08 Aug 2005 13:28:01 GMT
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/%3c42D6BF99.6070404@acadiau.ca%3e

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


* 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 rs1 = s1.executeQuery("select * from sys.systables");
Statement s2 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
ResultSet rs2 = s2.executeQuery("select * from sys.sysschemas");
rs1.next(); //SQLException thrown here
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();

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 rs1 = s1.executeQuery("CALL SYSIBM.SQLTABLES('', '', '', '', 
Statement s2 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet rs2 = s2.executeQuery("CALL SYSIBM.SQLTABLES('', '', '', '', 
System.out.println("End Test 1_2");

View raw message