Hi Hibernate Team,
I am calling a Simple Stored Procedure from my NHibernate Code. However I am getting the following error.
e = {"could not execute query\r\n[ exec sproc_GetAllTradeForStrategy(?) ]\r\n Name: StrategyId - Value: 40\r\n[SQL: exec sproc_GetAllTradeForStrategy(?)]"}
InnerException = {"Incorrect syntax near '@p0'."}
Here is my Stored Proc Hbm.xml file.
Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="ml.smi.mds" namespace="ml.smi.mds.server.dal.data">
<database-object>
<create>
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure sproc_HibGetAllTradeForStrategy
@StrategyId int
AS
BEGIN
SET NOCOUNT ON;
SELECT tt.*
FROM t_trade tt
WHERE tt.trd_strategy_id = @StrategyId
END
</create>
<drop>
drop procedure sproc_HibGetAllTradeForStrategy
</drop>
</database-object>
</hibernate-mapping>
Here is my SQL-QUERY Mapping File.
Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="ml.smi.mds" namespace="ml.smi.mds.server.dal.data">
<sql-query name="GetAllTradesForStrategy">
<return alias="tt" class="TTrade">
<return-property name="trdId" column="trd_id"/>
<return-property name="trdTradeId" column="trd_trade_id"/>
<return-property name="trdCoperId" column="trd_coper_id"/>
<return-property name="trdLegalEntity" column="trd_legal_entity"/>
<return-property name="trdSourceSystemId" column="trd_source_system_id"/>
<return-property name="trdProductType" column="trd_product_type"/>
<return-property name="trdTradeType" column="trd_trade_type"/>
<return-property name="trdTradeTypeDetail" column="trd_trade_type_detail"/>
<return-property name="trdNotional" column="trd_notional"/>
<return-property name="trdCurrency" column="trd_currency"/>
<return-property name="trdLongShort" column="trd_long_short"/>
<return-property name="trdTradeDate" column="trd_trade_date"/>
<return-property name="trdCobDate" column="trd_cob_date"/>
<return-property name="trdMtm" column="trd_mtm"/>
<return-property name="trdReasonId" column="trd_reason_id"/>
<return-property name="trdSpread" column="trd_spread"/>
<return-property name="trdStrike" column="trd_strike"/>
<return-property name="trdMaturityStatusCd" column="trd_maturity_status_cd"/>
<return-property name="trdStrategyId" column="trd_strategy_id"/>
<return-property name="trdResultId" column="trd_result_id"/>
</return>
exec sproc_GetAllTradeForStrategy(:StrategyId)
</sql-query>
</hibernate-mapping>
Here is the code that calls the Query.
Code:
ISession session=HibernateSessionFactory.getInstance().getSession();
list=session.GetNamedQuery("GetAllTradesForStrategy")
.SetParameter("StrategyId", strategyId)
.List<T>();
Here is the full stack trade
ml.smi.mds, Version=1.0.0.0, Culture=neutral, PublicKeyToken=55835b3f0ac4adfe]] [(null)] <(null)> - NHibernate.ADOException: could not execute query
[ exec sproc_GetAllTradeForStrategy(?) ]
Name: StrategyId - Value: 40
[SQL: exec sproc_GetAllTradeForStrategy(?)] ---> System.Data.SqlClient.SqlException: Incorrect syntax near '@p0'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.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, Boolean returnProxies)
at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
--- End of inner exception stack trace ---
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet querySpaces, IType[] resultTypes)
at NHibernate.Loader.Custom.CustomLoader.List(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Impl.SessionImpl.ListCustomQuery(ICustomQuery customQuery, QueryParameters queryParameters, IList results)
at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results)
at NHibernate.Impl.SessionImpl.List[T](NativeSQLQuerySpecification spec, QueryParameters queryParameters)
at NHibernate.Impl.SqlQueryImpl.List[T]()
at ml.smi.mds.server.dal.impl.dao.hibernate.TTradeDAOAdapter`1.getAllTradesForStrategy(Int32 strategyId)
Anyone Any Ideas. This is proving difficult as the examples on the NHibernate Web Site do note appear to work.
The Database I am using is SQL Server 2005.
thanks,
Richie.