Re: JDBC batching

On Tue, 22 Jan 2002 13:42:48 -0500, you wrote:
>I want to convert this to the batch interface, i.e., addBatch() and
>executeBatch(), but I read that this is implemented as multiple round trips
>anyway in 7.1.x and before. I also read that this might be fixed in 7.2, but
>I've been unable to verify this in the documentation.
>
>Can anyone enlighten me on the state of the batching interface? or is there
>some better way to do this?

In a discussion in august 2001 on this list we reached the
conclusion summarized below. However, AFAIK nobody is working on
this yet.

There is more in the list archive, when it is back online.

-+-+-
The current implementation of Statement.executeBatch() in the
JDBC driver does not provide any performance improvement
compared to processing statements individually. This is because
the driver executes the statements one-by-one when
executeBatch() is called, using one round trip per statement. We
intend to reimplement executeBatch() in the following way.

Statement.executeBatch() will send all statements in a single
semicolon separated query string, with only one round trip to
the backend. This will provide a performance improvement, as
intended by the JDBC specification.

The updateCounts array will be set as described below. Note that
the JDBC spec defines the meaning of the following special
values:

-2 the statement was executed successfully but the number of
affected rows is unknown
-3 the statement failed to execute successfully

If all statements succeed, executeBatch() returns an
updateCounts array with a row count for each statement in the
batch, however the value for all but the last statement will be
-2. The value for the last statement will be a proper update
count.
If a statement fails, executeBatch() throws a
BatchUpdateException containing an updateCounts array with a row
count of -3 for each statement in the batch.

Note that the behaviour will be the same when autocommit is
enabled and when it is disabled. Even with autocommit enabled,
the backend will commit or rollback all statements in the
semicolon-separated query string as a unit.

The new implementation of executeBatch() will cause a change in
behaviour of the driver: the driver will no longer return update
counts for all statements in a batch like it currently does, it
will return -2 ("unknown") for most statements. However, this
behaviour is allowed by the JDBC spec and applications should be
prepared to handle it.
-+-+-

Regards,
René Pijlman <rene@lab.applinet.nl>