RE: [JBoss-user] Handling Database Constraint Violations

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 454-5900

>The first case here can be handled using the exception processor in 3.2.1
>that will turn the SQLException into a DuplicateKeyException.

BTW, In JBoss 3.2.1, if turning SQLException into DuplicateKeyException is 
enabled, would that eliminate the explicit check JBoss currently performs 
to verify that primary key is unique. I'm seeing JBoss doing this before 
inserting things:

SELECT COUNT(*) FROM Foo WHERE fooId=1052844583037

I'm assuming this is done because JBoss 3.2 can't figure out from the 
SQLException that there is duplicate key.

>In the way
>you've described the second case, something seems fishy about the database
>relationship and you might want to re-examine the data model.

What I'm trying to do is to set things up so that within a set of objects 
that are associated with a particular parent a particular object 
characteristic (say name) must be unique. This is similar to a file system. 
You can't have files with the same name that belong to the same folder but 
it's OK to have the files with the same name that belong to different 
folders. Currently I'm enforcing this with a database constraint.

--Dmitry

At 11:41 AM 5/13/2003 -0700, Jeremy Boynes wrote:
>This is as expected.
>
>In the first case, the row exists in the database and the insert fails. This
>is returned as a CreateException because create failed and the database did
>not change.
>
>In the second case, both inserts suceeded because the column for the CMR
>field was null as the field is not set during ejbCreate. The update caused
>by setting the CMR in ejbPostCreate is deferred until the end of the
>transaction. At that time, one update suceeds, the second fails and because
>the database is now inconsistent we force a rollback by throwing
>EJBException.
>
>If the two objects were created in different transactions, the first would
>suceed and the second would fail exactly as in the first case above.
>
>The first case here can be handled using the exception processor in 3.2.1
>that will turn the SQLException into a DuplicateKeyException. In the way
>you've described the second case, something seems fishy about the database
>relationship and you might want to re-examine the data model.
>
>Jeremy
>
> > -----Original Message-----
> > From: jbo...@li...
> > [mailto:jbo...@li...]On Behalf Of Dmitry
> > Dolinsky
> > Sent: Tuesday, May 13, 2003 10:41 AM
> > To: jbo...@li...
> > Subject: RE: [JBoss-user] Handling Database Constraint Violations
> >
> >
> > At 07:04 AM 5/13/2003 -0700, Jeremy Boynes wrote:
> > >Please post the stacktraces from the two different exceptions.
> >
> > Thanks for your reply. There are stack traces for two cases below. These
> > cases are slightly different from what I described originally. Different
> > behavior now seems to relate to how the first object was created.
> > One other
> > thing is that the uniqueness constraint is applied to combination of two
> > fields one of the fields is a regular field and the other one is a CMR
> > (foreign key) field that is set in ejbPostCreate().
> >
> > 1. Client tries to create an object that conflicts with a object that was
> > created directly before the client ran. The object was created using
> > database tools (Sql Query Analyzer).
> >
> >     [junit] javax.ejb.CreateException: Could not create
> > entity:com.inet.tds.SQLException: Msg 2601, Level 14, State 3,
> > ne 1, Sqlstate 23000
> >     [junit] [BELIZE]Cannot insert duplicate key row in object 'Foo' with
> > unique index 'IDX_FOO_NAME'.
> >     [junit]     at
> > org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.insertEntit
> > y(JDBCCreateEntityCommand.java:292
> >
> >     [junit]     at
> > org.jboss.ejb.plugins.cmp.jdbc.JDBCCreateEntityCommand.execute(JDB
> > CCreateEntityCommand.java:182)
> >     [junit]     at
> > org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.createEntity(JDBCS
> > toreManager.java:569)
> >     [junit]     at
> > org.jboss.ejb.plugins.CMPPersistenceManager.createEntity(CMPPersis
> > tenceManager.java:225)
> >     [junit]     at
> > org.jboss.resource.connectionmanager.CachedConnectionInterceptor.c
> > reateEntity(CachedConnectionInterc
> > tor.java:270)
> >     [junit]     at
> > org.jboss.ejb.EntityContainer.createLocalHome(EntityContainer.java:571)
> >     [junit]     at
> > sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> >     [junit]     at
> > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorIm
> > pl.java:39)
> >     [junit]     at
> > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAc
> > cessorImpl.java:25)
> >     [junit]     at
> > org.jboss.ejb.EntityContainer$ContainerInterceptor.invokeHome(Enti
> > tyContainer.java:998)
> >     [junit]     at
> > org.jboss.ejb.plugins.AbstractInterceptor.invokeHome(AbstractInter
> > ceptor.java:88)
> >     [junit]     at
> > org.jboss.ejb.plugins.EntitySynchronizationInterceptor.invokeHome(
> > EntitySynchronizationInterceptor.j
> > a:188)
> >     [junit]     at
> > org.jboss.resource.connectionmanager.CachedConnectionInterceptor.i
> > nvokeHome(CachedConnectionIntercep
> > r.java:215)
> >     [junit]     at
> > org.jboss.ejb.plugins.AbstractInterceptor.invokeHome(AbstractInter
> > ceptor.java:88)
> >     [junit]     at
> > org.jboss.ejb.plugins.EntityMultiInstanceInterceptor.invokeHome(En
> > tityMultiInstanceInterceptor.java:
> > )
> >     [junit]     at
> > org.jboss.ejb.plugins.EntityLockInterceptor.invokeHome(EntityLockI
> > nterceptor.java:61)
> >     [junit]     at
> > org.jboss.ejb.plugins.EntityCreationInterceptor.invokeHome(EntityC
> > reationInterceptor.java:28)
> >     [junit]     at
> > org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxI
> > nterceptor.java:88)
> >     [junit]     at
> > org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInter
> > ceptorCMT.java:243)
> >     [junit]     at
> > org.jboss.ejb.plugins.TxInterceptorCMT.invokeHome(TxInterceptorCMT
> > .java:74)
> >     [junit]     at
> > org.jboss.ejb.plugins.SecurityInterceptor.invokeHome(SecurityInter
> > ceptor.java:92)
> >     [junit]     at
> > org.jboss.ejb.plugins.LogInterceptor.invokeHome(LogInterceptor.java:120)
> >     [junit]     at
> > org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invokeHome(Pro
> > xyFactoryFinderInterceptor.java:93
> >
> >     [junit]     at
> > org.jboss.ejb.EntityContainer.internalInvokeHome(EntityContainer.java:477)
> >     [junit]     at org.jboss.ejb.Container.invoke(Container.java:694)
> >     [junit]     at
> > org.jboss.ejb.plugins.local.BaseLocalProxyFactory.invokeHome(BaseL
> > ocalProxyFactory.java:272)
> >     [junit]     at
> > org.jboss.ejb.plugins.local.LocalHomeProxy.invoke(LocalHomeProxy.java:110)
> >     [junit]     at $Proxy58.create(Unknown Source)
> >     [junit]     at my session facade code that creates the entity bean
> > createFoo()
> >     [junit]     at
> > sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> >     [junit]     at
> > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorIm
> > pl.java:39)
> >     [junit]     at
> > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAc
> > cessorImpl.java:25)
> >     [junit]     at
> > org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invok
> > e(StatelessSessionContainer.java:6
> > )
> >     [junit]     at
> > org.jboss.resource.connectionmanager.CachedConnectionInterceptor.i
> > nvoke(CachedConnectionInterceptor.
> > va:186)
> >     [junit]     at
> > org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(S
> > tatelessSessionInstanceInterceptor
> > ava:72)
> >     [junit]     at
> > org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxI
> > nterceptor.java:84)
> >     [junit]     at
> > org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInter
> > ceptorCMT.java:243)
> >     [junit]     at
> > org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:104)
> >     [junit]     at
> > org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityIntercept
> > or.java:117)
> >     [junit]     at
> > org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:191)
> >     [junit]     at
> > org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFa
> > ctoryFinderInterceptor.java:122)
> >     [junit]     at
> > org.jboss.ejb.StatelessSessionContainer.internalInvoke(StatelessSe
> > ssionContainer.java:322)
> >     [junit]     at org.jboss.ejb.Container.invoke(Container.java:674)
> >     [junit]     at sun.reflect.GeneratedMethodAccessor114.invoke(Unknown
> > Source)
> >     [junit]     at
> > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAc
> > cessorImpl.java:25)
> >     [junit]     at
> > org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedM
> > BeanDispatcher.java:284)
> >     [junit]     at
> > org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:549)
> >     [junit]     at
> > org.jboss.invocation.local.LocalInvoker.invoke(LocalInvoker.java:101)
> >     [junit]     at
> > org.jboss.invocation.InvokerInterceptor.invoke(InvokerInterceptor.java:88)
> >     [junit]     at
> > org.jboss.proxy.TransactionInterceptor.invoke(TransactionIntercept
> > or.java:77)
> >     [junit]     at
> > org.jboss.proxy.SecurityInterceptor.invoke(SecurityInterceptor.java:80)
> >     [junit]     at
> > org.jboss.proxy.ejb.StatelessSessionInterceptor.invoke(StatelessSe
> > ssionInterceptor.java:109)
> >     [junit]     at
> > org.jboss.proxy.ClientContainer.invoke(ClientContainer.java:82)
> >     [junit]     at $Proxy74.createFoo(Unknown Source)
> >
> > 2. The client runs twice creating the same object. The first attempt
> > succeeds and the second fails with EJBException.
> >
> > The second call fails with this:
> >
> >     [junit] Testcase: testDuplicateName2 took 2.297 sec
> >     [junit]     Caused an ERROR
> >     [junit] null; nested exception is:
> >     [junit]     javax.ejb.EJBException: Store failed;
> > CausedByException is:
> >     [junit]     [BELIZE]Cannot insert duplicate key row in object 'Foo'
> > with unique index 'IDX_FOO_NAME'.; - nested throwable:
> > (javax.ejb.EJBException: Store failed; CausedByException is:
> >     [junit]     [BELIZE]Cannot insert duplicate key row in object 'Foo'
> > with unique index 'IDX_FOO_NAME'.)
> >     [junit] org.jboss.tm.JBossTransactionRolledbackException:
> > null; nested
> > exception is:
> >     [junit]     javax.ejb.EJBException: Store failed;
> > CausedByException is:
> >     [junit]     [BELIZE]Cannot insert duplicate key row in object 'Foo'
> > with unique index 'IDX_FOO_NAME'.; - nested throwable:
> > (javax.ejb.EJBException: Store failed; CausedByException is:
> >     [junit]     [BELIZE]Cannot insert duplicate key row in object 'Foo'
> > with unique index 'IDX_FOO_NAME'.)
> >     [junit]     at
> > org.jboss.ejb.plugins.TxInterceptorCMT.endTransaction(TxIntercepto
> > rCMT.java:394)
> >     [junit]     at
> > org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInter
> > ceptorCMT.java:253)
> >     [junit]     at
> > org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:104)
> >     [junit]     at
> > org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityIntercept
> > or.java:117)
> >     [junit]     at
> > org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:191)
> >     [junit]     at
> > org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFa
> > ctoryFinderInterceptor.java:122)
> >     [junit]     at
> > org.jboss.ejb.StatelessSessionContainer.internalInvoke(StatelessSe
> > ssionContainer.java:322)
> >     [junit]     at org.jboss.ejb.Container.invoke(Container.java:674)
> >     [junit]     at
> > sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> >     [junit]     at
> > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorIm
> > pl.java:39)
> >     [junit]     at
> > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAc
> > cessorImpl.java:25)
> >     [junit]     at
> > org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedM
> > BeanDispatcher.java:284)
> >     [junit]     at
> > org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:549)
> >     [junit]     at
> > org.jboss.invocation.local.LocalInvoker.invoke(LocalInvoker.java:101)
> >     [junit]     at
> > org.jboss.invocation.InvokerInterceptor.invoke(InvokerInterceptor.java:88)
> >     [junit]     at
> > org.jboss.proxy.TransactionInterceptor.invoke(TransactionIntercept
> > or.java:77)
> >     [junit]     at
> > org.jboss.proxy.SecurityInterceptor.invoke(SecurityInterceptor.java:80)
> >     [junit]     at
> > org.jboss.proxy.ejb.StatelessSessionInterceptor.invoke(StatelessSe
> > ssionInterceptor.java:109)
> >     [junit]     at
> > org.jboss.proxy.ClientContainer.invoke(ClientContainer.java:82)
> >     [junit]     at $Proxy74.createFoo(Unknown Source)
> >
> > --Dmitry
> >
> >
> >
> > >CMP writes to the database in two different ways:
> > >1) The INSERT after ejbCreate completes which writes all
> > cmp-fields set in
> > >ejbCreate
> > >2) An UPDATE statement issued when the transaction completes.
> > >
> > >Either of these can result in a SQLException caused by the
> > unique constraint
> > >violation. In 3.2.1 we introduced an exception processor that
> > will convert
> > >this to a DuplicateKeyException for any constaint violation (SQLSTATE ==
> > >"23000" to be precise) but that function is not enabled by default due to
> > >backward compatibilty concerns; there is a change note on it.
> > >
> > >The update case is harder to handle as by the time we do the store the
> > >business method has been completed. The SQLException will get wrapped and
> > >converted to an EJBException or RemoteException. The long term
> > solution is
> > >to enhance CMP to know about unique constraints and perform the update
> > >immediately the set accessor is called on a field that is a
> > member of one.
> > >That way an Exception can be thrown from the accessor which
> > would allow the
> > >business logic handle it. This is, of course, non-standard
> > behaviour. It is
> > >something we are looking at for 4.0 but not the 3.X series.
> > >
> > >One possible workaround would be to update the constrained field in a
> > >separate transaction but that obviously depends on the nature of your
> > >application.
> > >
> > >Jeremy
> > >
> > > > -----Original Message-----
> > > > From: jbo...@li...
> > > > [mailto:jbo...@li...]On Behalf Of Dmitry
> > > > Dolinsky
> > > > Sent: Monday, May 12, 2003 8:23 PM
> > > > To: jbo...@li...
> > > > Subject: [JBoss-user] Handling Database Constraint Violations
> > > >
> > > >
> > > > Hi,
> > > >
> > > > I have CMP entity beans that are accessed through session
> > facade. One of
> > > > the entity bean fields has a unique constraint (it's not the
> > primary key
> > > > though). In case the constraint is violated I'd like to be able
> > > > to throw a
> > > > decent exception to the client by intercepting SQLException in session
> > > > facade and examining the status. (BTW, this is JBoss 3.2 with MS
> > > > SqlServer
> > > > 2000, EJBs use "Required" transactions).
> > > >
> > > > I've ran into a few problems with this approach. I get inconsistent
> > > > exceptions. Sometimes I get CreateException thrown from the
> > entity bean
> > > > create method the exception seems to originate from  executeUpdate()
> > > > inserting new row. Other times EJBException (that wraps
> > SQLException) is
> > > > thrown directly to the client when the transaction is
> > committed. In this
> > > > case the session facade does not get a chance to intercept
> > the exception.
> > > >
> > > > I'd like to understand why I'm seeing this inconsistency. I'd
> > expect to
> > > > always get the exception when executing the INSERT. That's what I
> > > > get when
> > > > using JDBC directly. I experimented a little and I've noticed the
> > > > following:
> > > >
> > > > 1. If a row existed before the client runs, and the client
> > does not use
> > > > client-side transaction, the attempt to create a new row with the same
> > > > value results in CreateException.
> > > > 2. If a row existed before the client runs, and the client does use
> > > > client-side transaction, the attempt to create a new row with the same
> > > > value results in EJBException thrown when the transaction is
> > committed.
> > > > 3. If the client attempts to create two rows (no client-side
> > > > transactions,
> > > > therefore two separate transactions) and the first attempt
> > succeeds, the
> > > > second fails with EJBException when 2nd transaction ends.
> > > >
> > > > I'd appreciate if anyone could shed some light or point towards
> > > > documentation that explains this. Why sometime I get one exception and
> > > > sometimes the other? I'm not doing anything special with
> > isolation level,
> > > > BTW, just using the default.
> > > >
> > > > And another question is  whether there another place I can intercept
> > > > SQLException and turn it into something else. In addition to
> > the problem
> > > > with not being able to intercept the exception on commit, when I get
> > > > CreateException coming from create(), the underlying
> > SQLException is lost.
> > > >
> > > > I realize that I can do a few workarounds but all of ones I have
> > > > thought of
> > > > have downsides that I'm not ready to accept:
> > > > - Deal with the exception on the client side.
> > > > - "Manually" check for duplicates by explicitly querying DB.
> > > > - Begin and commit transactions in session facade.
> > > >
> > > > Thanks a lot.
> > > >
> > > > --Dmitry
> > > >
> > > >
> > > >
> > > >
> > > > -------------------------------------------------------
> > > > Enterprise Linux Forum Conference & Expo, June 4-6, 2003, Santa Clara
> > > > The only event dedicated to issues related to Linux
> > enterprise solutions
> > > > www.enterpriselinuxforum.com
> > > >
> > > > _______________________________________________
> > > > JBoss-user mailing list
> > > > JBo...@li...
> > > > https://lists.sourceforge.net/lists/listinfo/jboss-user
> > >
> > >
> > >
> > >-------------------------------------------------------
> > >Enterprise Linux Forum Conference & Expo, June 4-6, 2003, Santa Clara
> > >The only event dedicated to issues related to Linux enterprise solutions
> > >www.enterpriselinuxforum.com
> > >
> > >_______________________________________________
> > >JBoss-user mailing list
> > >JBo...@li...
> > >https://lists.sourceforge.net/lists/listinfo/jboss-user
> >
> >
> >
> >
> > -------------------------------------------------------
> > Enterprise Linux Forum Conference & Expo, June 4-6, 2003, Santa Clara
> > The only event dedicated to issues related to Linux enterprise solutions
> > www.enterpriselinuxforum.com
> >
> > _______________________________________________
> > JBoss-user mailing list
> > JBo...@li...
> > https://lists.sourceforge.net/lists/listinfo/jboss-user
>
>
>
>-------------------------------------------------------
>Enterprise Linux Forum Conference & Expo, June 4-6, 2003, Santa Clara
>The only event dedicated to issues related to Linux enterprise solutions
>www.enterpriselinuxforum.com
>
>_______________________________________________
>JBoss-user mailing list
>JBo...@li...
>https://lists.sourceforge.net/lists/listinfo/jboss-user