Re: Errors in Named queries:{queryname}

[Ricardo Peres]

Not sure about this, named queries are hard to debug, but have you tried prefixing the stored procedure name with its schema, like in "dbo.spGetMeasureValues"?

On Friday, May 17, 2013 3:21:33 PM UTC+1, Shravan Yadav wrote:

I'm new to nhibernate and fluent nhibernate. For one of our projects we are using fluent hibernate which works fine, we would like to extend this to support .hbm.xml files mainly to execute stored procedures on legacy systems. I've find many articles which helped me for getting started like 

http://robgibbens.blogspot.in/2009/10/nhibernate-sql-query-with-stored.html

But there is an exception encountered while building session factory. InnerException: Errors in named queries: {GetMeasures} StackTrae: at NHibernate.Impl.SessionFactoryImpl..ctor(Configuration cfg, IMapping mapping, Settings settings, EventListeners listeners) at NHibernate.Cfg.Configuration.BuildSessionFactory() at FluentNHibernate.Cfg.FluentConfiguration.BuildSessionFactory()

Here is my configuration Adding Asemblies:

configuration.Mappings(m => m.FluentMappings.AddFromAssembly(mapAssm));

configuration.Mappings(m => m.HbmMappings.AddFromAssembly(mapAssm));  

Here is my *.hbm.xml

     <?xml version="1.0" encoding="utf-8" ?> 

     <hibernate-mapping  xmlns="urn:nhibernate-mapping-2.2" auto-import="true"  assembly="MockTest"  namespace="MockTest.Models">

        <sql-query name="GetMeasures" callable ="true">

        <query-param name="Location" type="string" />

        <query-param name="StartDate" type="string" />

        <query-param name="EndDate" type="string" />

        <query-param name="RiskList" type="string" />

       <return class="MockTest.Models.Measures">

         <return-property column="MemberID" name="MemberID" />

         <return-property column="Appointmentid" name="Appointmentid" />

         <return-property column="LastName" name="LastName" />

         <return-property column="FirstName" name="FirstName" />

         <return-property column="MiddleName" name="MiddleName" />

         <return-property column="MeasureData" name="MeasureData" />

     </return>

      exec spGetMeasureValues @Location = :Location,                                         

                              @StartDate = :StartDate, 

                              @EndDate = :EndDate, 

                              @RiskList = :RiskList

      </sql-query>

   </hibernate-mapping>

Please let me know is there something wrong with this file? I tried wrapping stored procedure  call as following 

 <!CDATA[[exec spGetMeasureValues @Location = :Location, ... ]]> but it didn't work

namespace MockTest.Models

 {  public class Measures

    {

      public virtual string MemberID { get; set; }   

      public virtual int Appointmentid { get; set; }    

      public virtual string LastName { get; set; }

      public virtual string FirstName { get; set; }    

      public virtual string MiddleName { get; set; }   

      public virtual string MeasureData { get; set; }

    }

 }

 var query = session.GetNamedQuery("GetMeasures")

                            .SetString("Location", "88")

                            .SetDateTime("StartDate", startDate)

                            .SetDateTime("EndDate", endDate)

                            .SetString("RiskList", "1")

                            .List<Models.Measures>();

                Measures ms = query[0];

Please look into this and provide some inputs.

Thanks

Shravan

[Ricardo Peres]

Yes, in NHibernate you always use : as the parameter prefix, regardless of the actual database server.

[Ricardo Peres]

And can you run the query (calling the stored procedure) by using CreateSQLQuery?

On Tuesday, May 21, 2013 8:22:34 AM UTC+1, Shravan Yadav wrote:

I changed .hbm.xml to the following even that it not working

<?xml version="1.0" encoding="utf-8" ?>

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" auto-import="true" assembly="MockTest"  namespace="MockTest.Models" >

<sql-query name="GetMeasures" callable ="true">

     <return class="MockTest.Models.Measures, MockTest">

      <return-property column="MemberID" name="MemberID" />

      <return-property column="Appointmentid" name="Appointmentid" />

      <return-property column="LastName" name="LastName" />

      <return-property column="FirstName" name="FirstName" />

      <return-property column="MiddleName" name="MiddleName" />

      <return-property column="ApptDateTime" name="ApptDateTime" />

      <return-property column="Service" name="Service" />

      <return-property column="ApptProviderID" name="ApptProviderID" />

      <return-property column="ApptProviderName" name="ApptProviderName" />

      <return-property column="ApptFacilityID" name="ApptFacilityID" />

      <return-property column="ApptFacilityName" name="ApptFacilityName" />

      <return-property column="PrimaryProviderID" name="PrimaryProviderID" />

      <return-property column="PrimaryProviderName" name="PrimaryProviderName" />

      <return-property column="PrimaryFacilityID" name="PrimaryFacilityID" />

      <return-property column="PrimaryFacilityName" name="PrimaryFacilityName" />

      <return-property column="MeasureData" name="MeasureData" />

    </return>

    exec dbo.spGetMeasureValues :DiseaseStateName, :Location,:Physicians,:StartDate,:EndDate, :RiskList

    </sql-query>

</hibernate-mapping>

[Ricardo Peres]

Well, that's easy: the Measures class is not mapped!

Perhaps you can live without it by using the following trick:

session.GetNamedQuery("...").SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean<Measure>()).List<Measure>();

In order to use this, however, the column names returned by the stored procedure must match the property names of the class.

RP

On Tuesday, May 21, 2013 7:48:03 PM UTC+1, andres falcon wrote:

Which is the error?

I see that you don´t have the definition for parameters, just the return values.. Are you missing something?

<query-param name="Location" type="string" />

        <query-param name="StartDate" type="string" />

        <query-param name="EndDate" type="string" />

        <query-param name="RiskList" type="string" />