Ok. I get it. I will work on redesigning my design. Thanks.
On Sep 6, 4:00 pm, John Davidson <[email protected]> wrote: > 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.- 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.
