File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Object Relational Mapping and the fly likes Hibernate3.0 with DB2 8.1.3UDB - org.hibernate.exception. SQLGrammarException Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Hibernate3.0 with DB2 8.1.3UDB - org.hibernate.exception. SQLGrammarException" Watch "Hibernate3.0 with DB2 8.1.3UDB - org.hibernate.exception. SQLGrammarException" New topic
Author

Hibernate3.0 with DB2 8.1.3UDB - org.hibernate.exception. SQLGrammarException

Stanley George
Ranch Hand

Joined: Dec 09, 2000
Posts: 55
Hi all
I successfully ran a simple example on Hibernate 2.1.3 on Oracle 9i.

I am now trying to do it with Hibernate 3.0 on DB2 8.1.3 UDB. It is giving me an org.hibernate.exception.SQLGrammarException. From the generated sql, it looks like it cannot get the object's attribute values.

I would appreciate any help/pointers/links. Thanks a lot.

Hibernate version: 3.0 rc1

Mapping documents:
Cat.hbm.xml
-------------
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping
PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class name="com.stanleygeorge.hibernate3example.businessEntities.Cat" table="CAT">
<id name="id" type="integer" column="CAT_ID" unsaved-value="0">
<generator class="identity"/>
</id>

<property name="name" type="string" column="NAME"/>
<property name="weight" type="integer" column="WEIGHT"/>
</class>
</hibernate-mapping>

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

CatManager.java
---------------
Configuration cfg = new Configuration();
cfg.addClass( Cat.class );

SessionFactory sessions = cfg.buildSessionFactory();
session = sessions.openSession();
tx = session.beginTransaction();

session.save(newCat);

tx.commit();
session.close();...

Full stack trace of any exception that occurs:
org.hibernate.exception.SQLGrammarException: could not insert: [com.stanleygeorge.hibernate3example.businessEntities.Cat]
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:1747)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:2149)
at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:34)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:239)
at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:238)
at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:158)
at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:104)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:184)
at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:33)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:173)
at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:27)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:69)
at org.hibernate.impl.SessionImpl.save(SessionImpl.java:429)
at org.hibernate.impl.SessionImpl.save(SessionImpl.java:424)
at com.stanleygeorge.hibernate3example.businessEntities.CatManager.adoptCat(CatManager.java:35)
at com.stanleygeorge.hibernate3example.businessEntities.CatManager.main(CatManager.java:53)
Caused by: com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: DB2ADMIN.CAT
at com.ibm.db2.jcc.a.co.e(co.java:1361)
at com.ibm.db2.jcc.a.co.a(co.java:984)
at com.ibm.db2.jcc.b.bd.g(bd.java:121)
at com.ibm.db2.jcc.b.bd.a(bd.java:42)
at com.ibm.db2.jcc.b.r.a(r.java:31)
at com.ibm.db2.jcc.b.bp.g(bp.java:103)
at com.ibm.db2.jcc.a.co.g(co.java:968)
at com.ibm.db2.jcc.a.cp.T(cp.java:1378)
at com.ibm.db2.jcc.a.cp.d(cp.java:1781)
at com.ibm.db2.jcc.a.cp.L(cp.java:348)
at com.ibm.db2.jcc.a.cp.executeUpdate(cp.java:331)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:1737)
... 15 more

Name and version of the database you are using:
DB2 8.1.3 UDB

The generated SQL (show_sql=true):
[insert into CAT (NAME, WEIGHT, CAT_ID) values (?, ?, default)]

Debug level Hibernate log excerpt:

hibernate.log
-------------
16:35:38,052 DEBUG DTDEntityResolver:53 - found http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd in classpath
16:35:38,312 INFO HbmBinder:256 - Mapping class: com.stanleygeorge.hibernate3example.businessEntities.Cat -> CAT
16:35:38,342 DEBUG HbmBinder:1086 - Mapped property: id -> CAT_ID
16:35:38,362 DEBUG HbmBinder:1086 - Mapped property: name -> NAME
16:35:38,362 DEBUG HbmBinder:1086 - Mapped property: weight -> WEIGHT
16:35:38,372 DEBUG Configuration:1035 - Preparing to build session factory with filters : {}
...

16:35:39,343 DEBUG DriverManagerConnectionProvider:115 - created connection to: jdbc b2:animals, Isolation Level: 2
16:35:39,343 DEBUG DriverManagerConnectionProvider:129 - returning connection to pool, pool size: 1
...

