HSQLDB Stored Procedure ran through SQL Tool is throwing Scanner Error

Question

From Swing UI I am able execute the following Scripts one by one.

ALTER CATALOG PUBLIC RENAME TO SOMENAME;

CREATE SCHEMA SOMESCHEMA;

SET DATABASE SQL SYNTAX ORA TRUE;

CREATE PROCEDURE SOMENAME.SOMESCHEMA.SP_FAILED_COUNT(IN i_ssn VARCHAR(100), IN i_page_id NUMBER(10), IN i_ip_address VARCHAR(100), IN i_session_guid VARCHAR(100), OUT o_toomanyfails VARCHAR(2000))
    READS SQL DATA
        BEGIN ATOMIC
        SET o_toomanyfails = 'N';
    END
.;

But When I run the same from SQL Tool I am getting the following error

> java -jar sqltool-2.4.1.jar --rcfile=C:\my-files\sqltool.rc web C:\my-files\hello.sql
SEVERE  Rolling back SQL transaction.
Exception in thread "main" java.lang.Error: Error: could not match input
        at org.hsqldb.cmdline.sqltool.SqlFileScanner.zzScanError(Unknown Source)
        at org.hsqldb.cmdline.sqltool.SqlFileScanner.yylex(Unknown Source)
        at org.hsqldb.cmdline.SqlFile.scanpass(Unknown Source)
        at org.hsqldb.cmdline.SqlFile.execute(Unknown Source)
        at org.hsqldb.cmdline.SqlTool.objectMain(Unknown Source)
        at org.hsqldb.cmdline.SqlTool.main(Unknown Source)

I found similar question in stack overflow. Solution was to add .; at the end. But even after adding .; I am getting the same error.

I also added "." on the first line to enable raw mode. Now I am getting different exception

.
ALTER CATALOG PUBLIC RENAME TO SOMENAME;

CREATE SCHEMA SOMESCHEMA;

SET DATABASE SQL SYNTAX ORA TRUE;

CREATE PROCEDURE SOMENAME.SOMESCHEMA.SP_FAILED_COUNT(IN i_ssn VARCHAR(100), IN i_page_id NUMBER(10), IN i_ip_address VARCHAR(100), IN i_session_guid VARCHAR(100), OUT o_toomanyfails VARCHAR(2000))
    READS SQL DATA
        BEGIN ATOMIC
        SET o_toomanyfails = 'N';
    END
.;

Exception

> java -jar sqltool-2.4.1.jar --rcfile=C:\my-files\sqltool.rc web C:\my-files\hello.sql
SEVERE  SQL Error at 'C:\my-files\hello.sql' line 2:
".
ALTER CATALOG PUBLIC RENAME TO SOMENAME"
malformed numeric constant: .
org.hsqldb.cmdline.SqlTool$SqlToolException

score 0 · Answer 1 · answered Mar 22 '19 at 22:58

0

You begin raw mode by issuing the Special Command ".". You can then enter as much text in any format you want. When you are finished, enter a line consisting of only ".;" to store the input to the edit buffer and send it to the database server for execution.

See the Guide:

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

answered Mar 22 '19 at 22:58

fredt

24,044
3
40
61

i added "." on the first line of my .sql file and ran the command. I am getting the following exception - > java -jar sqltool-2.4.1.jar --rcfile=C:\my-files\sqltool.rc web C:\my-files\hello.sql SEVERE SQL Error at 'C:\my-files\hello.sql' line 2: ". ALTER CATALOG PUBLIC RENAME TO SOMENAME" malformed numeric constant: . org.hsqldb.cmdline.SqlTool$SqlToolException – Thiagarajan Ramanathan Mar 23 '19 at 19:36
Updated my question with new exception (i.e. after adding "." in first line). – Thiagarajan Ramanathan Mar 23 '19 at 23:22
It is working after adding blank line at last. But now I am getting different exception(user lacks privilege or object not found: SOMENAME org.hsqldb.cmdline.SqlTool$SqlToolException). I will ask about it in different post. – Thiagarajan Ramanathan Mar 25 '19 at 18:03

HSQLDB Stored Procedure ran through SQL Tool is throwing Scanner Error

1 Answers1