| 
HSQLDB
 » 
HSQLDB - User

user lacks privilege or object not found

View: New views
5 Messages — Rating Filter:   Alert me  

user lacks privilege or object not found

by mslinn :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message

I am not the first person to get this error, and possibly not the most frustrated, but I must be nearly so. I want to create a database that can be later read by an application. I am using HSQLDB v1.9beta6.

showme.bat -- should display table rows

@set HSQLDB=C:/Program Files/hsqldb
@set DIR=%~dp0
@set PARAMS="%HSQLDB%/bin/runSqlTool" --autoCommit --inlineRc "URL=jdbc:hsqldb:file:%DIR%data,USER=SA"
%PARAMS% --sql="select * from student;"

data.script -- cobbled together, probably has errors

SET DATABASE UNIQUE NAME null
SET DATABASE GC 0
SET DATABASE DEFAULT RESULT MEMORY ROWS 0
SET DATABASE EVENT LOG LEVEL 0
SET DATABASE SQL SIZE TRUE
SET DATABASE SQL NAMES FALSE
SET DATABASE TRANSACTION CONTROL LOCKS
SET FILES WRITE DELAY 20
SET FILES BACKUP INCREMENT TRUE
SET FILES CACHE SIZE 10000
SET FILES CACHE ROWS 50000
SET FILES SCALE 1
SET FILES DEFRAG 20
SET FILES NIO TRUE
SET FILES LOG SIZE 200
SET DATABASE TEXT TABLE DEFAULTS ''
CREATE USER SA PASSWORD ''
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
SET SCHEMA PUBLIC
CREATE MEMORY TABLE PUBLIC.CLASS(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 4) NOT NULL PRIMARY KEY,NAME VARCHAR(255) DEFAULT NULL)
ALTER TABLE PUBLIC.CLASS ALTER COLUMN ID RESTART WITH 4
CREATE MEMORY TABLE PUBLIC.STUDENT(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 5) NOT NULL PRIMARY KEY,FIRSTNAME VARCHAR(255) DEFAULT NULL,LASTNAME VARCHAR(255) DEFAULT NULL)
ALTER TABLE PUBLIC.STUDENT ALTER COLUMN ID RESTART WITH 5
CREATE MEMORY TABLE PUBLIC.STUDENT_CLASS(STUDENTID INTEGER NOT NULL,CLASSID INTEGER NOT NULL,NEWFK INTEGER,NEWFK2 INTEGER,PRIMARY KEY(STUDENTID,CLASSID),FOREIGN KEY(NEWFK) REFERENCES PUBLIC.STUDENT(ID),FOREIGN KEY(NEWFK2) REFERENCES PUBLIC.CLASS(ID))
ALTER SEQUENCE SYSTEM_LOBS.LOB_ID RESTART WITH 1
SET DATABASE DEFAULT INITIAL SCHEMA INFORMATION_SCHEMA
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 USAGE ON DOMAIN INFORMATION_SCHEMA.SQL_IDENTIFIER TO PUBLIC
GRANT DBA TO SA
SET SCHEMA PUBLIC
INSERT INTO CLASS VALUES(1,'Geography')
INSERT INTO CLASS VALUES(2,'Math')
INSERT INTO CLASS VALUES(3,'Chemistry')
INSERT INTO STUDENT VALUES(1,'Tim','Mack')
INSERT INTO STUDENT VALUES(2,'Mary','Slater')
INSERT INTO STUDENT VALUES(3,'Jim','Brown')
INSERT INTO STUDENT VALUES(4,'Adam','Keating')
INSERT INTO STUDENT_CLASS VALUES(1,1,NULL,NULL)
INSERT INTO STUDENT_CLASS VALUES(1,2,NULL,NULL)

Results

