-->
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.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » NHibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 11 posts ] 
  Previous topic | Next topic 
Author Message
McB
 Post subject: Stored procedure executed twice?
PostPosted: Wed Jan 09, 2008 4:43 am 
Newbie

Joined: Wed Jul 04, 2007 4:49 am
Posts: 6
Hi,

I am having some issues when executing a stored procedure using NHibernate. The sp is supposed to increment a number in a row, and return the new number. The reason for using a stored procedure has to do with the fact that some checks have to be made in the database prior to the number being generated.

The problem is that the procedure seems to be executed twice, giving me every other number back. The NHibernate debug output supports this.

Any ideas?

Hibernate version: 1.2.1.4000

Mapping documents:
Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Lancelot" namespace="Lancelot.Entities">
  <class name="Reservation" table="tbl_reservation">
    <id name="ReservationID" column="reservationID" type="int">
      <generator class="native" />
    </id>
    <property name="Year" column="year" type="int" />
    <property name="Number" column="number" type="int" />   
  </class>
  <sql-query name="sp_reserveNumber">
    <return alias="reservation" class="Reservation">
      <return-property name="Year" column="year" />
      <return-property name="Number" column="number" />
      <return-property name="ReservationID" column="reservationID" />
    </return>
    exec sp_reserveNumber
  </sql-query>
</hibernate-mapping>


Code between sessionFactory.openSession() and session.close():

Code:
IQuery query = HibernateManager.Instance.Session.GetNamedQuery("sp_reserveNumber");
query.SetCacheable(false);
Reservation reservation = query.UniqueResult<Reservation>();
HibernateManager.Instance.Transaction.Commit();
HibernateManager.Instance.Session.Evict(reservation);


Name and version of the database you are using:

MS SQL Server 9.0.3042.

The generated SQL (show_sql=true):

NHibernate: exec sp_reserveNumber
NHibernate: exec sp_reserveNumber
Top
 Profile  
 
McB
 Post subject:
PostPosted: Fri Feb 29, 2008 5:39 am 
Newbie

Joined: Wed Jul 04, 2007 4:49 am
Posts: 6
Sorry if I am nagging, but this has turned into a quite critical issue. This is the only (of those found) remaining bug before release. If anyone has any idea about what might be causing this behavior, please let me know.
Top
 Profile  
 
jta
 Post subject:
PostPosted: Fri Feb 29, 2008 6:51 am 
Regular
Regular

Joined: Wed Jan 25, 2006 1:11 am
Posts: 118
Location: Copenhagen, Denmark
When do you open the transaction? It must be either some code you execute or something in your stored procedure that triggers this.

I have no problems using code like this:

<sql-query name="GetMovie">
<return alias="movie" class="Movie">
<return-property name="ID" column="id" />
<return-property name="Title" column="title" />
<return-property name="Genre" column="genreid" />
</return>
exec sp_GetMovie
</sql-query>

using(ISession sess = sessionmanager.OpenSession()){
Movie mov = sess.GetNamedQuery("GetMovie").UniqueResult<Movie>();
}
Top
 Profile  
 
McB
 Post subject:
PostPosted: Fri Feb 29, 2008 8:08 am 
Newbie

Joined: Wed Jul 04, 2007 4:49 am
Posts: 6
The transaction is opened when the application is started and if it closes it is re-opened every time it is being accessed.

I've read something in the documentation about fetching strategies, and that the default select fetching might cause problems with N+1 operations. This is exactly what my procedure does, but I have not been able to figure out where in my mapping files to specify that join fetching should be used instead. I do not know if this has anything to do with the problem, but I am ready to try anything...
Top
 Profile  
 
jta
 Post subject:
PostPosted: Fri Feb 29, 2008 8:20 am 
Regular
Regular

Joined: Wed Jan 25, 2006 1:11 am
Posts: 118
Location: Copenhagen, Denmark
Try to just call the procedure and nothing else in a Testcase and watch if it executes twice?
Top
 Profile  
 
McB
 Post subject:
PostPosted: Fri Feb 29, 2008 8:47 am 
Newbie

Joined: Wed Jul 04, 2007 4:49 am
Posts: 6
jta wrote:
Try to just call the procedure and nothing else in a Testcase and watch if it executes twice?


Yes, it is executed twice then as well. It works just fine if I execute it from SQL Server Management Studio, or without NHibernate.
Top
 Profile  
 
jta
 Post subject:
PostPosted: Fri Feb 29, 2008 8:51 am 
Regular
Regular

Joined: Wed Jan 25, 2006 1:11 am
Posts: 118
Location: Copenhagen, Denmark
so running the following code gives you two executions of the named query?

Code:
[Test]
public void NamedQueryTestCase(){
HibernateManager.Instance.Transaction.Begin();
IQuery query = HibernateManager.Instance.Session.GetNamedQuery("sp_reserveNumber");
Reservation reservation = query.UniqueResult<Reservation>();
HibernateManager.Instance.Transaction.Commit();
}


If that is the case then:

1. What version of nHibernate are you running on?
2. How does your stored procedures code look like?
3. Hov does your HibernateManager look like
Top
 Profile  
 
McB
 Post subject:
PostPosted: Fri Feb 29, 2008 2:17 pm 
Newbie

Joined: Wed Jul 04, 2007 4:49 am
Posts: 6
In order to satisfy my manager, I suggested two alternatives; a quite ugly work-around, or to keep working on finding the bug. He went for the hack-approach. But I will still keep researching to find out what is wrong...

jta: I haven't tried that exact piece of code, but something similar. I will try your approach tomorrow and see if something interesting shows up. Thanks for your help.
Top
 Profile  
 
jeffhagen
 Post subject: There is a bug in IQuery.UniqueResult<T>()
PostPosted: Thu Mar 27, 2008 12:39 pm 
Newbie

Joined: Thu Mar 27, 2008 12:37 pm
Posts: 1
Use the non-generic version (IQuery.UniqueResult()) and the problem will disappear.

Reservation reservation = (Reservation)query.UniqueResult();


Top
 Profile  
 
McB
 Post subject: Re: There is a bug in IQuery.UniqueResult<T>()
PostPosted: Sat Apr 19, 2008 8:32 am 
Newbie

Joined: Wed Jul 04, 2007 4:49 am
Posts: 6
jeffhagen wrote:
Use the non-generic version (IQuery.UniqueResult()) and the problem will disappear.

Reservation reservation = (Reservation)query.UniqueResult();


You are absolutely right. That did the trick. Thank you very much.

Now that I know this, I am a bit curious about why this happens. Can anyone explain it?
Top
 Profile  
 
typhos
 Post subject:
PostPosted: Mon Apr 21, 2008 7:05 am 
Regular
Regular

Joined: Thu Mar 06, 2008 5:06 am
Posts: 68
I have recognized this behaviour also with "normal" hql statements. It seems to be an issue in the generic UniqueResult() method...
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 11 posts ] 

Board index » Hibernate & Java Persistence » NHibernate Users

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.