Advanced question on batched Inserts with JDBC

I am using the classes12.zip Thin JDBC driver on a 8.1.6 database. I am reading in from a large file with records made up of fixed length fields. Sometimes I get duplicate records and thus duplicate primary key in the same vicinity (i.e. the same batch). In this case the executeUpdate() fails before the end of the batch. If the duplicate keys are further apart than the batch size then the executeUpdate() that automatically runs sendBatch when the batch size is reached fails.

In fact it is even more complex than that. From the one input record I am using one OraclePreparedStatement for a parent table, another for a child table of that parent table and a final child of that table. These are sequenced to make sure the topmost parent is first and thus foreign keys in the child are available and so forth.

When I get an SQLException error with the executeUpdate() I attempt to rollback the batch and then call a routine that attempts to Insert a row at a time (for each of the 3 PreparedStatements in the correct sequence) and if that fails with a ORA-00001 (unique constraint violated) it attempts an update. After it has worked through all the records in that batch it does a commit.

My question is, does the connection.rollback rollback all Statements in the batch for each of the 3 PreparedStatements. If it does so, then why when I do the commit after the individual statements (batch size set to 1 for these) that fallback to an Update, do I still get ORA-00001 errors?

Thanks

David Received on Wed Jan 30 2002 - 16:22:50 CST