How to catch a unique constraint sql exception.

[pldougless]

I need to enforce a unique constraint with is not a primary key, I'm using CMP. The constraint is in place on the table but when the entity bean is called and the constraint is violated, the stack trace is as follows...

[BatteryContTypeSession] java.rmi.ServerException: Store failed; nested exception is:
[BatteryContTypeSession] java.sql.SQLException: ORA-00001: unique constraint (EVS.BATT_CONT_TYPE_DESC_UK) violated
[BatteryContTypeSession]
[BatteryContTypeSession] java.sql.SQLException: ORA-00001: unique constraint (EVS.BATT_CONT_TYPE_DESC_UK) violated
[BatteryContTypeSession] at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
[BatteryContTypeSession] at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
[BatteryContTypeSession] at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
[BatteryContTypeSession] at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1451)
[BatteryContTypeSession] at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:862)
[BatteryContTypeSession] at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:1846)
[BatteryContTypeSession] at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1771)
[BatteryContTypeSession] at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2361)
[BatteryContTypeSession] at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:422)
[BatteryContTypeSession] at org.opentools.minerva.jdbc.PreparedStatementInPool.executeUpdate(PreparedStatementInPool.java:82)
[BatteryContTypeSession] at org.jboss.ejb.plugins.jaws.jdbc.JDBCUpdateCommand.executeStatementAndHandleResult(JDBCUpdateCommand.java:49)
[BatteryContTypeSession] at org.jboss.ejb.plugins.jaws.jdbc.JDBCCommand.jdbcExecute(JDBCCommand.java:160)
[BatteryContTypeSession] at org.jboss.ejb.plugins.jaws.jdbc.JDBCStoreEntityCommand.execute(JDBCStoreEntityCommand.java:97)
[BatteryContTypeSession] at org.jboss.ejb.plugins.jaws.JAWSPersistenceManager.storeEntity(JAWSPersistenceManager.java:156)
[BatteryContTypeSession] at org.jboss.ejb.plugins.CMPPersistenceManager.storeEntity(CMPPersistenceManager.java:401)
[BatteryContTypeSession] at org.jboss.ejb.plugins.EntitySynchronizationInterceptor$InstanceSynchronization.beforeCompletion(EntitySynchronizationInterceptor.java:342)
[BatteryContTypeSession] at org.jboss.tm.TxCapsule.doBeforeCompletion(TxCapsule.java:1228)
[BatteryContTypeSession] at org.jboss.tm.TxCapsule.commit(TxCapsule.java:322)
[BatteryContTypeSession] at org.jboss.tm.TransactionImpl.commit(TransactionImpl.java:76)
[BatteryContTypeSession] at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:318)
[BatteryContTypeSession] at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:99)
[BatteryContTypeSession] at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:190)
[BatteryContTypeSession] at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:195)
[BatteryContTypeSession] at org.jboss.ejb.StatelessSessionContainer.invoke(StatelessSessionContainer.java:271)
[BatteryContTypeSession] at org.jboss.ejb.plugins.jrmp.server.JRMPContainerInvoker.invoke(JRMPContainerInvoker.java:392)
[BatteryContTypeSession] at org.jboss.ejb.plugins.jrmp.server.JRMPContainerInvoker.invoke(JRMPContainerInvoker.java:469)
[BatteryContTypeSession] at org.jboss.ejb.plugins.jrmp.interfaces.StatelessSessionProxy.invoke(StatelessSessionProxy.java:152)
[BatteryContTypeSession] at $Proxy27.updateDB(Unknown Source)
[BatteryContTypeSession] at java.lang.reflect.Method.invoke(Native Method)
[BatteryContTypeSession] at engtools.server.UtilServlet.service(UtilServlet.java:91)
[BatteryContTypeSession] at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
[BatteryContTypeSession] at org.apache.tomcat.core.ServletWrapper.doService(ServletWrapper.java:405)
[BatteryContTypeSession] at org.apache.tomcat.core.Handler.service(Handler.java:287)
[BatteryContTypeSession] at org.apache.tomcat.core.ServletWrapper.service(ServletWrapper.java:372)
[BatteryContTypeSession] at org.apache.tomcat.core.ContextManager.internalService(ContextManager.java:797)
[BatteryContTypeSession] at org.apache.tomcat.core.ContextManager.service(ContextManager.java:743)
[BatteryContTypeSession] at org.apache.tomcat.service.http.HttpConnectionHandler.processConnection(HttpConnectionHandler.java:213)
[BatteryContTypeSession] at org.apache.tomcat.service.TcpWorkerThread.runIt(PoolTcpEndpoint.java:416)
[BatteryContTypeSession] at org.apache.tomcat.util.ThreadPool$ControlRunnable.run(ThreadPool.java:501)
[BatteryContTypeSession] at java.lang.Thread.run(Thread.java:484)
[EmbeddedTomcatSX] Exception occurred!
[EmbeddedTomcatSX] Invocation target exception in UtilServlet...java.lang.reflect.UndeclaredThrowableException
[EmbeddedTomcatSX] Actual UtilServlet exception is... java.lang.reflect.InvocationTargetException

I have not been successful in catching any of these exception inside the BatteryContTypeSession bean, I've tried catching the ServerException and SQLException no luck. The JBoss server log has JAWS entries which seem to kick off the SQLException.

I would appreciate any help on this matter.

thanks

Paul

[dsundstrom]

The problem you have is you are causing the exception at the end of the transaction when the data is written to the database, and this happens out of context of your code.

There really isn't anything you can do. SQLExceptions are considered non-recoveralbe exceptions. Also, SQLExceptions are blackboxes, as there is no db independent info.

To make it worse, in several places the EJB spec requires us to throw a CreateException, RemoveException... and these exceptions don't support a root cause exception.

SQLException handling is bad in the JDBC spec and there isn't really anything we can do about it.