Hello All, Can anyone please help me to solve this problem. I am using the stored procedures from SQL Server 2000, through hibernate.

Hibernate version 3.0

Code:

    <sql-query name="FinalResultProc" callable= "true">
       <return alias="ResultsReview" class="com.command.hibernate.ResultsReview">
           <return-property name="ID" column="ID" />
           <return-property name="reJobId" column="ReJobId" />
        </return>
        { ? = call FinalResultProc() }
    </sql-query>

Code:

Query query = session.getNamedQuery("FinalResultProc");
            session.flush();
            tx.commit();
            return query.list();

java.lang.RuntimeException: org.hibernate.exception.SQLGrammarException: could not execute query
com.command.hibernate.FinalResultReviewService.getResultsReviewdataList(FinalResultReviewService.java:148)
org.apache.jsp.jsp.ResultAdded_jsp._jspService(org.apache.jsp.jsp.ResultAdded_jsp:102)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.struts.action.RequestProcessor.doForward(RequestProcessor.java:1069)
org.apache.struts.action.RequestProcessor.processForwardConfig(RequestProcessor.java:455)
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:279)
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

SQl Server 2000

Code:

CREATE PROCEDURE dbo.FinalResultProc  AS

declare @a varchar,@b varchar,@c varchar,@d varchar,@e varchar,@f varchar,@g varchar, @h varchar, @i varchar
DECLARE @GUID uniqueidentifier
SET @GUID = NEWID()
DECLARE get_cursor CURSOR FOR

select * from DNNResults

OPEN get_cursor
 
FETCH NEXT FROM get_cursor INTO @a,@b,@c,@d,@e,@f,@g, @h, @i

WHILE @@FETCH_STATUS = 0

BEGIN

Insert into ResultsReview values(@GUID,@a,@b,@c,@d,@e,@f,@g, @h, @i)
FETCH NEXT FROM get_cursor
    INTO @a,@b,@c,@d,@e,@f,@g, @h, @i

END

CLOSE get_cursor

DEALLOCATE get_cursor

select * from ResultsReview
GO

Can anyone please let me know where I am going wrong.

Is there a nested exception that you didn't post? "could not execute query" is pretty non-specific. If there isn't a nested exception, can you include the SQL and bindings (log4j.logger.org.hibernate.type=DEBUG).

Does the stored procedure run from query analyzer?

It shouldn't make a difference, but that looks like an awfully complicated way of inserting the contents of one table into another. Could you get rid of the cursor and replace it withYou may have to list the columns from DNNResults, can't remember. But that cursor seems way over the top, to me...

I faced same problem, please help me out.Stored procedure gave corrct result in sql server

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


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-scalar column="PORTALSESSION" type="java.lang.String"/>
{? = call sp_test1() }
</sql-query>


or


<resultset name="xxx">
<return alias="per" class="com.utc.hibernate.model.Usermetrics">
</return>
</resultset>
<sql-query name="sp_test3" resultset-ref="xxx" callable="true">
{ ? = 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)

mohanapriya, your problem is that that's incorrect SQL. You don't need a cursor, and you don't need an out parameter, either. Even if you were to use a cursor, you can't return it through JDBC: how would you fetch next from it? Or close/deallocate it? To work with hibernate, just use the plain vanilla select statement: so long as the stored proc only has one resultant result set, hibernate will be able to handle it. If you want to be able to iterate over the result set, just use Hibernate's scroll method, you don't need cursors.
As an aside, the convention is to use sp_ for "global" stored procs, that you put in your master database. Normal stored procs are named stpXXX, traditionally.

We have also tried without using a cursor.
We tried with a stored procedure which returns a result set, but it did not work.

CREATE procedure sp_test1
as select * from usermetrics
GO

Well nothing else is going to work. So long as your resultset includes a column with the correct name (PORTALSESSION, obviously) then it will work.

As per our disscusion i used below code, i got error
****************************
Stored procedure

create procedure sptacs1
as
begin
select portalsession,id from usermetrics
end

GO
****************************
XML code

<resultset name="xxx">
<return alias="per" class="com.utc.hibernate.model.Usermetrics">

</return>
</resultset>
<sql-query name="sp_test3" resultset-ref="xxx" callable="true">
{ ? = call sptacs1() }
</sql-query>

****************************
java code

Query query = hbSession.getNamedQuery("sp_test3");
System.out.println("Result"+query.scroll());

*****************************
exception

Exception:
org.hibernate.exception.SQLGrammarException: could not execute query using scroll
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 )

The base exception isThere's only one binding in your statement:Apparently, your stored proc isn't returning anything. It doesn't have to return anything special, though the convention is to return the number of lines in the result set. Change your proc to this:

Hello Tenwit,

Thanks for the reply. I am still unable to recover from the error. Can you please help me further, I have followed your suggestions and changed the code.

Here is the changed Stored Procedure from SQL Server 2000 as per suggestion. This is working fine when executed seperately.



Here is the mapping function for this Stored Procedure:



And here is where the error occurs:



The error is occurring when I execute query.list()

The following is the error:
java.lang.RuntimeException: org.hibernate.exception.SQLGrammarException: could not execute query
com.command.hibernate.FinalResultReviewService.getResultsReviewdataList(FinalResultReviewService.java:148)
org.apache.jsp.jsp.ResultAdded_jsp._jspService(org.apache.jsp.jsp.ResultAdded_jsp:102)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.struts.action.RequestProcessor.doForward(RequestProcessor.java:1069)
org.apache.struts.action.RequestProcessor.processForwardConfig(RequestProcessor.java:455)
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:279)
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802) [/code]

There's a nested exception that comes with SQLGrammarException, can you post that too?

Is the ResultsReview class mapped as having just two properties? So it has methods setID() and setReJobId()? You must mention every property of the class you're creating, when you're fetching from a stored proc.

Thanks for the reply Tenwit.

NO. I wasnt marpping all the properties of the ResultsReview Class. I have changed the mapping now to the following":



But still I am getting the same error. I am using eclipse and myeclipseide, I am unable to locate the nested exception, can you please tell me where can I find one....

Thanks a lot for your help

It'll be printed out immediately after the one you posted earlier, preceded by "Caused by: ". It'll probably be an SQLException.

No. This is what I get.

root cause

java.lang.RuntimeException: org.hibernate.exception.SQLGrammarException: could not execute query
com.command.hibernate.FinalResultReviewService.getResultsReviewdataList(FinalResultReviewService.java:150)
org.apache.jsp.jsp.ResultAdded_jsp._jspService(org.apache.jsp.jsp.ResultAdded_jsp:102)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.struts.action.RequestProcessor.doForward(RequestProcessor.java:1069)
org.apache.struts.action.RequestProcessor.processForwardConfig(RequestProcessor.java:455)
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:279)
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

note The full stack trace of the root cause is available in the Apache Tomcat/5.5.11 logs.
Apache Tomcat/5.5.11

I try to trace the logs, but could not find the error report. Can you please help me with this. Thanks.

You could turn on SQL output (hibernate.show_sql=true in your properties file or <property name="hibernate.show_sql">true</property> in your config file), then go through the last few SQL statements it issues looking for bad SQL grammar. Once you track it down, you can figure out where it came from.

Hi,

Thanks for your reply. But i got same error.This is my procedure now

create procedure sptacs1
as
begin
select portalsession,id from usermetrics
return 0
end