Hello,

I am able to run the "eg" sample application described in the new user roadmap against a MySQL database but when I run it against an iSeries (AS/400) DB2 database using the JTOpen "toolbox" JDBC driver it fails with the following error messages:

[java] Hibernate: insert into AuctionUser (userName, "password", email, firstName, "initial", lastName, id) values (?, ?, ?, ?, ?, ?, default)
[java] Hibernate: values IDENTITY_VAL_LOCAL()
[java] 13:35:24,199 WARN JDBCExceptionReporter:38 - SQL Error: -29, SQLState: 42601
[java] 13:35:24,199 ERROR JDBCExceptionReporter:46 - [SQL0029] INTO clause missing from embedded statement.

The tables and constraints are created without a problem. According to the database journal the row is added to the table then deleted with a rollback.

I have read in the forum that using Hibernate with iSeries DB2 is not a problem at OS level V5R2 and all recommended OS fixes are applied. The JTOpen driver and Hibernate are also most recent stable versions.

Here is my configuration:

hibernate.dialect net.sf.hibernate.dialect.DB2Dialect
hibernate.connection.driver_class com.ibm.as400.access.AS400JDBCDriver
hibernate.connection.url jdbc:as400://systemname/$mytestlib;translate binary=true;
hibernate.connection.username username
hibernate.connection.password userpassword


Any help would be appreciated.

Peter

Here is console output when running "eg" test application against IBM iSeries DB2 database

C:\apps\hibernate-2.1>ant eg
Buildfile: build.xml
[taskdef] Could not load definitions from resource clovertasks. It could not be found.

