How to solve java.sql.BatchUpdateException: String or binary data would be truncated in Java JDBC? [Solution]

Recently I was working in a Java application that uses Microsoft SQL Server at its backend. The architecture of the Java application was old i.e. even though there was heavy database communication back and forth there was no ORM used like no Hibernate, JPA, or Apache iBatis. The Java application was using an old DAO design pattern, where the DB related classes which are responsible for loading and storing data from the database was calling the stored procedure to do their job. These stored procedures take data from Java applications and insert it into SQL Server tables.

One day, one of my colleagues called me to troubleshoot "java.sql.BatchUpdateException: String or binary data would be truncated", which its application was throwing and he has no clue whatsoever that what is wrong with the data he is getting from other systems and trying to store.

The stack trace looks like below:

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

When I saw the stack trace I took a big sigh because there was no mention of an 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 fields is throwing error.

In this article, I'll explain how I troubleshoot the problem and fixed the issue only after my colleague promise me 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 the JDBC driver. The reason for this error was trying to store a value larger than the column can allow like if a column is declared as char(1) and you are trying to store a String with more than one character like "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 these Microsoft SQL Server Courses to learn more about "String or binary data would be truncated" errors in SQL Server.

Solution of 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 a data fault where you genuinely got the bad data like for a field that 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 the 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 a bunch of stored procedures. That was too many to look at 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 points me to the stored procedure and when I looked at it closely, I spotted the error.  It was 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 the column If you get this error in the Java application while updating a table using the stored procedure of more than a handful of 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 the 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. By the way, If you are new to JDBC then you can also join these free JDBC courses to learn more about them.

Tip:

If you are updating the table using JDBC and stored procedure and set 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 like "String or binary data would be truncated", but then you can play with each column by commenting on 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)