HSQLDB get next value for IDENTITY column

auto-incrementhsqldbidentity

I'm looking for a query to get the next IDENTITY value of a HSQL table. Like if I were to insert a new row into a table that has an auto id column, what value would that turn into.

I did some research and couldn't find anything sadly.

Example:

Suppose I have a table like this:

CREATE TABLE IF NOT EXISTS Names (
  id INTEGER IDENTITY PRIMARY KEY,
  name VARCHAR(16)
)

Now I can insert names without having to specify the id. Instead it gets added automatically and increments itself with each insert.
Now I'd like to get whatever value will be next (if I don't manually set it).

Edit:

I think I found a solution:

SELECT
  IDENTITY_START
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  TABLE_NAME = 'NAMES'
  AND COLUMN_NAME = 'ID'

Best Answer

You can get the last identity value already inserted, but not the next one.

Use the IDENTITY() function. The value is useful for inserting into another table which has a foreign key referencing the Names table. For example,

  INSERT INTO OtherTable VALUES DEFAULT, IDENTITY(), 'A reference to Names table'

See the Guide, http://hsqldb.org/doc/2.0/guide/builtinfunctions-chapt.html#bfc_system_functions

Related Question