"correct" way to select next sequence value in HSQLDB 2.0.0-rc8

Question

suppose i have a sequence, called TEST_SEQ what would be the correct way of selecting its next value ? this does not work:

select next value for TEST_SEQ

probably because it expects a "FROM" clause. looking at HSQLDialect.getSequenceNextValString() in hibernate i see this:

"select next value for " + sequenceName + " from dual_" + sequenceName

which in my case would result in something like:

select next value for TEST_SEQ from dual_TEST_SEQ

which does not work for 2.0.0-rc8 (i only assume this works in pre-2.0 versions - havent verified) I've come across a solution that involves creating a simple table with 1 row called DUAL, in which case this will work (oracle style):

select next value for TEST_SEQ from DUAL

but hsqldb does not come with this table out of the box, and im not sure how i can get hibernate to generate such a table on "first boot".

Im thinking there has to be a way to get the next value for a sequence out of the box and im just missing it. any ideas ?

Answer 1

suppose i have a sequence, called TEST_SEQ what would be the correct way of selecting its next value ?

While the documentation says:

The next value for a sequence can be included in SELECT, INSERT and UPDATE statements as in the following example:

SELECT [...,] NEXT VALUE FOR <sequencename> [, ...] FROM <tablename>;

the "correct" way (because simpler, because not involving a table like a dumb DUAL table that HSQLDB doesn't have) would be:

call NEXT VALUE FOR <sequence_name>;

This appeared in 1.7.2 and this is actually how Hibernate handles sequences in the HSQLDialect of "recent" versions of Hibernate Core (see HHH-2839).

And indeed, this is what I see in the HSQLDialect of hibernate-core-3.3.0.SP1.jar:

public String getSequenceNextValString(String sequenceName) {
    return "call next value for " + sequenceName;
}

So my advice is: upgrade to a newer version of Hibernate, you are very likely using Hibernate Core 3.2.5 or prior.

Answer 2

Apparently if you run

SET DATABASE SQL SYNTAX PGS TRUE (PGS standing for Postgres)

then you can query it using standard Postgres syntax like select nextval('sequence_name')

It even returns nothing for curval if nextval hasn't been called yet, possibly similar to how Postgres behaves.

http://hsqldb.org/doc/guide/dbproperties-chapt.html

Also note that if you once do this , typical HSQLDB sequence like call NEXT VALUE FOR SEQUENCE_NAME will no longer work. Seems they still work.

Also note that more "exotic" Postgres stuffs like select last_value from schemaName.sequence_name aren't yet emulated/don't work the same (for getting the current value of a sequence regardless of session).

Avoid this message Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: NEXTVAL

Answer 3

Maybe hibernate is creating those dual_xx tables on the fly? Weird.

Anyway, your way seems to work for me with hsqldb 2.3.2:

 select next value for <sequence name>;

but you can't alias the name, for that I had to do this:

  select next value for <sequence name> as <my name>, 3

Then the alias worked, no dual needed. call next value I couldn't seem to alias...