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

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


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.