eg:
[echo] remember to place your JDBC driver in the lib directory
[java] 15:02:18,747 INFO Environment:432 - Hibernate 2.1 final
[java] 15:02:18,807 INFO Environment:466 - loaded properties from resource hibernate.properties: {hibernate.connection.driver_class=com.ibm.as400.access.A
S400JDBCDriver, hibernate.cglib.use_reflection_optimizer=true, hibernate.cache.provider_class=net.sf.hibernate.cache.HashtableCacheProvider, hibernate.max_fetch
_depth=1, hibernate.dialect=net.sf.hibernate.dialect.DB2Dialect, hibernate.jdbc.use_streams_for_binary=true, hibernate.jdbc.batch_size=0, hibernate.query.substi
tutions=true 1, false 0, yes 'Y', no 'N', hibernate.proxool.pool_alias=pool1, hibernate.connection.username=username, hibernate.connection.url=jdbc:as400://pgm4
00/$mytestlib;, hibernate.show_sql=true, hibernate.connection.password=userpassword, hibernate.connection.pool_size=1}
[java] 15:02:18,857 INFO Environment:480 - using java.io streams to persist binary types
[java] 15:02:18,867 INFO Environment:481 - using CGLIB reflection optimizer
[java] 15:02:18,877 INFO Environment:504 - JVM does not support LinkedHasMap, LinkedHashSet - ordered maps and sets disabled
[java] 15:02:18,917 INFO Environment:507 - using workaround for JVM bug in java.sql.Timestamp
[java] 15:02:18,917 INFO Configuration:305 - Mapping resource: org/hibernate/auction/AuctionItem.hbm.xml
[java] 15:02:20,139 INFO Binder:220 - Mapping class: org.hibernate.auction.AuctionItem -> AuctionItem
[java] 15:02:20,339 INFO Configuration:305 - Mapping resource: org/hibernate/auction/Bid.hbm.xml
[java] 15:02:20,409 INFO Binder:220 - Mapping class: org.hibernate.auction.Bid -> Bid
[java] 15:02:20,489 INFO Binder:166 - Mapping subclass: org.hibernate.auction.BuyNow -> Bid
[java] 15:02:20,499 INFO Configuration:305 - Mapping resource: org/hibernate/auction/User.hbm.xml
[java] 15:02:20,560 INFO Binder:220 - Mapping class: org.hibernate.auction.User -> AuctionUser
[java] 15:02:20,890 INFO ReflectHelper:149 - reflection optimizer disabled for: org.hibernate.auction.Name, IllegalArgumentException: Cannot find matching
method/constructor
[java] 15:02:20,900 INFO Configuration:571 - processing one-to-many association mappings
[java] 15:02:20,900 INFO Binder:1104 - Mapping collection: org.hibernate.auction.AuctionItem.bids -> Bid
[java] 15:02:20,900 INFO Binder:1104 - Mapping collection: org.hibernate.auction.User.bids -> Bid
[java] 15:02:20,900 INFO Binder:1104 - Mapping collection: org.hibernate.auction.User.auctions -> AuctionItem
[java] 15:02:20,900 INFO Configuration:580 - processing one-to-one association property references
[java] 15:02:20,910 INFO Configuration:605 - processing foreign key constraints
[java] 15:02:20,940 INFO Dialect:82 - Using dialect: net.sf.hibernate.dialect.DB2Dialect
[java] 15:02:20,940 INFO SettingsFactory:58 - Maximim outer join fetch depth: 1
[java] 15:02:20,940 INFO SettingsFactory:62 - Use outer join fetching: true
[java] 15:02:20,960 INFO DriverManagerConnectionProvider:41 - Using Hibernate built-in connection pool (not for production use!)
[java] 15:02:20,970 INFO DriverManagerConnectionProvider:42 - Hibernate connection pool size: 1
[java] 15:02:21,070 INFO DriverManagerConnectionProvider:71 - using driver: com.ibm.as400.access.AS400JDBCDriver at URL: jdbc:as400://systemname/$mytestlib;
[java] 15:02:21,070 INFO DriverManagerConnectionProvider:72 - connection properties: {user=username, password=userpassword}
[java] 15:02:21,090 INFO TransactionManagerLookupFactory:33 - No TransactionManagerLookup configured (in JTA environment, use of process level read-write
cache is not recommended)
[java] 15:02:23,183 INFO SettingsFactory:89 - Use scrollable result sets:true
[java] 15:02:23,183 INFO SettingsFactory:96 - echoing all SQL to stdout
[java] 15:02:23,183 INFO SettingsFactory:99 - Query language substitutions: {false=0, no='N', yes='Y', true=1}
[java] 15:02:23,183 INFO SettingsFactory:110 - cache provider: net.sf.hibernate.cache.HashtableCacheProvider
[java] 15:02:23,193 INFO Configuration:1044 - instantiating and configuring caches
[java] 15:02:23,434 INFO SessionFactoryImpl:118 - building session factory
[java] 15:02:24,675 INFO SessionFactoryObjectFactory:82 - no JNDI name configured
[java] 15:02:24,685 INFO Dialect:82 - Using dialect: net.sf.hibernate.dialect.DB2Dialect
[java] 15:02:24,696 INFO Configuration:571 - processing one-to-many association mappings
[java] 15:02:24,696 INFO Configuration:580 - processing one-to-one association property references
[java] 15:02:24,696 INFO Configuration:605 - processing foreign key constraints
[java] 15:02:24,696 INFO Configuration:571 - processing one-to-many association mappings
[java] 15:02:24,696 INFO Configuration:580 - processing one-to-one association property references
[java] 15:02:24,706 INFO Configuration:605 - processing foreign key constraints
[java] 15:02:24,706 INFO SchemaExport:96 - Running hbm2ddl schema export
[java] 15:02:24,716 INFO SchemaExport:115 - exporting generated schema todatabase
[java] 15:02:24,716 INFO DriverManagerConnectionProvider:41 - Using Hibernate built-in connection pool (not for production use!)
[java] 15:02:24,716 INFO DriverManagerConnectionProvider:42 - Hibernate connection pool size: 1
[java] 15:02:24,726 INFO DriverManagerConnectionProvider:71 - using driver: com.ibm.as400.access.AS400JDBCDriver at URL: jdbc:as400://systemname/$mytestlib;
[java] 15:02:24,726 INFO DriverManagerConnectionProvider:72 - connection properties: {user=username, password=userpassword}
[java] 15:02:24,846 DEBUG SchemaExport:130 - drop table AuctionUser
[java] 15:02:25,306 DEBUG SchemaExport:130 - drop table AuctionItem
[java] 15:02:25,507 DEBUG SchemaExport:130 - drop table Bid
[java] 15:02:25,587 DEBUG SchemaExport:147 - create table AuctionUser (id BIGINT not null generated by default as identity, userName VARCHAR(255) not null, "password" VARCHAR(255), email VARCHAR(255), firstName VARCHAR(255), "initial" CHAR(1), lastName VARCHAR(255), primary key (id))
[java] 15:02:25,767 DEBUG SchemaExport:147 - create table AuctionItem (
[java] id BIGINT not null generated by default as identity,
[java] description VARCHAR(255),
[java] ends TIMESTAMP,
[java] condition INTEGER,
[java] seller BIGINT not null,
[java] successfulBid BIGINT,
[java] primary key (id)
[java] )
[java] 15:02:25,977 DEBUG SchemaExport:147 - create table Bid (
[java] id BIGINT not null generated by default as identity,
[java] isBuyNow CHAR(1) not null,
[java] amount FLOAT not null,
[java] datetime TIMESTAMP not null,
[java] bidder BIGINT not null,
[java] item BIGINT not null,
[java] primary key (id)
[java] )
[java] 15:02:26,218 DEBUG SchemaExport:147 - alter table AuctionItem add constraint FK522A9BD657291C03 foreign key (successfulBid) references Bid
[java] 15:02:26,368 DEBUG SchemaExport:147 - alter table AuctionItem add constraint FK522A9BD6C9FF4F7F foreign key (seller) references AuctionUser
[java] 15:02:26,488 DEBUG SchemaExport:147 - alter table Bid add constraint FK104DD317B13 foreign key (item) references AuctionItem
[java] 15:02:26,668 DEBUG SchemaExport:147 - alter table Bid add constraint FK104DDAD3189F4 foreign key (bidder) references AuctionUser
[java] 15:02:26,799 INFO SchemaExport:158 - schema export complete
[java] 15:02:26,809 INFO DriverManagerConnectionProvider:137 - cleaning up connection pool: jdbc:as400://systemname/$mytestlib;
[java] Setting up some test data
[java] 15:02:27,079 INFO DriverManagerConnectionProvider:137 - cleaning up connection pool: jdbc:as400://systemname/$mytestlib;
[java] Hibernate: insert into AuctionUser (userName, "password", email, firstName, "initial", lastName, id) values (?, ?, ?, ?, ?, ?, default)
[java] Hibernate: values IDENTITY_VAL_LOCAL()
[java] 15:02:27,540 WARN JDBCExceptionReporter:38 - SQL Error: -29, SQLState: 42601
[java] 15:02:27,540 ERROR JDBCExceptionReporter:46 - [SQL0029] INTO clausemissing from embedded statement.
[java] net.sf.hibernate.JDBCException: could not insert: [org.hibernate.auction.User]
[java] at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:585)
[java] at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:479)
[java] 15:02:27,550 WARN JDBCExceptionReporter:38 - SQL Error: -29, SQLState: 42601
[java] 15:02:27,560 ERROR JDBCExceptionReporter:46 - [SQL0029] INTO clause missing from embedded statement.
[java] at net.sf.hibernate.impl.SessionImpl.doSave(SessionImpl.java:877)
[java] at net.sf.hibernate.impl.SessionImpl.doSave(SessionImpl.java:818)
[java] 15:02:27,560 ERROR JDBCExceptionReporter:38 - could not insert: [org.hibernate.auction.User]
[java] at net.sf.hibernate.impl.SessionImpl.saveWithGeneratedIdentifier(SessionImpl.java:738)
[java] java.sql.SQLException: [SQL0029] INTO clause missing from embedded statement.
[java] at com.ibm.as400.access.JDError.throwSQLException(JDError.java:533)
[java] at net.sf.hibernate.impl.SessionImpl.save(SessionImpl.java:718)
[java] at org.hibernate.auction.Main.createTestAuctions(Main.java:291)
[java] at org.hibernate.auction.Main.main(Main.java:365)
[java] Caused by: java.sql.SQLException: [SQL0029] INTO clause missing from embedded statement.
[java] at com.ibm.as400.access.JDError.throwSQLException(JDError.java:504)
[java] at com.ibm.as400.access.AS400JDBCStatement.commonPrepare(AS400JDBCStatement.java:1302)
[java] at com.ibm.as400.access.JDError.throwSQLException(JDError.java:533)
[java] at com.ibm.as400.access.JDError.throwSQLException(JDError.java:504)
[java] at com.ibm.as400.access.AS400JDBCPreparedStatement.<init>(AS400JDBCPreparedStatement.java:182)
[java] at com.ibm.as400.access.AS400JDBCStatement.commonPrepare(AS400JDBCStatement.java:1302)
[java] at com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:1805)
[java] at com.ibm.as400.access.AS400JDBCPreparedStatement.<init>(AS400JDBCPreparedStatement.java:182)
[java] at com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:1628)
[java] at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:233)
[java] at com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:1805)
[java] at com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:1628)
[java] at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:233)
[java] at net.sf.hibernate.impl.BatcherImpl.prepareStatement(BatcherImpl.java:57)
[java] at net.sf.hibernate.impl.BatcherImpl.prepareStatement(BatcherImpl.java:57)
[java] at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:557)
[java] ... 7 more
[java] at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:557)
[java] at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:479)
[java] at net.sf.hibernate.impl.SessionImpl.doSave(SessionImpl.java:877)
[java] at net.sf.hibernate.impl.SessionImpl.doSave(SessionImpl.java:818)
[java] at net.sf.hibernate.impl.SessionImpl.saveWithGeneratedIdentifier(SessionImpl.java:738)
[java] at net.sf.hibernate.impl.SessionImpl.save(SessionImpl.java:718)
[java] at org.hibernate.auction.Main.createTestAuctions(Main.java:291)
[java] at org.hibernate.auction.Main.main(Main.java:365)
[java] Exception in thread "main"
[java] Java Result: 1
[echo] for more examples, download the hibernate-examples package