C:\Documents and Settings\Mike Slinn.BEAR\Desktop\data>"C:/Program Files/hsqldb/bin/runSqlTool" --autoCommit --inlineRc "URL=jdbc:hsqldb:file:C:\Documents and Settings\user\Desktop\data\data,USER=SA" --sql="select * from student;"
Enter password for SA:
SEVERE  SQL Error at 'C:\DOCUME~1\MIKESL~1.BEA\LOCALS~1\Temp\sqltool-23479.sql' line 3:
"select * from student"
user lacks privilege or object not found: STUDENT
2009-10-19T17:23:26.315-0700  SEVERE  SQL Error at 'C:\DOCUME~1\user\LOCALS~1\Temp\sqltool-23479.sql' line 3:
"select * from student"
user lacks privilege or object not found: STUDENT
What am I missing? Can I remove any of the lines in data.script to make it simpler?

Re: user lacks privilege or object not found

by Fred Toussi-2 :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message

Some parts of this message have been removed. Learn more about Nabble's security policy.
 The script shows a recently reported bug that heppens when opening old databases and results in the first line of the script in
 
SET DATABASE UNIQUE NAME null
Edit the script and replace with

SET DATABASE UNIQUE NAME HSQLDB2467A82B33
 
If there is any further error, it might have to do witht the interpretation of the URL paths, which are not clear to me.
 
To reduce the possibility of errors in this area, open an **existing** database with ";ifexists=true" added to the URL
URL=jdbc:hsqldb:file:%DIR%data;ifxeists=true
 
This will cause an error if your URL paths are mismatched.
 
Fred
 
On Mon, 19 Oct 2009 17:43 -0700, "mslinn" <mslinn@...> wrote:
I am not the first person to get this error, and possibly not the most frustrated, but I must be nearly so. I want to create a database that can be later read by an application. I am using HSQLDB v1.9beta6.

showme.bat -- should display table rows

@set HSQLDB=C:/Program Files/hsqldb
@set DIR=%~dp0
@set PARAMS="%HSQLDB%/bin/runSqlTool" --autoCommit --inlineRc "URL=jdbc:hsqldb:file:%DIR%data,USER=SA"
%PARAMS% --sql="select * from student;"

data.script -- cobbled together, probably has errors

SET DATABASE UNIQUE NAME null
SET DATABASE GC 0
SET DATABASE DEFAULT RESULT MEMORY ROWS 0
SET DATABASE EVENT LOG LEVEL 0
SET DATABASE SQL SIZE TRUE
SET DATABASE SQL NAMES FALSE
SET DATABASE TRANSACTION CONTROL LOCKS
SET FILES WRITE DELAY 20
SET FILES BACKUP INCREMENT TRUE
SET FILES CACHE SIZE 10000
SET FILES CACHE ROWS 50000
SET FILES SCALE 1
SET FILES DEFRAG 20
SET FILES NIO TRUE
SET FILES LOG SIZE 200
SET DATABASE TEXT TABLE DEFAULTS ''
CREATE USER SA PASSWORD ''
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
SET SCHEMA PUBLIC
CREATE MEMORY TABLE PUBLIC.CLASS(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 4) NOT NULL PRIMARY KEY,NAME VARCHAR(255) DEFAULT NULL)
ALTER TABLE PUBLIC.CLASS ALTER COLUMN ID RESTART WITH 4
CREATE MEMORY TABLE PUBLIC.STUDENT(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 5) NOT NULL PRIMARY KEY,FIRSTNAME VARCHAR(255) DEFAULT NULL,LASTNAME VARCHAR(255) DEFAULT NULL)
ALTER TABLE PUBLIC.STUDENT ALTER COLUMN ID RESTART WITH 5
CREATE MEMORY TABLE PUBLIC.STUDENT_CLASS(STUDENTID INTEGER NOT NULL,CLASSID INTEGER NOT NULL,NEWFK INTEGER,NEWFK2 INTEGER,PRIMARY KEY(STUDENTID,CLASSID),FOREIGN KEY(NEWFK) REFERENCES PUBLIC.STUDENT(ID),FOREIGN KEY(NEWFK2) REFERENCES PUBLIC.CLASS(ID))
ALTER SEQUENCE SYSTEM_LOBS.LOB_ID RESTART WITH 1
SET DATABASE DEFAULT INITIAL SCHEMA INFORMATION_SCHEMA
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 USAGE ON DOMAIN INFORMATION_SCHEMA.SQL_IDENTIFIER TO PUBLIC
GRANT DBA TO SA
SET SCHEMA PUBLIC
INSERT INTO CLASS VALUES(1,'Geography')
INSERT INTO CLASS VALUES(2,'Math')
INSERT INTO CLASS VALUES(3,'Chemistry')
INSERT INTO STUDENT VALUES(1,'Tim','Mack')
INSERT INTO STUDENT VALUES(2,'Mary','Slater')
INSERT INTO STUDENT VALUES(3,'Jim','Brown')
INSERT INTO STUDENT VALUES(4,'Adam','Keating')
INSERT INTO STUDENT_CLASS VALUES(1,1,NULL,NULL)
INSERT INTO STUDENT_CLASS VALUES(1,2,NULL,NULL)

