When inserting timestamp to db using <timestamp source="db">, a message is being displayed 'could not select current db timestamp' and the values are not inserted into the table. This is happening with DB2.

Thanks,
Krishna

Need to see the stack trace. Also what is the SQL issued to retrieve the timestamp? I seem to recall that the SQL was incorrect at some point.

It is not generating any SQL. Seems like it is failing even before that.

Mapping:
<timestamp
name="lst_upd_s"
column="LST_UPD_S"
source="db"
generated="true" />

Aug 30, 2005 9:02:24 AM org.hibernate.cfg.Environment <clinit>
INFO: Hibernate 3.1 beta 2
Aug 30, 2005 9:02:24 AM org.hibernate.cfg.Environment <clinit>
INFO: hibernate.properties not found
Aug 30, 2005 9:02:24 AM org.hibernate.cfg.Environment <clinit>
INFO: using CGLIB reflection optimizer
Aug 30, 2005 9:02:24 AM org.hibernate.cfg.Environment <clinit>
INFO: using JDK 1.4 java.sql.Timestamp handling
Aug 30, 2005 9:02:25 AM org.hibernate.cfg.Configuration configure
INFO: configuring from resource: /hibernate.cfg.xml
Aug 30, 2005 9:02:25 AM org.hibernate.cfg.Configuration getConfigurationInputStream
INFO: Configuration resource: /hibernate.cfg.xml
Aug 30, 2005 9:02:25 AM org.hibernate.cfg.Configuration addResource
INFO: Reading mappings from resource: com/jbhunt/reference/hibernate/SimpleAccess.hbm.xml
Aug 30, 2005 9:02:25 AM org.hibernate.cfg.HbmBinder bindRootPersistentClassCommonValues
INFO: Mapping class: com.jbhunt.reference.hibernate.bo.AllowedValues -> ALI.ALW_VAL
Aug 30, 2005 9:02:26 AM org.hibernate.cfg.Configuration doConfigure
INFO: Configured SessionFactory: null
Aug 30, 2005 9:02:26 AM org.hibernate.cfg.Configuration secondPassCompile
INFO: processing extends queue
Aug 30, 2005 9:02:26 AM org.hibernate.cfg.Configuration secondPassCompile
INFO: processing collection mappings
Aug 30, 2005 9:02:26 AM org.hibernate.cfg.Configuration secondPassCompile
INFO: processing association property references
Aug 30, 2005 9:02:26 AM org.hibernate.cfg.Configuration secondPassCompile
INFO: processing foreign key constraints
Aug 30, 2005 9:02:26 AM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: Using Hibernate built-in connection pool (not for production use!)
Aug 30, 2005 9:02:26 AM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: Hibernate connection pool size: 20
Aug 30, 2005 9:02:26 AM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: autocommit mode: false
Aug 30, 2005 9:02:28 AM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: using driver: COM.ibm.db2.jdbc.app.DB2Driver at URL: jdbc:db2:JBHDB2T
Aug 30, 2005 9:02:28 AM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: connection properties: {user=jisanb1, password=****}
Aug 30, 2005 9:02:30 AM org.hibernate.cfg.SettingsFactory buildSettings
INFO: RDBMS: DB2, version: 07.01.0001
Aug 30, 2005 9:02:30 AM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC driver: IBM DB2 JDBC 2.0 Type 2, version: 07.02.0004
Aug 30, 2005 9:02:30 AM org.hibernate.dialect.Dialect <init>
INFO: Using dialect: org.hibernate.dialect.DB2Dialect
Aug 30, 2005 9:02:30 AM org.hibernate.transaction.TransactionFactoryFactory buildTransactionFactory
INFO: Using default transaction strategy (direct JDBC transactions)
Aug 30, 2005 9:02:30 AM org.hibernate.transaction.TransactionManagerLookupFactory getTransactionManagerLookup
INFO: No TransactionManagerLookup configured (in JTA environment, use of read-write or transactional second-level cache is not recommended)
Aug 30, 2005 9:02:30 AM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Automatic flush during beforeCompletion(): disabled
Aug 30, 2005 9:02:30 AM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Automatic session close at end of transaction: disabled
Aug 30, 2005 9:02:30 AM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Scrollable result sets: enabled
Aug 30, 2005 9:02:30 AM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC3 getGeneratedKeys(): disabled
Aug 30, 2005 9:02:30 AM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Connection release mode: null
Aug 30, 2005 9:02:30 AM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Default batch fetch size: 1
Aug 30, 2005 9:02:30 AM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Generate SQL with comments: disabled
Aug 30, 2005 9:02:30 AM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Order SQL updates by primary key: disabled
Aug 30, 2005 9:02:30 AM org.hibernate.cfg.SettingsFactory createQueryTranslatorFactory
INFO: Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory
Aug 30, 2005 9:02:30 AM org.hibernate.hql.ast.ASTQueryTranslatorFactory <init>
INFO: Using ASTQueryTranslatorFactory
Aug 30, 2005 9:02:30 AM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Query language substitutions: {}
Aug 30, 2005 9:02:30 AM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Second-level cache: enabled
Aug 30, 2005 9:02:30 AM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Query cache: disabled
Aug 30, 2005 9:02:30 AM org.hibernate.cfg.SettingsFactory createCacheProvider
INFO: Cache provider: org.hibernate.cache.EhCacheProvider
Aug 30, 2005 9:02:30 AM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Optimize cache for minimal puts: disabled
Aug 30, 2005 9:02:30 AM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Structured second-level cache entries: disabled
Aug 30, 2005 9:02:30 AM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Echoing all SQL to stdout
Aug 30, 2005 9:02:30 AM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Statistics: disabled
Aug 30, 2005 9:02:30 AM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Deleted entity synthetic identifier rollback: disabled
Aug 30, 2005 9:02:30 AM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Default entity-mode: POJO
Aug 30, 2005 9:02:30 AM org.hibernate.impl.SessionFactoryImpl <init>
INFO: building session factory
Aug 30, 2005 9:02:30 AM net.sf.ehcache.config.Configurator configure
WARNING: No configuration found. Configuring ehcache from ehcache-failsafe.xml found in the classpath: jar:file:/D:/JavaProjects/hibernate31/lib/ehcache-1.1.jar!/ehcache-failsafe.xml
Creating a new Session for the thread
Aug 30, 2005 9:02:31 AM org.hibernate.impl.SessionFactoryObjectFactory addInstance
INFO: Not binding factory to JNDI, no JNDI name configured
Aug 30, 2005 9:02:31 AM org.hibernate.impl.SessionFactoryImpl checkNamedQueries
INFO: Checking 0 named queries
Hibernate: values current timestamp
Aug 30, 2005 9:02:31 AM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: -142, SQLState: 42612
Aug 30, 2005 9:02:31 AM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: [IBM][CLI Driver][DB2] SQL0142N The SQL statement is not supported. SQLSTATE=42612