BUILD SUCCESSFUL
Total time: 14 seconds

Try using a different id generation strategy instead of "native". Perhaps your DB2 does not support the "values IDENTITY_VAL_LOCAL()" call.

Thank you ... changing the id generation to "increment" worked and the tables are now getting populated.

Now there's a problem running viewAllAuctionsSlow() and viewAllAuctionsFast() as shown below. The rest of the application runs to completion without error if calls to these methods are commented out.

[java] Viewing all auction item objects
[java] Hibernate: select * from ( select rownumber() over() as row_, auctionite0_.id as id0_, bids1_.id as id1_, user2_.id as id2_, auctionite0_.description as descript2_0_, auctionite0_.ends as ends0_, auctionite0_.condition as condition0_, auctionite0_.seller as seller0_, auctionite0_.successfulBid as successf6_0_, bids1_.isBuyNow as isBuyNow1_, bids1_.amount as amount1_, bids1_.datetime as datetime1_, bids1_.bidder as bidder1_, bids1_.item as item1_, user2_.userName as userName2_, user2_."password" as y3_2_, user2_.email as email2_, user2_.firstName as firstName2_, user2_."initial" as y6_2_, user2_.lastName as lastName2_, bids1_.id as id__, bids1_.item as item__ from AuctionItem auctionite0_ left outer join Bid bids1_ on auctionite0_.id=bids1_.item left outer join AuctionUser user2_ on bids1_.bidder=user2_.id order by auctionite0_.ends desc ) as temp_ where row_ <= ?
[java] 10:33:29,974 WARN JDBCExceptionReporter:38 - SQL Error: -104, SQLState: 42601
[java] 10:33:29,974 ERROR JDBCExceptionReporter:46 - [SQL0104] Token ( was not valid. Valid tokens: , FROM INTO.
[java] 10:33:29,974 WARN JDBCExceptionReporter:38 - SQL Error: -104, SQLState: 42601
[java] 10:33:29,974 ERROR JDBCExceptionReporter:46 - [SQL0104] Token ( was not valid. Valid tokens: , FROM INTO.
[java] 10:33:29,974 ERROR JDBCExceptionReporter:38 - Could not execute query
[java] java.sql.SQLException: [SQL0104] Token ( was not valid. Valid tokens: , FROM INTO.
[java] at com.ibm.as400.access.JDError.throwSQLException(JDError.java:533)
[java] at com.ibm.as400.access.JDError.throwSQLException(JDError.java:504)
[java] at com.ibm.as400.access.AS400JDBCStatement.commonPrepare(AS400JDBCStatement.java:1302)
[java] at com.ibm.as400.access.AS400JDBCPreparedStatement.<init>(AS400JDBCPreparedStatement.java:182)
[java] at com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:1805)
[java] at com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:1628)
[java] at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:233)
[java] at net.sf.hibernate.impl.BatcherImpl.prepareQueryStatement(BatcherImpl.java:61)
[java] at net.sf.hibernate.loader.Loader.prepareQueryStatement(Loader.java:699)
[java] at net.sf.hibernate.loader.Loader.doQuery(Loader.java:180)
[java] at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:128)
[java] at net.sf.hibernate.loader.Loader.list(Loader.java:918)
[java] at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:983)
[java] at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1473)
[java] at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:45)
[java] at org.hibernate.auction.Main.viewAllAuctionsSlow(Main.java:86)
[java] at org.hibernate.auction.Main.main(Main.java:366)
[java] net.sf.hibernate.JDBCException: Could not execute query
[java] at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1476)
[java] at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:45)
[java] at org.hibernate.auction.Main.viewAllAuctionsSlow(Main.java:86)
[java] at org.hibernate.auction.Main.main(Main.java:366)
[java] Caused by: java.sql.SQLException: [SQL0104] Token ( was not valid. Valid tokens: , FROM INTO.
[java] at com.ibm.as400.access.JDError.throwSQLException(JDError.java:533)
[java] at com.ibm.as400.access.JDError.throwSQLException(JDError.java:504)
[java] at com.ibm.as400.access.AS400JDBCStatement.commonPrepare(AS400JDBCStatement.java:1302)
[java] at com.ibm.as400.access.AS400JDBCPreparedStatement.<init>(AS400JDBCPreparedStatement.java:182)
[java] at com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:1805)
[java] at com.ibm.as400.access.AS400JDBCConnection.prepareStatement(AS400JDBCConnection.java:1628)
[java] at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:233)
[java] at net.sf.hibernate.impl.BatcherImpl.prepareQueryStatement(BatcherImpl.java:61)
[java] at net.sf.hibernate.loader.Loader.prepareQueryStatement(Loader.java:699)
[java] at net.sf.hibernate.loader.Loader.doQuery(Loader.java:180)
[java] at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:128)
[java] at net.sf.hibernate.loader.Loader.list(Loader.java:918)
[java] at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:983)
[java] at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1473)
[java] ... 3 more
[java] Exception in thread "main"
[java] Java Result: 1
[echo] for more examples, download the hibernate-examples package

