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.
