Upgrading an old database

I'm working on upgrading our desktop application from HSQLDB 1.8 to 2.5.
That's a BIG jump.

One issue I'm having is opening an older database fails with:
   java.sql.SQLException: error in script file line: 2 org.hsqldb.HsqlException: system object cannot be modified

The script file from the old DB starts like this:

CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE MEMORY TABLE PLANT(GUID_PLANT VARCHAR(64) NOT NULL,I... (a bunch of fields)
...
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 20 MILLIS
SET SCHEMA PUBLIC
INSERT INTO PLANT VALUES('Guid_Plant01',...

After digging around on stackoverflow, I moved the Create User, Grand, set write_delay, and set SCHEMA public lines to the top, just under the create schema line.

This allowed the database to load, but now the application can't seem to USE the database.  The application does connect as user SA.
The first query it tries gets this:
   java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: STUFF

The start of the script file now looks like:
SET DATABASE UNIQUE NAME HSQLDB6C6F12D416
SET DATABASE GC 0
SET DATABASE DEFAULT RESULT MEMORY ROWS 0
SET DATABASE EVENT LOG LEVEL 0
SET DATABASE TRANSACTION CONTROL LOCKS
SET DATABASE DEFAULT ISOLATION LEVEL READ COMMITTED
SET DATABASE TRANSACTION ROLLBACK ON CONFLICT TRUE
SET DATABASE TEXT TABLE DEFAULTS ''
SET DATABASE SQL NAMES FALSE
SET DATABASE SQL REFERENCES FALSE
SET DATABASE SQL SIZE TRUE
SET DATABASE SQL TYPES FALSE
SET DATABASE SQL TDC DELETE TRUE
SET DATABASE SQL TDC UPDATE TRUE
SET DATABASE SQL CONCAT NULLS TRUE
SET DATABASE SQL UNIQUE NULLS TRUE
SET DATABASE SQL CONVERT TRUNCATE TRUE
SET DATABASE SQL AVG SCALE 0
SET DATABASE SQL DOUBLE NAN TRUE
SET FILES WRITE DELAY 20 MILLIS
SET FILES BACKUP INCREMENT FALSE
SET FILES CACHE SIZE 100
SET FILES CACHE ROWS 100
SET FILES SCALE 32
SET FILES LOB SCALE 32
SET FILES DEFRAG 0
SET FILES NIO FALSE
SET FILES NIO SIZE 256
SET FILES LOG TRUE
SET FILES LOG SIZE 0
CREATE USER SA PASSWORD DIGEST 'd41d8cd98f00b204e9800998ecf8427e'
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
SET SCHEMA PUBLIC
CREATE MEMORY TABLE PUBLIC.PLANT(GUID_PLANT VARCHAR(64) NOT NULL,...
...
ALTER SEQUENCE SYSTEM_LOBS.LOB_ID RESTART WITH 1
SET DATABASE DEFAULT INITIAL SCHEMA INFORMATION_SCHEMA
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.SQL_IDENTIFIER TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.YES_OR_NO TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.TIME_STAMP TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CARDINAL_NUMBER TO PUBLIC
GRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CHARACTER_DATA TO PUBLIC
GRANT DBA TO SA
SET SCHEMA SYSTEM_LOBS
INSERT INTO BLOCKS VALUES(0,2147483647,0)
SET SCHEMA PUBLIC
INSERT INTO PLANT VALUES('Guid_Plant01',...


Any ideas on why it won't let user SA access the tablers?

Also, is there ANY way to get hsqldb to upgrade the DB without manually editing the .script file? 


--
Bobcats and Cougars, oh my!  http://john.casadelgato.com/Pets

_______________________________________________
Hsqldb-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user