ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Christopher Lamey <cla...@localmatters.com>
Subject Re: public int executeBatch()
Date Fri, 02 Jun 2006 17:34:20 GMT
On Thu, 2006-06-01 at 20:45 -0400, rs wrote:
> 
>  --- On Thu 06/01, Christopher Lamey < clamey@localmatters.com > wrote:
> > FWIW, BatchUpdateException is thrown if there's an exception during a
> > batch update and has enough info for me to figure out what broke when.
> 
> I'd be interested to find out more how it works out for you.
> 
> Say, a batch has 10K records. 300 of them may fail, for various reasons. How do you figure
out *why* those records failed? Do you run your batch 300 times, fixing one record at a time?
Say, you need to give feedback to a 3rd party about failed records and the reasons, how do
you do that?

In my current project, because we support different databases that may
or may not have drivers that continue after hitting an error (most do
not), my first approach is to go through each one as they come up.

Since that's a slow process and not helpful for large 3rd party data
sets, there are some validation steps I might do before trying the
inserts.  The validation can be parsing the input file directly and
looking for problems or loading the data first into a flat staging
table.  In some cases I've used the RDBMS' native bulk loaders (sqlldr,
psql 'COPY', bc.exe, etc) to get large data sets (30+ million rows) into
a staging table as fast as possible.  Then I look for problem data in
the staging table data and go from there.

> Also, when one of the records fails, the whole batch will be rolled back. Suppose, some
records cannot be fixed but you still want "good" records to be imported. How do you deal
with that?

To be honest, since this behavior is driver and RDBMS dependent, I don't
expect or use it.  The update counts are in the same boat, what they
mean changes between vendors and even on the type of sql executed.

Oracle says they halt on the first error, set all the result counts to
-3, and a rollback is required when using prepared statements in a
batch.  Generic statements can be commit()'d up to to the one that
caused the problem and the counts are correct, but generic statements in
a batch are just executed as regular statements:

http://www.lc.leidenuniv.nl/awcourse/oracle/java.920/a96654/oraperf.htm#1059011

Postgres bails on the first exception in a batch as well, and also has
funky update count issues.  Last time I used SQL Server it did the same
thing.

It looks like MySQL can be configured (continueBatchOnError=true) to
continue after the first error and only throw one at the end.  It's not
clear how you would get a list of all the exceptions that happened at
that point and how you'd correlate that to the update counts.  My guess
would be you'd have to loop through the getNextException() and keep a
count?

Maybe for now with iBATIS you could loop through the exceptions using
getNextException() and hope the messages are descriptive enough?

Cheers,
Chris


Mime
View raw message