Results

C:\Documents and Settings\Mike Slinn.BEAR\Desktop\data>"C:/Program Files/hsqldb/bin/runSqlTool" --autoCommit --inlineRc "URL=jdbc:hsqldb:file:C:\Documents and Settings\user\Desktop\data\data,USER=SA" --sql="select * from student;"
Enter password for SA:
SEVERE  SQL Error at 'C:\DOCUME~1\MIKESL~1.BEA\LOCALS~1\Temp\sqltool-23479.sql' line 3:
"select * from student"
user lacks privilege or object not found: STUDENT
2009-10-19T17:23:26.315-0700  SEVERE  SQL Error at 'C:\DOCUME~1\user\LOCALS~1\Temp\sqltool-23479.sql' line 3:
"select * from student"
user lacks privilege or object not found: STUDENT
What am I missing? Can I remove any of the lines in data.script to make it simpler?
View this message in context: user lacks privilege or object not found
Sent from the HSQLDB - User mailing list archive at Nabble.com.

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

Re: user lacks privilege or object not found

by Fred Toussi-2 :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message

Some parts of this message have been removed. Learn more about Nabble's security policy.
 I also noticed another issue in the script, to do with opening an old database.

SET DATABASE DEFAULT INITIAL SCHEMA INFORMATION_SCHEMA  This should change to :  SET DATABASE DEFAULT INITIAL SCHEMA PUBLIC  Fred
 
On Tue, 20 Oct 2009 12:40 +0100, "Fred Toussi" <fredt@...> wrote:
 The script shows a recently reported bug that heppens when opening old databases and results in the first line of the script in
 
SET DATABASE UNIQUE NAME null
Edit the script and replace with

SET DATABASE UNIQUE NAME HSQLDB2467A82B33
 
If there is any further error, it might have to do witht the interpretation of the URL paths, which are not clear to me.
 
To reduce the possibility of errors in this area, open an **existing** database with ";ifexists=true" added to the URL
URL=jdbc:hsqldb:file:%DIR%data;ifxeists=true
 
This will cause an error if your URL paths are mismatched.
 
Fred
 
On Mon, 19 Oct 2009 17:43 -0700, "mslinn" <mslinn@...> wrote:
I am not the first person to get this error, and possibly not the most frustrated, but I must be nearly so. I want to create a database that can be later read by an application. I am using HSQLDB v1.9beta6.

showme.bat -- should display table rows

@set HSQLDB=C:/Program Files/hsqldb
@set DIR=%~dp0
@set PARAMS="%HSQLDB%/bin/runSqlTool" --autoCommit --inlineRc "URL=jdbc:hsqldb:file:%DIR%data,USER=SA"
%PARAMS% --sql="select * from student;"

data.script -- cobbled together, probably has errors

