In brief here is the problem I have.
I call a Stored Procedure from my code. It executes and I get the following error. No SQL is generated for me but I have no idea what is going on.
ml.smi.mds, Version=1.0.0.0, Culture=neutral, PublicKeyToken=55835b3f0ac4adfe]] [(null)] <(null)> - NHibernate.ADOException: could not execute query
[ exec sproc_GetAllTradesForStrategy ? ]
Name: StrategyId - Value: 40
[SQL: exec sproc_GetAllTradesForStrategy ?] ---> System.IndexOutOfRangeException: trd1_2_0_
at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName)
Hibernate version: 1.2.0-GA
Mapping documents:
Here is my SQL-QUERY Mapping File.
<?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 my Stored Proc in the SQL Server 2005 DB
USE [MDS_Prototype2] GO /****** Object: StoredProcedure [dbo].[sproc_GetAllTradesForStrategy] Script Date: 07/04/2007 14:33:51 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
-- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE PROCEDURE [dbo].[sproc_GetAllTradesForStrategy] -- Add the parameters for the stored procedure here @StrategyId int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
-- Insert statements for procedure here SELECT tt.*, tt_cd.*, tt_df.*, tt_sw.*, tt_strat.*, case when tt_cd.cdr_id is not null then 1 when tt_df.fra_id is not null then 2 when tt_sw.swn_id is not null then 3 when tt.trd_id is not null then 0 end as clazz_1_ FROM t_trade tt left outer join t_trade_credit_derivative tt_cd on tt.trd_id=tt_cd.cdr_id left outer join t_trade_debt_fra tt_df on tt.trd_id=tt_df.fra_id left outer join t_trade_swaption tt_sw on tt.trd_id=tt_sw.swn_id left outer join t_strategy tt_strat on tt.trd_strategy_id=tt_strat.sgy_strategy_id WHERE tt.trd_strategy_id = @StrategyId;
END
list=session.GetNamedQuery("GetAllTradesForStrategy") .SetParameter("StrategyId", strategyId) .List<T>();
Full stack trace of any exception that occurs: 2007-07-04 16:33:12,918 [2316] DEBUG NHibernate.Loader.Loader [(null)] <(null)> - BindNamedParameters() 40 -> StrategyId [0] 2007-07-04 16:33:12,918 [2316] DEBUG NHibernate.Type.Int32Type [(null)] <(null)> - binding '40' to parameter: 0 2007-07-04 16:33:12,918 [2316] INFO NHibernate.Loader.Loader [(null)] <(null)> - exec sproc_GetAllTradesForStrategy @p0 2007-07-04 16:33:12,918 [2316] DEBUG NHibernate.SQL [(null)] <(null)> - exec sproc_GetAllTradesForStrategy @p0; @p0 = '40' 2007-07-04 16:33:12,918 [2316] DEBUG NHibernate.Connection.DriverConnectionProvider [(null)] <(null)> - Obtaining IDbConnection from Driver 2007-07-04 16:33:12,918 [2316] DEBUG NHibernate.Impl.BatcherImpl [(null)] <(null)> - Opened IDataReader, open IDataReaders: 1 2007-07-04 16:33:12,918 [2316] DEBUG NHibernate.Loader.Loader [(null)] <(null)> - processing result set 2007-07-04 16:33:12,918 [2316] DEBUG NHibernate.Loader.Loader [(null)] <(null)> - result set row: 0 2007-07-04 16:33:12,934 [2316] DEBUG NHibernate.Driver.NHybridDataReader [(null)] <(null)> - running NHybridDataReader.Dispose() 2007-07-04 16:33:12,934 [2316] DEBUG NHibernate.Impl.BatcherImpl [(null)] <(null)> - Closed IDataReader, open IDataReaders :0 2007-07-04 16:33:12,934 [2316] DEBUG NHibernate.Impl.BatcherImpl [(null)] <(null)> - Closed IDbCommand, open IDbCommands: 0 2007-07-04 16:33:12,934 [2316] DEBUG NHibernate.Impl.ConnectionManager [(null)] <(null)> - aggressively releasing database connection 2007-07-04 16:33:12,934 [2316] DEBUG NHibernate.Connection.ConnectionProvider [(null)] <(null)> - Closing connection 2007-07-04 16:33:12,934 [2316] DEBUG NHibernate.Util.ADOExceptionReporter [(null)] <(null)> - could not execute query [ exec sproc_GetAllTradesForStrategy ? ] Name: StrategyId - Value: 40
System.IndexOutOfRangeException: trd1_2_0_ at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName) at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name) at NHibernate.Driver.NHybridDataReader.GetOrdinal(String name) at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name) at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String[] names, ISessionImplementor session, Object owner) at NHibernate.Loader.Loader.GetKeyFromResultSet(Int32 i, ILoadable persister, Object id, IDataReader rs, ISessionImplementor session) at NHibernate.Loader.Loader.GetRowFromResultSet(IDataReader resultSet, ISessionImplementor session, QueryParameters queryParameters, LockMode[] lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects, EntityKey[] keys, Boolean returnProxies) 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) 2007-07-04 16:33:12,934 [2316] WARN NHibernate.Util.ADOExceptionReporter [(null)] <(null)> - System.IndexOutOfRangeException: trd1_2_0_ at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName) at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name) at NHibernate.Driver.NHybridDataReader.GetOrdinal(String name) at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name) at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String[] names, ISessionImplementor session, Object owner) at NHibernate.Loader.Loader.GetKeyFromResultSet(Int32 i, ILoadable persister, Object id, IDataReader rs, ISessionImplementor session) at NHibernate.Loader.Loader.GetRowFromResultSet(IDataReader resultSet, ISessionImplementor session, QueryParameters queryParameters, LockMode[] lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects, EntityKey[] keys, Boolean returnProxies) 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) 2007-07-04 16:33:12,934 [2316] ERROR NHibernate.Util.ADOExceptionReporter [(null)] <(null)> - trd1_2_0_ 2007-07-04 16:33:12,950 [2316] DEBUG NHibernate.Impl.ConnectionManager [(null)] <(null)> - after autocommit 2007-07-04 16:33:12,950 [2316] DEBUG NHibernate.Impl.ConnectionManager [(null)] <(null)> - aggressively releasing database connection 2007-07-04 16:33:12,950 [2316] DEBUG NHibernate.Impl.SessionImpl [(null)] <(null)> - transaction completion 2007-07-04 16:33:14,496 [2316] ERROR ml.smi.mds.server.dal.impl.dao.TTradeDAO`1[[ml.smi.mds.server.dal.data.TTrade, ml.smi.mds, Version=1.0.0.0, Culture=neutral, PublicKeyToken=55835b3f0ac4adfe]] [(null)] <(null)> - NHibernate.ADOException: could not execute query [ exec sproc_GetAllTradesForStrategy ? ] Name: StrategyId - Value: 40 [SQL: exec sproc_GetAllTradesForStrategy ?] ---> System.IndexOutOfRangeException: trd1_2_0_ at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName) at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name) at NHibernate.Driver.NHybridDataReader.GetOrdinal(String name) at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name) at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String[] names, ISessionImplementor session, Object owner) at NHibernate.Loader.Loader.GetKeyFromResultSet(Int32 i, ILoadable persister, Object id, IDataReader rs, ISessionImplementor session) at NHibernate.Loader.Loader.GetRowFromResultSet(IDataReader resultSet, ISessionImplementor session, QueryParameters queryParameters, LockMode[] lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects, EntityKey[] keys, Boolean returnProxies) 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) in
Name and version of the database you are using: SQL Server 2005
The generated SQL (show_sql=true): I have show sql set but its not producing any SQL
Debug level Hibernate log excerpt: Portion of Hibernate Log that this concerns
2007-07-04 17:01:04,485 [4092] DEBUG NHibernate.Impl.SessionFactoryImpl [(null)] <(null)> - Instantiated session factory 2007-07-04 17:01:07,529 [4092] DEBUG NHibernate.Impl.SessionImpl [(null)] <(null)> - opened session 2007-07-04 17:01:08,701 [4092] DEBUG NHibernate.Loader.Custom.SQLCustomQuery [(null)] <(null)> - starting processing of sql query [exec sproc_GetAllTradesForStrategy :StrategyId] 2007-07-04 17:01:08,701 [4092] DEBUG NHibernate.Loader.Custom.SQLQueryReturnProcessor [(null)] <(null)> - mapping alias [tt] to entity-suffix [0_] 2007-07-04 17:01:08,732 [4092] DEBUG NHibernate.Impl.SessionImpl [(null)] <(null)> - flushing session 2007-07-04 17:01:08,732 [4092] DEBUG NHibernate.Impl.SessionImpl [(null)] <(null)> - Flushing entities and processing referenced collections 2007-07-04 17:01:08,732 [4092] DEBUG NHibernate.Impl.SessionImpl [(null)] <(null)> - Processing unreferenced collections 2007-07-04 17:01:08,732 [4092] DEBUG NHibernate.Impl.SessionImpl [(null)] <(null)> - scheduling collection removes/(re)creates/updates 2007-07-04 17:01:08,732 [4092] DEBUG NHibernate.Impl.SessionImpl [(null)] <(null)> - Flushed: 0 insertions, 0 updates, 0 deletions to 0 objects 2007-07-04 17:01:08,732 [4092] DEBUG NHibernate.Impl.SessionImpl [(null)] <(null)> - Flushed: 0 (re)creations, 0 updates, 0 removals to 0 collections 2007-07-04 17:01:08,732 [4092] DEBUG NHibernate.Impl.SessionImpl [(null)] <(null)> - dont need to execute flush 2007-07-04 17:01:08,747 [4092] DEBUG NHibernate.Impl.BatcherImpl [(null)] <(null)> - Opened new IDbCommand, open IDbCommands: 1 2007-07-04 17:01:08,747 [4092] DEBUG NHibernate.Impl.BatcherImpl [(null)] <(null)> - Building an IDbCommand object for the SqlString: exec sproc_GetAllTradesForStrategy ? 2007-07-04 17:01:08,747 [4092] DEBUG NHibernate.Loader.Loader [(null)] <(null)> - BindNamedParameters() 40 -> StrategyId [0] 2007-07-04 17:01:08,747 [4092] DEBUG NHibernate.Type.Int32Type [(null)] <(null)> - binding '40' to parameter: 0 2007-07-04 17:01:08,747 [4092] INFO NHibernate.Loader.Loader [(null)] <(null)> - exec sproc_GetAllTradesForStrategy @p0 2007-07-04 17:01:08,747 [4092] DEBUG NHibernate.SQL [(null)] <(null)> - exec sproc_GetAllTradesForStrategy @p0; @p0 = '40' 2007-07-04 17:01:08,747 [4092] DEBUG NHibernate.Connection.DriverConnectionProvider [(null)] <(null)> - Obtaining IDbConnection from Driver 2007-07-04 17:01:08,763 [4092] DEBUG NHibernate.Impl.BatcherImpl [(null)] <(null)> - Opened IDataReader, open IDataReaders: 1 2007-07-04 17:01:08,763 [4092] DEBUG NHibernate.Loader.Loader [(null)] <(null)> - processing result set 2007-07-04 17:01:08,763 [4092] DEBUG NHibernate.Loader.Loader [(null)] <(null)> - result set row: 0 2007-07-04 17:01:08,763 [4092] DEBUG NHibernate.Driver.NHybridDataReader [(null)] <(null)> - running NHybridDataReader.Dispose() 2007-07-04 17:01:08,763 [4092] DEBUG NHibernate.Impl.BatcherImpl [(null)] <(null)> - Closed IDataReader, open IDataReaders :0 2007-07-04 17:01:08,763 [4092] DEBUG NHibernate.Impl.BatcherImpl [(null)] <(null)> - Closed IDbCommand, open IDbCommands: 0 2007-07-04 17:01:08,763 [4092] DEBUG NHibernate.Impl.ConnectionManager [(null)] <(null)> - aggressively releasing database connection 2007-07-04 17:01:08,779 [4092] DEBUG NHibernate.Connection.ConnectionProvider [(null)] <(null)> - Closing connection 2007-07-04 17:01:08,779 [4092] DEBUG NHibernate.Util.ADOExceptionReporter [(null)] <(null)> - could not execute query [ exec sproc_GetAllTradesForStrategy ? ] Name: StrategyId - Value: 40
System.IndexOutOfRangeException: trd1_2_0_ at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName) at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name) at NHibernate.Driver.NHybridDataReader.GetOrdinal(String name) at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name) at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String[] names, ISessionImplementor session, Object owner) at NHibernate.Loader.Loader.GetKeyFromResultSet(Int32 i, ILoadable persister, Object id, IDataReader rs, ISessionImplementor session) at NHibernate.Loader.Loader.GetRowFromResultSet(IDataReader resultSet, ISessionImplementor session, QueryParameters queryParameters, LockMode[] lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects, EntityKey[] keys, Boolean returnProxies) 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) 2007-07-04 17:01:08,779 [4092] WARN NHibernate.Util.ADOExceptionReporter [(null)] <(null)> - System.IndexOutOfRangeException: trd1_2_0_ at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName) at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name) at NHibernate.Driver.NHybridDataReader.GetOrdinal(String name) at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name) at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String[] names, ISessionImplementor session, Object owner) at NHibernate.Loader.Loader.GetKeyFromResultSet(Int32 i, ILoadable persister, Object id, IDataReader rs, ISessionImplementor session) at NHibernate.Loader.Loader.GetRowFromResultSet(IDataReader resultSet, ISessionImplementor session, QueryParameters queryParameters, LockMode[] lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects, EntityKey[] keys, Boolean returnProxies) 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) 2007-07-04 17:01:08,779 [4092] ERROR NHibernate.Util.ADOExceptionReporter [(null)] <(null)> - trd1_2_0_ 2007-07-04 17:01:08,779 [4092] DEBUG NHibernate.Impl.ConnectionManager [(null)] <(null)> - after autocommit 2007-07-04 17:01:08,779 [4092] DEBUG NHibernate.Impl.ConnectionManager [(null)] <(null)> - aggressively releasing database connection 2007-07-04 17:01:08,779 [4092] DEBUG NHibernate.Impl.SessionImpl [(null)] <(null)> - transaction completion 2007-07-04 17:01:10,185 [4092] ERROR ml.smi.mds.server.dal.impl.dao.TTradeDAO`1[[ml.smi.mds.server.dal.data.TTrade, ml.smi.mds, Version=1.0.0.0, Culture=neutral, PublicKeyToken=55835b3f0ac4adfe]] [(null)] <(null)> - NHibernate.ADOException: could not execute query [ exec sproc_GetAllTradesForStrategy ? ] Name: StrategyId - Value: 40 [SQL: exec sproc_GetAllTradesForStrategy ?] ---> System.IndexOutOfRangeException: trd1_2_0_ at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName) at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name) at NHibernate.Driver.NHybridDataReader.GetOrdinal(String name) at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name) at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String[] names, ISessionImplementor session, Object owner) at NHibernate.Loader.Loader.GetKeyFromResultSet(Int32 i, ILoadable persister, Object id, IDataReader rs, ISessionImplementor session) at NHibernate.Loader.Loader.GetRowFromResultSet(IDataReader resultSet, ISessionImplementor session, QueryParameters queryParameters, LockMode[] lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects, EntityKey[] keys, Boolean returnProxies) 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) in C:\svn_project\uma_mds\trunk\mds\code\source\ml\smi\mds\server\dal\impl\dao\hibernate\TTradeDAOAdapter.cs:line 48
|