Hi

I'm using DB2 and Hibernate2.1 and I'm having trouble generating a sequence value for an id.

The code worked fine with Oracle but is failing with DB2.

Here is the hbm.xml file :

Hibernate version:

Hibernate 2.1

Mapping documents:


<class name="com.zz.zz.zz.zz.zz.impl.FormImpl" table="FORM">
<id name="FormID" type="java.lang.Long" unsaved-value="null">
<column name="FORM_ID" sql-type="INTEGER" />
<generator class="sequence">
<param name="sequence">FORM_ID_SEQ</param>
</generator>
</id>
<property name="DepartmentID" type="java.lang.Long" column="DEPARTMENT_ID"/>
</class>


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


public void add(Form form, String sData ) throws DAOException {
byte[] data = sData.getBytes();
Session session = null;
boolean autoCommit = false;
try {
session = SessionHelper.openSession();
form.setRepresentation(Hibernate.createBlob(new byte[1]));
session.save(form);
session.flush();

// save AutoCommit setting
autoCommit = session.connection().getAutoCommit();
session.connection().setAutoCommit(false);
session.refresh(form, LockMode.UPGRADE);
java.sql.Blob blob = form.getRepresentation();
OutputStream os = blob.setBinaryStream(Long.parseLong("1"));
os.write(data);
os.close();
session.connection().commit();
session.connection().setAutoCommit(autoCommit);
} catch (Exception e) {
e.printStackTrace();
throw new DAOException("FormDAO.add(form,sData) HibernateException " , e);
} finally {
try {
SessionHelper.close(session);
} catch (Exception e) {
e.printStackTrace();
throw new DAOException("FormDAO.add(form,sData) SessionHelper.close failed " , e);
}
}
}




Full stack trace of any exception that occurs:


Hibernate: values nextval for FORM_ID_SEQ
13:09:46,573 WARN JDBCExceptionReporter:38 - SQL Error: -84, SQLState: 42612
13:09:46,573 ERROR JDBCExceptionReporter:46 - [BEA][DB2 JDBC Driver][DB2]UNACCEPTABLE SQL STATEMENT
13:09:46,573 WARN JDBCExceptionReporter:38 - SQL Error: -84, SQLState: 42612
13:09:46,573 ERROR JDBCExceptionReporter:46 - [BEA][DB2 JDBC Driver][DB2]UNACCEPTABLE SQL STATEMENT
13:09:46,573 ERROR JDBCExceptionReporter:38 - Could not save object
java.sql.SQLException: [BEA][DB2 JDBC Driver][DB2]UNACCEPTABLE SQL STATEMENT
at weblogic.jdbc.base.BaseExceptions.createException(Unknown Source)
at weblogic.jdbc.base.BaseExceptions.getException(Unknown Source)
at weblogic.jdbc.db2.drda.DRDARequest.processSQLCA(Unknown Source)
at weblogic.jdbc.db2.drda.DRDARequest.processCodePoint(Unknown Source)
at weblogic.jdbc.db2.drda.DRDAStatementRequest.processCodePoint(Unknown Source)
at weblogic.jdbc.db2.drda.DRDAExecuteStatementRequest.processCodePoint(Unknown Source)
at weblogic.jdbc.db2.drda.DRDARequest.processReply(Unknown Source)
at weblogic.jdbc.db2.DB2ImplStatement.getNextResultType(Unknown Source)
at weblogic.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source)
at weblogic.jdbc.base.BaseStatement.postImplExecute(Unknown Source)
at weblogic.jdbc.base.BasePreparedStatement.postImplExecute(Unknown Source)
at weblogic.jdbc.base.BaseStatement.commonExecute(Unknown Source)
at weblogic.jdbc.base.BaseStatement.executeQueryInternal(Unknown Source)
at weblogic.jdbc.base.BasePreparedStatement.executeQuery(Unknown Source)
at weblogic.jdbc.wrapper.PreparedStatement.executeQuery(PreparedStatement.java:92)
at net.sf.hibernate.id.SequenceGenerator.generate(SequenceGenerator.java:69)
at net.sf.hibernate.impl.SessionImpl.saveWithGeneratedIdentifier(SessionImpl.java:727)
at net.sf.hibernate.impl.SessionImpl.save(SessionImpl.java:717)
at com.zz.zz.zz.zz.zz.impl.hibernate.FormDAOImpl.add(FormDAOImpl.java:67)

Note that the statement :
Name and version of the database you are using:

DB2 8.1

The generated SQL (show_sql=true):

values nextval for FORM_ID_SEQ

--------------------------------------------


Note that the statement :

values nextval for FORM_ID_SEQ

works fine when executed in AQT, a DB GUI program where
the application and AQT usernames are the same.


Might a modification to the hbm.xml fix this problem?

(Like what?)

Ah ... I tried swapping out sequence with native as in :

<generator class="native">
<param name="sequence">FORM_ID_SEQ</param>
</generator>

But the database baulks saying it can't save because the id value is null.

Any luck with this problem in DB2?

Thanks,
anand

I am having the same problem, did any one here ever got 'sequence' working in hibernate for DB2 for AS/400 ??

it seems to me that this is bug in hibernate 3.1 because DB2 seems do not like sql statement : values nextval for sequence_id_name.

[quote="huangp"]I am having the same problem, did any one here ever got 'sequence' working in hibernate for DB2 for AS/400 ??

it seems to me that this is bug in hibernate 3.1 because DB2 seems do not like sql statement : values nextval for sequence_id_name.[/quote]

