"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 ?

Solution 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.

Solution 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

Comments

[Devanshu Mevada]

@hatchetman82 You're welcome. BTW: The common way of recognizing a good answer is upvoting it ;)

[Devanshu Mevada]

@hatchetman82 No problem. It's just that if you consider an answer as a good answer (which is very likely if you accept it), then it makes sense to upvote it IMO, this is how SO works.

[James P.]

Hi, this may be related. I'm using Apache's DdlUtils to generate the database on startup using this xml for the id: <column name="id" type="INTEGER" required="true" primaryKey="true" />. What should I be doing to get a new key value when inserting objects? I'm also using iBatis.