Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 4 posts ] 
Author Message
 Post subject: HSQLDB Sequence problem by fetching the next value
PostPosted: Thu Oct 30, 2008 6:03 am 
Newbie

Joined: Thu Oct 30, 2008 5:52 am
Posts: 5
Location: Germany
Hi all,

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

Hibernate-Version: 3.2.2.ga
HSQLDB: 1.8.0


The story is like this; I created a sequence in my database using this line of code:

Code:
CREATE SEQUENCE MIT_SEQ AS INTEGER
START WITH 1 INCREMENT BY 1;



I have an entity named Mitarbeiter. So, what I want to do is to get the id for this entity by fetching the next value of MIT_SEQ in case of an insert operation.

I mapped my entity as follows:


Code:
    <class name="Mitarbeiter" table="MITARBEITER" schema="PUBLIC">

      <id name="id" column="MIT_ID" type="int" access="field">
         <generator class="sequence">
            <param name="sequence">MIT_SEQ</param>
         </generator>
      </id>

   ...
   ...



However, when I try to let my Mitarbeiter object inserted, I see that the next value of MIT_SEQ is tried to be fetched using a temporary table (dual_mit_seq) and a select for it.


Code:
final MitarbeiterIfc m = Mitarbeiter.getInstance(MAENNLICH, "Rocky", "Balboa");

try {         
   tx = HibernateUtil.getSessionFactory().openSession().beginTransaction();
   tx.setTimeout(5);
   
   // Save the PO
   getSession().save(m);
   tx.commit();
} catch (RuntimeException e) {
   try {
      tx.rollback();
   } catch (RuntimeException e1) {
      log.error(" Rollback failed ", e1);
   }
   throw e;
}



Here's the concerning logging part:

------------------------------------------
WARN - SQL Error: -22, SQLState: S0002
ERROR - Table not found in statement [select next value for MIT_SEQ from dual_MIT_SEQ]
Exception in thread "main" 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:99)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:187)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:172)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.performSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:94)
at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:70)
at org.hibernate.impl.SessionImpl.fireSaveOrUpdate(SessionImpl.java:507)
at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:499)
at org.hibernate.engine.CascadingAction$1.cascade(CascadingAction.java:218)
at org.hibernate.engine.Cascade.cascadeToOne(Cascade.java:268)
at org.hibernate.engine.Cascade.cascadeAssociation(Cascade.java:216)
at org.hibernate.engine.Cascade.cascadeProperty(Cascade.java:169)
at org.hibernate.engine.Cascade.cascadeCollectionElements(Cascade.java:296)
at org.hibernate.engine.Cascade.cascadeCollection(Cascade.java:242)
at org.hibernate.engine.Cascade.cascadeAssociation(Cascade.java:219)
at org.hibernate.engine.Cascade.cascadeProperty(Cascade.java:169)
at org.hibernate.engine.Cascade.cascade(Cascade.java:130)
at org.hibernate.event.def.AbstractFlushingEventListener.cascadeOnFlush(AbstractFlushingEventListener.java:131)
at org.hibernate.event.def.AbstractFlushingEventListener.prepareEntityFlushes(AbstractFlushingEventListener.java:122)
at org.hibernate.event.def.AbstractFlushingEventListener.flushEverythingToExecutions(AbstractFlushingEventListener.java:65)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:26)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
at de.alpercelik.uebungen.acmeag.controller.Tester.neuMitarbeiterVw(Tester.java:188)
at de.alpercelik.uebungen.acmeag.controller.Tester.main(Tester.java:49)
Caused by: java.sql.SQLException: Table not found in statement [select next value for MIT_SEQ from dual_MIT_SEQ]
at org.hsqldb.jdbc.Util.throwError(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StmtAcquireTask.run(GooGooStatementCache.java:525)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
------------------------------------------


I search this problem in the forums but didn't find a proper solution for me. There are some ways in which the HSQLDialect.getSequenceNextValString() is overridden etc. But I think what I want to do must not be so tricky. The sequence on an HSQLDB does not necessarily need any temporary table like dual_mit_seq and the required next value could easily be fetched by using:

Code:
insert into mitarbeiter(mit_id, mit_name,...) values (next value for mit_seq, 'John',...)


I wonder why my mapping setup doesn't create such a simple code and deal with some extra dual_mit_seq tables instead. Can anybody explain me what I am doing wrong and what the correct way would be?

Thanks in advance!


Cheers,


Alper


Top
 Profile  
 
 Post subject:
PostPosted: Fri Oct 31, 2008 4:44 am 
Newbie

Joined: Thu Oct 30, 2008 5:52 am
Posts: 5
Location: Germany
Pity that nobody responded...

I just read the Hibernate Reference for version 3.3.1, it says under 5.1.5. Enhanced identifier generators:

This generator has a number of configuration parameters:
" sequence_name (optional, defaults to hibernate_sequence): The name of the sequence (or table) to be used.


So I then modified the parameter name and wrote:

Code:
       <id name="id" column="MIT_ID" type="int" access="field">
           <generator class="sequence">
               <param name="sequence_name">MIT_SEQ</param>
           </generator>
       </id>



I get the error below.

then tried:

Code:
       <id name="id" column="MIT_ID" type="int" access="field">
           <generator class="sequence">
               <param name="MIT_SEQ">MIT_SEQ</param>
           </generator>
       </id>


no way the same error:

Code:
Hibernate:
    select
       next value for hibernate_sequence
    from
       dual_hibernate_sequence
WARN  - SQL Error: -191, SQLState: S0002
ERROR - Sequence not found: HIBERNATE_SEQUENCE in statement [select next value for hibernate_sequence from dual_hibernate_sequence]
Exception in thread "main" 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:99)
   at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:187)
   at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:172)
   at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.performSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:94)
   at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:70)
   at org.hibernate.impl.SessionImpl.fireSaveOrUpdate(SessionImpl.java:507)
   at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:499)
   at org.hibernate.engine.CascadingAction$1.cascade(CascadingAction.java:218)
   at org.hibernate.engine.Cascade.cascadeToOne(Cascade.java:268)
   at org.hibernate.engine.Cascade.cascadeAssociation(Cascade.java:216)
   at org.hibernate.engine.Cascade.cascadeProperty(Cascade.java:169)
   at org.hibernate.engine.Cascade.cascadeCollectionElements(Cascade.java:296)
   at org.hibernate.engine.Cascade.cascadeCollection(Cascade.java:242)
   at org.hibernate.engine.Cascade.cascadeAssociation(Cascade.java:219)
   at org.hibernate.engine.Cascade.cascadeProperty(Cascade.java:169)
   at org.hibernate.engine.Cascade.cascade(Cascade.java:130)
   at org.hibernate.event.def.AbstractFlushingEventListener.cascadeOnFlush(AbstractFlushingEventListener.java:131)
   at org.hibernate.event.def.AbstractFlushingEventListener.prepareEntityFlushes(AbstractFlushingEventListener.java:122)
   at org.hibernate.event.def.AbstractFlushingEventListener.flushEverythingToExecutions(AbstractFlushingEventListener.java:65)
   at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:26)
   at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
   at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:338)
   at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
   at de.alpercelik.uebungen.acmeag.controller.Tester.neuMitarbeiterVw(Tester.java:188)
   at de.alpercelik.uebungen.acmeag.controller.Tester.main(Tester.java:49)
