#86 bug in the getMoreResults() method of jdbcStatement

[Toby Byron]

The getMoreResults() method of org.hsql.jdbcStatement class fails to follow the Statement interface specs exactly. This causes problems with code that should work.

The basic problem is that getMoreResults() fails to advance the current ResultSet. (It also fails to close the current ResultSet, which is also a violation of the spec.)

Since HypersonicSQL currently does not support multiple ResultSets, what SHOULD happen if the specs are to be exactly followed is that the next ResultSet should be advanced to null after closing the initial one when getMoreResults() is called.

Below is the current (and buggy) implementation of getMoreResults():
------------------------------------------------------------------

public boolean getMoreResults() {
if(Trace.TRACE) Trace.trace();
return false;
}

And here is a simple patch which corrects the above bug:
-------------------------------------------------------

public boolean getMoreResults() throws SQLException {
if (Trace.TRACE) Trace.trace();

if (rSet != null) {
rSet.close(); // MUST do this to satisfy Statement interface spec! Good idea anyways
rSet = null; // advances to the next RS, which is always null for now
}

return false;
}

To illustrate the current bug, and also to test that the above patch actually solves the probelm, consider the following code which works with any properly written JDBC Statement implementation (but fails in HypersonicSQL):

// *********************************************

String sql;
Statement s = null;
boolean isResultSet;
ResultSet rs = null;
int updateCount;
boolean moreResults;

<<some code here which estables a database connection, etc>>

//The following code executes a generic SQL statment and marches thru all the possible results:

isResultSet = s.execute(sql);
rs = s.getResultSet();
updateCount = s.getUpdateCount();

// process the first result for sure, and also any additional results:
do {
// handle the current result:
if (isResultSet)
processResultSet(rs);
else
processUpdate(updateCount);

// get the next result:
isResultSet = s.getMoreResults();
rs = s.getResultSet();
updateCount = s.getUpdateCount();

// determine if the loop should continue:
moreResults = !( !isResultSet && (updateCount == -1) );
}
while ( moreResults );

/*
From the Javadoc on Statment:
s.getResultSet() SHOULD be null if the result is actually an update count or there are no more results

s.getUpdateCount() SHOULD be -1 if the result is actually a ResultSet or there are no more results
*/

// *********************************************

The above code fails with updates (but not queries) because the loop never ends. This happens because
getUpdateCount()
fails to return -1 the second time that it is called. If the above patch to getMoreResults()
were in place, then getUpdateCount() WOULD recognize that the current RS is null and then would return -1.