StoredProcedureItemReader issue adding IN Parameter

[kkelleher]

Hi,

For the following oracle stp

--PL/SQL procedure

CREATE OR REPLACE PROCEDURE get_resultset(

ret_cursor OUT SYS_REFCURSOR )

IS
BEGIN
OPEN ret_cursor FOR
'SELECT * FROM my_table' ;
END;
/

this StoredProcedureItemReader works fine ...

<bean id="pupilItemReader" class="org.springframework.batch.item.database.Sto redProcedureItemReader" scope="step" >

<property name="dataSource" ref="dataSource"/>
<property name="procedureName" value="get_resultset"/>
<property name="parameters">
<list>

<bean class="org.springframework.jdbc.core.SqlOutParamet er">
<constructor-arg index="0" value="newid"/>
<constructor-arg index="1">
<util:constant static-field="oracle.jdbc.OracleTypes.CURSOR"/>
</constructor-arg>
</bean>

</list>
</property>
<property name="refCursorPosition" value="1"/>

<property name="rowMapper">
<bean class="ie.gov.edu.ppod.batch.database.PupilRowMapp er"/>
</property>

</bean>

However when adding an IN parameter to the stp

--PL/SQL procedure
CREATE OR REPLACE PROCEDURE get_resultset(

ret_cursor OUT SYS_REFCURSOR,
roll_no IN VARCHAR )

IS
BEGIN
OPEN ret_cursor FOR
'SELECT * FROM my_table where ROLL_NO =' ||roll_no;
END;
/

this StoredProcedureItemReader does not works ...

<bean id="pupilItemReader" class="org.springframework.batch.item.database.Sto redProcedureItemReader" scope="step" >

<property name="dataSource" ref="dataSource"/>
<property name="procedureName" value="get_resultset"/>
<property name="parameters">
<list>

<bean class="org.springframework.jdbc.core.SqlOutParamet er">
<constructor-arg index="0" value="newid"/>
<constructor-arg index="1">
<util:constant static-field="oracle.jdbc.OracleTypes.CURSOR"/>
</constructor-arg>
</bean>

<bean class="org.springframework.jdbc.core.SqlParameter" >
<constructor-arg index="0" value="123456"/>
<constructor-arg index="1">
<util:constant static-field="java.sql.Types.VARCHAR"/>
</constructor-arg>
</bean>

</list>
</property>
<property name="refCursorPosition" value="1"/>

<property name="rowMapper">
<bean class="ie.gov.edu.ppod.batch.database.PupilRowMapp er"/>
</property>

</bean>

I'm getting the following error

Caused by: java.sql.SQLException: Missing IN or OUT parameter at index:: 2

I have the following questions

• Why are there two constructor-args per sqlparameter?
• What does the index property of the constructor-arg parameter mean? I tried various combos.

Any help greatly appreciated! I'm very confused.
An end-to-end example of an stp with an IN & OUT example would be great.

Thanks in advance ....
Kevin

[sanjeev m]

Hi Kevin,

1.Two constructor-args in sqlparameter,
one is for variable name and another for datatype

If you are looking for xml configuration to fetch data from storedProcedure,this may help you...

<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:batch="http://www.springframework.org/schema/batch"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:util="http://www.springframework.org/schema/util"

xsi:schemaLocation="
http://www.springframework.org/schema/batch http://www.springframework.org/schem...-batch-2.1.xsd
http://www.springframework.org/schema/jdbc http://www.springframework.org/schem...g-jdbc-3.0.xsd
http://www.springframework.org/schema/beans http://www.springframework.org/schem...-beans-3.0.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schem...ontext-3.0.xsd
http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.0.xsd">

<description>IA Batch</description>

<batch:job id="inAuJob">
<batch:step id="inAuStep">
<batch:tasklet transaction-manager="transactionManager">
<batch:chunk reader="inAuReader"
processor="inAuProcessor" writer="inAuWriter"
commit-interval="${batch.jdbc.commit.interval}" />
<batch:listeners>
<batch:listener ref="batchExceptionListner" />
</batch:listeners>
</batch:tasklet>
</batch:step>
</batch:job>



<!-- IA Reader Part -->

