How to recover from failed JDBC batch Insert?

From: David <david.wynter_at_btclick.com>
Date: Thu, 31 Jan 2002 10:40:38 -0000
Message-ID: <1c968.15045$oK1.115962@NewsReader>

I posted another on this subject but it is a little involved. I am using 3 different OraclePreparedStatement with a batch size of 48. Because the data I am processing sometimes has duplicate keys that I cannot detect the batch fails when it reaches the limit of the batch and it is automatically executed. Pseudo code is:

somemethod()
{

    while(nextRecord()) {

// process a file to extract a record and its individual fields

        fields[] fields=processRecord(record);


        // cache record in case we needed to recover

        cache.addRecord(record);


        // call executeStatement for top most table

        executeStatement(top, fields);


        // call executeStatement for middle table (child to topmost and

parent to bottom)
        executeStatement(middle, fields);


        // call executeStatement for bottom table

        executeStatement(bottom, fields);
        }

}

executeStatement (Key whichone, fields[] fields) {

    // get the correct statement from HashMap     OraclePreparedStatement ps=(OraclePreparedStatement)psMap.get(whichone);     // populate statement with values
    ps.doPopulate(fields);
    // keep track of how many have been done     if(whichone==topmost) {

        insBatchSizeCount++;
    }
    // Do the execute to the batch
    try {

        ps.executeUpdate();
    }
    catch (SQLException e) {

// rollback which I assume means all of the OraclePreparedStatement
s

        dbConn.rollback();
        Set entries=psMap.entrySet();
        for (Iterator j=entries.iterator(); j.hasNext(); ) {
              Map.Entry e = (Map.Entry) j.next();
              OraclePreparedStatement
aStatement=(OraclePreparedStatement)e.getValue();
              aStatement.clearBatch();  <------------------------First error
        }


        // This method uses the cache of records setup in the calling method

to attempt an individual insert for each


        // If this fails because of a duplicate key (ORA-00001) it attempts

a Update, batchsize for these statements


        // is set to 1

        outputRecordCache(insBatchSizeCount);
        dbConn.commit();    <---------------------------------second error

    }

Where it shows error 1 above I get a SQLException with message "operation not allowed: operation cannot be mixed with Oracle-style batching". So what is clearBatch() for then!

If I leave out the clearBatch() loop from the code then I get a second SQLException, a ORA-00001 (unique constraint violated, i.e. duplicate keys). This makes me think that because I am not able to use clearBatch() that the statements still have the data in them and therefore execute when the commit happens. Is that right? How do I clear them out without destroying them, I want to continue using the OraclePreparedStatements.

I must say that the Oracle documentation on their 'performance extensions' is lousy, only the simplest case where nothing ever goes wrong is shown. Also this classes12.zip driver is long in the tooth. Surely a pro Java company as big as Oracle would have a driver that supports JDBC V2.1 or even at least V2.0, very poor.

Thanks

David Received on Thu Jan 31 2002 - 04:40:38 CST