>> You can have multiple open result sets for different statements in
>> non-autocommit mode. So you can loop though one result set and execute
>> statements within the loop.
>>
>> In auto-commit mode specification requires us to close previous
>> statement before we execute next one. In this case previous example with
>> loop won't work.
>
> I´m very glad to know i can open multiple statements/resultsets in non-autocommit mode. Is it part of JDBC specification? It will avoid me to use CachedRowSets in many places in my application...

I'd formulate it rather, that JDBC specification in non-autocommit mode
does _not_ require to "finish" the statement when another one is executed.

I suspect that this is still implementation dependent, whether
underlying DB supports this. Firebird does, Oracle does, DB2 does, IIRC
MySQL does as well. Maybe there are some the don't.

>> If you specify that you want scrollable result set, Jaybird will cache
>> all rows in memory. But that is implementation specifics.
>
> It´s a shame it's not part of JDBC specification :-(

I think this is a dark side of the specification. On one side it
requires to "finish" statement and close the result set, on the other
side it allows to have a "hold-cursor-over-commit" result sets.

I would expect that "scrollable" and "hold-cursor-over-commit" result
sets remain scrollable when another one is executed also in auto-commit
mode.

Roman