Spring JDBC and Universe Database

[mlythgoe]

This is a long shot but has anybody used Spring JDBC and The RDBMS Operation classes in particular, with the Universe database.

I've developed some classes that work with Oracle. When I try the same classes connecting to Universe, I get :
java.sql.SQLException: The result set is closed

The code has successfully gone into the overridden
protected Object mapRow(ResultSet resultSet, int i) throws SQLException
method but throws an exception somewhere in the execute method of the MappingSqlQuery.

I was wondering if it was closing the connection too soon. I overrode SingleConnectionDataSource to return false in the shouldClose method but it didn't make any difference.

If anybody has any suggestions, I'd be very grateful because I'd much rather use Spring JDBC than any other JDBC framework, such as iBatis.

Thanks in advance

Mike

[mlythgoe]

OK, after a lot of investigation, I think I've found a problem and its within Spring rather than the Universe JDBC driver, which is not what I'd expected!

JdbcTemplate class closes the result set after performing a query.
See:
public Object query( final String sql, final ResultSetExtractor rse ) throws DataAccessException

protected Object query(
PreparedStatementCreator psc, final PreparedStatementSetter pss, final ResultSetExtractor rse )

protected Map processResultSet( ResultSet rs, ResultSetSupportingSqlParameter param ) throws SQLException

It then callsGetWarnings() to get any SQLWarning messages.

But the Java spec (http://java.sun.com/j2se/1.4.2/docs/...ResultSet.html)

states:

"The warning chain is automatically cleared each time a new row is read. This method may not be called on a ResultSet object that has been closed; doing so will cause an SQLException to be thrown."

Does this mean most JDBC drivers are not checking whether the result set is closed when getWarnings() is called?
It certainly is being called in the Universe JDBC driver, and this appears to be the right way according to the JDBC spec.

Should this be raised in the Issue Tracker? I can't find anything relating to this.

Mike

[davison]

Hi Mike,

JdbcTemplate only calls getWarnings() on the various statement objects, not the actual result sets as far as I can see. When these calls are made, the statement objects have not themselves been closed.

Do you have a stack trace?

Cheers,

[mlythgoe]

Darren,
thanks for the reply.

The getWarnings is being called on statement objects but according to the spec on the Statement class:

Note: If you are processing a ResultSet object, any warnings associated with reads on that ResultSet object will be chained on it rather than on the Statement object that produced it.

So the statement is going to the result set for warnings (and I've checked it is actually doing it, not just that the spec says it should). Hence the problem.

The stack trace I'm getting is:

org.springframework.jdbc.UncategorizedSQLException : PreparedStatementCallback; uncategorized SQLException for SQL [SELECT StoredDecimalPlaces FROM JSQL.CURRENCY WHERE CurrencyCode = ?]; SQL state [01S01]; error code [951053]; The result set is closed; nested exception is java.sql.SQLException: The result set is closed
java.sql.SQLException: The result set is closed
at com.ibm.u2.jdbc.UniJDBCMsgFactory.createException( UniJDBCMsgFactory.java:109)
at com.ibm.u2.jdbc.UniJDBCResultSetImpl.getWarnings(U niJDBCResultSetImpl.java:478)
at com.ibm.u2.jdbc.UniJDBCStatementImpl.getWarnings(U niJDBCStatementImpl.java:362)
at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:461)
at org.springframework.jdbc.core.JdbcTemplate.query(J dbcTemplate.java:512)
at org.springframework.jdbc.core.JdbcTemplate.query(J dbcTemplate.java:543)
at org.springframework.jdbc.core.JdbcTemplate.query(J dbcTemplate.java:564)
at org.springframework.jdbc.object.SqlQuery.execute(S qlQuery.java:114)
at org.springframework.jdbc.object.SqlQuery.execute(S qlQuery.java:124)

Mike

[trisberg]

That's not how I interpret the docs. They state:

public SQLWarning getWarnings() throws SQLException

Retrieves the first warning reported by calls on this Statement object. Subsequent Statement object warnings will be chained to this SQLWarning object.
The warning chain is automatically cleared each time a statement is (re)executed. This method may not be called on a closed Statement object; doing so will cause an SQLException to be thrown.

Note: If you are processing a ResultSet object, any warnings associated with reads on that ResultSet object will be chained on it rather than on the Statement object that produced it.

I interpret this to mean that if you want the warnings produced by the ResultSet then you call getWarnings on the ResultSet object before it is closed. If you want warning produced by operations on the Statement then call the getWarnings on the Statement object. I don't see any mention of the Statement.getWarnings() chaining to any ResultSet warnings. It actually says you have to call getWarnings() on the ResultSet object to get warnings produced by any calls to it.

[davison]

	at com.ibm.u2.jdbc.UniJDBCResultSetImpl.getWarnings(UniJDBCResultSetImpl.java:478)
	at com.ibm.u2.jdbc.UniJDBCStatementImpl.getWarnings(UniJDBCStatementImpl.java:362)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:461)

I agree with Thomas. It does look from the stack trace like the driver is either incorrectly propagating Statement.getWarnings() calls on to the closed ResultSet, or not swallowing the resulting SQLException when it finds the ResultSet closed.

[mlythgoe]

Well guys, I think you're right.

I hadn't read the spec accurately enough.
Which means its a problem with the JDBC driver after all.
The odds were always in favour of that being the case

It does mean that I have to try to get an updated driver, and whilst I wait for Hell to freeze over, I'll have to use something like iBATIS rather than Spring JDBC. Still, that's what I've been doing today and Spring helps out with that quite well.

Thanks for looking into it.

[trisberg]

Worst case, we could always add a flag to JdbcTemplate to bypass any calls to getWarnings. There already is a flag to set whether you want exceptions to be thrown or not for warnings. This would have to wait until after the 1.2.4 release though.

[mlythgoe]

Thomas,

anything that would allow me to use Spring JDBC would be great.
And changing JdbcTemplate to not bother calling getWarnings() if the flag is set to ignore warnings seems to make sense anyway.

Thanks in advance

Mike

[Charles]

Hi,

Disassembling the IBM driver code reveals an issue in UniJDBCStatementImpl.java where the getWarrnings is chained through to a UniJDBCResultSet (mSavedResult.getWarnings()) which raises a SQLException. The following code silently swallows the expected exception:

   
public SQLWarning getWarnings()
        throws SQLException {
        if(mSavedResult != null) {
            SQLWarning sqlwarning = null;
			try {
				sqlwarning = mSavedResult.getWarnings();
			} catch (SQLException expectedException) {
				// Almost certainly a 951053 Result Set already closed
				assert (expectedException.getErrorCode() == 951053): "Improperly swallowed exception: " + expectedException.getLocalizedMessage();
			}
            if(sqlwarning != null) {
                addWarning(sqlwarning);
                mSavedResult.clearWarnings();
            }
        }
        return mStmtWarnings;

Please remember that I don't have access to IBM's test cases, so there may be exceptions that should be passed back up the chain. I'll update this thread should I find any such instances.

Cheers,
Charles