Search code examples
javaintegration-testingh2spring-jdbc

How to get nextvalue in a sequence using H2 Embedded Database?

Background

I'm using Oralce as database and H2 Embedded in-memory database for integration tests.

I've created some tables in H2 and was able to get some inserted data. However, I could not retrieve the current sequence value for the sequence I've created in H2.

I'm aware that Oracle and H2 are not the same and use similar but different syntax. I'm also aware that you can define some alias in H2 in-memory database and embed a java code in-lieu of Oracle SQL functions. This gives me a hint that there must be a workaround in retrieving the sequence value using Oracle's syntax over H2 database.

The question

How do I make Oracle's syntax for selecting the current value of a sequence work on H2? Do I need to create alias and write embedded java code in-lieu of Oracle's syntax? What are my options?

The code under test uses the following hypothetical but similar SQL

select myschema.mysequence.nextval from dual

But I'm getting error like the following

org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "nextval" not found [42122-199]

It's probably obvious that that isn't going to work because of the difference in syntax. I'm looking for a workaround without having to change the code being tested which uses Oracle's syntax.

Updates

I'm using Spring JDBC's EmbeddedDatabaseBuilder which means I don't connect to a separate independent H2 database instance but to an instance created on-the-fly in which I include DDL scripts to create the DB objects.

The following post along with the accepted answer helped solved it.

Does Spring embedded database support different SQL dialects?

Solution

  • Such Oracle-style expression is actually supported by H2, including the version 1.4.199:

    set mode Oracle;
create schema myschema;
create sequence myschema.mysequence;
select myschema.mysequence.nextval from dual;
> 1
select myschema.mysequence.nextval from dual;
> 2

    You can use syntax from the SQL Standard in H2, if you wish, but it isn't supported by Oracle:

    VALUES NEXT VALUE FOR myschema.mysequence;

    I have no idea how you got Column "nextval" not found with your SQL; if schema or sequence doesn't exist, the exception will be different.

    You need to update your question with your real query or post it in a separate question, because this question already contains an answer in the question inself: your own sample query is valid for H2.