wood burning stoves 2.0*
The moose likes JDBC and the fly likes Detect Column Causes Truncation Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Detect Column Causes Truncation" Watch "Detect Column Causes Truncation" New topic

Detect Column Causes Truncation

James Gordon
Ranch Hand

Joined: Aug 09, 2002
Posts: 106
If we would to insert a string with length wider than the defined
table column length, we will get a truncation error and the
SQLException will be thrown.
I'm just wondering whether there is a way to determine which
column that causes it. Else, we'll just have to print out
all fields being inserted and check 1 by 1.
This is very tedious and I end up wasting lots of time just
for this.
Please enlighten me.

Paul Sturrock

Joined: Apr 14, 2004
Posts: 10336

Have a look at java.sql.DatabaseMetaData. This is where all the meta data methods for JDBC are, and it gives you a route to interegate the model. Generally though you should never allow SQLExceptions caused by inserting values greater than the defined data type will allow, this should always be caught before you try the DB operation with some sort of validation layer. I wouldn't try your suggested validation method though - it would cause far more DB round trips than necessary, and is asking for data integrity errors.

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Jeanne Boyarsky
internet detective

Joined: May 26, 2003
Posts: 30123

The SQL exception contains a textual error message too. It tells you the column number that is causing the problem.

[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
James Gordon
Ranch Hand

Joined: Aug 09, 2002
Posts: 106
Hello Jeanne,
Perhaps that thing is Database and driver dependent.
I'm using datadirect driver and SQL Server 2000.
What I get is only this:
java.sql.SQLException: [DataDirect][SequeLink JDBC Driver][SQL Server]String or binary data would be truncated.
at java.lang.Throwable.<init>(Throwable.java:54)
at java.lang.Throwable.<init>(Throwable.java:68)
at java.sql.SQLException.<init>(SQLException.java:51)
at com.ddtek.sequelink.ssp.Diagnostic.toSQLException(Unknown Source)
at com.ddtek.sequelink.ssp.Chain.cnvDiagnostics(Unknown Source)
at com.ddtek.sequelink.ssp.Chain.decodeDiagnostic(Unknown Source)
at com.ddtek.sequelink.ssp.Chain.decodeBody(Unknown Source)
at com.ddtek.sequelink.ssp.Chain.decode(Unknown Source)
at com.ddtek.sequelink.ssp.Chain.send(Unknown Source)
at com.ddtek.sequelink.ctxt.stmt.StatementContext.execute(Unknown Source)
at com.ddtek.jdbc.sequelink.SequeLinkImplStatement.execute(Unknown Source)
at com.ddtek.jdbc.slbase.BaseStatement.commonExecute(Unknown Source)
at com.ddtek.jdbc.slbase.BaseStatement.executeUpdateInternal(Unknown Source)
at com.ddtek.jdbc.slbase.BasePreparedStatement.executeUpdate(Unknown Source)
at com.ddtek.jdbcx.slbase.BasePreparedStatementWrapper.executeUpdate(Unknown Source)
at com.ibm.ejs.cm.proxy.StatementProxy.executeUpdateCommon(StatementProxy.java:431)
at com.ibm.ejs.cm.proxy.PreparedStatementProxy.executeUpdate(PreparedStatementProxy.java:58)

Ali Gohar
Ranch Hand

Joined: Mar 18, 2004
Posts: 572
I think you should validate the data before inserting it into the DB. Its the better way.
Jeanne Boyarsky
internet detective

Joined: May 26, 2003
Posts: 30123

Interesting. I didn't realize the error message was so database specific.
(By the way, I do agree with the others that you should validate the data first.)
James Gordon
Ranch Hand

Joined: Aug 09, 2002
Posts: 106
Ok, if validation before insert is the only way, then looks like I got no
other choice.
Thanks for all the replies. Really appreciate it.
It is sorta covered in the JavaRanch Style Guide.
subject: Detect Column Causes Truncation
Similar Threads
Occasional inaccuracy in addition calculations. Why?
Data truncation issue
float point inexact result
question related to ragged array
DB error in servlet while inserting data