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.