HSQLDB table not found using script to create table and insert data

Question

I am using the GUI (hsqldb.jar) in HSQLDB 2.2.9 to create a DB. I have all the SQL commands in a separate text file. So to create the DB, I just copy the text and paste into the HSQLDB editor and hit the "Execute SQL" button. I have successfully created my DB several times with different revisions, each time executing the CREATE TABLE commands with one press of the "Execute SQL" button, and the INSERT INTO commands with a subsequent press of the "Execute SQL" button. This works, but it would be more convenient to execute both the CREATE and the INSERT commands at the same time. I've tried to combine these into one "Execute SQL", but I keep getting this error:

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

Here's what I've tried:

CREATE TABLE Shop (
    Id int NOT NULL IDENTITY, 
    Name varchar(255) NOT NULL, 
    UNIQUE (Name)
)

INSERT INTO Shop VALUES (
    NULL, 
    'Test Shop'
)

Note that this exact same code works if I execute the SQL in two separate steps. I've tried putting COMMIT between the CREATE and the INSERT commands, as well as CHECKPOINT, but neither of these solved the problem. I also tried adding SET WRITE_DELAY FALSE at the top, but this didn't solve it either.

What do I need to add to this code to make it work in one step? Thanks!

Answer 1

This is not possible.

The GUI client sends all the text in the window to the database engine. The engine compiles all the statements before executing them. Because of this, if a statement relies on the completion of a previous statement, it won't compile.

The better way to populate your database from a script is the SqlFile tool which is part of SqlTool.jar. This tool executes statements one by one.

The separate Guide is here:

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