Editable Data Table with mass edit features. Unique constraint error

[jskillings07]

Hello All,

We started developing an application with Seam recently.  Our application uses large number of editable data tables (mass edit capability).  Here is a URL for the editable data table forms we are developing.
http://3.bp.blogspot.com/4aRLuh8AWA/ST1DVAZbpdI/AAAAAAAAAZ0/Wjm5LOGejHA/s1600-h/2.gif

In this example the base table has 3 columns (id, name, value).  ID has a primary key (sequence), and  name has a unique index.

The problem we are facing is the on the update of the Name field. We are running into a duplicate key value  violates unique constraint that we need some help or few ideas on how to solve the problem.

Here is the problem.

There are few situations where the Name needs to be updated.
on

"For e.g.  on Row 1,  Name = 'bgp51y', and Row 2, Name = '7hu2v3';

Now I update Row 1,  Name = '7hu2v3', and Row 2, Name = 'bgp51y'.  Basically, I am swapping the Name values on Rows 1 and 2."

When I enter save, I am getting the duplicate key value violates unique constraint error.  It appears that records are saved sequentially, hence the error from database.

I was assuming that this was a transaction, and all the updates were to be committed at the end. But that does not seems to be case here.  The problem also is that we cannot seem to catch the exception from entity manager.

Can any one give some ideas on how resolve our problem please.

Thanks.

John

[jskillings07]

Link to the form:

http://3.bp.blogspot.com/4aRLuh8AWA/ST1DVAZbpdI/AAAAAAAAAZ0/Wjm5LOGejHA/s1600-h/2.gif

[jskillings07]

The forum software is deleting some characters from the original link.  Hence reposting the link again.

http://www.screencast.com/t/ZGI1YzQ5NjI

Appreciate any responses to my problem.

[corte86]

hey, just an info...unfortunately i can't solve your problem....by the way, i'm trying to make a form really similar to your one....i select one object from a many to many relation, and in the bottom of the page, make a simple extendeddatatable with editable fields on it...the problem is that it works, but when i try to persist or merge values, my datamodel has old values.....can u post or send me the most important code parts of your form? like bean and xhtml of a row...

Thanks in advance....

[corte86]

hey, just an info...unfortunately i can't solve your problem....by the way, i'm trying to make a form really similar to your one....i select one object from a many to many relation, and in the bottom of the page, make a simple extendeddatatable with editable fields on it...the problem is that it works, but when i try to persist or merge values, my datamodel has old values.....can u post or send me the most important code parts of your form? like bean and xhtml of a row...

Thanks in advance....

[mikebgx]

I get a similar problem in XyzHome.update() even when writing a single record to the database, if the user has edited it so as to violate the unique constraint.

I can catch a EntityExistsException or ConstraintViolationException, but in the process my entity becomes detached and subsequent operations fail badly, ie user sees the debug.seam page.

My solution could work, but I'd need to find away around the detached entity & closed session problem.

Any ideas please?

Mike Burton

[mikebgx]

I found (and enhanced) an elegant solution to this problem, in case anyone's interested see: http://lazygeeks.blogspot.com/2009/03/hibernate-unique-validator-with-seam.html

Mike Burton

[ben_utzer]

Unfortunately, your solution does not handle the case when another user updates or inserts a row with the value of the unique field that has just passed validation but which has not been flushed to the database yet. Then you're left with dealing with the closed transaction, detached entities again.

[gardellajuan]

Who try implement this datatable see:

http://balusc.blogspot.com/2006/06/using-datatables.html