16:35:39,704 DEBUG SessionFactoryImpl:149 - Session factory constructed with filter configurations : {}
16:35:39,714 DEBUG SessionFactoryImpl:152 - instantiating session factory with properties: {java.runtime.name=Java(TM) 2
...

16:35:40,345 DEBUG SessionFactoryObjectFactory:39 - initializing class SessionFactoryObjectFactory
16:35:40,355 DEBUG SessionFactoryObjectFactory:76 - registered: ff8080810284182d0102841831390000 (unnamed)
16:35:40,355 INFO SessionFactoryObjectFactory:82 - Not binding factory to JNDI, no JNDI name configured
16:35:40,355 DEBUG SessionFactoryImpl:249 - instantiated session factory
16:35:40,355 INFO SessionFactoryImpl:366 - Checking 0 named queries
16:35:40,415 DEBUG SessionImpl:229 - opened session
16:35:40,415 DEBUG JDBCTransaction:46 - begin
16:35:40,415 DEBUG AbstractBatcher:379 - opening JDBC connection
16:35:40,415 DEBUG DriverManagerConnectionProvider:93 - total checked-out connections: 0
16:35:40,415 DEBUG DriverManagerConnectionProvider:99 - using pooled JDBC connection, pool size: 0
16:35:40,415 DEBUG JDBCTransaction:50 - current autocommit status: false
16:35:40,425 DEBUG DefaultSaveOrUpdateEventListener:159 - saving transient instance
16:35:40,425 DEBUG AbstractSaveEventListener:89 - generated identifier: , using strategy: org.hibernate.id.IdentityGenerator
16:35:40,425 DEBUG AbstractSaveEventListener:132 - saving [com.stanleygeorge.hibernate3example.businessEntities.Cat#<null>]
16:35:40,435 DEBUG AbstractSaveEventListener:193 - executing insertions
16:35:40,445 DEBUG BasicEntityPersister:1705 - Inserting entity: com.stanleygeorge.hibernate3example.businessEntities.Cat (native id)
16:35:40,445 DEBUG AbstractBatcher:258 - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
16:35:40,455 DEBUG SQL:292 - insert into CAT (NAME, WEIGHT, CAT_ID) values (?, ?, default)
16:35:40,455 DEBUG AbstractBatcher:343 - preparing statement
16:35:40,495 DEBUG BasicEntityPersister:1587 - Dehydrating entity: [com.stanleygeorge.hibernate3example.businessEntities.Cat#<null>]
16:35:40,535 DEBUG AbstractBatcher:266 - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
16:35:40,535 DEBUG AbstractBatcher:363 - closing statement
16:35:40,545 DEBUG JDBCExceptionReporter:49 - could not insert: [com.stanleygeorge.hibernate3example.businessEntities.Cat] [insert into CAT (NAME, WEIGHT, CAT_ID) values (?, ?, default)]
com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: DB2ADMIN.CAT
at com.ibm.db2.jcc.a.co.e(co.java:1361)
at com.ibm.db2.jcc.a.co.a(co.java:984)
at com.ibm.db2.jcc.b.bd.g(bd.java:121)
at com.ibm.db2.jcc.b.bd.a(bd.java:42)
at com.ibm.db2.jcc.b.r.a(r.java:31)
at com.ibm.db2.jcc.b.bp.g(bp.java:103)
at com.ibm.db2.jcc.a.co.g(co.java:968)
at com.ibm.db2.jcc.a.cp.T(cp.java:1378)
at com.ibm.db2.jcc.a.cp.d(cp.java:1781)
at com.ibm.db2.jcc.a.cp.L(cp.java:348)
at com.ibm.db2.jcc.a.cp.executeUpdate(cp.java:331)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:1737)
at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:2149)
at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:34)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:239)
at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:238)
at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:158)
at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:104)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:184)
at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:33)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:173)
at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:27)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:69)
at org.hibernate.impl.SessionImpl.save(SessionImpl.java:429)
at org.hibernate.impl.SessionImpl.save(SessionImpl.java:424)
at com.stanleygeorge.hibernate3example.businessEntities.CatManager.adoptCat(CatManager.java:36)
at com.stanleygeorge.hibernate3example.businessEntities.CatManager.main(CatManager.java:54)
16:35:40,545 WARN JDBCExceptionReporter:57 - SQL Error: -204, SQLState: 42704
16:35:40,545 ERROR JDBCExceptionReporter:58 - DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: DB2ADMIN.CAT
16:35:40,545 WARN JDBCExceptionReporter:57 - SQL Error: -727, SQLState: 56098
16:35:40,545 ERROR JDBCExceptionReporter:58 - DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098, SQLERRMC: 2;-204;42704;DB2ADMIN.CAT
16:35:40,555 WARN JDBCExceptionReporter:57 - SQL Error: -727, SQLState: 56098
16:35:40,555 ERROR JDBCExceptionReporter:58 - DB2 SQL error: SQLCODE: -727, SQLSTATE: 56098, SQLERRMC: 2;-204;42704;DB2ADMIN.CAT
16:35:40,565 DEBUG JDBCTransaction:124 - rollback
Stanley George
Ranch Hand

Joined: Dec 09, 2000
Posts: 55
The error was glaring at me from the stack trace:

16:35:40,545 ERROR JDBCExceptionReporter:58 - DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: DB2ADMIN.CAT

By default DB2 was appending the username to the table 'DB2ADMIN.CAT' when it should have been 'STANLEY.CAT'. All I had to do was change the mapping file Cat.hbm.xml to have the tablename as 'STANLEY.CAT' instead of just 'CAT'.

Thanks for looking.
Cheers
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Hibernate3.0 with DB2 8.1.3UDB - org.hibernate.exception. SQLGrammarException
 
Similar Threads
Invalid Operation while accessing the query
insert/update value of the FOREIGN KEY is not equal to any value of parent key of parent table:DB2
Hibernate: ORM Mapping "Could not get next sequence value" SQLCODE: -142
Problem when delete 1 record in ROOM table
Eclispe + Hibernate + DB2 version 9