BUILD SUCCESSFUL
Total time: 12 seconds

I'm fairly certain that DB2/400 does not support rownumber() over(). I know that we're not using it, but then again we're using version 1.x

Thanks. I've researched your comment and you're quite right: IBM says that V5R2 does not yet support rownumber() over() and there is no work around. http://search400.techtarget.com/ateQues ... 16,00.html

I'm totally new at this and am just trying to get the sample application "eg" to work in full with iSeries as database server. Is there a way to have hibernate not use rownumber() over() through some kind of configuration setting? I've heard here that Hibernate works with DB2/400 on iSeries especially if you're on V5R2. http://forum.hibernate.org/old/921150.html

Are viewAllAuctionsSlow() and viewAllAuctionsFast() methods doing something that could be avoided on iSeries but still work with full functionality? I got the application to run entirely without error including these functions by commenting out setMaxResults(100). Upon doing this it no longer generated the "rownumber() over()" SQL. So the only thing missing is the limitation on rows returned (I guess). The SQL clause "fetch first 100 row only" is valid with DB2/400. Can I configure Hibernate to use this syntax rather than the rownumber() over() when setMaxResults is used?

You might try to write a custom dialect. Extend DB2Dialect, and override getLimitString(). Would be great if you would share your solution if you get this to work.

