My original code is actually in HQL. It generates exactly the same query. Do you have suggestions as to how to write a query in HQL that would return a single row without using rownum. My only requirement is to include typecode and status in my query.
Thanks for your help. On Sep 6, 1:42 pm, John Davidson <[email protected]> wrote: > This may be a bug in NHibernate, but because it is such an edge case it is > unlikely to get fixed quickly. I would recommend you try to replicate your > query in HQL and see if that corrects the issue - you may not want to use > HQL, but it is the safe fallback to get results, as ICriteria does not cover > as many query scenarios as HQL. Alternatively you could try to redesign your > processes so that the query returns a single row without using the rownum, > which has the added side-effect of eliminating the requirement for a > row-level lock > > John Davidson > > > > On Tue, Sep 6, 2011 at 2:17 PM, Waqar Sadiq <[email protected]> wrote: > > Does anybody has any suggestions? This issue is really killing us. > > We have multiple processes that need to access this table, select the > > first available record, use the certificate number in that table and > > then mark it as used. Without the ability to do row-level locking, > > multiple processes are overwriting each other's transactions and > > without limiting the result of the query to 1 record is resulting in > > extreemley poor performance. I really need to figure out if this is a > > nhibernate bug or I am not using it correctly. > > > Please help...... > > > Waqar > > > On Aug 31, 3:04 pm, Waqar Sadiq <[email protected]> wrote: > > > I have a need to retrieve the first row from a table that matches a > > > certain criteria, with row level lock. If I retrieve the first row > > > that matches the criteria, without the row level lock, it works fine. > > > Also, it works fine if apply the row level lock, withouth limiting the > > > number fo records. But when I use the two in conjunction, nhibernate > > > generates an invalid query for Oracle. > > > > My code looks somewhat like this. > > > > -------------------- My Code --------------------------- > > > ICriteria crit = > > > CurrentSession.CreateCriteria(typeof(RewardBarCodes)); > > > crit.SetLockMode(lockMode); > > > crit.Add(Expression.Eq("TypeCode", "ABCDEF")); > > > crit.Add(Expression.Eq("Status", 0)); > > > crit.SetFirstResult(1); > > > crit.SetMaxResults(batchInfo.BatchSize); > > > IList < IClientDataObject> list = crit.List<IClientDataObject>(); > > > return list; > > > ----------------------------------------------------------------- > > > > This code results in the following query being generated. > > > > ----------------------- NHibernate generated query > > > ---------------------- > > > select * > > > from (select row_.*, > > > rownum rownum_ > > > from (SELECT this_.A_RowKey as A1_93_0_, > > > this_.A_IpCode as A2_93_0_, > > > this_.A_ParentRowKey as A3_93_0_, > > > this_.A_TypeCode as A4_93_0_, > > > this_.A_BarCode as A5_93_0_, > > > this_.A_Status as A6_93_0_, > > > this_.StatusCode as StatusCode93_0_, > > > this_.CreateDate as CreateDate93_0_, > > > this_.UpdateDate as UpdateDate93_0_, > > > this_.LastDmlId as LastDmlId93_0_ > > > FROM ats_RewardBarCodes this_ > > > WHERE this_.A_TypeCode = 'ABCDEF' /* :p0 */ > > > and this_.A_Status = 0 /* :p1 */) row_ > > > where rownum <= 2 /* :p2 */) > > > where rownum_ > 1 /* :p3 */ > > > for update of this_.A_RowKey > > > -------------------------------------------------------------------------------------------------- > > > > This query fails in Oracle because it does not recognize A_RowKey, > > > which is the primary key, outside of the inner select statement. > > > > Has anybody come across this situation before and can offer any help. > > > > Thanks in advance. > > > > Waqar > > > -- > > You received this message because you are subscribed to the Google Groups > > "nhusers" group. > > To post to this group, send email to [email protected]. > > To unsubscribe from this group, send email to > > [email protected]. > > For more options, visit this group at > >http://groups.google.com/group/nhusers?hl=en.- Hide quoted text - > > - Show quoted text - -- You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
