Hi everybody.

I'm facing problems with saving data in DB2.


Hibernate version: 3.2.4.sp1

Mapping:

<id name="id" column="LOC_ID" type="java.lang.Integer">
<generator class="native"/>
</id>

<property name="type1" column="LOC_TYPE_1" />
<property name="type2" column="LOC_TYPE_2" />
<property name="street" column="LOC_STREET" />
<property name="zipCode" column="LOC_ZIPCODE"/>
<property name="hpc" column="LOC_HPC"/>
<property name="city" column="LOC_CITY"/>
<property name="phone" column="LOC_PHONE"/>
<property name="fax" column="LOC_FAX"/>
<property name="mobile" column="LOC_MOBILE"/>
<property name="email" column="LOC_EMAIL"/>
<property name="webPage" column="LOC_WEBPAGE"/>
<property name="countryCode" column="LOC_CNTR_CODE"/>
<property name="orderNumber" column="LOC_ONR"/>

<many-to-one name="countryLookup" class="de.tsystems.fbs3.hibernate.beans.Lookup" column="LOC_CNTR_ID" not-null="false"/>

<set name="users" inverse="true" lazy="true">
<key column="USR_LOC_ID"/>
<one-to-many class="de.tsystems.fbs3.hibernate.beans.User"/>
</set>


Code between sessionFactory.openSession() and session.close():

Location location = new Location();
// Now set fields
location.setType1(createForm.getType1());
location.setType2(createForm.getType2());
location.setStreet(createForm.getStreet());
location.setZipCode(createForm.getZipCode());
location.setHpc(createForm.getHpc());
location.setCity(createForm.getCity());
location.setCountryCode(createForm.getCountryCode());
location.setPhone(createForm.getPhone());
location.setFax(createForm.getFax());
location.setMobile(createForm.getMobile());
location.setEmail(createForm.getEmail());
location.setWebPage(createForm.getWebPage());
String generatedId = (String)hiberSession.save(location);
log.info("Location successfully created with ID = "+generatedId);


Full stack trace of any exception that occurs:

[java] org.hibernate.exception.ConstraintViolationException: could not insert: [de.tsystems.fbs3.hibernate.beans.Location]
[java] at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:71)
[java] at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
[java] at org.hibernate.id.insert.AbstractSelectingDelegate.performInsert(AbstractSelectingDelegate.java:40)
[java] at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2158)
[java] at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2638)
[java] at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:48)
[java] at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:250)
[java] at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:298)
[java] at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:181)
[java] at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:107)
[java] at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:187)
[java] at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:33)
[java] at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:172)
[java] at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:27)
[java] at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:70)
[java] at org.hibernate.impl.SessionImpl.fireSave(SessionImpl.java:535)
[java] at org.hibernate.impl.SessionImpl.save(SessionImpl.java:523)
[java] at org.hibernate.impl.SessionImpl.save(SessionImpl.java:519)
[java] at de.tsystems.fbs3.struts.location.CreateLocationAction.performFbsStrurtsAction(CreateLocationAction.java:70)
[java] at de.tsystems.fbs3.struts.FbsStrutsAction.execute(FbsStrutsAction.java:96)
[java] at org.apache.struts.chain.commands.servlet.ExecuteAction.execute(ExecuteAction.java:53)
[java] at org.apache.struts.chain.commands.AbstractExecuteAction.execute(AbstractExecuteAction.java:64)
[java] at org.apache.struts.chain.commands.ActionCommandBase.execute(ActionCommandBase.java:48)
[java] at org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:190)
[java] at org.apache.commons.chain.generic.LookupCommand.execute(LookupCommand.java:304)
[java] at org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:190)
[java] at org.apache.struts.chain.ComposableRequestProcessor.process(ComposableRequestProcessor.java:280)
[java] at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1858)
[java] at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:459)
[java] at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
[java] at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
[java] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
[java] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
[java] at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
[java] at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
[java] at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
[java] at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
[java] at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
[java] at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
[java] at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
[java] at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
[java] at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
[java] at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
[java] at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
[java] at java.lang.Thread.run(Unknown Source)
[java] Caused by: com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -407, SQLSTATE: 23502, SQLERRMC:
[java] at com.ibm.db2.jcc.b.sf.d(sf.java:1396)
[java] at com.ibm.db2.jcc.c.jb.l(jb.java:367)
[java] at com.ibm.db2.jcc.c.jb.a(jb.java:64)
[java] at com.ibm.db2.jcc.c.w.a(w.java:48)
[java] at com.ibm.db2.jcc.c.dc.b(dc.java:302)
[java] at com.ibm.db2.jcc.b.tf.cb(tf.java:1719)
[java] at com.ibm.db2.jcc.b.tf.d(tf.java:2319)
[java] at com.ibm.db2.jcc.b.tf.Y(tf.java:540)
[java] at com.ibm.db2.jcc.b.tf.executeUpdate(tf.java:523)
[java] at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
[java] at org.hibernate.id.insert.AbstractSelectingDelegate.performInsert(AbstractSelectingDelegate.java:33)
[java] ... 42 more

