"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 Seems they still work.call NEXT VALUE FOR SEQUENCE_NAME
will no longer 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.