#1327 Cannot use table dual in stored procedure

[Pollerboy]

I create a database like this:

SET DATABASE SQL SYNTAX ORA TRUE;

CREATE SEQUENCE test_id_seq;

CREATE TABLE procedure_test (
id DECIMAL(13) NOT NULL,
name VARCHAR(50)
);

CREATE PROCEDURE test_procedure(IN p_name VARCHAR(50))
MODIFIES SQL DATA
BEGIN ATOMIC
INSERT INTO procedure_test (
id,
name
)
SELECT
test_id_seq.NextVal,
p_name
FROM dual;
END
.;

In the resulting script, the procedure is created as follows:

CREATE PROCEDURE PUBLIC.TEST_PROCEDURE(IN P_NAME VARCHAR(50)) SPECIFIC TEST_PROCEDURE_10096 LANGUAGE SQL NOT DETERMINISTIC MODIFIES SQL DATA NEW SAVEPOINT LEVEL BEGIN ATOMIC INSERT INTO PUBLIC.PROCEDURE_TEST(ID,NAME)SELECT TEST_ID_SEQ.NEXTVAL,P_NAME FROM SYSTEM_SCHEMA.DUAL;END

Trying to connect to the database complaints about invalid schema SYSTEM_SCHEMA:

Caused by: org.hsqldb.HsqlException: invalid schema name: SYSTEM_SCHEMA
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.SchemaManager.getSchemaHsqlName(Unknown Source)
at org.hsqldb.SchemaManager.getSchemaName(Unknown Source)
at org.hsqldb.Session.getSchemaName(Unknown Source)
at org.hsqldb.SchemaManager.getTable(Unknown Source)
at org.hsqldb.ParserDQL.readTableName(Unknown Source)
at org.hsqldb.ParserDQL.readTableOrSubquery(Unknown Source)
at org.hsqldb.ParserDQL.XreadTableReference(Unknown Source)
at org.hsqldb.ParserDQL.XreadFromClause(Unknown Source)
at org.hsqldb.ParserDQL.XreadTableExpression(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.ParserRoutine.compileSQLProcedureStatementOrNull(Unknown Source)
at org.hsqldb.ParserRoutine.compileSQLProcedureStatementList(Unknown Source)
at org.hsqldb.ParserRoutine.compileCompoundStatement(Unknown Source)
at org.hsqldb.ParserRoutine.compileSQLProcedureStatementOrNull(Unknown Source)
at org.hsqldb.ParserRoutine.readRoutineBody(Unknown Source)
at org.hsqldb.ParserRoutine.compileCreateProcedureOrFunction(Unknown Source)
at org.hsqldb.ParserDDL.compileCreate(Unknown Source)
at org.hsqldb.ParserCommand.compilePart(Unknown Source)
at org.hsqldb.ParserCommand.compileStatement(Unknown Source)
at org.hsqldb.Session.compileStatement(Unknown Source)
... 20 more

[Fred Toussi]

Thanks.

Fixed in the latest snapshot #55 which can be downloaded here:

http://www.hsqldb.org/repos/org/hsqldb/hsqldb/SNAPSHOT/