Detect Column Causes Truncation

[James Gordon]

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]

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]

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

[James Gordon]

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]

I think you should validate the data before inserting it into the DB. Its the better way.

[Jeanne Boyarsky]

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]

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.