Executing storedprocedure through HIBERNATE in SPRING

[Vikas Kapoor]

Hello friends,

I am trying to execute very simple stored procedure using HIBERNATE in SPRING.

I refer this link in javaranch but couldn't solve the problem.

I paste the code snap here one by one:

1)This is my Login.hbm.xml file

    
<hibernate-mapping>
<class name="com.ans.config.Login" table="Login">
<id name="id" column="userid">
<generator class="identity"></generator>
</id>
<property name="username" column="username"></property>
<property name="password" column="password"></property>
</class>
<class name="com.ans.config.Product" table="product_master">
<id name="product_number">
<generator class="identity"></generator>
</id>
<property name="product_name"></property>
<property name="dispensed_quantity"></property>
<property name="sell_date" type="timestamp"></property>
</class>
<sql-query name="test_proc_sp" callable="true">
<return alias="paymentMaster" class="com.ans.config.Product">
<return-property name="product_name" column="product_name"></return-property>
<return-property name="product_number" column="product_number"></return-property>
<return-property name="dispensed_quantity" column="dispensed_quantity"></return-property>
<return-property name="sell_date" column="sell_date"></return-property>
</return>
{ call test_proc(?) }
</sql-query>
</hibernate-mapping>

2)DaoImplimentation file

    
 
public class ReportDaoImpl extends HibernateDaoSupport implements ReportDao {
 
public List reportDetails(String startDate, String endDate) {
Session sessionObject = getSession();
try{
Product product = (Product) sessionObject.getNamedQuery("test_proc_sp").list();
System.out.println("product_name:" + product.getProduct_name());
}catch(Exception e){
e.printStackTrace();
}
return null;
}

3)xxx-servlet mapping file (just part of file)

    
<bean id="dataSource"
class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName">
<value>sun.jdbc.odbc.JdbcOdbcDriver</value>
</property>
<property name="url">
<value>jdbc :-o	 dbc:ANS</value>
</property>
<property name="username">
<value>sa</value>
</property>
<property name="password">
<value></value>
</property>
</bean>
<bean id="sessionFactory"
class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
 
<property name="dataSource">
<ref bean="dataSource" />
</property>
 
<property name="mappingResources">
<list>
<value>/com/ans/config/Login.hbm.xml</value>
</list>
</property>
 
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">
org.hibernate.dialect.SQLServerDialect
</prop>
<prop key="hibernate.show_sql">true</prop>
</props>
</property>
</bean>

4)POJO ...
It is having four fields that which i return from stored procedure so i dont include the code.

5)Stored procedure

    
CREATE procedure test_proc
as
begin 
select product_number,product_name,dispensed_quantity,sell_date from product_master;

end
 

 

GO
 

I am using SQL server 2000...

When i run the application i could login (authentication through database so can connect to DB ) very well.

but getting this exception..

    
Hibernate: select login0_.userid as userid0_, login0_.username as username0_, login0_.password as password0_ from Login login0_ where login0_.username=? and login0_.password=?
Hibernate: { ? = call test_proc(?) }
[B]org.hibernate.exception.SQLGrammarException: could not execute query[/B]
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:2148)
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:111)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1655)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:164)
at com.ans.dao.ReportDaoImpl.reportDetails(ReportDaoImpl.java:17)
at com.ans.service.ReportServiceImpl.reportDetails(ReportServiceImpl.java:12)
at com.ans.web.ManageReportController.handelReport(ManageReportController.java:76)
at com.ans.web.ManageReportController.handleRequestInternal(ManageReportController.java:89)
at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:139)
at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:44)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:684)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:625)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:392)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:347)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Unknown Source)
[B]Caused by: java.sql.SQLException: [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error[/B]at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLExecute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(Unknown Source)
at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:169)
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:2145)
... 32 more
 

I feel that i am near to solve it..

thanks

Vishal Pandya

[Nathan Hook]

It looks as though your query is not working quite right. I don't see the issue, but do a google search on "COUNT field incorrect or syntax error". Most of the results point toward a query syntax error.

Best of luck.

[Jaikiran Pai]

I personally, havent used stored proceedures with Hibernate. I looked up the
Hibernate Documentation and it mentioned:

Recommended call form is standard SQL92: { ? = call functionName(<parameters>) } or { ? = call procedureName(<parameters>}.

However, your mapping file has:

{ call test_proc(?) }

Shouldnt this have been

{ ? = call test_proc() }

And by the way, as per one of the documents that i saw, the "COUNT field incorrect or syntax error" occurs because

You are trying to insert a record into a database field that has a question mark in its field name.

[ August 30, 2007: Message edited by: Jaikiran Pai ]

[Vikas Kapoor]

Originally posted by Nathan Hook:
It looks as though your query is not working quite right. I don't see the issue, but do a google search on "COUNT field incorrect or syntax error". Most of the results point toward a query syntax error.

Best of luck.

hello Nathan,

Thanks for your suggestion and i did google for it. and i did scan the query syntax again but the same problem persist.

Vishal Pandya

[Vikas Kapoor]

hi Jaikiran Pai ,

Thanks for your reply...

1)
I tried the way you suggested { ? = call test_proc() } but getting the same exception.


2)

You are trying to insert a record into a database field that has a question mark in its field name.

I don't have any field containing ? that you would have checked from Login.hbm.xml file.

Vishal Pandya

[Vikas Kapoor]

Hello friends ,

I am also expecting some expert comments from our moderators for ORM Paul Sturrock, Mark Spritzler for this problem.

Thanks,
Vishal Pandya