hibernate and db2 using com.ibm.db2.jcc.DB2Driver

[crawste...@gmail.com]

I am trying to use hibernate with db2. I have set the hibernate dialect
to be DB2Dialect. I am using the com.ibm.db2.jcc.DB2Driver make a
connection with the database. Database connection details are as
follows:

jdbc.driverClassName=com.ibm.db2.jcc.DB2Driver
jdbc.url=jdbc:db2://<host>:50000/<database>

I have the db2jcc.jar on my classpath. DB2 v8.2 is running on a Linux
box.

When i try and save to the database i am getting the following
exception back from hibernate/driver

org.springframework.jdbc.BadSqlGrammarException: Hibernate operation:
could not insert: [nz.co.vircom.model.WorkOrder]; bad SQL grammar
[insert into EMSDATA.MM_MRP_CONNECTIONS (Account_Balance, Address,
Charge, Charge_Code, City, Comments, Competed_Date, Contract_A_c,
Customer, Date_Due, Date_Due_2,
Date_Received, Device_Location, Device_No, Disconnect, Disco_Type_ID,
EmailSent, Escalated, ETA_Request_Count, ETA_Time, ICP___1,
Installation_number, Invoice_to, Issue_Time, Job_No, Job_SubType,
Job_Type_ID, Key, Locality, Meter_Seal_Number, Non_Disco_Type_ID,
Period_No, Postal_Cod, Premise, Reade
rs__Mesaage, Reading, Reason, Retailer_ID, Service_Provider_ID,
Source_Doc_ID, Status_ID, Suburb, Technician_ID, Telephone, Time_mode,
Urgency, ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, default)]; nested exception is
com.ibm.db2.jcc.b.SQLException: ACCOUNT_BALANCE
com.ibm.db2.jcc.b.SQLException: ACCOUNT_BALANCE
at com.ibm.db2.jcc.b.ce.e(ce.java:1093)
at com.ibm.db2.jcc.b.ce.a(ce.java:869)
at com.ibm.db2.jcc.a.bd.g(bd.java:125)
at com.ibm.db2.jcc.a.bd.a(bd.java:40)
at com.ibm.db2.jcc.a.r.a(r.java:31)
at com.ibm.db2.jcc.a.bp.g(bp.java:141)
at com.ibm.db2.jcc.b.ce.h(ce.java:853)
at com.ibm.db2.jcc.b.cf.G(cf.java:1185)
at com.ibm.db2.jcc.b.cf.a(cf.java:1497)
at com.ibm.db2.jcc.b.cf.executeUpdate(cf.java:322)
at
org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:1976)
at
org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2405)
at
org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:37)
at
org.hibernate.engine.ActionQueue.execute(ActionQueue.java:243)
at
org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:269)
at
org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:167)
at
org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:101)
at
org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:186)
at
org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:175)
at
org.hibernate.event.def.DefaultSaveOrUpdateEventListener.performSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:98)
at
org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:70)
at
org.hibernate.impl.SessionImpl.fireSaveOrUpdate(SessionImpl.java:520)
at
org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:513)
at
org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:509)
at
org.springframework.orm.hibernate3.HibernateTemplate$18.doInHibernate(HibernateTemplate.java:690)
at
org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:365)
at
org.springframework.orm.hibernate3.HibernateTemplate.saveOrUpdate(HibernateTemplate.java:687)
at
nz.co.vircom.dao.hibernate.WorkOrderDAOHibernate.saveWorkOrder(WorkOrderDAOHibernate.java:34)
at
nz.co.vircom.service.impl.WorkOrderManagerImpl.saveWorkOrder(WorkOrderManagerImpl.java:28)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at
org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:335)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:181)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:148)
at
org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:170)

The actual problem here is that the table and column identifiers are
not inside quotes (i.e. "ACCOUNT_BALANCE") when i copy and paste this
into something like DBVisualizer and add the quotes it works fine.
(DBVisualizer is also connecting to the DB2 database using the
com.ibm.db2.jcc.DB2Driver)

Does anyone have any ideas. Do I have to use a different hibernate
dialect to add in the quotes before running the query?

Thanks in advance

-Steven

[Steven]

The answer can be found here

http://www.hibernate.org/hib_docs/reference/en/html/mapping.html

Section 5.3. SQL quoted identifiers

I guess I should have RTFM

-Steven