Search code examples
sqlhsqldb

Hsqldb not found sequence that I just created

I'm trying to put this simple code to work for create a sequence in a DDL SQL script:

CREATE SEQUENCE seq START WITH 1 INCREMENT BY 1;
INSERT INTO MyTable (id, perc, tablex_id, tabley_id) SELECT NEXT VALUE FOR seq, 0, t.id, cb.id FROM tablex t, tabley cb;

But I receive this error:

Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: SEQ
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.ParserDQL.readSequenceExpressionOrNull(Unknown Source)
    at org.hsqldb.ParserDQL.XreadSimpleValueExpressionPrimary(Unknown Source)
    at org.hsqldb.ParserDQL.XreadAllTypesValueExpressionPrimary(Unknown Source)
    at org.hsqldb.ParserDQL.XreadAllTypesPrimary(Unknown Source)
    at org.hsqldb.ParserDQL.XreadAllTypesFactor(Unknown Source)
    at org.hsqldb.ParserDQL.XreadAllTypesTerm(Unknown Source)
    at org.hsqldb.ParserDQL.XreadAllTypesCommonValueExpression(Unknown Source)
    at org.hsqldb.ParserDQL.XreadValueExpression(Unknown Source)
    at org.hsqldb.ParserDQL.XreadSelect(Unknown Source)
    at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)
    at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)
    at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source)
    at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source)
    at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)
    at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source)
    at org.hsqldb.ParserDML.compileInsertStatement(Unknown Source)
    at org.hsqldb.ParserCommand.compilePart(Unknown Source)
    at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
    at org.hsqldb.Session.executeDirectStatement(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
    ... 31 more

Why Hqdlb can't found the sequence seq? I'm using Hsqldb 2.4.0.

Solution

  • You need to execute these SQL statements one by one.

    When multiple SQL statements are submitted to the database as one statement, HSQLDB parses and compiles the complete text before executing the statements one by one. When the second statement is compiled, the first statement has not yet been executed and the sequence does not exist at this point.