7

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?

supertonsky
  • 2,563
  • 6
  • 38
  • 68

1 Answers1

8

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.

Evgenij Ryazanov
  • 6,960
  • 2
  • 10
  • 18
  • The example I've given is not any different from the actual. I notice you have `set mode Oracle` statement. I've included it in my script when executing my DDL when creating the in-memory DB instance but that didn't work. Perhaps yours is not applicable to the embedded H2 DB? – supertonsky Apr 22 '20 at 08:58
  • I've used your `set mode Oracle;` in my script `myDDL.sql` in the following code `new EmbeddedDatabaseBuilder().setType(EmbeddedDatabaseType.H2).addScript("myDDL.sql").build()` but that doesn't seem to make the difference, I'm still getting the same error. – supertonsky Apr 22 '20 at 09:01
  • I think I need to make `set mode Oracle` work on my setup and that could complete the answer to my question. – supertonsky Apr 22 '20 at 09:16
  • 1
    H2 1.4.199 supports this Oracle-style command in the Regular mode too (but you really should use the Oracle compatibility mode in your case for better compatibility, you can simply append it to the JDBC URL with `;MODE=Oracle`). – Evgenij Ryazanov Apr 22 '20 at 09:47
  • It looks like you have a stray `nextval` somewhere, because H2 reports the full name of the column in such error messages. – Evgenij Ryazanov Apr 22 '20 at 09:57
  • 1
    Found out what the problem was. `nextval` is the column name the code is expecting the query will use as output but H2 used `NEXT VALUE FOR MYSCHEMA.MYSEQUENCE` as a column name instead. To fix it, I had to create an alias `select myschema.mysequence.nextval nextval from dual`. It actually needs a stray `nextval`. – supertonsky Apr 22 '20 at 10:17