Common approach to stored procedure

[gmatthews]

Check out TransactionFactoryProxyBean

http://www.springframework.org/docs/...ctoryBean.html

If you declare one of these and base your business logic beans on it, Spring will take care of commit/rollback in a way similar to J2EE CMT.

Sounds like you should also read the manual. All of this stuff in is there.

[dshravankumar]

Now, what if the StoredProcedure returns a "ResultSet" rather than just a single parameter. What changes are required to be done?

[dshravankumar]

Correct me if i am wrong. To me it looks like the approach discussed above is the Spring way of invoking the StoredProcedures on the Persistance Layer. This approach can be implemented using either Hibernate2 or Hibernate3. But, it is said that Hibernate3 introduces special ways of handling StoredProcedures. I believe the approach discussed above does not take advantage of the support Hibernate3 has for StoredProcedures.

Look at this link. http://www.hibernate.org/hib_docs/v3...ngle/#sp_query

What is the complete way of using StoredProcedures with Hibernate3 ?

[clonedcatb]

I have been working with CallableStatementFactory and have come up with a similar approach to stored procedures and templatizing it. I'm pretty sure that this example doesn't use hibernate to access the stored proc, but it uses the data source directly.

[vijig]

I have a oracle stored procedure that I am calling which returns a cursor
and I am doubtful about what to use when i use SqlOutParameter for declaring a Parameter in the constructor.

Any suggestions?

Thanks!

[chanaka]

Hi,

I have tried the same way.. but parameters are not correctly passed to procedure.. I'm trying to invoke procedure inside a package.. All the parameters seems to have null values after passing.. Does any one have same experience? Please help..

[MPatric]

I followed the same approach (Refering to jakim8915 post) which you have mentioned, in calling the stored procedure, it throws the following exception
(I have created Prcedure "getStateName" which takes one parameter (StateID) and should return State Name.). Can you please tell me what is that I am missing. Thanks in advance.

Exception:
org.springframework.dao.TransientDataAccessResourc eException: CallableStatementCallback; SQL [{call getStateName(?)}]; Callable statments not supported.; nested exception is java.sql.SQLException: Callable statments not supported.
at org.springframework.jdbc.support.SQLStateSQLExcept ionTranslator.doTranslate(SQLStateSQLExceptionTran slator.java:106)
at org.springframework.jdbc.support.AbstractFallbackS QLExceptionTranslator.translate(AbstractFallbackSQ LExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackS QLExceptionTranslator.translate(AbstractFallbackSQ LExceptionTranslator.java:80)
at org.springframework.jdbc.support.AbstractFallbackS QLExceptionTranslator.translate(AbstractFallbackSQ LExceptionTranslator.java:80)
at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:952)
at org.springframework.jdbc.core.JdbcTemplate.call(Jd bcTemplate.java:985)
at org.springframework.jdbc.object.StoredProcedure.ex ecute(StoredProcedure.java:117)
at com.wipro.daoImpl.CallPocedure.execute(CallPocedur e.java:33)
at com.wipro.daoImpl.CallProcedureDaoImpl.getStateNam e(CallProcedureDaoImpl.java:32)
at com.wipro.serviceImpl.CallProcedureServiceImpl.get StateName(CallProcedureServiceImpl.java:27)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknow n Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Un known Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.springframework.aop.support.AopUtils.invokeJoi npointUsingReflection(AopUtils.java:307)
at org.springframework.aop.framework.ReflectiveMethod Invocation.invokeJoinpoint(ReflectiveMethodInvocat ion.java:182)
at org.springframework.aop.framework.ReflectiveMethod Invocation.proceed(ReflectiveMethodInvocation.java :149)
at org.springframework.transaction.interceptor.Transa ctionInterceptor.invoke(TransactionInterceptor.jav a:106)
at org.springframework.aop.framework.ReflectiveMethod Invocation.proceed(ReflectiveMethodInvocation.java :171)
at org.springframework.aop.interceptor.ExposeInvocati onInterceptor.invoke(ExposeInvocationInterceptor.j ava:89)
at org.springframework.aop.framework.ReflectiveMethod Invocation.proceed(ReflectiveMethodInvocation.java :171)
at org.springframework.aop.framework.JdkDynamicAopPro xy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy1.getStateName(Unknown Source)
at com.wipro.client.CallProcedureClient.getStateName( CallProcedureClient.java:25)
at com.wipro.client.CallProcedureClient.main(CallProc edureClient.java:36)
Caused by: java.sql.SQLException: Callable statments not supported.
at com.mysql.jdbc.Connection.prepareCall(Connection.j ava:1287)
at org.springframework.jdbc.core.CallableStatementCre atorFactory$CallableStatementCreatorImpl.createCal lableStatement(CallableStatementCreatorFactory.jav a:167)
at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:930)

[al0]

1. Such questions should be posted Data forum (http://forum.springsource.org/forumdisplay.php?27-Data).
2. It looks as if a JDBC driver you are using does not support a stored procedures (nested exception is java.sql.SQLException: Callable statments not supported.). So you either have to use a different JDBC driver for your DB (if exists) or use another DB.