Name and version of the database you are using:
DB2 v.7.x.

The generated SQL (show_sql=true):

insert into D$TV91.V91RLOC (LOC_ID, LOC_TYPE_1, LOC_TYPE_2, LOC_STREET, LOC_ZIPCODE, LOC_HPC, LOC_CITY, LOC_PHONE, LOC_FAX, LOC_MOBILE, LOC_EMAIL, LOC_WEBPAGE, LOC_CNTR_CODE, LOC_ONR, LOC_CNTR_ID) values (default, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)



I'm doing something wrong, but what???

Many thanks in advance. I'm realy stuck with this problem.

Giorgi

It looks to me like your code is correct. However the exception seems to indicate that the DB2 table doesn't have a default value assigned upon insertion of rows.

Altering the table to assign an incremented value should solve your problem. Or you could just assign your own value to the PK field.

I believe that if you don't define how hibernate is supposed to use id fields (ie: specifying what DB2 Sequence to get the next value from prior to an insert) then Hibernate just won't do anything. It'll assume the database will take care of filling in that value.


To identify a sequence that exists in DB2, do the following:


-B

Hi,

since I can't find your CREATE TABLE here's two possibilities:
1.) LOC_ONR and LOC_CNTR_ID haven't been set in your code. If any of these columns is defined as NOT NULL you'll get the -407 sqlcode.
2.) LOC_ID has to be declared as GENERATED on table-creation or you'll get the -407.

Hi, thanks for replies!

Sorry, have forgotten the SQL part:

CREATE TABLE D$TV91.V91RLOC
(LOC_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY
,LOC_ID_MIGR INT
,LOC_TYPE_1 VARCHAR(16)
,LOC_TYPE_2 VARCHAR(255)
,LOC_STREET VARCHAR(255)
,LOC_ZIPCODE VARCHAR(11)
,LOC_HPC VARCHAR(11)
,LOC_CITY VARCHAR(100)
,LOC_COUNTRY VARCHAR(40)
,LOC_PHONE VARCHAR(50)
,LOC_FAX VARCHAR(50)
,LOC_MOBILE VARCHAR(50)
,LOC_EMAIL VARCHAR(255)
,LOC_WEBPAGE VARCHAR(255)
,LOC_CNTR_CODE VARCHAR(16)
,LOC_ONR INTEGER NOT NULL DEFAULT 0
,LOC_CNTR_ID VARCHAR(20)
,FOREIGN KEY (LOC_CNTR_ID) REFERENCES D$TV91.V91RLKP1(LKP_ID)
)
IN V91C01.V91ALOC
;

CREATE UNIQUE INDEX D$TV91.V91lLOCID ON D$TV91.V91RLOC (LOC_ID)
;

One more thing:
We have done a data migration. So this table has been filled with data. We generated and executed insert statements. Something like

'INSERT INTO D$TV91.V91RLOC (LOC_ID_MIGR, LOC_TYPE_1, LOC_TYPE_2, LOC_STREET, LOC_ZIPCODE, LOC_HPC, LOC_CITY, LOC_COUNTRY, LOC_PHONE, LOC_FAX, LOC_MOBILE, LOC_EMAIL, LOC_WEBPAGE) VALUES(INT('1'),'d','','Erich-Herion-Straße 11-13','70736','0','Fellbach','','49159','','','','');'

Of course without using LOC_ID - it was generated automatically. Everything worked fine. So it seems on DB-side to be allright. There is something wrong(or I'm doing something wrong) in direction Hibernate->DB.

Thank you,

giorgi

Hi,

it seems the trouble is with the LOC_ONR-column. It is specified as NOT NULL WITH DEFAULT 0 which (if I understand the DB2-manual right) will insert 0 if a value for LOC_ONR is not specified on INSERT.
But hibernate does explicitly specify NULL as value for LOC_ONR since this is set (or rather not set) in your Location-object.
So what can you do as a workaround?
1.) Specify 0 as default for OrderNumber in your Location-class so it can never be null
or
2.) specify dynamic-insert="true" on your class-mapping. this will cause hibernate to skip all null-valued properties when generationg the insert-statement so the DB2-default will take over.

Oh my god!!! Thank you! It started to do at least something, but I think now the story goes deep into DB2...

