If it does not work in HQL, then you can be almost certain that it will not work in ICriteria. The only likely way to factor rownum out of your design is to fix the table design so that it does not need it - there must be some definable criteria that can be stored in the DB that allows only 1 row to be returned.
John Davidson On Tue, Sep 6, 2011 at 4:27 PM, Waqar Sadiq <[email protected]> wrote: > 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. > > -- 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.
