• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

HSQLDB Sequence problem by fetching the next value

 
Atharva Gupta
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,

I am having trouble on getting the sequence mechanism work for HSQLDB. So here are the versions I use:

Hibernate-Version: 3.1
HSQLDB: 2.2.6

I created a sequence in my database using this line of code :
CREATE SEQUENCE PUBLIC.LOGIN_SEQ START WITH 1 INCREMENT BY 1;

mapping file shown below :

<hibernate-mapping>

<class name="demo.pojos.pkg.Login" table="LOGIN">
<cache usage="read-write" />
<id name="userId" column="USERID">
<generator class="sequence">
<param name="sequence">LOGIN_SEQ</param>
</generator>
</id>

<property name="userName" column="USERNAME"/>

<property name="password" column="PASSWORD"/>


</class>

</hibernate-mapping>

So, When I insert into table LOGIN, which is assocaited with LOGIN_SEQ, it gives issue as shown below.

Jul 08, 2013 12:40:20 PM org.hibernate.cache.EhCacheProvider buildCache
WARNING: Could not find configuration [demo.pojos.pkg.Login]; using defaults.
Hibernate: select this_.USERID as USERID0_0_, this_.USERNAME as USERNAME0_0_, this_.PASSWORD as PASSWORD0_0_ from LOGIN this_ where this_.USERNAME=? and this_.PASSWORD=?
Hibernate: select next value for LOGIN_SEQ from dual_LOGIN_SEQ
Jul 08, 2013 12:40:31 PM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: -5501, SQLState: 42501
Jul 08, 2013 12:40:31 PM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: user lacks privilege or object not found: DUAL_LOGIN_SEQ
Jul 08, 2013 12:40:43 PM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet LoginServlet threw exception
org.hibernate.exception.SQLGrammarException: could not get next sequence value
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.id.SequenceGenerator.generate(SequenceGenerator.java:96)
at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:91)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:186)
at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:33)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:175)
at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:27)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:70)
at org.hibernate.impl.SessionImpl.fireSave(SessionImpl.java:530)
at org.hibernate.impl.SessionImpl.save(SessionImpl.java:518)
at org.hibernate.impl.SessionImpl.save(SessionImpl.java:514)
at demo.dao.pkg.LoginDAO.getUserByName(LoginDAO.java:35)
at demo.service.pkg.LoginServiceImpl.getUserByName(LoginServiceImpl.java:15)
at demo.servlet.pkg.LoginServlet.doPost(LoginServlet.java:34)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:643)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:723)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: DUAL_LOGIN_SEQ

I have already made LOGIN Table, So, why hibernate is issuing query from dual_LOGIN_SEQ table.

*******select next value for LOGIN_SEQ from dual_LOGIN_SEQ


Can anybody help me out in solving this issue,

Thanks in advance,
Abhishek Gupta

 
vicky pandey
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Have you given the grant access to your sequence. ?


grant select on SQUENCE_NAME to USER_ROLE;


where USER_ROLE = your application user
SQUENCE_NAME= your sequence name.
 
Atharva Gupta
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi vicky,

Is the user role will be the user through i login into the database or what ?

Can you please tell me that.
 
Atharva Gupta
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I have tried using below query on HSQLDB.

grant select on LOGIN_SEQ to SA;

Here LOGIN_SEQ is my SEQ Name and SA is USER.

its giving error like :

user lacks privilege or object not found: LOGIN_SEQ in statement [grant select on LOGIN_SEQ to SA] / Error Code: -5501 / State: 42501

but when I fire query to check weather seq. is there in DB or not

CREATE SEQUENCE PUBLIC.LOGIN_SEQ START WITH 1 INCREMENT BY 1;

it is showing error like seq. already exist

object name already exists: LOGIN_SEQ in statement [CREATE SEQUENCE PUBLIC.LOGIN_SEQ START WITH 1 INCREMENT BY 1] / Error Code: -5504 / State: 42504

Can you please let me know what to do in this case.

 
Atharva Gupta
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I got the solution for this as described below.

1. Create Sequence :

CREATE SEQUENCE LOGIN_SEQ START WITH 1 INCREMENT BY 1;

2. Create Dual Table in HSQLDB

CREATE TABLE DUAL
(
SEQ INT
);

3. Map the ID column with the sequence LOGIN_SEQ

<id name="userId" column="USERID">
<generator class="sequence">
<param name="sequence">LOGIN_SEQ</param>
</generator>
</id>

4. Create Own class Extending HSQLDialect (e.g. MyHSQLDialect), and override method getSequenceNextValString

public class MyHSQLDialect extends HSQLDialect {

@Override
public String getSequenceNextValString(String sequenceName) {
return "select next value for "+sequenceName+" from dual";
}

}

5. Change the dialect to your own dialect in hibernate.cfg.xml file.
 
Atharva Gupta
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Atharva Gupta wrote:Hi,

I got the solution for this as described below.

1. Create Sequence :

CREATE SEQUENCE LOGIN_SEQ START WITH 1 INCREMENT BY 1;

2. Create Dual Table in HSQLDB

CREATE TABLE DUAL
(
SEQ INT
);

3. Map the ID column with the sequence LOGIN_SEQ

<id name="userId" column="USERID">
<generator class="sequence">
<param name="sequence">LOGIN_SEQ</param>
</generator>
</id>

4. Create Own class Extending HSQLDialect (e.g. MyHSQLDialect), and override method getSequenceNextValString

public class MyHSQLDialect extends HSQLDialect {

@Override
public String getSequenceNextValString(String sequenceName) {
return "select next value for "+sequenceName+" from dual";
}

}

5. Change the dialect to your own dialect in hibernate.cfg.xml file.

That's all, now we can use Sequence for HSQLDB data base also, as in HSQLDB no dual table is present, So we have to follow these steps to achieve the using sequence.

If we change the generator class to increment, then it will work fine and no need to do any of these changes.

 
Atharva Gupta
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

find attached file for MyHSQLDialect which is extending MyHSQLDialect
MyHSQLDialect.jpg
[Thumbnail for MyHSQLDialect.jpg]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic