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.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 
Author Message
 Post subject: "Incorrect" sql is generated by nhibernate
PostPosted: Wed Apr 04, 2007 5:18 pm 
Newbie

Joined: Mon Jan 29, 2007 2:44 pm
Posts: 6
"Incorrect" sql is generated by nhibernate (sql that does not work in sql server 2005)

ICriteria criteria1 = NHibSessionManager.Instance.GetSession().CreateCriteria(typeof(Category));
criteria1.Add(Expression.IsNull("ParentId"));
criteria1.SetFirstResult(0);
criteria1.SetMaxResults(1);

Generates sql:

exec sp_executesql N'WITH query AS (SELECT TOP 1 ROW_NUMBER() OVER (ORDER BY this_.SortIndex desc) as __hibernate_row_nr__, this_.CategoryId as CategoryId20_0_,
this_.ApplicationId as Applicat2_20_0_, this_.ParentId as ParentId20_0_, this_.Name as Name20_0_, this_.Description as Descript5_20_0_, this_.Selectable as
Selectable20_0_, this_.SortIndex as SortIndex20_0_, this_.InsertStamp as InsertSt8_20_0_, this_.UpdateStamp as UpdateSt9_20_0_ FROM
CommunityFramework.dbo.cfCategory this_ WHERE this_.ParentId = @p0 ORDER BY this_.SortIndex desc) SELECT * FROM query WHERE __hibernate_row_nr__ > 0 ORDER BY
__hibernate_row_nr__',N'@p0 uniqueidentifier',@p0=NULL

The problem is that sql server does not work with ...WHERE this_.ParentId = @p0... ...N'@p0 uniqueidentifier',@p0=NULL

If I modify this sql in query analyzer to

exec sp_executesql N'WITH query AS (SELECT TOP 1 ROW_NUMBER() OVER (ORDER BY this_.SortIndex desc) as __hibernate_row_nr__, this_.CategoryId as CategoryId20_0_,
this_.ApplicationId as Applicat2_20_0_, this_.ParentId as ParentId20_0_, this_.Name as Name20_0_, this_.Description as Descript5_20_0_, this_.Selectable as
Selectable20_0_, this_.SortIndex as SortIndex20_0_, this_.InsertStamp as InsertSt8_20_0_, this_.UpdateStamp as UpdateSt9_20_0_ FROM
CommunityFramework.dbo.cfCategory this_ WHERE this_.ParentId IS NULL ORDER BY this_.SortIndex desc) SELECT * FROM query WHERE __hibernate_row_nr__ > 0 ORDER BY
__hibernate_row_nr__',N'@p0 uniqueidentifier',@p0=NULL

I get the row i asking for

Is this a bug in nhibernate or a limitation in SQL Server 2005

Hibernate version:1.2.0RC1


Top
 Profile  
 
 Post subject:
PostPosted: Thu Apr 05, 2007 4:19 am 
Newbie

Joined: Mon Jan 29, 2007 2:03 am
Posts: 11
Hi,

I just tried it agains NH1.2RC1, and it generates the following sql(using AdventureWorks):

WITH query AS (SELECT TOP 1 ROW_NUMBER() OVER (ORDER BY CURRENT_TIME
STAMP) as __hibernate_row_nr__, this_.EmployeeId as EmployeeId0_0_, this_.Title
as Title0_0_, this_.BirthDate as BirthDate0_0_ FROM HumanResources.Employee thi
s_ WHERE this_.Title is null) SELECT * FROM query WHERE __hibernate_row_nr__ > 0
ORDER BY __hibernate_row_nr__

no error here.. maybe you can post your mappings/data schema, so that we can also reproduce.

hth


Top
 Profile  
 
 Post subject:
PostPosted: Thu Apr 05, 2007 3:38 pm 
Newbie

Joined: Mon Jan 29, 2007 2:44 pm
Posts: 6
Here is the mappingfile:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-access="field.camelcase-underscore">
<class name="CF.Core.Category, CF" table="cfCategory">
<id name="Id" column="CategoryId" type="Guid" unsaved-value="00000000-0000-0000-0000-000000000000">
<generator class="guid.comb" />
</id>
<property name="ParentId" type="Guid" not-null="false" />
<property name="Name" type="String" length="256" not-null="true" />
<property name="Description" type="String" not-null="false" />
<property name="Selectable" type="Boolean" not-null="true" />
<property name="SortIndex" type="Int32" not-null="true" />
<bag name="ObjectTypes" cascade="save-update" lazy="true" inverse="true" generic="true" fetch="select">
<key column="ObjectTypeId" />
<one-to-many class="CF.Core.Data.ObjectType, CF" />
</bag>
<property name="InsertStamp" type="DateTime" not-null="true" />
<property name="UpdateStamp" type="DateTime" not-null="true" />
</class>
</hibernate-mapping>


Top
 Profile  
 
 Post subject:
PostPosted: Sat Apr 07, 2007 4:10 am 
Newbie

Joined: Mon Jan 29, 2007 2:44 pm
Posts: 6
Case closed. Bug made by me


Top
 Profile  
 
 Post subject:
PostPosted: Sun Apr 08, 2007 12:40 am 
Newbie

Joined: Mon Jan 29, 2007 2:03 am
Posts: 11
hi andr,

Care to share what that bug was? And the resolution made?

Thanks


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 5 posts ] 

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.