Syntax issue with HSQL sequence: `NEXTVAL` instead of `NEXT VALUE`

Question

I have webapps deployed on a Jetty server and connected to HSQLDB databases located on the HSQLDB server. I get this error when I try to create an entity:

ERROR org.hibernate.util.JDBCExceptionReporter Unexpected token: NEXTVAL in statement [/* dynamic native SQL query */ SELECT nextval('campagne_sequence')]

I initialize the sequence as follows:

CREATE SEQUENCE PUBLIC.CAMPAGNE_SEQUENCE START WITH 1 INCREMENT BY 1

In my webapp, I set the Hibernate dialect with org.hibernate.dialect.HSQLDialect

I checked the syntax to create a sequence and it's correct. I'm using hsqldb-1.8.0.10 and I also checked if the syntax have changed for the next version, it didn't.

I know the correct syntax is NEXT VALUE and not NEXTVAL as in PostgreSQL, but I don't how to fix it. Can anyone help me with this? Thanks

Edit:

In test-environment, everything works fine. All my tests run fine. But in dev-environment, I get the error when I try to create a Campagne. There is a test which creates one too and it works. So it should work with hsqldb-1.8.0.10 version.

I tried to add the String ;sql.syntax_pgs=true after the database URL, didn't work.

Edit:

This is the full stack trace I get:

2014-01-22 10:24:22,813 [RMI TCP Connection(6)-127.0.0.1] WARN  org.hibernate.util.JDBCExceptionReporter SQL Error: -11, SQLState: 37000
2014-01-22 10:24:22,820 [RMI TCP Connection(6)-127.0.0.1] ERROR org.hibernate.util.JDBCExceptionReporter Unexpected token: NEXTVAL in statement [/* dynamic native SQL query */ SELECT nextval('campagne_sequence')]
2014-01-22 10:24:22,827 [RMI TCP Connection(6)-127.0.0.1] WARN  org.springframework.remoting.support.RemoteInvocationTraceInterceptor Processing of RmiServiceExporter remote call resulted in fatal exception: com.square.core.service.interfaces.CampagneService.creerCampagne
org.hibernate.exception.SQLGrammarException: could not execute query
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.loader.Loader.doList(Loader.java:2536)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
    at org.hibernate.loader.Loader.list(Loader.java:2271)
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:316)
    at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1842)
    at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
    at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:157)
    at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:859)
    at com.square.core.dao.implementations.CampagneDaoImplementation.rechercherSequence(CampagneDaoImplementation.java:169)
    at com.square.core.util.sequence.CampagneSequenceUtilImpl.rechercherSequence(CampagneSequenceUtilImpl.java:32)
    at com.square.core.service.implementations.CampagneServiceImplementation.creerCampagne(CampagneServiceImplementation.java:156)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    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.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
    at $Proxy263.creerCampagne(Unknown Source)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    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.security.intercept.method.aopalliance.MethodSecurityInterceptor.invoke(MethodSecurityInterceptor.java:66)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
    at $Proxy264.creerCampagne(Unknown Source)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    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.remoting.support.RemoteInvocationTraceInterceptor.invoke(RemoteInvocationTraceInterceptor.java:77)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
    at $Proxy263.creerCampagne(Unknown Source)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at org.springframework.remoting.support.RemoteInvocation.invoke(RemoteInvocation.java:205)
    at org.springframework.security.context.rmi.ContextPropagatingRemoteInvocation.invoke(ContextPropagatingRemoteInvocation.java:103)
    at org.springframework.remoting.support.DefaultRemoteInvocationExecutor.invoke(DefaultRemoteInvocationExecutor.java:38)
    at org.springframework.remoting.support.RemoteInvocationBasedExporter.invoke(RemoteInvocationBasedExporter.java:78)
    at org.springframework.remoting.rmi.RmiBasedExporter.invoke(RmiBasedExporter.java:72)
    at org.springframework.remoting.rmi.RmiInvocationWrapper.invoke(RmiInvocationWrapper.java:72)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:322)
    at sun.rmi.transport.Transport$1.run(Transport.java:177)
    at sun.rmi.transport.Transport$1.run(Transport.java:174)
    at java.security.AccessController.doPrivileged(Native Method)
    at sun.rmi.transport.Transport.serviceCall(Transport.java:173)
    at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:553)
    at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(TCPTransport.java:808)
    at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:667)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
    at java.lang.Thread.run(Thread.java:722)