Caused by: java.sql.SQLException: Sequence not found: HIBERNATE_SEQUENCE in statement [select next value for hibernate_sequence from dual_hibernate_sequence]
   at org.hsqldb.jdbc.Util.throwError(Unknown Source)
   at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
   at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:585)
   at com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StmtAcquireTask.run(GooGooStatementCache.java:525)
   at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)





If I try the one with

Code:
<param name="sequence">MIT_SEQ</param>


The default sequence name hibernate_sequence is overridden, but this time the value is tried to be fetched via a table.

The problem is in

Code:
HSQLDialect.configure(Type type, Properties params, Dialect dialect) throws MappingException.

sql = dialect.getSequenceNextValString(sequenceName);



instead of getSequenceNextValString, the getSelectSequenceNextValString must have been called so that the additional dual tables are not considered and simply the next value of the sequence is fetched. No idea how to solve it. The reference doesn't say anything further about that point. I wouldn't extend the HSQLDialect for that.

_________________
The identity of a person is what s/he thinks.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Feb 26, 2009 2:49 pm 
Newbie

Joined: Thu Feb 26, 2009 2:39 pm
Posts: 2
I have a problem that mybe is corelated

I use :
hibernate 3.3.1
hsqldb 1_8_0_10.

I have a sequence like this :

Code:
CREATE SEQUENCE MY_SEQ AS INTEGER
START WITH 1 INCREMENT BY 1;


the porblem is thet when i insert an record the id of the inserted record in database is not 1 but 50

when i insert the next record the sequence goes to 2, the second record just inserted in database has 51...then I insert the third record and i got for inserted id 100....

so the inserted ID-s in the database are not exactly corelated with the sequence vale...if I look the sequence value it is incrementing corectly by one but it looks like hibernate is inserting strange numbers for record Id-s


Top
 Profile  
 
 Post subject:
PostPosted: Thu Feb 26, 2009 3:08 pm 
Newbie

Joined: Thu Feb 26, 2009 2:39 pm
Posts: 2
I found the solution to my problem i havent set the alocationSize parameter in entity class

Code:
@SequenceGenerator(name="my_id", sequenceName="MY_SEQ", allocationSize=1)


the alocationSize must be set to 1 to insert the exact sequence value in db

[url=http://www.hibernate.org/hib_docs/annotations/reference/en/html_single/]
http://www.hibernate.org/hib_docs/annot ... ml_single/[/url]

Quote:
The default allocation size is 50, so if you want to use a sequence and pickup the value each time, you must set the allocation size to 1.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 4 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.