Re: Upgrading an old database

Hello JohnWithout seeing the entire .script file, it's difficult to say for certain why access to the STUFF object throws exception.  Is "STUFF" a real name or simply a placeholder?  Regardless, you might try further qualifying the name, e.g. PUBLIC.STUFF, to see if it helps.  Also, can you access the object in SQLTool when using SA account? These steps may give clues as to the root cause.I cannot comment on your other questions.Good luck,Andrew Richardson Sent from my Verizon, Samsung Galaxy Tablet
-------- Original message --------From: John Lussmyer <[email protected]> Date: 8/7/19  22:55  (GMT-05:00) To: [email protected] Subject: [Hsqldb-user] 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 modifiedThe script file from the old DB starts like this:CREATE SCHEMA PUBLIC AUTHORIZATION DBACREATE MEMORY TABLE PLANT(GUID_PLANT VARCHAR(64) NOT NULL,I... (a bunch of fields)...CREATE USER SA PASSWORD ""GRANT DBA TO SASET WRITE_DELAY 20 MILLISSET SCHEMA PUBLICINSERT 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: STUFFThe start of the script file now looks like:SET DATABASE UNIQUE NAME HSQLDB6C6F12D416SET DATABASE GC 0SET DATABASE DEFAULT RESULT MEMORY ROWS 0SET DATABASE EVENT LOG LEVEL 0SET DATABASE TRANSACTION CONTROL LOCKSSET DATABASE DEFAULT ISOLATION LEVEL READ COMMITTEDSET DATABASE TRANSACTION ROLLBACK ON CONFLICT TRUESET DATABASE TEXT TABLE DEFAULTS ''SET DATABASE SQL NAMES FALSESET DATABASE SQL REFERENCES FALSESET DATABASE SQL SIZE TRUESET DATABASE SQL TYPES FALSESET DATABASE SQL TDC DELETE TRUESET DATABASE SQL TDC UPDATE TRUESET DATABASE SQL CONCAT NULLS TRUESET DATABASE SQL UNIQUE NULLS TRUESET DATABASE SQL CONVERT TRUNCATE TRUESET DATABASE SQL AVG SCALE 0SET DATABASE SQL DOUBLE NAN TRUESET FILES WRITE DELAY 20 MILLISSET FILES BACKUP INCREMENT FALSESET FILES CACHE SIZE 100SET FILES CACHE ROWS 100SET FILES SCALE 32SET FILES LOB SCALE 32SET FILES DEFRAG 0SET FILES NIO FALSESET FILES NIO SIZE 256SET FILES LOG TRUESET FILES LOG SIZE 0CREATE USER SA PASSWORD DIGEST 'd41d8cd98f00b204e9800998ecf8427e'CREATE SCHEMA PUBLIC AUTHORIZATION DBASET SCHEMA PUBLICCREATE MEMORY TABLE PUBLIC.PLANT(GUID_PLANT VARCHAR(64) NOT NULL,......ALTER SEQUENCE SYSTEM_LOBS.LOB_ID RESTART WITH 1SET DATABASE DEFAULT INITIAL SCHEMA INFORMATION_SCHEMAGRANT USAGE ON DOMAIN INFORMATION_SCHEMA.SQL_IDENTIFIER TO PUBLICGRANT USAGE ON DOMAIN INFORMATION_SCHEMA.YES_OR_NO TO PUBLICGRANT USAGE ON DOMAIN INFORMATION_SCHEMA.TIME_STAMP TO PUBLICGRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CARDINAL_NUMBER TO PUBLICGRANT USAGE ON DOMAIN INFORMATION_SCHEMA.CHARACTER_DATA TO PUBLICGRANT DBA TO SASET SCHEMA SYSTEM_LOBSINSERT INTO BLOCKS VALUES(0,2147483647,0)SET SCHEMA PUBLICINSERT 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