• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

Problem with BATCH UPDATES

 
Ranch Hand
Posts: 33
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 33
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 33
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Oracle/JDBC 2.0
regards,
Sam.
 
He's giving us the slip! Quick! Grab this tiny ad!
Smokeless wood heat with a rocket mass heater
https://woodheat.net
reply
    Bookmark Topic Watch Topic
  • New Topic