Thank you for the good suggestion. I'll give it a try and share it if it works.

Here is a subclass of DB2Dialect which will run the eg sample application without error. DB2/400 does not currently support identify columns nor does it support rownumber() over().

-- Peter

To use, lines similar to the following lines are needed in hibernate.properties:

## DB2/400

hibernate.dialect net.sf.hibernate.dialect.DB2400Dialect
##hibernate.connection.driver_class COM.ibm.db2.jdbc.app.DB2Driver
##hibernate.connection.url jdbc:db2:systemname
hibernate.connection.driver_class com.ibm.as400.access.AS400JDBCDriver
hibernate.connection.url jdbc:as400://systemname/libname;
hibernate.connection.username username
hibernate.connection.password userpassword

DB2400Dialect.java

package net.sf.hibernate.dialect;

import java.sql.Types;

import net.sf.hibernate.cfg.Environment;

/**
* An SQL dialect for DB2/400
* @author Peter DeGregorio (pdegregorio)
*/
public class DB2400Dialect extends DB2Dialect {

public DB2400Dialect() {
super();
}

public boolean supportsIdentityColumns() {
return false;
}

public boolean supportsSequences() {
return false;
}

public boolean supportsLimit() {
return true;
}

public boolean supportsLimitOffset() {
return false;
}

public String getLimitString(String sql, boolean hasOffset, int limit) {
return new StringBuffer( sql.length()+40 )
.append(sql)
.append(" fetch first ")
.append(limit)
.append(" rows only ")
.toString();
}

public boolean useMaxForLimit() {
return true;
}

public boolean supportsVariableLimit() {
return false;
}
}

Please submit a patch to JIRA

Will do. The community overview states "Patches may be submitted via the Hibernate JIRA. Please submit patches in patch format" with the word "patch" emphasized perhaps to suggest a link but there is no link. What is patch format and how does one submit at patch in this format? Nothing in the manual about this that I can see. Is this a generic CVS facility?

Follow the apache way.
http://jakarta.apache.org/site/source.html#Patches
http://cocoon.apache.org/2.1/howto/howto-patch.html

Note that Eclipse does it in a very simple manner. (Team->Create patch)

Patch HB-595 has been submitted. Thanks for the assistance.
-- Peter