> To better understand: if I have a system with 1-million lines of code,
> with so many Statements/Prepared/Call/etc and AutoCommit=true (default
> value), do I have to change all the code putting AutoCommit=false?

More or less... though I doubt that you can have a stable system that works
in auto-commit mode with more than 1,000 lines of code, but that is my
personal opinion :-)

> I think this is a crazy method for SUN to change the way things work in
> systems.

Well... the issue is that we allowed too much in our previous version of
JDBC driver. The behaviour of the driver in auto-commit mode was not
specified by Sun detailed enough to allow same interpretation until JDBC
3.0.

Firebird does not support open cursors accross commits (there is a
workaround with COMMIT RETAIN, but it brings more problems than solves).
Since auto-commit means that each statement has to be executed in a separate
transaction, we had to provide this functionality in the driver. We (Jaybird
team) felt that supporting only one open statement in auto-commit mode is
too restrictive and JDBC specification (and support forums) were of little
help, it was decided to cache the complete result set locally and commit
transaction right after query execution.

This decision had one very big drawback - the memory usage. So, when JDBC
3.0 has specified the exact behaviour in auto-commit case, we have decided
to follow it, as we no longer needed to cache the result set locally, since
the lifetime of the request is now clearly defined and this case is fully
supported by Firebird.

You can get the old functionality by constructing scrollable and holdable
result set:

Statement stmt = conn.createStatement(
ResultSet.TYPE_SROLL_INSENSITIVE,
ResultSet.CONCUR_READONLY,
ResultSet.HOLD_CURSORS_OVER_COMMIT);
ResultSet rs = stmt.executeQuery(....);
while(rs.next()) {
// do something else
}

but this requires changing the application.

So, as it was already posted on this list many times, in Jaybird 2.1 we will
include connection propery that will allow to specify the type, concurrency
and holdability of the default result set (the one you create by
Connection.createStatement() method).

Why didn't we include this in 2.0? The answer is simple - nobody complained
loudly, and those that found their system to be incompatible with JDBC 3.0
decided to rather fix the system to be specification compatible than to
require a workaround. However, already after 2.0 release it was discovered
that OpenOffice 2.0 has problems too, and since we want OpenOffice beginners
to use Firebird, we will add such workaround.

> Imagine each new JDBC spec if we have to change our "error-proof" code,
> that worked fine in older versions of JDBC.

That is not 100% true. Previous case was "implementation specific", now it
is standardized. This is always so when one relies on
implementation-specific artifacts.

Roman