Hello,

I am very new to hibernate and am having a problem with using the identity generator with DB2 V7.2. After creating a new object and passing it to saveOrUpdate I believe hibernate is supposed to save the new id value for the newly created row in the database in that property. This does not occur however, it just sets it to zero. I believe it is having problems retrieving the Identity value for the new entry. From the debug output below it appears to call some function IDENTITY_VAL_LOCAL to get the identity value but that just returns 0. The last line is a call to getID after saveorupdate has been called from the driving program. I have changed it over to use a sequence instead and it is now working. Has anyone else encountered the same problem or know what I might be doing wrong ? Thanks in advance for your help!

Thanks,
Eric


--Debug output --
14:07:02,160 DEBUG [BatcherImpl] about to open: 0 open PreparedStatements, 0 open ResultSets
14:07:02,160 DEBUG [SessionFactoryImpl] prepared statement get: insert into BaseDataObject (type, id) values (?, default)
14:07:02,160 DEBUG [SessionFactoryImpl] preparing statement
14:07:02,160 DEBUG [StringType] binding 'HelloWorld1' to parameter: 1
14:07:02,160 DEBUG [BatcherImpl] done closing: 0 open PreparedStatements, 0 open ResultSets
14:07:02,160 DEBUG [SessionFactoryImpl] closing statement
14:07:02,160 DEBUG [BatcherImpl] about to open: 0 open PreparedStatements, 0 open ResultSets
14:07:02,160 DEBUG [SessionFactoryImpl] prepared statement get: values IDENTITY_VAL_LOCAL()
14:07:02,160 DEBUG [SessionFactoryImpl] preparing statement
14:07:02,160 DEBUG [NormalizedEntityPersister] Natively generated identity: 0
14:07:02,160 DEBUG [BatcherImpl] done closing: 0 open PreparedStatements, 0 open ResultSets
14:07:02,160 DEBUG [SessionFactoryImpl] closing statement
14:07:02,160 DEBUG [Cascades] processing cascades for: edu.jhu.ccbm.pathway.data.BaseDataObject
14:07:02,175 DEBUG [Cascades] done processing cascades for: edu.jhu.ccbm.pathway.data.BaseDataObject
14:07:02,175 DEBUG [HibernateTestServlet] 0

Looks fine to me. Zero is probably the first available id!

This was generated after having made a couple of inserts into the database through a sql script to initalize the db. I am pretty certain this is not the first value. If I later try to add a related item I get a FK error because the inserted id is not a valid id in the related table. I was also having issues with using the sequence mapping for some reason it looked like the inserts were happening but nothign was persisted to the database. I noticed this only happens if I open up a session do a saveorUPdate then a find and then a saveorupdate within the same session. In this situation the second saveorupdate generates debugging output but nothing is persisted to the database, if I remove the find inbetween then it works fine. In the context of a session I thought I should be able to do some inserts then some queries then some more inserts, if this is not the correct use of a session then I will go change my code. I've since moved things to mysql and it seems to work well (with identity columns), but am willing to switch back to DB2 and get more output or anything. Thanks in advance for all of your help.

Thanks,
Eric

Are you sure you have "GENETRATED BY DEFAULT AS IDENTITY" in your schema definition for the primary key?

Yes I am pretty sure. Like if I look at the actual database the IDs are generated correctly. Also if I do an saveorupdate to add a new object and then query for it and call getID on it it will return the correct value. But if I do a getID after calling saveorupdate it will return 0. Thanks for your help!


Eric

Do you have some kind of trigger on the table?

Nope I just ran the SQL that came out from the hibernate schema generator to create the database.

well, i dunno. ask your db2 expert.

Hey! Are you 100% certain that autocommit is disabled!?

Galvin, thanks for your help. This might be the problem, I have it deployed as a SAR in jboss using a connection pool that I created as a speerate resource in jboss. I believe autocommit is automatically turned on when getting a connection this way, although I am not sure. I will try to investigate this more tomorrow, although it seems to work ok with mysql. Thanks again for your help.

I imagine that the difference is that the equivant MySQL function call is scoped to a connection, whereas the DB2 call is scoped to a transaction. This makes sense since MySQL is nontransactional, be default.

I've been researching this problem and I believe it is caused by:

Correct use of identity

So, you either need to use the word 'default' in your scripts (as Hibernate correctly does), or you need to execute:

ALTER TABLE <table> ALTER COLUMN <id col> RESTART WITH <nextval>

after your inserts.

I hope this helps you.