Exception Stack Trace:
could not select current db timestamp
org.hibernate.exception.SQLGrammarException: could not select current db timestamp
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.type.DbTimestampType.usePreparedStatement(DbTimestampType.java:76)
at org.hibernate.type.DbTimestampType.getCurrentTimestamp(DbTimestampType.java:55)
at org.hibernate.type.DbTimestampType.seed(DbTimestampType.java:44)
at org.hibernate.engine.Versioning.seed(Versioning.java:34)
at org.hibernate.engine.Versioning.seedVersion(Versioning.java:56)
at org.hibernate.event.def.AbstractSaveEventListener.substituteValuesIfNecessary(AbstractSaveEventListener.java:357)
at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:247)
at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:167)
at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:114)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:186)
at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:33)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:175)
at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:27)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:70)
at org.hibernate.impl.SessionImpl.fireSave(SessionImpl.java:516)
at org.hibernate.impl.SessionImpl.save(SessionImpl.java:506)
at org.hibernate.impl.SessionImpl.save(SessionImpl.java:502)
at com.jbhunt.reference.hibernate.dao.SimpleAccessDAO.createAllowedValues(SimpleAccessDAO.java:46)
at com.jbhunt.reference.hibernate.main.SimpleAccess.createAllowedValues(SimpleAccess.java:46)
at com.jbhunt.reference.hibernate.main.SimpleAccess.main(SimpleAccess.java:25)
Caused by: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2] SQL0142N The SQL statement is not supported. SQLSTATE=42612

at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExceptionGenerator.java:269)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExceptionGenerator.java:206)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(SQLExceptionGenerator.java:457)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2(DB2PreparedStatement.java:1759)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.executeQuery(DB2PreparedStatement.java:1247)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:137)
at org.hibernate.type.DbTimestampType.usePreparedStatement(DbTimestampType.java:63)
... 19 more

considering the underlying cause is a JDBC exception I am fairly certain the SQL is being generated ;)

In the mapping file I have show_sql as true and still it doesnt show the sql in this respect. SQL is displayed when I have <timestamp source="vm" and it works fine then.

The message from the exception is could not select current db timestamp . I guess it is failing when it tries to select the timestamp from DB2 before inserting the data.

Do you have an example on using timestamp with source as db and DB2 dialect. I have been searching for this for a long time and couldnt find an answer.

Has there been a resolution to this? I've run into the same problem.

To answer the question of what is the SQL being generated, it is generating the statement VALUES CURRENT TIMESTAMP

It isn't displayed in the hibernate logs, but I can see it coming through on the db. The logs from the db are as follows