Hi I´m developing an application using HIBERNATE 3
calling a database in MS SQL SERVER 200
well I have a problem when I call a stored procedure
I read in the documentation an example of a stored procedure that returns a cursor but my stored procedure returns a name

CREATE FUNCTION FuncionUnica3()
RETURNS varchar(10)
AS
BEGIN
DECLARE @i varchar(10)
SET @i = 'Rosa'
RETURN (@i)
END


the stored procedure in the mapping is

<sql-query callable="true" name="NameDocente" >
<return alias="docente" class="Docente">
<return-property name="strNombre" column="NOMBRES"/>
</return>
{ ? = call FuncionUnica3() }
</sql-query>


in DAOHibernate

public void getDocentes3() {
List listDocente;
try {
listDocente = getSession().getNamedQuery("NameDocente").list();
System.out.println("el size de la lista devuelta es "+listDocente.size());
System.out.println("el valor del pos 0 es "+listDocente.get(0));
}
catch (Exception e) {
System.out.println("el Error es el named query 3 docenteDAOHibernate es: "+ e.getMessage());
}
}

When I executed an errors ocurr

DEBUG [http-80-Processor25] (TransactionAspectSupport.java:212) - Getting transaction for edu.centrum.service.DocenteManager.getDocentes3
DEBUG [http-80-Processor25] (AbstractPlatformTransactionManager.java:205) - Using transaction object [org.springframework.orm.hibernate3.HibernateTransactionManager$HibernateTransactionObject@dc5733]
DEBUG [http-80-Processor25] (AbstractPlatformTransactionManager.java:287) - Creating new transaction
DEBUG [http-80-Processor25] (SessionFactoryUtils.java:324) - Opening Hibernate session
DEBUG [http-80-Processor25] (SessionImpl.java:272) - opened session at timestamp: 4680131992313856
DEBUG [http-80-Processor25] (HibernateTransactionManager.java:377) - Opened new session [SessionImpl(PersistenceContext[entityKeys=[],collectionKeys=[]];ActionQueue[insertions=[] updates=[] deletions=[] collectionCreations=[] collectionRemovals=[] collectionUpdates=[]])] for Hibernate transaction
DEBUG [http-80-Processor25] (JDBCContext.java:115) - opening user JDBC connection, application must close it
DEBUG [http-80-Processor25] (DataSourceUtils.java:176) - Setting JDBC connection [C3P0ProxyConnection [Invocation Handler: com.mchange.v2.c3p0.impl.C3P0PooledConnection$ProxyConnectionInvocationHandler@9d1714]] read-only
DEBUG [http-80-Processor25] (SessionImpl.java:1230) - setting flush mode to: NEVER
DEBUG [http-80-Processor25] (JDBCTransaction.java:54) - begin
DEBUG [http-80-Processor25] (ConnectionManager.java:313) - opening JDBC connection
DEBUG [http-80-Processor25] (JDBCTransaction.java:59) - current autocommit status: true
DEBUG [http-80-Processor25] (JDBCTransaction.java:62) - disabling autocommit
DEBUG [http-80-Processor25] (JDBCContext.java:202) - after transaction begin
DEBUG [http-80-Processor25] (HibernateTransactionManager.java:419) - Exposing Hibernate transaction as JDBC transaction [C3P0ProxyConnection [Invocation Handler: com.mchange.v2.c3p0.impl.C3P0PooledConnection$ProxyConnectionInvocationHandler@9d1714]]
DEBUG [http-80-Processor25] (TransactionSynchronizationManager.java:154) - Bound value [org.springframework.jdbc.datasource.ConnectionHolder@9bc36d] for key [com.mchange.v2.c3p0.ComboPooledDataSource@1a544a5] to thread [http-80-Processor25]
DEBUG [http-80-Processor25] (TransactionSynchronizationManager.java:154) - Bound value [org.springframework.orm.hibernate3.SessionHolder@3b5587] for key [org.hibernate.impl.SessionFactoryImpl@11fc6b2] to thread [http-80-Processor25]
DEBUG [http-80-Processor25] (TransactionSynchronizationManager.java:206) - Initializing transaction synchronization
DEBUG [http-80-Processor25] (TransactionSynchronizationManager.java:129) - Retrieved value [org.springframework.orm.hibernate3.SessionHolder@3b5587] for key [org.hibernate.impl.SessionFactoryImpl@11fc6b2] bound to thread [http-80-Processor25]
DEBUG [http-80-Processor25] (QueryPlanCache.java:111) - unable to locate native-sql query plan in cache; generating ({ ? = call FuncionUnica3() })
DEBUG [http-80-Processor25] (SessionImpl.java:1597) - SQL query: { ? = call FuncionUnica3() }
DEBUG [http-80-Processor25] (AbstractBatcher.java:311) - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
DEBUG [http-80-Processor25] (AbstractBatcher.java:346) - { ? = call FuncionUnica3() }
Hibernate: { ? = call FuncionUnica3() }
DEBUG [http-80-Processor25] (AbstractBatcher.java:424) - preparing statement
DEBUG [http-80-Processor25] (AbstractBatcher.java:319) - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
DEBUG [http-80-Processor25] (AbstractBatcher.java:470) - closing statement
DEBUG [http-80-Processor25] (JDBCExceptionReporter.java:63) - could not execute query [{ ? = call FuncionUnica3() }]
java.sql.SQLException: Parameter #1 has not been set.
at net.sourceforge.jtds.jdbc.TdsCore.executeSQL(TdsCore.java:994)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:436)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.execute(JtdsPreparedStatement.java:459)
at com.mchange.v2.sql.filter.FilterCallableStatement.execute(FilterCallableStatement.java:402)
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(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2150)
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 edu.centrum.daos.hibernate.DocenteDAOHibernate.getDocentes3(DocenteDAOHibernate.java:74)
at edu.centrum.service.impl.DocenteManagerImpl.getDocentes3(DocenteManagerImpl.java:83)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:310)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:155)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:122)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:57)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:144)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:174)
at $Proxy1.getDocentes3(Unknown Source)
at edu.centrum.acciones.ListarDocenteAccion.execute(ListarDocenteAccion.java:71)
at org.springframework.web.struts.DelegatingActionProxy.execute(DelegatingActionProxy.java:105)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:534)
WARN [http-80-Processor25] (JDBCExceptionReporter.java:71) - SQL Error: 0, SQLState: 07000
ERROR [http-80-Processor25] (JDBCExceptionReporter.java:72) - Parameter #1 has not been set.
el Error es el named query 3 docenteDAOHibernate es: could not execute query



some idea of what is happening?
thanks a lot

NANCY

[/b]

the docs also states the stored procedure *must* return a resultset to be usable from within hibernate.

if you just want a name returned then just use session.connection() no need for any hibernate special mapping

well thats means that i can´t make stored procedures that returns only a simple value?
when you say that only returns a result set it means that only returns a cursor?

thanks a lot
NANCY

yes and yes.

but it does not prevent you from calling stored procedures that reutnrs simple values via normal jdbc api...

I am investigating the viability of using NHibernate.

You can use NHibernate stored procedures with NHibernate by creating a mapping for the stored procedure and then calling through HQL.

If HQL is not flexible enough to do something then you don't use HQL and switch over to normal conventional methods?


I think using hibernate to create a transparency between the domain model and the database is fantastic but if in the end , you end up laying the ground work for a great facade and then still have conventional stuff it defeats the purpose.

you are not doing a mapping to anything else than a integer! That does not require all this stuff, why *force* people to use the facade for that ?

you are still using the same connection etc. as hibernate.