Thursday, 10 November 2016

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. 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.

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.

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.

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

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.