Search code examples
sqlstored-procedureshsqldb

Unterminated input error trying to create a stored procedure using SQL Tool with HSQLDB 2.2.8

I have a very simple HSQLDB CREATE PROCEDURE statement:

CREATE PROCEDURE test_procedure()
CONTAINS SQL
BEGIN
DECLARE test_variable INTEGER;
SET test_variable = 0;
END

When I try to execute this SQL using SQLTool, it fails with a "Unterminated input" error. The command I am using is:

java -jar -cp $HSQLDB_HOME/lib/sqltool.jar:$HSQLDB_HOME/lib/hsqldb.jar $HSQLDB_HOME/lib/sqltool.jar db script.sql

The exact error I get is:

SEVERE  Error at 'script.sql' line 7:
"? CREATE PROCEDURE test_procedure()
CONTAINS SQL
BEGIN ATOMIC
DECLARE test_variable INTEGER;
SET test_variable = 0;
END"
Unterminated input:  "CREATE PROCEDURE test_procedure()
CONTAINS SQL
BEGIN ATOMIC
DECLARE test_variable INTEGER;
SET test_variable = 0;
END"
org.hsqldb.cmdline.SqlTool$SqlToolException

So far I have figured out this much:

  1. If I run this SQL using one of the GUI tools provided by HSQLDB (like DatabaseManagerSwing), it works fine.
  2. I can execute other SQL statements using the exact same command (selects, inserts, deletes, etc.), but not CREATE PROCEDURE
  3. My script.sql file contains only 6 lines, but the error mentions line 7, which makes no sense to me
  4. Adding a semicolon at the end of the SQL in the file makes no difference
  5. I have tried this both on Windows and on Linux with the exact same results
  6. I have tried to pass the file to SQL Tool through stdin and by using inline SQL with the exact same results
  7. I have tried changing the stored procedure body to something else, but it did not make a difference

I am out of ideas at this point. Has anyone encountered this error? Is there something that can be done about it?

Solution

  • You can follow the examples in the guide and use the sample files as template:

    http://hsqldb.org/doc/2.0/util-guide/sqltool-chapt.html#sqltool_embedded-langs-sect