I'm using Hibernate 3.1 with DB2.

I’m trying, without any success, to use a stored procedure (SP), which returns two result sets.

With Hibernate, I’m able to use a SP which returns one result set without any problem.

With JDBC, I’m able to use a SP which returns more than one result set and access all the result sets. But I’m not able to map the two result sets to two Java classes using Hibernate.

Anyone is using SP which returns more than one result set with Hibernate?

François J.

not supported.

Is there any plan in supporting this feature in the future?

If I’m interested in adding support for this feature where can I start?

Thank You.

François J.

start by figuring out how you would actually map it ;)

It’s a naive initiative, but let’s image a store procedure, SELECT_PERSON_ADDRESS, which need two input arguments and returns two result sets. I would imagine something like this to do the mapping.

<sql-query name="SELECT2_WT_HOSPITAL_SP" callable="true">
<!-- First result set -->
<return alias="per" class="com.something.persistence.Person">
<return-property name="id" column="ID" />
<return-property name="name" column="NAME" />
<return-property name="father" column="Father" />
</return>
<!-- Second result set -->
<return alias="add" class="com.something.persistence.Address">
<return-property name="id" column="ID" />
<return-property name="address" column="ADDRESS" />
<return-property name="zip" column="ZIP" />
<return-property name="phone" column="PHONE" />
</return>
{ call SELECT_PERSON_ADDRESS(?, ?) }
</sql-query>

I understand that it’s not now implemented but if it may be done, I would like to help and have some hints on where to start.

François J.

I got problem while calling stored procedure.Please give solution.

I am using hibernate3 version for executing stored procdure in SQLserver 2000.

I have started working on this by learining from www.hibernate.org.

I am getting below error and could not rectify from few days.
Could any one add your comments for Stored procedure execution for SQL
server 2000.
Where can i get information regarding execution of Stored procedures for
SQL server in hibernat3

-------------------------------------------------
XML Mapping for Calling Stored Procedure

<sql-query name="sp_test1" callable="true">
<return alias="per" class="com.utc..hibernate.model.Usermetrics">
<return-property name="portalsession" column="PORTALSESSION" />
</return>
{? = call sp_test1() }
</sql-query>

----------------------------------------------
SQL Server Stored Procedure

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER PROC sp_test1 @sowcursor cursor varying OUT AS

DECLARE s CURSOR
LOCAL
FOR SELECT PORTALSESSION FROM acs.dbo.Usermetrics
OPEN s
SET @sowcursor=s
RETURN(0)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-------------------------------------------------

Calling procedure in DAO

hbSession = (Session)CSessionFactory.getSession();
Query query = (hbSession.getNamedQuery("sp_test1");
List results = query.list();
-----------------------------------------------

Exception:
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.j
ava:65)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelp
er.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2153)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:117)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1607)

at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:
121)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:169)
at com.utc.pw.acs.hibernate.dao.CMainMenuDAO.getMetrics(CMainMenuDAO.jav
a:76)
at com.utc.pw.acs.action.CMainMenuAction.loadMainMenu(CMainMenuAction.ja va:203)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl. java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces sorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchActio n.java:274)
at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java: 194)
at org.apache.struts.action.RequestProcessor.processActionPerform(Reques tProcessor.java:419)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:224)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletReque
stDispatcher.java:765)
at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(Ser
vletRequestDispatcher.java:317)
at com.evermind.server.http.ServletRequestDispatcher.forward(ServletRequ
estDispatcher.java:220)
at com.evermind.server.http.GetParametersRequestDispatcher.forward(GetPa
rametersRequestDispatcher.java:257)
at org.apache.struts.action.RequestProcessor.doForward(RequestProcessor. java:1062)
at org.apache.struts.action.RequestProcessor.processForwardConfig(RequestProcessor.java:386)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:229)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletReque
stDispatcher.java:765)
at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(Ser
vletRequestDispatcher.java:317)
at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:790)
at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.ja va:270)
at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:112)
at com.evermind.util.ReleasableResourcePooledExecutor$MyWorker.run(Relea
sableResourcePooledExecutor.java:186)
at java.lang.Thread.run(Thread.java:534)
Caused by: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
JDBC]Inv
alid parameter binding(s).
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source )
at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.validateParameters(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.validateParameters(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.preImplExecute(UnknownSource)
at com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeInternal(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.execute(Unknown Source)
at org.hibernate.dialect.SybaseDialect.getResultSet(SybaseDialect.java:149)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:146)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1666)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Lo ader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2150)