Hibernate Books

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 6 posts ] 
Author Message
 Post subject: Output Parameters in Stored Procedures
PostPosted: Thu Nov 16, 2006 4:01 am 
Newbie

Joined: Mon Oct 30, 2006 1:23 am
Posts: 1
Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Beta 1.2.0

How do I retrieve output parameters of a stored procedure in my code?
I am using MS SQL 2005, if anybody could give me the exact syntax of calling a stored procedure with output parameters, it would be really helpful.


Problems with Session and transaction handling?

Read this: http://hibernate.org/42.html


Top
 Profile  
 
 Post subject:
PostPosted: Thu Nov 16, 2006 4:18 am 
Contributor
Contributor

Joined: Wed May 11, 2005 4:59 pm
Posts: 1766
Location: Prague, Czech Republic
NHibernate does not support it.


Top
 Profile  
 
 Post subject: NHibernate Stored Procedures Example
PostPosted: Wed Jul 04, 2007 10:45 am 
Beginner
Beginner

Joined: Fri May 25, 2007 5:55 am
Posts: 26
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.


Top
 Profile  
 
 Post subject: NHibernate Stored Procedures Example
PostPosted: Wed Jul 04, 2007 10:48 am 
Beginner
Beginner

Joined: Fri May 25, 2007 5:55 am
Posts: 26
I am using NHibernate 1.2.0 GA.

Also the stored proc that I am calling is actually sproc_HibGetAllTradesForStrategy so dont worry about that. The error is the same whether I call a stored proc on the SQL Server Database or the hibernate stored proc in the hbm.xml file.

Any help much appreciated. It will also server as a good example to other people out there as there are few full examples of this out there.


Top
 Profile  
 
 Post subject: NHibernate Stored Procedures Example
PostPosted: Wed Jul 04, 2007 11:01 am 
Beginner
Beginner

Joined: Fri May 25, 2007 5:55 am
Posts: 26
This might also help. More of the Hibernate Log.

2007-07-04 15:26:14,621 [3936] DEBUG NHibernate.Impl.SessionFactoryImpl [(null)] <(null)> - Instantiated session factory
2007-07-04 15:26:17,497 [3936] DEBUG NHibernate.Impl.SessionImpl [(null)] <(null)> - opened session
2007-07-04 15:26:18,904 [3936] DEBUG NHibernate.Loader.Custom.SQLCustomQuery [(null)] <(null)> - starting processing of sql query [exec sproc_GetAllTradeForStrategy(:StrategyId)]
2007-07-04 15:26:18,904 [3936] DEBUG NHibernate.Loader.Custom.SQLQueryReturnProcessor [(null)] <(null)> - mapping alias [tt] to entity-suffix [0_]
2007-07-04 15:26:18,919 [3936] DEBUG NHibernate.Impl.SessionImpl [(null)] <(null)> - flushing session
2007-07-04 15:26:18,919 [3936] DEBUG NHibernate.Impl.SessionImpl [(null)] <(null)> - Flushing entities and processing referenced collections
2007-07-04 15:26:18,919 [3936] DEBUG NHibernate.Impl.SessionImpl [(null)] <(null)> - Processing unreferenced collections
2007-07-04 15:26:18,919 [3936] DEBUG NHibernate.Impl.SessionImpl [(null)] <(null)> - scheduling collection removes/(re)creates/updates
2007-07-04 15:26:18,919 [3936] DEBUG NHibernate.Impl.SessionImpl [(null)] <(null)> - Flushed: 0 insertions, 0 updates, 0 deletions to 0 objects
2007-07-04 15:26:18,919 [3936] DEBUG NHibernate.Impl.SessionImpl [(null)] <(null)> - Flushed: 0 (re)creations, 0 updates, 0 removals to 0 collections
2007-07-04 15:26:18,935 [3936] DEBUG NHibernate.Impl.SessionImpl [(null)] <(null)> - dont need to execute flush
2007-07-04 15:26:18,935 [3936] DEBUG NHibernate.Impl.BatcherImpl [(null)] <(null)> - Opened new IDbCommand, open IDbCommands: 1
2007-07-04 15:26:18,935 [3936] DEBUG NHibernate.Impl.BatcherImpl [(null)] <(null)> - Building an IDbCommand object for the SqlString: exec sproc_GetAllTradeForStrategy(?)
2007-07-04 15:26:18,950 [3936] DEBUG NHibernate.Loader.Loader [(null)] <(null)> - BindNamedParameters() 40 -> StrategyId [0]
2007-07-04 15:26:18,950 [3936] DEBUG NHibernate.Type.Int32Type [(null)] <(null)> - binding '40' to parameter: 0
2007-07-04 15:26:18,950 [3936] INFO NHibernate.Loader.Loader [(null)] <(null)> - exec sproc_GetAllTradeForStrategy(@p0)
2007-07-04 15:26:18,950 [3936] DEBUG NHibernate.SQL [(null)] <(null)> - exec sproc_GetAllTradeForStrategy(@p0); @p0 = '40'
2007-07-04 15:26:18,950 [3936] DEBUG NHibernate.Connection.DriverConnectionProvider [(null)] <(null)> - Obtaining IDbConnection from Driver
2007-07-04 15:26:18,966 [3936] DEBUG NHibernate.Util.ADOExceptionReporter [(null)] <(null)> - SQL Exception
System.Data.SqlClient.SqlException: Incorrect syntax near '@p0'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)


Based on this it does not seem to like the way that the parameter 0 (which has a value of 40) is called @p0.

When it makes the call to the Procedure it does so like this.

exec sproc_GetAllTradeForStrategy(@p0); @p0 = '40'

And then we get the error. Is this an NHibernate Bug ?

thanks,
Richie.


Top
 Profile  
 
 Post subject: Hibernate Stored Proc Example Question
PostPosted: Wed Jul 04, 2007 11:35 am 
Beginner
Beginner

Joined: Fri May 25, 2007 5:55 am
Posts: 26
I can call the Proc now with the following command in the sql-query after playing around with it for a bit.

Code:
exec sproc_GetAllTradesForStrategy :StrategyId


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 6 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.