Hi,

Is it possible for NHibernate to read the results of a stored procedure into a set/list/collection of strictly *read-only* objects that are not mapped/bound to an actual table? The data is actually generated by a complex select query against a remote SQL server; and I need to keep this separate from the hbm xml mapping files.

First I tried the read-only cache within <class> element, but there is no table for me to specify so it would not work.

Next I tried using <sql-query> element but it fails with "No persistor for: <<classname>>". Details below.

Please let me know if NHibernate was even designed to support this. If not I can use ADO.NET, but I would prefer NHibernate.

Thanks
Chris


NHibernate version:
1.2.0.Beta1
Mapping documents:
<sql-query name="spGetCompanies" >
<return alias="companylist" class="Test.Company, Test.Data">
<return-property column="DateOfOriginalInvestment" name="DateOfOriginalInvestment" />
<return-property column="InitialCost" name="InitialCost" />
<return-property column="EnterpriseValue" name="EnterpriseValue" />
</return>
exec spGetCompanies
</sql-query>

Code between sessionFactory.openSession() and session.close():
IQuery q = sess.GetNamedQuery("spGetCompanies");
IList result = q.List();

Full stack trace of any exception that occurs:
NHibernate.MappingException: No persister for: Test.Company, Test.Data, Version=1.0.0.0, Culture=neutral, PublicKeyToken=1b66c4a630e22817
at NHibernate.Impl.SessionFactoryImpl.GetEntityPersister(String className, Boolean throwIfNotFound)
at NHibernate.Impl.SessionFactoryImpl.GetEntityPersister(String className)
at NHibernate.Loader.Custom.SQLQueryReturnProcessor.GetSQLLoadable(String entityName)
at NHibernate.Loader.Custom.SQLQueryReturnProcessor.ProcessRootReturn(SQLQueryRootReturn rootReturn)
at NHibernate.Loader.Custom.SQLQueryReturnProcessor.ProcessReturn(ISQLQueryReturn rtn)
at NHibernate.Loader.Custom.SQLQueryReturnProcessor.Process()
at NHibernate.Loader.Custom.SQLCustomQuery..ctor(ISQLQueryReturn[] queryReturns, String sqlQuery, ICollection additionalQuerySpaces, ISessionFactoryImplementor factory)
at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results)
at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters)
at NHibernate.Impl.SqlQueryImpl.List()
at Test.DataManager.GetCompanies() in C:\Test\DataManager.cs:line 1223
at Test.PortfolioCompany.button2_Click(Object sender, EventArgs e) in C:\Test\PortfolioCompany.Test.cs:line 82
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at Test.Program.Main() in C:\Test\Program.cs:line 21

Name and version of the database you are using:
MS SQL 2005

Debug level Hibernate log excerpt:

2006-09-30 03:40:41,758 [5548] DEBUG NHibernate.Impl.SessionImpl::opened session
2006-09-30 03:40:43,102 [5548] DEBUG NHibernate.Loader.Custom.SQLCustomQuery::starting processing of sql query [exec spGetCompanies]

Create a query returning just the columns and use a result transformer in the code:

Note that Company will have to have read-write properties for this to work. You can also write a custom result transformer if you are not satisfied with this.

Awesome, it works.

Thank you Sergey for the quick reply, my faith is renewed !!!

Regards,
Chris

hi sergey,

I am not able get the result with the method you propose when i do a stored procedure. I still have an error "No persistor for: <<classname>>".
here is a snipplet of my code.

...
return
.Session
.GetNamedQuery("GetEpisodeEncounterByPatientId")
.SetInt32(0, PatientOID)
.SetResultTransformer(Transformers.AliasToBean(typeof(EpisodeEncounterInfo)))
.List<EpisodeEncounterInfo>();
...

hbm file
<sql-query name="GetEpisodeEncounterByPatientId" flush-mode ="never">
<return class ="Nhg.Plato.Entities.RIM.PatientAdministration.EpisodeEncounterInfo,Nhg.Plato.Entities" lock-mode ="read">
<return-property column ="OID" name ="EncounterOID"/>
</return>
<![CDATA[
select
ep.oid as EncounterOID
from
pat_episodeencounteract_tx ep
inner join
pat_patientrol_tx pat
on
pat.oid=ep.patientoid
where
pat.oid=?
]]>
</sql-query>

entity class
namespace Nhg.Plato.Entities.RIM.PatientAdministration
{
public class EpisodeEncounterInfo
{

private int _encounteroid;
private string _caseTypeCode;
private string _caseTypeDesc;
private IList<PatientEncounterInfo> _patientEncounters;

public IList<PatientEncounterInfo> PatientEncounters
{
get { return _patientEncounters;}
set { _patientEncounters = value;}
}

public int EncounterOID
{
get { return _encounteroid; }
set { _encounteroid = value; }
}

public string CaseTypeCode
{
get { return _caseTypeCode; }
set { _caseTypeCode = value; }
}

public string CaseTypeDesc
{
get { return _caseTypeDesc; }
set { _caseTypeDesc = value; }
}
}
}

hendry78,

have you been able to solve the no persisister for xxx error yet? I am receiving it calling a stored proc and I have what I think is a valid class to persist it to.

Ah the penny just dropped I think..

Was trying to populate a MonthlySetTargets class using:



and kept getting No Persister for <class>.
I had not defined the class in a mapping file since I didn't want nhib to manage it. I didn't really grok what was occuring overall.

Dropped properties and set scalars.



Then using

on the IQuery and everything is working now.