Search code examples
javasequencehsqldb

File-based HSQLDB does not track call next value on sequence

If I have a file-based HSQLDB instance and I try to get some raw sequence values into my java code:

JdbcTemplate(dataSource).queryForObject("call next value for mysequence", int.class)

Then when I restart the database I see the following line in db.script:

ALTER SEQUENCE PUBLIC.MYSEQUENCE RESTART WITH 1

That means each time I restart, MYSEQUENCE begins again at 1 irrespective of how many times call next value is invoked outside a DML statement.

If the next value for mysequence is invoked as part of an update or insert, then the RESTART WITH looks correct in the db.script file.

What could I be doing wrong?

(follow up: I've tried the same idiom in Derby and, apart from a minor issue where an unexpected DB shutdown will cause the sequence to start after the previously pre-allocated value, rather than the next value, it works as expected. H2 works as expected)

Solution

  • You should execute the SHUTDOWN statement to close the database. This should persist the sequence value.