Two Laptop Bag*
The moose likes JDBC and the fly likes HSQLDB - user lacks privilege or object not found Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Reply locked New topic
Author

HSQLDB - user lacks privilege or object not found

Timothy Sam
Ranch Hand

Joined: Sep 18, 2005
Posts: 746
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.


SCJP 1.5
http://devpinoy.org/blogs/lamia/ - http://everypesocounts.com/
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30537
    
150

Timothy,
What user do you run as when you are outside the test. is it the same "sa" user?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Timothy Sam
Ranch Hand

Joined: Sep 18, 2005
Posts: 746
Hi Jeanne

Thanks for the reply! Yes, I also run as user "sa"
Timothy Sam
Ranch Hand

Joined: Sep 18, 2005
Posts: 746
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

Joined: Jan 15, 2008
Posts: 3
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
Ranch Hand

Joined: Apr 04, 2009
Posts: 507
    
    6
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

Joined: Dec 20, 2006
Posts: 2500
    
    8

Thank you for posting this, Karthik.
Welcome to JavaRanch!


OCUP UML fundamental and ITIL foundation
youtube channel
Som Bal
Greenhorn

Joined: Jul 29, 2010
Posts: 1
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

Joined: Dec 17, 2010
Posts: 48
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

Joined: Dec 20, 2006
Posts: 2500
    
    8

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)
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: HSQLDB - user lacks privilege or object not found