Announcement Announcement Module
Collapse
No announcement yet.
StoredProcedureItemReader issue adding IN Parameter Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • StoredProcedureItemReader issue adding IN Parameter

    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

  • #2
    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>

    Comment


    • #3
      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

      Comment


      • #4
        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;
        }

        }

        Comment

        Working...
        X