Can Nhibernate work with SqlServer 2005？

tulip · **Joined:** Fri Dec 30, 2005 1:16 am **Posts:** 2

I found that there is no DataBase Driver for SqlServer 2005 in Nhibernate。
So,am I should wait until Nhibernate support SqlServer 2005,then I can use Nhibernate to work with it?

tulip · **Joined:** Fri Dec 30, 2005 1:16 am **Posts:** 2

"NHibernate.Dialect.MsSql2000Dialect" seems Ok！It really work with Sqlserver 2005.

michal · **Posted:** Fri Dec 30, 2005 4:57 am

Hi

I am using NHibernate with Sql Server 2005 (both full and express editions) and it is performing very well, with one exception. I have posted the problem at the forum some time ago, but received no response. Here is it in case if you are interested (or maybe someone can advice something with it):

Quote:

I have problems with date functions - if "formula" with date functions is used in the mapping e.g. formula="datepart(yyyy,myfield)" or formula="datediff(yyyy,myfield1,myfield2)" strange errors from sqlclient are reported - something like "mytable0_.yyyy is not a recognized datepart option" or "this.yyyy is not a recognized datediff option". (no errors with sql2k)

BTW - maybe someone has a solution for this ? My current workaround is rather not nice - I have written 'wrapper' user-definied functions for the above ones.

baggins · **Joined:** Mon May 16, 2005 1:35 am **Posts:** 67

Can you post the actual SQL from the log (using show_sql="true") that succeeds with SQL Server 2000 but fails on 2005?

michal · **Posted:** Tue Jan 03, 2006 4:00 am

Sure, here is the sql

Code:

SELECT this.Id as Id0_, this.StackTrace as StackTrace0_, this.IdUzytkownik as IdUzytko3_0_, this.Data as Data0_, this.Message as Message0_, DateDiff(this.d,GetDate(),this.Data) as f0_0_, DatePart(this.yyyy,this.Data) as f1_0_ FROM tblException this WHERE 1=1

here is the mapping (i've added test properties Age and Year):

Code:

<?xml version="1.0" encoding="utf-8" ?> 
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0" assembly="interfejsLib" namespace="interfejsLib">
   <class name="AppException" table="tblException" >
      <id name="Id" type="Int32" unsaved-value="0">
         <generator class="native" />
      </id>

      <property name="Data" type="DateTime" />
      <property name="IdUzytkownik" type="Int32" />
      <property name="Message" type="StringClob" />
      <property name="StackTrace" type="StringClob" />
      
      
      <property name="Age" formula="DateDiff(d,GetDate(),Data)" type="Int32" />
      <property name="Year" formula="DatePart(yyyy,Data)" type="Int32" />
      
   </class>
</hibernate-mapping>

No exception on Sql2000, but on the 2005:

Code:

System.Data.SqlClient.SqlException: 'this.d' is not a recognized datediff option.
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
   at System.Data.SqlClient.SqlCommand.ExecuteReader()
   at System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader()
   at NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
   at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session)
2006-01-03 08:47:37,812 'this.d' is not a recognized datediff option.
2006-01-03 08:47:37,812 System.Data.SqlClient.SqlException: 'this.d' is not a recognized datediff option.
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
   at System.Data.SqlClient.SqlCommand.ExecuteReader()
   at System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader()
   at NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
   at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session)
   at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies)
   at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies)
   at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet querySpaces, IType[] resultTypes)
   at NHibernate.Loader.CriteriaLoader.List(ISessionImplementor session)
   at NHibernate.Impl.SessionImpl.Find(CriteriaImpl criteria)
2006-01-03 08:47:37,812 'this.d' is not a recognized datediff option.
2006-01-03 08:47:37,812 Unable to perform find
System.Data.SqlClient.SqlException: 'this.d' is not a recognized datediff option.
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
   at System.Data.SqlClient.SqlCommand.ExecuteReader()
   at System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader()
   at NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
   at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session)
   at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies)
   at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies)
   at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet querySpaces, IType[] resultTypes)
   at NHibernate.Loader.CriteriaLoader.List(ISessionImplementor session)
   at NHibernate.Impl.SessionImpl.Find(CriteriaImpl criteria)

After removing the "Age" property DatePart gives the exception too:

Code:

2006-01-03 08:53:30,031 System.Data.SqlClient.SqlException: 'this.yyyy' is not a recognized datepart option.
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
   at System.Data.SqlClient.SqlCommand.ExecuteReader()
   at System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader()
   at NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
   at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session)
2006-01-03 08:53:30,031 'this.yyyy' is not a recognized datepart option.
2006-01-03 08:53:30,031 System.Data.SqlClient.SqlException: 'this.yyyy' is not a recognized datepart option.
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
   at System.Data.SqlClient.SqlCommand.ExecuteReader()
   at System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader()
   at NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
   at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session)
   at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies)
   at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies)
   at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet querySpaces, IType[] resultTypes)
   at NHibernate.Loader.CriteriaLoader.List(ISessionImplementor session)
   at NHibernate.Impl.SessionImpl.Find(CriteriaImpl criteria)
2006-01-03 08:53:30,031 'this.yyyy' is not a recognized datepart option.
2006-01-03 08:53:30,031 Unable to perform find
System.Data.SqlClient.SqlException: 'this.yyyy' is not a recognized datepart option.
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
   at System.Data.SqlClient.SqlCommand.ExecuteReader()
   at System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader()
   at NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
   at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session)
   at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies)
   at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Object optionalObject, Object optionalId, Object[] optionalCollectionKeys, Boolean returnProxies)
   at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet querySpaces, IType[] resultTypes)
   at NHibernate.Loader.CriteriaLoader.List(ISessionImplementor session)
   at NHibernate.Impl.SessionImpl.Find(CriteriaImpl criteria)

sergey · **Posted:** Tue Jan 03, 2006 6:25 am

That's because NHibernate has no knowledge that yyyy and d and others are special SQL identifiers. I don't know if there's an easy way to make them work while still allowing them to be used as column names.

michal · **Posted:** Tue Jan 03, 2006 6:57 am

Hi Sergey - thanks for explanation.

I wonder what was wrong with datediff/datepart old bahavior so M$ changed it to be sensible for those extra aliases (`this` e.g.). But - going back to the problem - I thought we can use "hibernate.query.substitutions" to workaround it somehow - I've made some tries but with no luck:

Code:

<add key="hibernate.query.substitutions" value="yyyy=yyyy" />
 - or -
<add key="hibernate.query.substitutions" value="theY=yyyy" /> <!-- trying to use DatePart(theY,.....) here -->
 

NH seems to completly ignore this setting while generating sql. Any suggestions?

harry111222333 · **Joined:** Fri Mar 26, 2010 11:55 am **Posts:** 1

I solved this by escaping like such in my entity @Formular field:

@Formula("[Last Date Modified] + ( " +
"(DATEPART(\"hh\",[Last Time Modified]) * 3600.0" +
"+ DATEPART(\"mi\",[Last Time Modified]) * 60.0" +
"+ DATEPART(\"ss\",[Last Time Modified]) )" +
"/ (24.0*3600.0)" +
")")
private Date calculated_date;

In short: If you put quotes around "hh" "mi" and all the other special datepart things, hibernate does not go and replace the bits.

Note: I used hibernate on an MSSQL-Server not nhibernate.