Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
    Bookmark Topic Watch Topic
  • New Topic

HSQLDB - user lacks privilege or object not found

 
Timothy Sam
Ranch Hand
Posts: 751
  • Mark post as helpful
  • send pies
  • Report post to moderator
Hi Ranchers!

I'm currently facing a problem when running a JUnit test within an ANT script. My Unit test calls for an HSQLDB query. Outside of this JUnit test, all my queries like creating a table, insert data to the table and selecting data from the table runs fine. However, when time comes to call my test case class, it throws out a nested exception is java.sql.SQLException: user lacks privilege or object not found: USERS_TBL

I'm running an in-memory database
db.driver=org.hsqldb.jdbcDriver
db.url=jdbc:hsqldb:mem:.
db.user=sa
db.pw=

Below is the stack trace:

[junit] StatementCallback; bad SQL grammar [SELECT user_seq_id, username, pa
ssword FROM users_tbl]; nested exception is java.sql.SQLException: user lacks pr
ivilege or object not found: USERS_TBL
[junit] org.springframework.jdbc.BadSqlGrammarException: StatementCallback;
bad SQL grammar [SELECT user_seq_id, username, password FROM users_tbl]; nested
exception is java.sql.SQLException: user lacks privilege or object not found: US
ERS_TBL
[junit] at org.springframework.jdbc.support.SQLStateSQLExceptionTranslat
or.doTranslate(SQLStateSQLExceptionTranslator.java:98)
[junit] at org.springframework.jdbc.support.AbstractFallbackSQLException
Translator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
[junit] at org.springframework.jdbc.support.AbstractFallbackSQLException
Translator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
[junit] at org.springframework.jdbc.support.AbstractFallbackSQLException
Translator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
[junit] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTempla
te.java:406)
[junit] at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate
.java:455)
[junit] at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate
.java:463)
[junit] at com.mycompany.dao.spring.UserDAOSpringImpl.findUsers(UserDAO
SpringImpl.java:77)



Below is the Spring JDBC code



Thank you very much and I look forward to your kind answers.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34401
346
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Report post to moderator
Timothy,
What user do you run as when you are outside the test. is it the same "sa" user?
 
Timothy Sam
Ranch Hand
Posts: 751
  • Mark post as helpful
  • send pies
  • Report post to moderator
Hi Jeanne

Thanks for the reply! Yes, I also run as user "sa"
 
Timothy Sam
Ranch Hand
Posts: 751
  • Mark post as helpful
  • send pies
  • Report post to moderator
Uhmmm... Help? I tried to GRANT ALL PRIVILEGES ON USERS_TBL TO PUBLIC before running the JUnit tests but still not luck...
 
Zongjian Feng
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Report post to moderator
exception:

Console output:

cause: physical table do not have the column called SMS_SEGMENTSUM
so: alter table tb_sms_rtn add column SMS_SEGMENTSUM varchar(10);
and my test case works.
 
Karthik Shiraly
Bartender
Posts: 1208
25
Android C++ Java Linux PHP Python
  • Mark post as helpful
  • send pies
  • Report post to moderator
I know this is an old thread, but I'm putting up my findings to help others who faced this issue, and were sent here by google.

I faced this same error, with the following steps:

- Tables were created by an SQL script which had CREATE TABLE statements and a COMMIT.

- Ant was used to execute this script, with hsqldb in standalone mode using "jdbc:hsqldb:file:..." URL under "SA" user credential. Ant reported success and terminated.

- A junit test attempted to insert a record, but failed with error: user lacks privilege or object not found : MYTABLE

- When I opened the .script file, I observed that it didn't have any of my CREATE TABLE statements in it.

- Switched hsqldb version, but faced same problem with both hsqldb v1.8.1.2 (stable) and v2.0.0-rc8 (release candidate).

After going through the guides in depth, I inferred that the SHUTDOWN command is very important to hsqldb. It does a clean shutdown and persists all cached information only if SHUTDOWN is called. It does not actually save the CREATE TABLE statements in the .script file even if a COMMIT is done, till a SHUTDOWN is done.

So I added a "SHUTDOWN;" SQL command at the end of my table creation SQL script, and sure enough everything worked after that.

I feel the documentation could have mentioned the importance of shutdown explicitly for new users. It is kind of mentioned, but not very strongly, and the implications of not shutting down can't be easily inferred by users who're used to other database products, where shutdown is mostly implicit.

I haven't experimented in memory mode or server mode, so I'm not sure whether these observations are true there too.

- Karthik Shiraly
 
Jan Cumps
Bartender
Posts: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Report post to moderator
Thank you for posting this, Karthik.
Welcome to JavaRanch!
 
Som Bal
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Report post to moderator
Something to be aware of -
if you are using Ant to launch hbm2ddl for schema export, in <hbm2ddl> task make sure the export is turned to true.
Otherwise, the error could be similar to the one discussed here.
I spend few hours to figure this out.

 
Rajani Gummadi
Ranch Hand
Posts: 48
  • Mark post as helpful
  • send pies
  • Report post to moderator
First of all, I apologize to dig this old thread out, but I thought this would be the best continuation as my problem is exactly the same as the subject line says.

Secondly, I never used HSQLDB before and I installed it, set up my settings

Type : HSQL Database Engine Standalone
Driver : org.hsqldb.jdbcDriver
URL : jdbc:hsqldb:file:MyDB
User : SA
Password : <<Null>> //No password

I created a table called Employee under schema PUBLIC and from the script I see the following statements


From HSQLDB, when I did select, it worked and retrieved me a record (1,ABC,ITG,5000)

So I thought the table creation is fine and then moved onto write a sample JDBC app to retrieve the record


As you see, it is a very simple program to retrieve the records. But upon running, it is failing at ResultSet rs = s.executeQuery("SELECT * FROM PUBLIC.EMPLOYEE"); with the exception



Is my query in extracting records wrong ? or is that because of the statement in .script file

CREATE MEMORY TABLE PUBLIC.EMPLOYEE(ID INTEGER NOT NULL PRIMARY KEY,EMP_NAME VARCHAR(20),EMP_DEPT VARCHAR(20),EMP_SALARY DOUBLE)

which may necessarily mean that the table is created in memory, but not accessible from another process like external java application but on same jvm.
 
Jan Cumps
Bartender
Posts: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Report post to moderator
Rajani Gummadi wrote:First of all, I apologize to dig this old thread out, but I thought this would be the best continuation as my problem is exactly the same as the subject line says.

...
We ask you to create a new one. You have a different issue (otherwise, the resolution posted in this thread would have resolved your problem)
 
    Bookmark Topic Watch Topic
  • New Topic