Hi,

> I was testing your JDBC driver with an Open Source Persistence Layer
> package written by Artyom Rudoy that handles all the direct calls to
> the JDBC API. I finally figured out what was happening between the
> time the ResultSet is created and the next() method was called.
> Here is a simple test case that duplicates the error:
>
> ResultSet rs = pstmt.executeQuery();
> conn.commit();
> while(rs.next()) {
>
> The Persistence Layer determines if the JDBC driver supports
> transactions and turns off AutoCommit on the connection. It then
> calls commit() after each successfull transaction and rollback()
> after a failed transaction. It does this with Query transactions as
> well. Apparently the commit call to the JDBC driver invalidates the
> database cursor.

I suspected that. It seems that that persistence layer ignores (or
does not check) DatabaseMetaData.supportsOpenCursorsAcrossCommit() result.

> I am not sure what is the correct way to resolve this problem. For
> now I have disabled the transaction processing so that autocommit is
> never turned off. Should I modify the Persistence Layer to only
> call commit after an Update transaction or should the JDBC driver
> preserve the ResultSet after a call to commit()?

You should modify persistence layer by either doing commit after
fetching your data or to check the result of
DatabaseMetaData.supportsOpenCursorsAcrossCommit() and commit only
when the result is "true". Actually commit after doing a select is
needed only if you do a select from selectable procedure that modifies
database behind the scenes. In other cases commit will not help you.

Roman