Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

DataIntegrityViolationException Caused by: java.sql.BatchUpdateException: Data truncation

 
Aurelia Sony
Greenhorn
Posts: 6
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a running java batch process with thousands of records going into Microsoft SQL DB. Below is pretty much the code:

ApplicationContext:

DaoImpl:

It runs fine most of the time but once in a while it stops with below exception and I am clueless because it doesn't say anything about any data size exceeding allowed column size or violation of any constraint. exception trace:

org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO USERS_temp(uid, FirstName, MI, LastName, SyncDate, Status, DisabledDateTime, Exclude, Notes, UpdateID, UpdateDateTime) VALUES (?,?,?,?,?,?,?,?,?,?,?)]; Data truncation; nested exception is java.sql.BatchUpdateException: Data truncation
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:603)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:615)
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:884)
at com.example.UserDAOImpl.batchInsertUsers(UsersDAOImpl.java:175)
at com.example.UserServiceImpl.processData(UserServiceImpl.java:184)
at com.example.UserMain.main(UserMain.java:23)
Caused by: java.sql.BatchUpdateException: Data truncation
at net.sourceforge.jtds.jdbc.JtdsStatement.executeBatch(JtdsStatement.java:947)
at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:294)
at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:899)
at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:1)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587)
... 5 more

Table Structure

The process continues and gives same error for the next two batch insertions in other two tables. But here there are no details of any column or record. Are there any suggestions on how to figure out the problem and resolve it?
 
Joe Harry
Ranch Hand
Posts: 10128
3
Eclipse IDE Mac PPC Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What is the constraint that you have? Could you please show us your create table scripts?
 
Aurelia Sony
Greenhorn
Posts: 6
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There is only a primary key constraint and an Index on UID column. I will share the script after I go to office.
Just an update, I ran same code from my eclipse and a unix machine almost at the same time. The eclipse one gave the mentioned error while the unix one ran fine and inserted ~100k records in db.
 
Aurelia Sony
Greenhorn
Posts: 6
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Fixed it. UpdateID column is limited to 10 characters but I was trying to insert "JAVAPROCESS". I had to find this manually though.
Funny thing I tried to fix a spellling mistake "JAVAPROCES" and ended up with this error that took so long to fix
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Aurelia Sony,

First of all, a warm welcome to CodeRanch!

Aurelia Sony wrote:Fixed it. UpdateID column is limited to 10 characters but I was trying to insert "JAVAPROCESS". I had to find this manually though.
Funny thing I tried to fix a spellling mistake "JAVAPROCES" and ended up with this error that took so long to fix

Yeah, sometimes JDBC error messages are quite obscure and it could take plenty of time to find the cause of the error. Although "Data truncation" is quite obvious (it means some value was too long to fit in a column), it would be very nice if the error mentioned to which column this error occured and it would be really awesome if the violating value was added to the error as well

Thanks for coming back and sharing the solution of your problem. Have a cow for that!

Kind regards,
Roel
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic