HSQL database user does not have privilege or object not found error

Question

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

I created the database using the following data.

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

I created a table called ALBUM in the schema "MYDB"

In the 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 do jdbcTemplate.query("SELECT * FROM MYDB.ALBUM", new AlbumRowMapper());

And this gives me an 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 the same query through the hsqldb SQL editor, it runs fine. Could you help me with this.

Answer 1

user lacks privilege or object not found may have several reasons, the most obvious of which is access to a table that does not exist. A less obvious reason is that when you run the code, connecting to the database URL can actually create the database. The scenario you are experiencing may be that you configured the database using the HSQL Database Manager, added tables and rows, but this is not the specific instance that your Java code uses. You can verify that you do not have multiple copies of these files: mydb.log , mydb.lck , mydb.properties , etc. In your work area. In case your Java code created these files, the location depends on how you run your program. For example, in a Maven project inside Netbeans, files are stored along with pom.xml.

Answer 2

If you tried all the other answers on this question, then most likely you are faced with a case sensitivity case.

By default, HSQLDB is case sensitive. If you do not specify double quotes around the name of the schema or column or table, then by default it will convert it to uppercase. If your object was created in uppercase, you're in luck. If it is lowercase, you will have to surround your object name with double quotes.

For instance:

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

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

 select "product_id" from "t1" 

Answer 3

As stated in the previous answer, there are many possible reasons. One of them is that the table was not created due to syntax incompatibility. If you use specific database provider syntax or a specific feature, HSQLDB does not recognize it . Then, while the create code is executing, you can see that the table has not been created for this syntax reason. For example, if an entity is marked @Column(columnDefinition = "TEXT") creation of the table will fail.

There is a workaround that tells HSQLDB that it should be in compatible mode for pgsl, you should add the url of your connection to this

 "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 that there is an option, depending on your configuration, it could be hibernate.connection.url= or spring.datasource.url= for those who do not use hibernate schema creation, but the SQL script, you should use this type of syntax in your script

 SET DATABASE SQL SYNTAX ORA TRUE; 

This will also fix problems due to the specific syntax in the SQL query, such as array_agg for posgresql

Note : the problem occurs very early when the code analyzes the model to create the circuit and then hides in many lines of logs, and then unitTested code crashes with a confusing and incomprehensible exception: "the user lacks privileges or the object was not found error" which does not indicate real problem. Therefore, be sure to read the entire trace from the very beginning and fix all possible problems.

Answer 4

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

Answer 5

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

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

Answer 6

When the HSWLDB server starts. for example, your Java configuration file has:

hsql.jdbc.url = jdbc:hsqldb:hsql://localhost:9005/YOURDB;sql.enforce_strict_size=true;hsqldb.tx=mvcc

check to make sure you set server.dbname.# . for example my server.properties file:

  server.database.0=eventsdb server.dbname.0=eventsdb server.port=9005 

Answer 7

I inserted data into hsql db using the following script

 INSERT INTO Greeting (text) VALUES ("Hello World"); 

I had a problem with the fact that the Hello World object was not found, and the user of the HSQL database does not have enough privileges or the object was not found.

which I changed to the script below

 INSERT INTO Greeting (text) VALUES ('Hello World'); 

And now everything is working fine.

Answer 8

I recently encountered the same problem. We run the Grails application, and someone pasted an SQL script into the BootStrap file (this is the entry point for Grails). This script was supposed to run only in a production environment, but due to poor logic, it also ran in the test. So, the error I received was:

 User lacks privilege or object not found: 

without any further explanation ...

I just needed to make sure that the script was not running in the test environment, and this fixed the problem for me, although it took me 3 hours to figure it out. I know this is a very, very specific problem, but still, if I could save someone a couple of hours digging the code, that would be great.

Answer 9

I had the same error. In my case, I forgot to put apas in strings.

I did String test = "inputTest";

The correct one is String test = "'inputTest'";

An error occurred when I tried to put something into the database

 connection.createStatement.execute("INSERT INTO tableTest values(" + test +")"; 

In my case, just put the quotation marks ' to fix the error.

Answer 10

In my case, an error occurred because I did NOT put TABLE_NAME in double quotation marks "TABLE_NAME" and had the oracle schema prefix.

Does not work:

 SELECT * FROM FOOSCHEMA.BAR_TABLE 

Working:

 SELECT * FROM "BAR_TABLE" 

Answer 11

there was this problem with the concatenation of variables in the insert statement. it worked

 // var1, var3, var4 are String variables // var2 and var5 are Integer variables result = statement.executeUpdate("INSERT INTO newTable VALUES ('"+var1+"',"+var2+",'"+var3+"','"+var4+"',"+var5 +")");