Hi, guys:

Here is my problem: if a database table is empty originally, data can be
inserted without a problem; if I use a database table with existing data,
I get the following exception. I am using "native" generator for ids (primary keys):

COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "RICK.SAMPLE" from having duplicate rows for those columns. SQLSTATE=23505

Is your database table set up as an identity?
(Something like this in the create table script :
INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL)

Also, what DB2 server platform are you using? We had to change the autogenerating key script in the dialect for DB2 on the OS 390.

Jenica

Yes, in my sample table, I have the following on the first line:

sample_id INTEGER not null generated by default as identity,

I am using DB2 UDB7.2 Personal Edition, is this the cause of my problem?

Thanks.

What SQL is being executed?


I suggest you turn up the hibernate logging.

The default SQL for the hibernate dialect is:

values IDENTITY_VAL_LOCAL()

If that statement works for you just using straight jdbc it probably isn't a problem. What Hibernate does is it saves your object, then calls the above statement to find out what DB2 assigned to the identity field.

Another potential cause of this problem would be that your database connection has autocommit true. The post and the subsequent sql to retrieve the identity have to be in the same transaction, so you can't be autocommitting. Connections retrieved from Websphere JNDI automatically have autocommit true.

The DB2 error that you're getting suggests that for some reason you're trying to save two different records with the same id. I'd guess that most likely that id is a null. I've seen this happen when Hibernate's retrieval of the identity didn't work correctly so when you update the object it gives you an error. It could be caused by any number of different things, though!

If you add
hibernate.show_sql = true
to your hibernate.properties, and something like
log4j.logger.cirrus.hibernate = DEBUG, console
to your log4j.properties, you'll get a wealth of messages that will tell you what hibernate was trying to do when the error cropped up. That should narrow things down a bit!

Good luck!
Jenica

Hi, guys:

I got the same problem. I am wondering if any other people using DB2
have this problem? Thanks for your pointers anyways.

This problem could have many potential causes.

#1. the "values IDENTITY_VAL_LOCAL()" function doesn't work in your version of SQL. If you run this and it throws an SQL error, you may need to use a custom dialect. I can send you mine - I had to change the identity select string to "select sysibm.identity_val_local() from sysibm.sysdummy1"

#2. Your connection has autocommit true. Put in a logging statement or something to see if your connection has autocommit true. If it does, you need to set it to false after you get it.

#3. Could be none of the above. Add some logging statements, turn on hibernate show_sql and logging, and see what exactly is happening when you save the offending object. That will narrow it down so that someone can help you!

I had the problem with DB2, but it wasn't a showstopper, and not necessarily specific to db2. Also, it worked fine with db2 7.1 on a NT server but didn't work with db2 7.1 on the OS390, so problem #1 is database server platform specific.

Jenica

Rick, check out this post. I too, thought it was a hibernate issue, it turns out that with identity, my scripts had to be changed. Check out this post.