This week's book giveaways are in the Java EE and JavaScript forums.
We're giving away four copies each of The Java EE 7 Tutorial Volume 1 or Volume 2(winners choice) and jQuery UI in Action and have the authors on-line!
See this thread and this one for details.
The moose likes JDBC and the fly likes Problem with BATCH UPDATES Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Problem with BATCH UPDATES" Watch "Problem with BATCH UPDATES" New topic
Author

Problem with BATCH UPDATES

Sam Drake
Ranch Hand

Joined: Jun 05, 2003
Posts: 33
Hi,
I'm using code to do some batch updates for a huge set of records (400,000 approx) from one table into another. So, obviously, I read from one table into a resultset and then iterate through it and insert it using the batch update mechanism.
The problem is that after say 'n' number of batch updates the update-query gives me this error - "inserted value too large for column". Now I expected this to be an error with my first table data and some fields not matching. But it is not so. If the batchsize that i use to insert is varied then the error also occurs at random record count.
eg:
If the insertbatchsize = 10; error at record# 5000
If the insertbatchsize = 100; error at record# 305000
If the insertbatchsize = 200; error at record# 332000
The code for doing batch inserting is here -
<code>
// Setting up the batch size initially
batchInsertSize = 100;
....
....
// Adding inserts to the preparedStatement
preparedStatement.addBatch();
// Keeping a count of Records
countOfRecordsInBatch++;
// If number of batch inserts matches the count perform executeBatch
if (countOfRecordsInBatch % batchInsertSize == 0) {
int[] updatedRecords = preparedStatement.executeBatch();

// Commit the transaction
commitTransaction();
// Clear the statement
preparedStatement.clearBatch();
// Re-initialize the count
countOfRecordsInBatch = 0;
}
// This is called in a method yet again..and so on
</code>
Please tell me where the error is at. Please help. I have tried a lot.
Is there anything wrong or anything specific to do while doing batch inserts?
Thanks,
Sam.
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Sam, this is not a batch problem, but one of data. One of the data columns you are inserting into is too short for the column value you are trying to insert. Say you have a VARCHAR2(25) column and you try to jam 26 or more characters in there, and kaplowie ( aka crash and burn )!!! The only way to know where this error is occurring is to test this code without batching first, or check the column/data size before you batch it. This may throw a batch exception, but the root is most likely a simple SQLException. So remove the batch, and update each one separately to see what exactly is causing the error and move on from there.
Jamie
Sam Drake
Ranch Hand

Joined: Jun 05, 2003
Posts: 33
Hi,
But that is the inetersting part - Single inserts are working like a charm. Though it takes time, single inserts are working fine.
Any clue as to what could be going wrong?
Thanks n regards,
Sam.
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

which database/driver implementation are you using? What if you re-create the PreparedStatement say every 4000 inserts? Not sure what is going on here?
Sam Drake
Ranch Hand

Joined: Jun 05, 2003
Posts: 33
Oracle/JDBC 2.0
regards,
Sam.
 
Consider Paul's rocket mass heater.
 
subject: Problem with BATCH UPDATES