[java] Hibernate: insert into D$TV91.V91RLOC (LOC_TYPE_1, LOC_TYPE_2, LOC_STREET, LOC_ZIPCODE, LOC_HPC, LOC_CITY, LOC_PHONE, LOC_FAX, LOC_MOBILE, LOC_EMAIL, LOC_WEBPAGE, LOC_CNTR_CODE, LOC_ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, default)
[java] Hibernate: values identity_val_local()
[java] 2007-06-08 12:02:39,723 WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: -142, SQLState: 42612
[java] 2007-06-08 12:02:39,723 ERROR org.hibernate.util.JDBCExceptionReporter - DB2 SQL error: SQLCODE: -142, SQLSTATE: 42612, SQLERRMC: null
[java] 2007-06-08 12:02:39,723 WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: -516, SQLState: 26501
[java] 2007-06-08 12:02:39,723 ERROR org.hibernate.util.JDBCExceptionReporter - DB2 SQL error: SQLCODE: -516, SQLSTATE: 26501, SQLERRMC: null
[java] 2007-06-08 12:02:39,723 WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: -514, SQLState: 26501
[java] 2007-06-08 12:02:39,723 ERROR org.hibernate.util.JDBCExceptionReporter - DB2 SQL error: SQLCODE: -514, SQLSTATE: 26501, SQLERRMC: SQL_CURLH200C1
[java] 2007-06-08 12:02:39,739 ERROR de.tsystems.fbs3.struts.FbsStrutsActionWithTransaction - Unexpected arror occured
[java] org.hibernate.exception.SQLGrammarException: could not retrieve generated id after insert: [de.tsystems.fbs3.hibernate.beans.Location]
[java] at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
[java] at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
[java] at org.hibernate.id.insert.AbstractSelectingDelegate.performInsert(AbstractSelectingDelegate.java:69)
[java] at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2158)
[java] at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2631)
[java] at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:48)
[java] at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:250)
[java] at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:298)
[java] at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:181)
[java] at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:107)
[java] at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:187)
[java] at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:172)
[java] at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.performSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:94)
[java] at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:70)
[java] at org.hibernate.impl.SessionImpl.fireSaveOrUpdate(SessionImpl.java:507)
[java] at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:499)
[java] at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:495)
[java] at de.tsystems.fbs3.struts.location.CreateLocationAction.performFbsStrurtsAction(CreateLocationAction.java:72)
[java] at de.tsystems.fbs3.struts.FbsStrutsActionWithTransaction.execute(FbsStrutsActionWithTransaction.java:99)
[java] at org.apache.struts.chain.commands.servlet.ExecuteAction.execute(ExecuteAction.java:53)
[java] at org.apache.struts.chain.commands.AbstractExecuteAction.execute(AbstractExecuteAction.java:64)
[java] at org.apache.struts.chain.commands.ActionCommandBase.execute(ActionCommandBase.java:48)
[java] at org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:190)
[java] at org.apache.commons.chain.generic.LookupCommand.execute(LookupCommand.java:304)
[java] at org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:190)
[java] at org.apache.struts.chain.ComposableRequestProcessor.process(ComposableRequestProcessor.java:280)
[java] at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1858)
[java] at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:459)
[java] at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
[java] at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
[java] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
[java] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
[java] at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
[java] at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
[java] at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
[java] at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
[java] at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
[java] at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
[java] at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
[java] at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
[java] at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
[java] at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
[java] at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
[java] at java.lang.Thread.run(Unknown Source)
[java] Caused by: com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -142, SQLSTATE: 42612, SQLERRMC: null
[java] at com.ibm.db2.jcc.b.sf.e(sf.java:1680)
[java] at com.ibm.db2.jcc.b.sf.a(sf.java:1239)
[java] at com.ibm.db2.jcc.b.sf.a(sf.java:1225)
[java] at com.ibm.db2.jcc.c.jb.h(jb.java:132)
[java] at com.ibm.db2.jcc.c.jb.a(jb.java:43)
[java] at com.ibm.db2.jcc.c.w.a(w.java:30)
[java] at com.ibm.db2.jcc.c.cc.f(cc.java:161)
[java] at com.ibm.db2.jcc.b.sf.n(sf.java:1219)
[java] at com.ibm.db2.jcc.b.tf.gb(tf.java:1816)
[java] at com.ibm.db2.jcc.b.tf.d(tf.java:2298)
[java] at com.ibm.db2.jcc.b.tf.X(tf.java:508)
[java] at com.ibm.db2.jcc.b.tf.executeQuery(tf.java:491)
[java] at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
[java] at org.hibernate.id.insert.AbstractSelectingDelegate.performInsert(AbstractSelectingDelegate.java:55)

As I said I'm using DB2 7.x and in hibernate configuration org.hibernate.dialect.DB2Dialect...

What Dialect and what DB2-Version are you using?
Because the SQL for retrieving the generated ids differs between DB2Dialect (used for PC-based DB2-Versions) and DB2390Dialect (when DB2 is running on zOS or OS390) and DB2400Dialect (on an AS400).

Have just checked:
This is OS/390!!!

And with both DB2390Dialect and DB2400Dialect everything works fine! Only "just" DB2Dialect is not lucky :-)