Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Detect Column Causes Truncation

 
James Gordon
Ranch Hand
Posts: 108
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,
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.

Thanks.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34973
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
James,
The SQL exception contains a textual error message too. It tells you the column number that is causing the problem.
 
James Gordon
Ranch Hand
Posts: 108
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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)

Thanks.
 
Ali Gohar
Ranch Hand
Posts: 572
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think you should validate the data before inserting it into the DB. Its the better way.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34973
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 108
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic