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.
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
posted
0
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.
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?
0
