-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 
Author Message
 Post subject: Problem with inserting new index value from sequence
PostPosted: Wed Jul 12, 2006 3:53 pm 
Newbie

Joined: Wed Jul 12, 2006 3:39 pm
Posts: 2
Hi,
I am relatively new to the Eclipse and Hibernate world and any help will be greatly appreciated.
I have a table in the database that stores Employer related information (Table name:Employers). The table has an index field that used to be implemented as an Identity field in Sql Server. As Oracle does not have that feature, i created a sequence (S_COMPANYINFO) and used that to get the next index value to insert into the Employers table when creating a new employer. The sequence has an initial value of 100, increment by 1. In addition to the index, the employer table has an employer name field and an activity index field.
I created the required hibernate class and defined the ID field as follows
<id name="ERID" type="int" column="CI_COID" >
<generator class="sequence">
<param name="sequence">S_COMPANYINFO</param>
</generator>
</id>

I then did the following to create a new Employer.
Transaction tx = session.beginTransaction();
Employer ER = new Employer();
ER.setERName("Test ER 1");
ER.setActivityID(1);
session.save(ER);
session.flush();
tx.commit();

When i create a new Employer ("Test ER 1"), everything seems to work as advertised and i dont get an error. I checked the DB and found that a new Employer was created. The record in the DB was as follows
Indx, ERName, Activity
102, Test ER 1, 1
The value of Indx that should have been inserted into the DB is 101 and not 102. When i used the getERID method to get the Index of the newly created Employer, the value returned was 101. It seems to me that the sequence is used to get the next available number (101) and is assigned to the object (ER). When the record is written to the DB, the sequence is again used to get the next available number (102) and that value is written to the DB, causing the discrepancy.
I was wondering if anyone could shed some light on what might be happening and how to fix this issue. I have done some looking around but have as yet not been able to figure out what to do.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jul 13, 2006 3:08 am 
Newbie

Joined: Tue Jul 11, 2006 5:14 am
Posts: 10
Location: Paris, France
Have you checked if you have a trigger for your table that calculates CI_COID and automatically resets its value for an insert.
It is usual to have a trigger like this


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jul 14, 2006 1:02 pm 
Newbie

Joined: Wed Jul 12, 2006 3:39 pm
Posts: 2
Hi,
I do have a trigger that sets the value of CI_COID on insert and that is exactly the reason there is a discrepency between the 2 values. Thanks... that helps a lot in understanding what the problem is. now i need to figure out what i need to do in order to make things right.

Nikhil


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 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.