Hibernate named query with sequence.nextval errors with "column nextval not found"

[John Russell]

I have a hibernate 3 hbm.xml mapping file with a named query in it.
I create the sequence in it by using RunScript.execute() and then some
part of the code calls the named query using hibernate.

<sql-query name="GetNextIcmAgentId">
<return-scalar column="NEXTVAL" type="java.lang.Integer"/>
SELECT icmagentid.NEXTVAL
</sql-query>

I have tried a wide variety of select statements to no avail. Is
there something obvious I'm doing wrong? Below are the select
varieties I've tried with the root exception each one caused.



SELECT icmagentid.NEXTVAL

Caused by: org.h2.jdbc.JdbcSQLException: Column NEXTVAL not
found [42122-104]

SELECT LIMIT 1 icmagentid.NEXTVAL FROM mmca_dbversion <----- (this
one works with informix, tried with and without terminating semicolon)

Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL
statement SELECT LIMIT 1 ICMAGENTID.NEXTVAL FROM MMCA_DBVERSION ;
expected SELECT; SQL statement:
SELECT LIMIT 1 icmagentid.NEXTVAL FROM mmca_dbversion [42001-104]

SELECT icmagentid.NEXTVAL FROM mmca_dbversion;

Caused by: org.h2.jdbc.JdbcSQLException: Column NEXTVAL not found
[42122-104]

SELECT icmagentid.NEXTVAL;
Caused by: org.h2.jdbc.JdbcSQLException: Column NEXTVAL not found
[42122-104]

SELECT nextval('icmagentid');
Caused by: org.h2.jdbc.JdbcSQLException: Column NEXTVAL not found
[42122-104]



Thanks a lot for any help you can provide.

[Thomas Mueller]

Hi,

What is your database URL, and could you post the complete stack trace please?

I can't reproduce the problem. My test case is:

create sequence icmagentid;
select icmagentid.nextval from dual;
SELECT nextval('icmagentid');
drop table test if exists;
create table test(icmagentid int) as select 1;
select icmagentid.nextval from test;
SELECT nextval('icmagentid') from test;
drop sequence icmagentid;
select icmagentid.nextval from test;
SELECT nextval('icmagentid') from test;


Regards,
Thomas

[John Russell]

The URL is jdbc.url=jdbc:h2:/Users/jorussel/eng/output/conf/h2db

The stack traces are mostly the same. Here's one of them. The entire
setup is that the named query is defined in a hibernate XML mapping
file and then called

Query query = sessionFactory.getCurrentSession().getNamedQuery
( namedQuery );

The sql file that creates the sequences looks like this:

DROP SEQUENCE IF EXISTS defaultseq;

CREATE SEQUENCE defaultseq INCREMENT BY 1 START WITH 10000000000;

DROP SEQUENCE IF EXISTS icmskillgrouppernum;

CREATE SEQUENCE icmskillgrouppernum INCREMENT BY 1 START WITH 1;
...... many many more


If I ran the RunScript.execute creating the sequences with a different
Connection object than the one that ran the named query, would that be
a problem? I had assumed that the sequences were in the database so
all Connections could see them.

Thanks.

