JDBC - How to solve java.sql.BatchUpdateException: String or binary data would be truncated.

Recently I was working in Java application which uses Microsoft SQL Server at its backend. The architecture of Java application was old i.e. even though there was heavy database communication back and forth there was no ORM used e.g. no Hibernate, JPA, or Apache iBatis. The Java application was using old DAO design pattern, where the DB related classes which are responsible for loading and storing data from database was calling stored procedure to do their Job. These stored procedure takes data from Java application and insert into SQL Server tables. One day, one of my collegue called me to troubleshoot "java.sql.BatchUpdateException: String or binary data would be truncated", which it's application was throwing and he has no clue whatsoever that what is wrong with the data he is getting from other system and trying to store.

The stacktrace look like below:
java.sql.BatchUpdateException: String or binary data would be truncated.
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(Unknown Source)

When I saw the stacktrace I took big sigh because there was no mention of attribute or column which is failing the JDBC call. To make things worse, it was coming from a batch i.e. there could be 100 records in a batch and out of those 100 records, each having more than 80 fields, one of the field is throwing error.

In this article, I'll explains how I troubleshoot the problem and fixed the issue only after my collegue promise me a lunch :-)

Cause of java.sql.BatchUpdateException: String or binary data would be truncated:

The error was quite general if you have worked in SQL Server and it was coming from JDBC driver. The reason for this error was trying to store value larger than the column can allow e.g. if a column is declared as char(1) and you are trying to store a String more than one character e.g. "DEFAULT" then this error will occur. A similar error can occur in the case of the numeric data type where the value is larger than what the datatype of a particular column can allow.  You can also see T-SQL fundamentals or Querying Microsoft SQL Server to learn more about "String or binary data would be truncated" error in SQL Server.

Solutionof java.sql.BatchUpdateException: String or binary data would be truncated:

Obviously, the solution is to find and fix the code which is trying to do that. Sometimes it's data fault where you genuinely got the bad data e.g. for a field which can accept "Y" and "N", you got the "Yes" or "No", but most of the time it's your code which is doing the wrong thing. To find out the actual cause, I asked him if he had made any change in DAO layer. He told me that he has added a new field. That was enough for me to troubleshoot, I asked what are the places he has made changes and he told me 18 Java files and bunch of stored procedures. That was too many to look each of them, but we had to.

The next step was to minimize the search and locate the problem, so I asked which stored proc is called when this batch is stored into DB, he point me to the stored procedure and when I looked at it closely, I spotted the error.  It was a a classic case of human error while updating a stored procedure and we are storing the value of another VARCHAR variable into a char(1) field. Since both values are quite similar, we didn't realize until we checked every single field.

The biggest challenge with this problem is to find the column for which this error is coming up. If you see the error message "java.sql.BatchUpdateException: String or binary data would be truncated", there is no mention of the column. This makes it very difficult to locate the source/target of column If you get this error in Java application while updating a table using stored procedure of more than a handful parameters.

At this time, your experience will help you e.g. from when did the error start coming up and then comparing the last working version of stored procedure with the current one, not just by looking at it but by using a professional comparing tool like Beyond Compare. This will highlight such errors. If you are new to JDBC then you can also read JDBC chapters from Java: How to Program by Dietel and Dietel to learn more about them.

If you are updating the table using JDBC and stored procedure and setting up a lot of parameters then as I said, it becomes extremely difficult to find the column for which this error is coming up. To find the column, you should capture the value of each parameter in the log and try to manually execute the stored procedure in the database. There also you will get the same error e.g. "String or binary data would be truncated", but then you can play with each column by commenting them until you find the column which is creating the problem.

Other Java and JDBC error troubleshooting guides you may like
  • java.sql.SQLException: No suitable driver found for 'jdbc:mysql://localhost:3306/mysql [Solution]
  • How to solve java.lang.ClassNotFoundException: com.mysql.jdbc.Driver error? (hint)
  • How to solve java.lang.classnotfoundexception oracle.jdbc.driver.oracledriver? (solution)
  • java.sql.SQLServerException: The index 58 is out of range - JDBC (solution)
  • How to fix "Error: Could not find or load main class" in Eclipse? (guide)
  • 10 common reasons of java.lang.NumberFormatException in Java? (tutorial)
  • How to fix Caused By: java.lang.NoClassDefFoundError: org/apache/log4j/Logger (solution)
  • How to fix 'javac' is not recognized as an internal or external command (solution)
  • How to fix "illegal start of expression" compile time error in Java? (tutorial)
  • How to connect to MySQL database in Java? (tutorial)
  • java.lang.ClassNotFoundException: org.apache.commons.logging.LogFactory error (solution)
  • Common reasons of java.lang.ArrayIndexOutOfBoundsException in Java? (solution)
  • java.lang.ClassNotFoundException : org.Springframework.Web.Context.ContextLoaderListener (solution)
  • How to solve "variable might not have initialized" compile time error in Java? (answer)
  • Fixing java.lang.unsupportedclassversionerror unsupported major.minor version 50.0 (solution)
  • How to solve "could not create the Java virtual machine" error in Java? (solution)
  • How to solve java.lang.OutOfMemoryError: Java Heap Space in Eclipse, Tomcat? (solution)
  • How to avoid ConcurrentModificationException in Java? (tutorial)
  • Cause and solution of "class, interface, or enum expected" compiler error in Java? (fix)

Further Learning
JSP, Servlets and JDBC for Beginners: Build a Database App
Complete JDBC Programming Part 1 and 2
Java Platform: Working with Databases Using JDBC

No comments:

Post a Comment

Feel free to comment, ask questions if you have any doubt.