Recently I was working inwards Java application which uses Microsoft SQL Server at its backend. The architecture of Java application was erstwhile i.e. fifty-fifty though at that topographic point was heavy database communication dorsum as well as forth at that topographic point was no ORM used e.g. no Hibernate, JPA, or Apache iBatis. The Java application was using erstwhile DAO pattern pattern, where the DB related classes which are responsible for loading as well as storing information from database was calling stored physical care for to practise their Job. These stored physical care for takes information from Java application as well as insert into SQL Server tables. One day, i of my collegue called me to troubleshoot "java.sql.BatchUpdateException: String or binary information would last truncated", which it's application was throwing as well as he has no clue whatsoever that what is incorrect alongside the information he is getting from other organisation as well as trying to store.
The stacktrace appear similar below:
java.sql.BatchUpdateException: String or binary information would last truncated.
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(Unknown Source)
When I saw the stacktrace I took large sigh because at that topographic point was no scream of attribute or column which is failing the JDBC call. To brand things worse, it was coming from a batch i.e. at that topographic point could last 100 records inwards a batch as well as out of those 100 records, each having to a greater extent than than eighty fields, i of the land is throwing error.
In this article, I'll explains how I troubleshoot the work as well as fixed the resultant exclusively later my collegue hope me a luncheon :-)
The mistake was quite full general if yous receive got worked inwards SQL Server as well as it was coming from JDBC driver. The argue for this mistake was trying to shop value larger than the column tin move allow e.g. if a column is declared equally char(1) as well as yous are trying to shop a String to a greater extent than than i grapheme e.g. "DEFAULT" as well as thus this mistake volition occur. H5N1 similar mistake tin move occur inwards the instance of the numeric information type where the value is larger than what the datatype of a item column tin move allow. You tin move also see T-SQL fundamentals or Querying Microsoft SQL Server to acquire to a greater extent than nearly "String or binary information would last truncated" mistake inwards SQL Server.
Obviously, the solution is to honor as well as ready the code which is trying to practise that. Sometimes it's information fault where yous really got the bad information e.g. for a land which tin move receive got "Y" as well as "N", yous got the "Yes" or "No", but most of the fourth dimension it's your code which is doing the incorrect thing. To honor out the actual cause, I asked him if he had made whatever modify inwards DAO layer. He told me that he has added a novel field. That was plenty for me to troubleshoot, I asked what are the places he has made changes as well as he told me xviii Java files as well as bunch of stored procedures. That was also many to appear each of them, but nosotros had to.
The side past times side mensuration was to minimize the search as well as locate the problem, thus I asked which stored proc is called when this batch is stored into DB, he indicate me to the stored physical care for as well as when I looked at it closely, I spotted the error. It was a a classic instance of human mistake spell updating a stored physical care for as well as nosotros are storing the value of to a greater extent than or less other VARCHAR variable into a char(1) field. Since both values are quite similar, nosotros didn't realize until nosotros checked every unmarried field.
The biggest challenge alongside this work is to honor the column for which this mistake is coming up. If yous meet the mistake message "java.sql.BatchUpdateException: String or binary information would last truncated", at that topographic point is no scream of the column. This makes it real hard to locate the source/target of column If yous acquire this mistake inwards Java application spell updating a tabular array using stored physical care for of to a greater extent than than a handful parameters.
At this time, your sense volition aid yous e.g. from when did the mistake initiative of all coming upward as well as and thus comparison the concluding working version of stored physical care for alongside the electrical flow one, non simply past times looking at it but past times using a professional person comparison tool similar Beyond Compare. This volition highlight such errors. If yous are novel to JDBC as well as thus yous tin move also read JDBC chapters from Java: How to Program past times Dietel as well as Dietel to acquire to a greater extent than nearly them.
Tip:
If yous are updating the tabular array using JDBC as well as stored physical care for as well as setting upward a lot of parameters as well as thus equally I said, it becomes extremely hard to honor the column for which this mistake is coming up. To honor the column, yous should capture the value of each parameter inwards the log as well as endeavour to manually execute the stored physical care for inwards the database. There also yous volition acquire the same mistake e.g. "String or binary information would last truncated", but as well as thus yous tin move play alongside each column past times commenting them until yous honor the column which is creating the problem.
Other Java as well as JDBC mistake troubleshooting guides yous may like
Further Learning
JSP, Servlets as well as JDBC for Beginners: Build a Database App
Complete JDBC Programming Part 1 as well as 2
Java Platform: Working alongside Databases Using JDBC
The stacktrace appear similar below:
java.sql.BatchUpdateException: String or binary information would last truncated.
com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(Unknown Source)
When I saw the stacktrace I took large sigh because at that topographic point was no scream of attribute or column which is failing the JDBC call. To brand things worse, it was coming from a batch i.e. at that topographic point could last 100 records inwards a batch as well as out of those 100 records, each having to a greater extent than than eighty fields, i of the land is throwing error.
In this article, I'll explains how I troubleshoot the work as well as fixed the resultant exclusively later my collegue hope me a luncheon :-)
Cause of java.sql.BatchUpdateException: String or binary information would last truncated:
The mistake was quite full general if yous receive got worked inwards SQL Server as well as it was coming from JDBC driver. The argue for this mistake was trying to shop value larger than the column tin move allow e.g. if a column is declared equally char(1) as well as yous are trying to shop a String to a greater extent than than i grapheme e.g. "DEFAULT" as well as thus this mistake volition occur. H5N1 similar mistake tin move occur inwards the instance of the numeric information type where the value is larger than what the datatype of a item column tin move allow. You tin move also see T-SQL fundamentals or Querying Microsoft SQL Server to acquire to a greater extent than nearly "String or binary information would last truncated" mistake inwards SQL Server.
Solutionof java.sql.BatchUpdateException: String or binary information would last truncated:
Obviously, the solution is to honor as well as ready the code which is trying to practise that. Sometimes it's information fault where yous really got the bad information e.g. for a land which tin move receive got "Y" as well as "N", yous got the "Yes" or "No", but most of the fourth dimension it's your code which is doing the incorrect thing. To honor out the actual cause, I asked him if he had made whatever modify inwards DAO layer. He told me that he has added a novel field. That was plenty for me to troubleshoot, I asked what are the places he has made changes as well as he told me xviii Java files as well as bunch of stored procedures. That was also many to appear each of them, but nosotros had to.
The side past times side mensuration was to minimize the search as well as locate the problem, thus I asked which stored proc is called when this batch is stored into DB, he indicate me to the stored physical care for as well as when I looked at it closely, I spotted the error. It was a a classic instance of human mistake spell updating a stored physical care for as well as nosotros are storing the value of to a greater extent than or less other VARCHAR variable into a char(1) field. Since both values are quite similar, nosotros didn't realize until nosotros checked every unmarried field.
The biggest challenge alongside this work is to honor the column for which this mistake is coming up. If yous meet the mistake message "java.sql.BatchUpdateException: String or binary information would last truncated", at that topographic point is no scream of the column. This makes it real hard to locate the source/target of column If yous acquire this mistake inwards Java application spell updating a tabular array using stored physical care for of to a greater extent than than a handful parameters.
At this time, your sense volition aid yous e.g. from when did the mistake initiative of all coming upward as well as and thus comparison the concluding working version of stored physical care for alongside the electrical flow one, non simply past times looking at it but past times using a professional person comparison tool similar Beyond Compare. This volition highlight such errors. If yous are novel to JDBC as well as thus yous tin move also read JDBC chapters from Java: How to Program past times Dietel as well as Dietel to acquire to a greater extent than nearly them.
Tip:
If yous are updating the tabular array using JDBC as well as stored physical care for as well as setting upward a lot of parameters as well as thus equally I said, it becomes extremely hard to honor the column for which this mistake is coming up. To honor the column, yous should capture the value of each parameter inwards the log as well as endeavour to manually execute the stored physical care for inwards the database. There also yous volition acquire the same mistake e.g. "String or binary information would last truncated", but as well as thus yous tin move play alongside each column past times commenting them until yous honor the column which is creating the problem.
Other Java as well as JDBC mistake troubleshooting guides yous may like
- java.sql.SQLException: No suitable driver institute 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 gain - JDBC (solution)
- How to ready "Error: Could non honor or charge original class" inwards Eclipse? (guide)
- 10 mutual reasons of java.lang.NumberFormatException inwards Java? (tutorial)
- How to ready Caused By: java.lang.NoClassDefFoundError: org/apache/log4j/Logger (solution)
- How to ready 'javac' is non recognized equally an internal or external command (solution)
- How to ready "illegal initiative of all of expression" compile fourth dimension mistake inwards Java? (tutorial)
- How to connect to MySQL database inwards Java? (tutorial)
- java.lang.ClassNotFoundException: org.apache.commons.logging.LogFactory mistake (solution)
- Common reasons of java.lang.ArrayIndexOutOfBoundsException inwards Java? (solution)
- java.lang.ClassNotFoundException : org.Springframework.Web.Context.ContextLoaderListener (solution)
- How to solve "variable powerfulness non receive got initialized" compile fourth dimension mistake inwards Java? (answer)
- Fixing java.lang.unsupportedclassversionerror unsupported major.minor version 50.0 (solution)
- How to solve "could non create the Java virtual machine" mistake inwards Java? (solution)
- How to solve java.lang.OutOfMemoryError: Java Heap Space inwards Eclipse, Tomcat? (solution)
- How to avoid ConcurrentModificationException inwards Java? (tutorial)
- Cause as well as solution of "class, interface, or enum expected" compiler mistake inwards Java? (fix)
Further Learning
JSP, Servlets as well as JDBC for Beginners: Build a Database App
Complete JDBC Programming Part 1 as well as 2
Java Platform: Working alongside Databases Using JDBC
No comments:
Post a Comment