Thursday, May 21, 2009

java.sql.DataTruncation: Data truncation



Problem :
We come across this common exception when we are using Database. While we are inserting or updating a table field with data which is longer than the actual data which the field can hold.

Exception I Encountered Was :
#########################################################################################
java.sql.DataTruncation: Data truncation
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:382)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:631)
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:505)
at CSV2DBadaptor.readExcel(CSV2DBadaptor.java:176)
at CSV2DBadaptor.main(CSV2DBadaptor.java:325)
#########################################################################################

Solution :
As said above in Problem section we face this common exception when we are inserting data which exceeds the table field size. We need to log the actual field name and then compare the data size with the filed size. If the data size is more than the table field size then you can either truncate the data of the field to fit into the table field or increase the table field size to accomodate the data. Think appropriately before designing your solution.

Example :
Say i am using:
DB : SalesLibrary (MySql DB)
Table Name: Global
Field Name: Object_id (Size varchar(20))
Actual Data: "5983-0020NOE5983-0020ITE"

In my case the data was important so i coud'nt truncate the data, instead i increased the Field size to varchar(30) and was able to resolve the issue.

Similar Exception :
     - java.sql.BatchUpdateException: Data truncation
- java.sql.SQLException: Data truncation

No comments:

Post a Comment