HSQL database user lacks privilege or object not found error

Question

I am trying to use hsqldb-2.3.4 to connect from Spring applicastion.

I created data base using the following details

Type : HSQL Database Engine Standalone
Driver: org.hsqldb.jdbcDriver
URL: jdbc:hsqldb:file:mydb
UserName: SA
Password: SA

I created a table named ALBUM under "MYDB" schema

In spring configuration file:

<bean id="jdbcTemplate"
    class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
    <constructor-arg ref="dbcpDataSource" />
</bean>

<bean id="dbcpDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="org.hsqldb.jdbcDriver" />
    <property name="url" value="jdbc:hsqldb:file:mydb" />
    <property name="username" value="SA" />
    <property name="password" value="SA" />
</bean>

And in my spring controller I am doing jdbcTemplate.query("SELECT * FROM MYDB.ALBUM", new AlbumRowMapper());

And It gives me exception:

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT * FROM MYDB.ALBUM]; nested exception is java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: ALBUM
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:982)
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861)
javax.servlet.http.HttpServlet.service(HttpServlet.java:622)
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

If I execute same query through SQL editor of hsqldb it executes fine. Can you please help me with this.

Answer 1

user lacks privilege or object not found can have multiple causes, the most obvious being you're accessing a table that does not exist. A less evident reason is that, when you run your code, the connection to a file database URL actually can create a DB. The scenario you're experiencing might be you've set up a DB using HSQL Database Manager, added tables and rows, but it's not this specific instance your Java code is using. You may want to check that you don't have multiple copies of these files: mydb.log, mydb.lck, mydb.properties, etc in your workspace. In the case your Java code did create those files, the location depends on how you run your program. In a Maven project run inside Netbeans for example, the files are stored alongside the pom.xml.

Answer 2

As said by a previous response there is many possible causes. One of them is that the table isn't created because of syntax incompatibility. If specific DB vendor syntax or specific capability is used, HSQLDB will not recognize it. Then while the creation code is executed you could see that the table is not created for this syntax reason. For exemple if the entity is annoted with @Column(columnDefinition = "TEXT") the creation of the table will fail.

There is a work around which tell to HSQLDB to be in a compatible mode for pgsl you should append your connection url with that

"spring.datasource.url=jdbc:hsqldb:mem:testdb;sql.syntax_pgs=true"

and for mysql with

"spring.datasource.url=jdbc:hsqldb:mem:testdb;sql.syntax_mys=true"

oracle

"spring.datasource.url=jdbc:hsqldb:mem:testdb;sql.syntax_ora=true" 

note there is variant depending on your configuration it could be hibernate.connection.url= or spring.datasource.url= for those who don't use the hibernate schema creation but a SQL script you should use this kind of syntax in your script

SET DATABASE SQL SYNTAX ORA TRUE;

It will also fix issues due to vendor specific syntax in SQL request such as array_agg for posgresql

Nota bene : The the problem occurs very early when the code parse the model to create the schema and then is hidden in many lines of logs, then the unitTested code crash with a confusing and obscure exception "user lacks privilege or object not found error" which does not point the real problem at all. So make sure to read all the trace from the beginning and fix all possible issues

Answer 3

If you've tried all the other answers on this question, then it is most likely that you are facing a case-sensitivity issue.

HSQLDB is case-sensitive by default. If you don't specify the double quotes around the name of a schema or column or table, then it will by default convert that to uppercase. If your object has been created in uppercase, then you are in luck. If it is in lowercase, then you will have to surround your object name with double quotes.

For example:

CREATE MEMORY TABLE "t1"("product_id" INTEGER NOT NULL)

To select from this table you will have to use the following query

select "product_id" from "t1"

Answer 4

I had the error user lacks privilege or object not found while trying to create a table in an empty in-memory database. I used spring.datasource.schema and the problem was that I missed a semicolon in my SQL file after the "CREATE TABLE"-Statement (which was followed by "CREATE INDEX").

Answer 5

I had similar issue with the error 'org.hsqldb.HsqlException: user lacks privilege or object not found: DAYS_BETWEEN' turned out DAYS_BETWEEN is not recognized by hsqldb as a function. use DATEDIFF instead.

DATEDIFF ( <datetime value expr 1>, <datetime value expr 2> )