CODEWITHSUNDEEP
sqldatabaseselecthsqldb
Unknownx9
Unknownx9

Reputation: 41

HSQLDB user lacks privilege or object not found error when making select statements with where

I use SQuirrel SQL Client Version 3.5.3 and HSQLDB for my database. I have been able to specify the corresponding driver (In-memory) to it and create an Alias.

I have created a table

CREATE TABLE ENTRY(
NAME VARCHAR(100) NOT NULL,
DESC VARCHAR(500) NOT NULL,
PRIMARY KEY (NAME))

and added a few lines of data into it. While statements like these work:

select * from ENTRY
select NAME from ENTRY
select DESC from ENTRY

I always get Error: user lacks privilege or object not found" when adding a where clause to my statement, e.g. select DESC from ENTRY where NAME=CAR

Any help is greatly appreciated as I can slowly feel my sanity waning

Upvotes: 3

Views: 18796

Answers (5)

Task
Task

Reputation: 3686

Your problem is:

I always get Error: user lacks privilege or object not found" when adding a where clause to my statement, e.g. select DESC from ENTRY where NAME=CAR

Yes, of course you do. NAME is a field of the ENTRY table. CAR isn't a field of anything. Perhaps your WHERE clause should look like this instead:

WHERE NAME='CAR'

Thereby comparing a field value with a literal string value instead of trying to compare it with a nonexistent other field value.

Upvotes: 0

Iman Mirzadeh
Iman Mirzadeh

Reputation: 13560

I had the same problem, but my table name and other things were ok except my query for VARCHAR were inside double quotes("") but it should be in single quotes('')

example:
assume you have table like this which flightId is primary keyenter image description here

now this query is wrong:

SELECT * FROM flights WHERE flightId="0f3ae9b3-6bb1-4c95-9394-6179555f5879"

while this one is ok:

SELECT * FROM flights WHERE flightId='0f3ae9b3-6bb1-4c95-9394-6179555f5879'

Upvotes: 2

shrinathM
shrinathM

Reputation: 372

HSQLDB has default schema called PUBLIC. All SQL queries will be pointing to PUBLIC; If you have created your own schema like eg:OWNSCHEMA then edit the xxx.script and change the following line

SET DATABASE DEFAULT INITIAL SCHEMA PUBLIC

to 

SET DATABASE DEFAULT INITIAL SCHEMA OWNSCHEMA

Upvotes: 1

zovits
zovits

Reputation: 915

When I received the same exception the root cause was that I had a table in the SELECT clause that was not present in the FROM clause.

Upvotes: 0

Unknownx9
Unknownx9

Reputation: 41

I was finally able to fix this myself. I had used a wrong table name for my select statements and after changing it to the real one it worked. The only thing that confuses me is that I also used the wrong table name for my insert statements but they were executed successfully and all data is showing up in them.

Upvotes: 1

Related Questions