HSQLDB SqlTool is throwing user lacks privilege or object not found

Question

I'm executing .sql scripts using SqlTool. It keep on saying user lacks privilege or object not found. The same script is working perfectly from Swing UI.

My Script (hello.sql)

\.
SET DATABASE SQL SYNTAX ORA TRUE;
ALTER CATALOG PUBLIC RENAME TO SOMENAME;
COMMIT;
CREATE SCHEMA SOMESCHEMA;
COMMIT;
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
COMMIT;
.
:;

Exception

> java -jar sqltool-2.4.1.jar --autocommit --rcfile C:\\my-files\\hsqldb\\2.4.1\\dbmanager.rc web C:\\my-files\\hsqldb\\2.4.1\\hello.sql                                                              
Executing command from edit buffer:                                                                                                                                                                   
"SET DATABASE SQL SYNTAX ORA TRUE;                                                                                                                                                                    
ALTER CATALOG PUBLIC RENAME TO SOMENAME;                                                                                                                                                              
COMMIT;                                                                                                                                                                                               
CREATE SCHEMA SOMESCHEMA;                                                                                                                                                                             
COMMIT;                                                                                                                                                                                               
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                                                                                                                                                                                               
COMMIT;"                                                                                                                                                                                              

SEVERE  SQL Error at 'C:\my-files\hsqldb\2.4.1\hello.sql' line 14:                                                                                                                                    
"SET DATABASE SQL SYNTAX ORA TRUE;                                                                                                                                                                    
ALTER CATALOG PUBLIC RENAME TO SOMENAME;                                                                                                                                                              
COMMIT;                                                                                                                                                                                               
CREATE SCHEMA SOMESCHEMA;                                                                                                                                                                             
COMMIT;                                                                                                                                                                                               
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                                                                                                                                                                                               
COMMIT;"                                                                                                                                                                                              
user lacks privilege or object not found: SOMENAME                                                                                                                                                    
org.hsqldb.cmdline.SqlTool$SqlToolException

The same script is working from HSQLDB Swing UI.

I tried adding commit but still it is not working. It is working fine if I remove catalogname.schemaname.(SOMENAME.SOMESCHEMA.) from script

Also, i didn't understand one thing.

If I execute the following command in Swing UI it working perfectly for first time but if i execute for the second time i get the following exception

user lacks privilege or object not found: PUBLIC / Error Code: -5501 / State: 42501

ALTER CATALOG PUBLIC RENAME TO SOMENAME;

It confirms that the catalog is renamed. But where if I run the same script using sqltool again and again it never throws the same exception. How to make it working from sqltool (i.e. after importing it from .sql script)

Blaine Simpson · Accepted Answer · 2019-04-07T19:29:05.007

There's no way that "The same script is working from HSQLDB Swing UI."

I loaded the exact script into HSQLDB Swing UI v. 2.4.1 and it fails as I expected with "unknown token: / Error Code: -5582 / State: 42582" because you have SqlTool-specific grammar in your script.

If I remove the Sql-Tool specific grammar then Swing UI reports "user lacks privilege or object not found: SOMENAME / Error Code: -5501 / State: 42501".

Fred T. can explain exactly why, but those commands apparently have to be in two transactions and I can make it work from Swing UI by just executing everything before the "CREATE SCHEMA" in one execution; then the rest in another execution. I suspect that you actually edited your script (not running "The same script") and then executed it with an older version of HyperSQL Swing UI where it did not execute all of the commands in a single transaction.

SqlTool doesn't have that limitation and allows you to control exactly what commands go over in which transaction. Just allow all commands (the COMMITs are unnecessary) in their own transactions (which is default behavior). It then works.

TIPS:

You don't need the COMMITs because these are DDL statements.
You can execute the . transaction by terminating it with ".;" instead of separate "." and ":." commands.
You don't need the \. at all because SqlTool automatically knows that you need a new grouped transaction when it sees "CREATE PROCEDURE" beginning a command. If it makes the code more readable for you, you can insert the ".\" immediately before the CREATE PROCEDURE.

In summary this works from SqlTool:

SET DATABASE SQL SYNTAX ORA TRUE;
ALTER CATALOG PUBLIC RENAME TO SOMENAME;
CREATE SCHEMA SOMESCHEMA;

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
.;

HSQLDB SqlTool is throwing user lacks privilege or object not found

1 Answers1