1

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?

Sevas
  • 4,215
  • 3
  • 27
  • 26

3 Answers3

2

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

fredt
  • 24,044
  • 3
  • 40
  • 61
  • 1
    Thanks for the link - I found the answer in the first example there. The thing missing was a .; at the end of the CREATE PROCEDURE statement – Sevas Aug 23 '12 at 20:12
0

I was pulling my hair out over this as well. The examples didn't actually solve the problem but a little tinkering got me there. To handle the embedded semi-colons you need to 'chunk' the input in 'raw mode'. To enter raw mode enter the command \. (yes, slash dot) Then your code then, ON A NEW LINE

.;

If you just put the .; after the END, it will not be seen (the .; is a command in itself, I guess).

Andy
  • 1
0

I stumbled on this error when executing SQL statements with HSQLDB that contained SQL-escaped single quotes, as such:

INSERT INTO my_table ('Joe\'s string value');

The above syntax doesn't appear to be correct (at least for MySQL, see here for instance) and one shouldn't come across it. For reference the correct escaping syntax is:

INSERT INTO my_table ('Joe''s string value');

So with '' rather than \'. However some SQL dump that was passed on to me did contain such escaping syntax and I had to deal with it.

mks-d
  • 166
  • 3
  • 14