I think you may use the increment algorithm for Id´s generation. Because in order to your stack strace, i detect that DB2 doesn`t soport the SQL sentences with "nextval", and so on.

It will be fine if you use Oracle DB. I suggest you use other Id´s generation algorithm rather than "sequence".

Request for comments.

ricardly, Neketsu Shonen

stack trace:

[12/03/06 15:13:18:370 EST] 00000025 PrivExAction W J2CA0144W: No mappingConfigAlias found for connection factory or datasource jdbc/xa/tchr400c/ar.
[12/03/06 15:13:19:230 EST] 00000025 InternalGener I DSRA8203I: Database product name : DB2 UDB for AS/400
[12/03/06 15:13:19:261 EST] 00000025 InternalGener I DSRA8204I: Database product version : 05.03.0000 V5R3m0
[12/03/06 15:13:19:261 EST] 00000025 InternalGener I DSRA8205I: JDBC driver name : AS/400 Toolbox for Java JDBC Driver
[12/03/06 15:13:19:261 EST] 00000025 InternalGener I DSRA8206I: JDBC driver version : 5.0
[12/03/06 15:13:19:402 EST] 00000025 SettingsFacto I net.sf.hibernate.cfg.SettingsFactory buildSettings Use scrollable result sets: true
[12/03/06 15:13:19:464 EST] 00000025 SettingsFacto I net.sf.hibernate.cfg.SettingsFactory buildSettings Use JDBC3 getGeneratedKeys(): true
[12/03/06 15:13:19:527 EST] 00000025 SettingsFacto I net.sf.hibernate.cfg.SettingsFactory buildSettings Optimize cache for minimal puts: false
[12/03/06 15:13:19:574 EST] 00000025 SettingsFacto I net.sf.hibernate.cfg.SettingsFactory buildSettings echoing all SQL to stdout
[12/03/06 15:13:19:683 EST] 00000025 SettingsFacto I net.sf.hibernate.cfg.SettingsFactory buildSettings Query language substitutions: {}
[12/03/06 15:13:19:745 EST] 00000025 SettingsFacto I net.sf.hibernate.cfg.SettingsFactory buildSettings cache provider: net.sf.hibernate.cache.EhCacheProvider
[12/03/06 15:13:19:808 EST] 00000025 Configuration I net.sf.hibernate.cfg.Configuration configureCaches instantiating and configuring caches
[12/03/06 15:13:19:902 EST] 00000025 Configurator W net.sf.ehcache.config.Configurator configure No configuration found. Configuring ehcache from ehcache-failsafe.xml found in the classpath: wsjar:file:/D:/Rad60Workspace/ARWebServices/ARWebServicesWeb/WebContent/WEB-INF/lib/ehcache-0.9.jar!/ehcache-failsafe.xml
[12/03/06 15:13:20:324 EST] 00000025 SessionFactor I net.sf.hibernate.impl.SessionFactoryImpl <init> building session factory
[12/03/06 15:13:21:449 EST] 00000025 SessionFactor I net.sf.hibernate.impl.SessionFactoryObjectFactory addInstance Not binding factory to JNDI, no JNDI name configured
[12/03/06 15:13:21:511 EST] 00000025 WebSphereTran I net.sf.hibernate.transaction.WebSphereTransactionManagerLookup getTransactionManager WebSphere 5.1
[12/03/06 15:13:21:792 EST] 00000025 SystemOut O Hibernate: select invoicetyp0_.TYPE as TYPE, invoicetyp0_.DESCRIPTION as DESCRIPT2_, invoicetyp0_.DISTRIB_METHOD as DISTRIB_3_, invoicetyp0_.WRITE_OFF_MIN as WRITE_OF4_, invoicetyp0_.WRITE_OFF_MAX as WRITE_OF5_ from ARP100 invoicetyp0_ where (invoicetyp0_.TYPE=? )
[12/03/06 15:13:24:652 EST] 00000025 SystemOut O Hibernate: values nextval for INVOICE_ID_SEQ
[12/03/06 15:13:25:011 EST] 00000025 JDBCException W net.sf.hibernate.util.JDBCExceptionReporter logExceptions SQL Error: -29, SQLState: 42601
[12/03/06 15:13:25:074 EST] 00000025 JDBCException E net.sf.hibernate.util.JDBCExceptionReporter logExceptions [SQL0029] INTO clause missing from embedded statement.
[12/03/06 15:13:25:183 EST] 00000025 JDBCException W net.sf.hibernate.util.JDBCExceptionReporter logExceptions SQL Error: -29, SQLState: 42601
[12/03/06 15:13:25:246 EST] 00000025 JDBCException E net.sf.hibernate.util.JDBCExceptionReporter logExceptions [SQL0029] INTO clause missing from embedded statement.
[12/03/06 15:13:43:417 EST] 00000025 SystemOut O [12/03/06 15:13:43:339 EST] AR FATAL L Exception doing saveOrUpdate on object of type com.otpp.db.ar.InvoiceImpl [WebContainer : 1] com.otpp.db.hibernate.base.FactoryHelper
net.sf.hibernate.exception.SQLGrammarException: Could not save object
at net.sf.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:58)
at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
at net.sf.hibernate.impl.SessionImpl.convert(SessionImpl.java:4131)
at net.sf.hibernate.impl.SessionImpl.saveWithGeneratedIdentifier(SessionImpl.java:794)
at net.sf.hibernate.impl.SessionImpl.save(SessionImpl.java:749)


I think DB2 support "values nextval for sequence_id_seq into :var"

not "values nextval for sequence_id_seq" which is currently the hibernate implementation.

Not exactly what you're looking for but I've used 'identity' with db2 and it works.