SET DATABASE UNIQUE NAME null
SET DATABASE GC 0
SET DATABASE DEFAULT RESULT MEMORY ROWS 0
SET DATABASE EVENT LOG LEVEL 0
SET DATABASE SQL SIZE TRUE
SET DATABASE SQL NAMES FALSE
SET DATABASE TRANSACTION CONTROL LOCKS
SET FILES WRITE DELAY 20
SET FILES BACKUP INCREMENT TRUE
SET FILES CACHE SIZE 10000
SET FILES CACHE ROWS 50000
SET FILES SCALE 1
SET FILES DEFRAG 20
SET FILES NIO TRUE
SET FILES LOG SIZE 200
SET DATABASE TEXT TABLE DEFAULTS ''
CREATE USER SA PASSWORD ''
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
SET SCHEMA PUBLIC
CREATE MEMORY TABLE PUBLIC.CLASS(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 4) NOT NULL PRIMARY KEY,NAME VARCHAR(255) DEFAULT NULL)
ALTER TABLE PUBLIC.CLASS ALTER COLUMN ID RESTART WITH 4
CREATE MEMORY TABLE PUBLIC.STUDENT(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 5) NOT NULL PRIMARY KEY,FIRSTNAME VARCHAR(255) DEFAULT NULL,LASTNAME VARCHAR(255) DEFAULT NULL)
ALTER TABLE PUBLIC.STUDENT ALTER COLUMN ID RESTART WITH 5
CREATE MEMORY TABLE PUBLIC.STUDENT_CLASS(STUDENTID INTEGER NOT NULL,CLASSID INTEGER NOT NULL,NEWFK INTEGER,NEWFK2 INTEGER,PRIMARY KEY(STUDENTID,CLASSID),FOREIGN KEY(NEWFK) REFERENCES PUBLIC.STUDENT(ID),FOREIGN KEY(NEWFK2) REFERENCES PUBLIC.CLASS(ID))
ALTER SEQUENCE SYSTEM_LOBS.LOB_ID RESTART WITH 1
SET DATABASE DEFAULT INITIAL SCHEMA INFORMATION_SCHEMA
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 USAGE ON DOMAIN INFORMATION_SCHEMA.SQL_IDENTIFIER TO PUBLIC
GRANT DBA TO SA
SET SCHEMA PUBLIC
INSERT INTO CLASS VALUES(1,'Geography')
INSERT INTO CLASS VALUES(2,'Math')
INSERT INTO CLASS VALUES(3,'Chemistry')
INSERT INTO STUDENT VALUES(1,'Tim','Mack')
INSERT INTO STUDENT VALUES(2,'Mary','Slater')
INSERT INTO STUDENT VALUES(3,'Jim','Brown')
INSERT INTO STUDENT VALUES(4,'Adam','Keating')
INSERT INTO STUDENT_CLASS VALUES(1,1,NULL,NULL)
INSERT INTO STUDENT_CLASS VALUES(1,2,NULL,NULL)

Results

C:\Documents and Settings\Mike Slinn.BEAR\Desktop\data>"C:/Program Files/hsqldb/bin/runSqlTool" --autoCommit --inlineRc "URL=jdbc:hsqldb:file:C:\Documents and Settings\user\Desktop\data\data,USER=SA" --sql="select * from student;"
Enter password for SA:
SEVERE  SQL Error at 'C:\DOCUME~1\MIKESL~1.BEA\LOCALS~1\Temp\sqltool-23479.sql' line 3:
"select * from student"
user lacks privilege or object not found: STUDENT
2009-10-19T17:23:26.315-0700  SEVERE  SQL Error at 'C:\DOCUME~1\user\LOCALS~1\Temp\sqltool-23479.sql' line 3:
"select * from student"
user lacks privilege or object not found: STUDENT
What am I missing? Can I remove any of the lines in data.script to make it simpler?
View this message in context: user lacks privilege or object not found
Sent from the HSQLDB - User mailing list archive at Nabble.com.

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

Re: user lacks privilege or object not found

by mslinn :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message

Each time I change null to HSQLDB2467A82B33, it gets reset back to null
when the database starts again.
I reverted to v1.8.1 and problems went away.

BTW, ;fexists=true needs proper documentation.  Not sure where to
suggest that.

Mike

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user

Re: user lacks privilege or object not found

by Blaine Simpson :: Rate this Message:

Reply to Author | View Threaded | Show Only this Message

http://hsqldb.org/doc/2.0/guide/dbproperties-chapt.html#conn_props-sect

Michael Slinn wrote:
> ...
> BTW, ;fexists=true needs proper documentation.  Not sure where to
> suggest that.
>
> Mike

------------------------------------------------------------------------------
Come build with us! The BlackBerry(R) Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9 - 12, 2009. Register now!
http://p.sf.net/sfu/devconference
_______________________________________________
Hsqldb-user mailing list
Hsqldb-user@...
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Free embeddable forum powered by Nabble Forum Help