<bean id="InAuReader"
class="org.springframework.batch.item.database.Sto redProcedureItemReader">
<property name="dataSource" ref="dataSource" />
<property name="procedureName" value="PKG_getPEvents" />
<property name="fetchSize" value="50" />
<property name="parameters">
<list>
<bean class="org.springframework.jdbc.core.SqlParameter" >
<constructor-arg index="0" value="iProcessDate" />
<constructor-arg index="1">
<util:constant static-field="java.sql.Types.DATE" />
</constructor-arg>
</bean>

<bean class="org.springframework.jdbc.core.SqlOutParamet er">
<constructor-arg index="0" value="inAus" />
<constructor-arg index="1">
<util:constant static-field="oracle.jdbc.OracleTypes.CURSOR" />
</constructor-arg>
</bean>

</list>
</property>
<property name="refCursorPosition" value="2" />
<property name="preparedStatementSetter" ref="inAuPreparedStatementSetter" />
<property name="rowMapper">
<bean
class="batch.rowmapper.RowMapper" />
</property>
</bean>


<bean id="inAuPreparedStatementSetter"
class="PreparedStatementSetter"
scope="step">
<property name="iProcessDate" value="#{jobParameters[iProcessDate]}" />
</bean>

<!-- Writer Part -->
<bean id="inAuProcessor"
class="DataProcessor"
scope="step" />


<bean id="inAuWriter"
class="org.springframework.batch.item.support.Comp ositeItemWriter">
<property name="delegates">
<list>
<ref local="inAuDataWriter" />
</list>
</property>
</bean>

<bean id="inAuDataWriter"
class="DataWriter"
scope="step">
<property name="inAuDao" ref="inAuDao" />
<property name="batchExceptionDao" ref="batchExceptionDao" />
</bean>

<bean id="inAuDao"
class="dao.impl.inAuDao">
<property name="dataSource" ref="DataSource" />
</bean>

<bean id="batchExceptionDao"
class="dao.impl.BatchExceptionDaoImpl">
<property name="dataSource" ref="DataSource" />
</bean>
<!-- Common Part -->


<bean id="jobRepository"
class="org.springframework.batch.core.repository.s upport.MapJobRepositoryFactoryBean">
<property name="transactionManager" ref="transactionManager" />
</bean>

<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSou rceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean>



<!-- Listener configurations -->

<bean id="batchExceptionListner"
class="batch.listener.BatchExceptionListner">
<property name="batchExceptionDao" ref="batchException" />
</bean>

<bean id="stopListener" class="batch.listener.StopListener" />
<!-- Exception details -->
<bean id="batchException"
class="dao.impl.BatchExceptionDaoImpl">
<property name="dataSource" ref="dataSource" />
</bean>

<bean id="dataSource" destroy-method="close" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="${batch.jdbc.driver}"/>
<property name="url" value="${batch.jdbc.url}"/>
<property name="username" value="${batch.jdbc.user}"/>
<property name="password" value="${batch.jdbc.password}"/>
</bean>


</beans>

[kkelleher]

looking at the following document

http://static.springsource.org/sprin...dureItemReader

it looks like I have to specify a preparedStatementSetter property.

Can I have an example of said please?

I tried

<property name="preparedStatementSetter">

<bean class="org.springframework.batch.core.resource.Lis tPreparedStatementSetter">
<property name="parameters">
<list>
<value>#{jobParameters['query.rollNo']}</value>
</list>
</property>
</bean>

</property>

but still got same error.

Do I have to specify the OUT param as a value in the list? If so how do I do it?

thanks in advance ....
Kevin

[kkelleher]

got it working ..... in summary I wrote a custom preparedStatementSetter

Config

<property name="preparedStatementSetter" >
<bean class="myPreparedStatementSetter" scope="step">
<property name="rollNo">
<value>#{jobParameters['query.rollNo']}</value>
</property>
</bean>
</property>

Java

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.springframework.jdbc.core.PreparedStatementSet ter;

public class myPreparedStatementSetter implements PreparedStatementSetter {

String rollNo = null;

public void setValues(PreparedStatement ps) throws SQLException {
CallableStatement cs = (CallableStatement) ps;
cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
cs.setString(2, rollNo);
}

public void setRollNo(String rollNo) {
this.rollNo = rollNo;
}

}