You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by abdesai <ab...@nomura.com> on 2009/12/08 05:56:37 UTC
Re: iBatis queryforList not working for dynamic Stored Proc
Hi Jeff,
Thanks for the reply.
Actually, the issue was a bit different, but got resolved.
I had a stored proc which returned multiple data sets to iBatis, depending
on the optional parameters. However, only some of the data sets were being
captured by iBatis.
Later I observed that it was happening I hadnt done "set nocount on". This
was leading to intermediate "x rows affected", which iBatis couldnt capture
:)
Jeff Butler-2 wrote:
>
> If I understand the question, it seems you are asking if ibatis
> supports optional parameters for stored procedure calls. The simple
> answer is "yes".
>
> See the <if...> tag with ibatis 3 or the <isnotnull...> tag with ibatis 2.
>
> Jeff Butler
>
>
> On 11/2/09, abdesai <ab...@nomura.com> wrote:
>>
>> Hi ppl,
>>
>> I am using iBatis to call a stored proc, which in turn calls 6 other
>> stored
>> procs, resulting in 7 resultSets.
>>
>> Everything was working fine earlier. Later, I changed the 2nd stored
>> proc(SP) to take in an additional OPTIONAL variable, (declare @var int =
>> null), and then, changed the SP to be dynamic i.e. it builds a where
>> clause
>> dynamically depending on @var variable.
>>
>> if @var is not null
>> select @l_var = " and a.var = " + convert(varchar(20), @var)
>>
>> exec("select .....
>> from .....
>> where 1=1 " + @l_var
>> + " and ......"
>> )
>>
>> Does iBatis support such dynamic StoredProcs?
>>
>> This throws the following error:
>> ================================================================
>> java.lang.IndexOutOfBoundsException: Index: 1, Size: 1
>> at java.util.ArrayList.RangeCheck(ArrayList.java:547)
>> at java.util.ArrayList.get(ArrayList.java:322)
>> at
>> com.nomura.rdp.rdmgateway.dao.impl.OrganisationDaoImpl.findByRDMId(OrganisationDaoImpl.java:92)
>> 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:597)
>> at
>> org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
>> at
>> org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
>> at
>> org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
>> at
>> org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:77)
>> at
>> com.nomura.rdp.rdmgateway.interceptor.PerformanceMonitorInterceptor.doPerformanceMonitor(PerformanceMonitorInterceptor.java:39)
>> 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:597)
>> at
>> org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:627)
>> at
>> org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:616)
>> at
>> org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:64)
>> at
>> org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
>> at
>> org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
>> at
>> org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
>> at
>> org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
>> at $Proxy22.findByRDMId(Unknown Source)
>> at
>> com.nomura.rdp.rdmgateway.business.OrganisationBo.findByRDMId(OrganisationBo.java:68)
>> 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:597)
>> at
>> org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
>> at
>> org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
>> at
>> org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
>> at
>> org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:54)
>> at
>> org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
>> at
>> org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
>> at
>> org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
>> at
>> org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
>> at $Proxy23.findByRDMId(Unknown Source)
>> at
>> com.nomura.rdp.rdmgateway.service.impl.OrganisationServiceImpl.findByRDMId(OrganisationServiceImpl.java:62)
>> 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:597)
>> at
>> org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
>> at
>> org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
>> at
>> org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
>> at
>> org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:54)
>> at
>> org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
>> at
>> org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
>> at
>> org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
>> at
>> org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
>> at
>> org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
>> at
>> org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
>> at $Proxy23.findByRDMId(Unknown Source)
>> at
>> com.nomura.rdp.rdmgateway.service.OrganisationServiceTest.findByRDMIdusingFinderHelper(OrganisationServiceTest.java:94)
>> at
>> com.nomura.rdp.rdmgateway.service.OrganisationServiceTest.findByRDMIdusingFinder(OrganisationServiceTest.java:66)
>> 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:597)
>> at
>> org.springframework.test.context.junit4.SpringTestMethod.invoke(SpringTestMethod.java:160)
>> at
>> org.springframework.test.context.junit4.SpringMethodRoadie.runTestMethod(SpringMethodRoadie.java:233)
>> at
>> org.springframework.test.context.junit4.SpringMethodRoadie$RunBeforesThenTestThenAfters.run(SpringMethodRoadie.java:333)
>> at
>> org.springframework.test.context.junit4.SpringMethodRoadie.runWithRepetitions(SpringMethodRoadie.java:217)
>> at
>> org.springframework.test.context.junit4.SpringMethodRoadie.runTest(SpringMethodRoadie.java:197)
>> at
>> org.springframework.test.context.junit4.SpringMethodRoadie.run(SpringMethodRoadie.java:143)
>> at
>> org.springframework.test.context.junit4.SpringJUnit4ClassRunner.invokeTestMethod(SpringJUnit4ClassRunner.java:160)
>> at
>> org.junit.internal.runners.JUnit4ClassRunner.runMethods(JUnit4ClassRunner.java:51)
>> at
>> org.junit.internal.runners.JUnit4ClassRunner$1.run(JUnit4ClassRunner.java:44)
>> at
>> org.junit.internal.runners.ClassRoadie.runUnprotected(ClassRoadie.java:27)
>> at
>> org.junit.internal.runners.ClassRoadie.runProtected(ClassRoadie.java:37)
>> at
>> org.junit.internal.runners.JUnit4ClassRunner.run(JUnit4ClassRunner.java:42)
>> at
>> org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:97)
>> at
>> org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:38)
>> at
>> org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
>> at
>> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
>> at
>> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
>> at
>> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
>> at
>> org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
>>
>>
>>
>> ================================================================
>> --
>> View this message in context:
>> http://old.nabble.com/iBatis-queryforList-not-working-for-dynamic-Stored-Proc-tp26157123p26157123.html
>> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
>> For additional commands, e-mail: user-java-help@ibatis.apache.org
>>
>>
>
> --
> Sent from my mobile device
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
> For additional commands, e-mail: user-java-help@ibatis.apache.org
>
>
>
--
View this message in context: http://old.nabble.com/iBatis-queryforList-not-working-for-dynamic-Stored-Proc-tp26157123p26688734.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org