Hi,
I am facing one problem i.e How to call stored procedure which is internally calling stored procedure with output parameters from Hibernate3.x.
I have converted internal stored procedure as function, It is working fine. But I don't want disturb existing one, and
some procedures calling other stored procedures having multiple register out parameters.

Please help me. It is urgent.

The user manual has helped many urgent people. Check it out under Native Query section.

I have gone through that , But could not get any help.

I am trying to call stored procedure which is calling other stored procedure in Hibernate.
Is It possible with Hibernate3.x ?

If the internal stored procedure is not having any output params, Then I can able to call.
But , When the internal stored procedure is having multiple outparams , It is giving Exception.

In the manual , They have mentioned to use session.connection(). But I want It to be happen through HibernateTemplate of the Spring.

Hibernate have no problem invoking store procedure. You have particular problem, try post your code with stacktrace.

Read sticky post on how to ask better question.

CREATE PROCEDURE dbo.getmoduledetails
(
@flag int,
@groupid int
)
as
set nocount on
declare @query2 varchar(1000),
@accessprofile varchar(128),
@accprodecimal varchar(128),
@operatorid int
begin
if ( @flag = 7 ) -- this will return the resultset for menus selected for a user.
begin
set @operatorid = @groupid

exec getuseraccessprofilebinary @operatorid, @accessprofile output
set @accprodecimal = convert(varchar(100), dbo.getdecimalofbinary(@accessprofile))

select @query2 = 'select moduledesc, moduletypeid, messagekey, actionmapping from groupmodulesm
where moduleid in ('+dbo.getmenuidforaccessprofile(@accprodecimal)+') and menuitem = ''Y'' order by moduletypeid, sequence'

exec (@query2)
end
end
GO
The line which I have made as red color , there I am getting following Exception
exec getuseraccessprofilebinary @operatorid, @accessprofile output
here @accessprofile varibale is getting empty value.

Hibernate: {call getmoduledetails(?,?)}
Sep 14, 2010 10:57:41 AM org.hibernate.type.NullableType nullSafeGet
INFO: could not read column value from result set: moduletypeid; S0022: Invalid column name 'moduletypeid'.
Sep 14, 2010 10:57:41 AM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 0, SQLState: S0022
Sep 14, 2010 10:57:41 AM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: S0022: Invalid column name 'moduletypeid'.
Sep 14, 2010 10:57:41 AM com.hb1.EventCollection main
WARNING: Closing Resources.....
Sep 14, 2010 10:57:41 AM org.hibernate.impl.SessionFactoryImpl close
INFO: closing
Sep 14, 2010 10:57:41 AM org.hibernate.connection.DriverManagerConnectionProvider close
INFO: cleaning up connection pool: jdbc:sybase:Tds:192.9.200.118:5000/iobmb
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2223)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
at com.hb1.EventCollection.main(EventCollection.java:78)
Caused by: java.sql.SQLException: S0022: Invalid column name 'moduletypeid'.
at com.sybase.jdbc2.jdbc.ErrorMessage.raiseError(ErrorMessage.java:487)
at com.sybase.jdbc2.tds.TdsResultSet.findColumnByLabel(TdsResultSet.java:461)
at com.sybase.jdbc2.jdbc.SybResultSet.findColumn(SybResultSet.java:505)
at com.sybase.jdbc2.jdbc.SybResultSet.getInt(SybResultSet.java:338)
at org.hibernate.type.IntegerType.get(IntegerType.java:28)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:163)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:154)
at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:1097)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:565)
at org.hibernate.loader.Loader.doQuery(Loader.java:701)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
... 7 more
Java Result: 1

following is the mapping definition
<class name="MenuDetails" >
<id name="moduleID" />
<property name="moduleDesc" />
<property name="messageKey"/>
<property name="messageAction"/>
</class>
<sql-query name="GET_GROUP_MODULE_DETAILS" callable="true">
<return alias="menuDetails" class="MenuDetails">
<return-property name="moduleID" column="moduletypeid"/>
<return-property name="moduleDesc" column="moduledesc"/>
<return-property name="messageKey" column="messagekey"/>
<return-property name="messageAction" column="actionmapping"/>
</return>
{call getmoduledetails(:flag,:operatorID)}
</sql-query>

I am not an MSSQL user, but you do want to ensure that the stored proc you called from Hibernate DOES return a result set. The error indicate that the stored proc Hibernate called returned a result set without the columns you specified in the mapping.

Looking at your store proc, not sure why you want to store your sql string into variable and then call a exec. Can't you just execute that select as last statement without the exec? Give that a try and see. Again I am not a MSSQL user, so can't help you there.

Also, to clearify, there is no difference between an internal store proc or external one. To hibernate, a stored proc is just a native sql it executes, it doesn't care what you do inside there. But there is some general rules that stored proc MUST follow in other for Hibernate to use it. Read http://docs.jboss.org/hibernate/stable/ ... /#sp_query

Hi ,

Thank You for your reply.
It is Sybase. We have some restriction in sybase.
The third point, you have mentioned is right. I to dnt know why hibernate is worrying abt internal stored procedure.

Hi ,

Thank You for your reply.
It is Sybase. We have some restriction in sybase.

The third point, you have mentioned is right. I to dnt know why hibernate is worrying abt internal stored procedure.

Hi ,

Thank You for your reply.
It is Sybase. We have some restriction in sybase.

The third point, you have mentioned is right. I to dnt know why hibernate is worrying abt internal stored procedure.