Caused by: java.sql.SQLException: Unexpected token: NEXTVAL in statement [/* dynamic native SQL query */ SELECT nextval('campagne_sequence')]
    at org.hsqldb.jdbc.Util.throwError(Unknown Source)
    at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
    at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
    at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:171)
    at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:278)
    at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:534)
    at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:452)
    at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:161)
    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1700)
    at org.hibernate.loader.Loader.doQuery(Loader.java:801)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
    at org.hibernate.loader.Loader.doList(Loader.java:2533)
    ... 68 more

score 4 · Answer 1 · answered Jan 18 '14 at 18:05

4

You can use this with HSQLDB version 1.8.0 and later if you create a table ONE_ROW_TABLE and insert exactly one row into it.

 SELECT NEXT VALUE FOR campagne_sequence FROM ONE_ROW_TABLE

But you should move to HSQLDB version 2.3.x which gives you the following options:

 VALUES NEXT VALUE FOR campagne_sequence

or to use the same method as PostgreSQL, add this string to the end of the database URL ;sql.syntax_pgs=true to enable PostgreSQL syntax compatibility mode in HSQLDB.

 SELECT nextval('campagne_sequence')

answered Jan 18 '14 at 18:05

fredt

24,044
3
40
61

Wel.. it didn't work. I added this string to my server.properties for Hsqldb Server and in my webapp. I'd rather not to upgrade my version of Hsqldb, there are modifications between the two versions, as the name of the datasource bean (1.8: jdbcDataSource ; 2.3: JDBCDataSource), but I don't know them all. But if it's the only way, I'll do it – Soma Jan 20 '14 at 09:01
Anything worked. The thing, it works fine in test-environment with a dataset and a database in memory but not in dev-environment with a filed database. I've checked all dependencies in the project and in parent-projects or plugin, I only have hslqdb-1.8.0.10 or hsqldb-1.8.0.7 – Soma Jan 20 '14 at 15:20

Angular University · Accepted Answer · 2014-01-22T10:14:10.693

3

Based on the QUERY comment /* dynamic native SQL query */ the query is not generated by the dialect but by a native SQL query, so fixing the query would solve the problem.

Have a look at the project code to see if there any native queries calling sequences using nextval.

edited Jan 22 '14 at 10:14

answered Jan 21 '14 at 22:11

Angular University

42,341
15
74
81

The class `HSQLDialect` is located is the jar `hibernate-core-3.6.0.Final.jar`. The two methods are exactly like you put it. In the comment part, it says `this version supports HSQLDB version 1.8 and higher` and I use `hsqldb-1.8.0.10`. The class is also set for this version: `int hsqldbVersion = 18;`. – Soma Jan 22 '14 at 08:19
OK, so that confirms that the query with the wrong syntax is not being written by the dialect. The comment on the query /*dynamic native SQL query*/ means that this query is a native query, created directly in the application code and not the framework, for example with entityManager.createNativeQuery(). If a search in the code for nextval does not return anything, can you post the full stacktrace? Because if it's not the application writing this query, then it's some other framework/library involved, and the stacktrace will tells us which one. – Angular University Jan 22 '14 at 09:03
Well, it's pretty long.. I can't add all of it – Soma Jan 22 '14 at 09:29
I added the first part in my question – Soma Jan 22 '14 at 09:33
It's these methods CampagneDaoImplementation.rechercherSequence() / CampagneSequenceUtilImpl.rechercherSequence() that are making a native query. could you post these methods ? – Angular University Jan 22 '14 at 09:54
Okay, it's there, in the first method... Someone before implemented it with `nextval`. I don't knwo why my search didn't find it :( I'll have to look for the others sequence :( Should you create another answer or edit this one ? I'll accept it after – Soma Jan 22 '14 at 10:04
I edited the answer and removed the part from the answer that said how to confirm the dialect/hibernate compatibility, and added a mention to search for nextval usage. – Angular University Jan 22 '14 at 10:16

Syntax issue with HSQL sequence: `NEXTVAL` instead of `NEXT VALUE`

2 Answers2

Linked