com.cisco.ccbu.oamp.omgr.db.DBException: could not execute query
at
com.cisco.ccbu.oamp.omgr.db.DBExceptionMapper.getAppropriateException
(DBExceptionMapper.java:172)
at com.cisco.ccbu.oamp.omgr.db.ExceptionInterceptor.afterThrowing
(ExceptionInterceptor.java:55)
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:597)
at
org.springframework.aop.framework.adapter.ThrowsAdviceInterceptor.invokeHandlerMethod
(ThrowsAdviceInterceptor.java:146)
at
org.springframework.aop.framework.adapter.ThrowsAdviceInterceptor.invoke
(ThrowsAdviceInterceptor.java:131)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed
(ReflectiveMethodInvocation.java:171)
at
org.springframework.aop.framework.adapter.AfterReturningAdviceInterceptor.invoke
(AfterReturningAdviceInterceptor.java:50)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed
(ReflectiveMethodInvocation.java:171)
at
org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke
(MethodBeforeAdviceInterceptor.java:50)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed
(ReflectiveMethodInvocation.java:171)
at
org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke
(ExposeInvocationInterceptor.java:89)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed
(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke
(JdkDynamicAopProxy.java:204)
at $Proxy7.findByNamedQuery(Unknown Source)
at com.cisco.ccbu.oamp.omgr.db.GenericBO.findByNamedQuery
(GenericBO.java:1089)
at com.cisco.ccbu.oamp.omgr.db.GenericBO$$FastClassByCGLIB$
$333c2e99.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
at org.springframework.aop.framework.Cglib2AopProxy
$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:700)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed
(ReflectiveMethodInvocation.java:149)
at
org.springframework.transaction.interceptor.TransactionInterceptor.invoke
(TransactionInterceptor.java:106)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed
(ReflectiveMethodInvocation.java:171)
at
org.springframework.transaction.interceptor.TransactionInterceptor.invoke
(TransactionInterceptor.java:106)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed
(ReflectiveMethodInvocation.java:171)
at
org.springframework.transaction.interceptor.TransactionInterceptor.invoke
(TransactionInterceptor.java:106)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed
(ReflectiveMethodInvocation.java:171)
at
org.springframework.aop.framework.adapter.ThrowsAdviceInterceptor.invoke
(ThrowsAdviceInterceptor.java:126)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed
(ReflectiveMethodInvocation.java:171)
at
org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke
(ExposeInvocationInterceptor.java:89)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed
(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.Cglib2AopProxy
$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:635)
at com.cisco.ccbu.oamp.omgr.business.AssignmentQueueBO$
$EnhancerByCGLIB$$a3a60860.findByNamedQuery(<generated>)
at
com.cisco.ccbu.oamp.omgr.business.AssignmentQueueBOTest.createResources
(AssignmentQueueBOTest.java:355)
at com.cisco.ccbu.oamp.omgr.business.AssignmentQueueBOTest.setup
(AssignmentQueueBOTest.java:160)
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:597)
at org.junit.internal.runners.BeforeAndAfterRunner.invokeMethod
(BeforeAndAfterRunner.java:74)
at org.junit.internal.runners.BeforeAndAfterRunner.runBefores
(BeforeAndAfterRunner.java:50)
at org.junit.internal.runners.BeforeAndAfterRunner.runProtected
(BeforeAndAfterRunner.java:33)
at org.junit.internal.runners.TestMethodRunner.runMethod
(TestMethodRunner.java:75)
at org.junit.internal.runners.TestMethodRunner.run
(TestMethodRunner.java:45)
at org.junit.internal.runners.TestClassMethodsRunner.invokeTestMethod
(TestClassMethodsRunner.java:66)
at org.junit.internal.runners.TestClassMethodsRunner.run
(TestClassMethodsRunner.java:35)
at org.junit.internal.runners.TestClassRunner$1.runUnprotected
(TestClassRunner.java:42)
at org.junit.internal.runners.BeforeAndAfterRunner.runProtected
(BeforeAndAfterRunner.java:34)
at org.junit.internal.runners.TestClassRunner.run
(TestClassRunner.java:52)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run
(JUnit4TestReference.java:45)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run
(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests
(RemoteTestRunner.java:460)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests
(RemoteTestRunner.java:673)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run
(RemoteTestRunner.java:386)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main
(RemoteTestRunner.java:196)
Caused by: org.hibernate.exception.SQLGrammarException: could not
execute query
at org.hibernate.exception.SQLStateConverter.convert
(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert
(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2223)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:
289)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:
1695)
at org.hibernate.impl.AbstractSessionImpl.list
(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
at com.cisco.ccbu.oamp.omgr.db.GenericDAOImpl.findByNamedQuery
(GenericDAOImpl.java:726)
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:597)
at
org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection
(AopUtils.java:307)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint
(ReflectiveMethodInvocation.java:182)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed
(ReflectiveMethodInvocation.java:149)
at
org.springframework.transaction.interceptor.TransactionInterceptor.invoke
(TransactionInterceptor.java:106)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed
(ReflectiveMethodInvocation.java:171)
at
org.springframework.transaction.interceptor.TransactionInterceptor.invoke
(TransactionInterceptor.java:106)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed
(ReflectiveMethodInvocation.java:171)
at
org.springframework.aop.framework.adapter.ThrowsAdviceInterceptor.invoke
(ThrowsAdviceInterceptor.java:126)
... 48 more

Caused by: org.h2.jdbc.JdbcSQLException: Column NEXTVAL not found
[42122-104]

at org.h2.message.Message.getSQLException(Message.java:103)
at org.h2.message.Message.getSQLException(Message.java:114)
at org.h2.message.Message.getSQLException(Message.java:77)
at org.h2.jdbc.JdbcResultSet.getColumnIndex(JdbcResultSet.java:2964)
at org.h2.jdbc.JdbcResultSet.get(JdbcResultSet.java:3015)
at org.h2.jdbc.JdbcResultSet.getInt(JdbcResultSet.java:315)
at com.mchange.v2.c3p0.impl.NewProxyResultSet.getInt
(NewProxyResultSet.java:2573)
at org.hibernate.type.IntegerType.get(IntegerType.java:28)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:163)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:189)
at org.hibernate.loader.custom.CustomLoader
$ScalarResultColumnProcessor.extract(CustomLoader.java:474)
at org.hibernate.loader.custom.CustomLoader
$ResultRowProcessor.buildResultRow(CustomLoader.java:420)
at org.hibernate.loader.custom.CustomLoader.getResultColumnOrRow
(CustomLoader.java:317)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:606)
at org.hibernate.loader.Loader.doQuery(Loader.java:701)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections
(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
... 67 more




On Apr 29, 12:24 am, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

> Hi,
>
> What is your database URL, and could you post the complete stack trace please?
>
> I can't reproduce the problem. My test case is:
>
> create sequence icmagentid;
> select icmagentid.nextval from dual;
> SELECT nextval('icmagentid');
> drop table test if exists;
> create table test(icmagentid int) as select 1;
> select icmagentid.nextval from test;
> SELECT nextval('icmagentid') from test;
> drop sequence icmagentid;
> select icmagentid.nextval from test;
> SELECT nextval('icmagentid') from test;
>
> Regards,
> Thomas
>

[Thomas Mueller]

Hi,

Thanks! I think I understand the problem now. The column label in H2
is not NEXTVAL but
"NEXT VALUE FOR PUBLIC.DEFAULTSEQ". Try:

<sql-query name="GetNextIcmAgentId">
<return-scalar column="NEXTVAL" type="java.lang.Integer"/>

SELECT icmagentid.NEXTVAL AS NEXTVAL
</sql-query>

Regards,
Thomas