Announcement Announcement Module
Collapse
No announcement yet.
StoredProcedureItemReader error when using OUT parameters Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • StoredProcedureItemReader error when using OUT parameters

    Hello,

    We are using StoredProcedureItemReader (Spring Batch 2.1.0.RELEASE) to call a DB2 stored Procedure.

    We have the following error [1]

    This error is apparently due because StoredProcedureItemReader doesn't call registerOutParameter for parameters out or in/out (it only calls this method
    for the refCursor parameter.

    Our stored DB2 procedure signature is :
    Code:
    CREATE PROCEDURE  ààD000.M10K001                                   
           ( OUT   YCDERR CHARACTER(4)                                 
            ,OUT   YCTERR CHARACTER(4)                                 
            ,OUT   YLIERR CHARACTER(72)                               
            ,IN    YCTTRB CHARACTER(1)                                 
            ,IN    K_COAPAP CHARACTER(3)                               
            ,IN    K_YCOSRV CHARACTER(1)                               
            )                                                         
           RESULT SETS         1                                       
           EXTERNAL NAME 'M10K001'                                     
           LANGUAGE            COBOL                                   
           PARAMETER STYLE     SQL                                     
           DETERMINISTIC                                               
           NULL CALL                                                   
           FENCED                                                     
           MODIFIES SQL DATA                                           
           NO DBINFO                                                   
           COLLID              C000K1                                 
           WLM ENVIRONMENT     AE_DB2T_ENV01                           
           ASUTIME LIMIT       100000                                 
           STAY RESIDENT NO                                             
           PROGRAM TYPE SUB                                             
           SECURITY            DB2                                     
           RUN OPTIONS         'MSGFILE(SYSOUT,,,,ENQ)'                 
           COMMIT ON RETURN NO                                         
           INHERIT SPECIAL REGISTERS                                   
           STOP AFTER  5       FAILURES                                 
           ;
    Our Spring configuration file is :
    Code:
    	<bean id="personnesMoralesDbReader" class="org.springframework.batch.item.database.StoredProcedureItemReader">
    		<property name="dataSource" ref="siteCentralDataSource" />
    		<property name="procedureName" value="M10K001" />
    		<property name="fetchSize" value="50"/>
    		<property name="parameters">
    			<list>
    				<bean class="org.springframework.jdbc.core.SqlOutParameter">
    					<constructor-arg index="0" value="YCDERR" />
    					<constructor-arg index="1">
    						<util:constant static-field="java.sql.Types.CHAR" />
    					</constructor-arg>
    				</bean>
    				<bean class="org.springframework.jdbc.core.SqlOutParameter">
    					<constructor-arg index="0" value="YTYERR" />
    					<constructor-arg index="1">
    						<util:constant static-field="java.sql.Types.CHAR" />
    					</constructor-arg>
    				</bean>
    				<bean class="org.springframework.jdbc.core.SqlOutParameter">
    					<constructor-arg index="0" value="YLIERR" />
    					<constructor-arg index="1">
    						<util:constant static-field="java.sql.Types.CHAR" />
    					</constructor-arg>
    				</bean>
    				<bean class="org.springframework.jdbc.core.SqlParameter">
    					<constructor-arg index="0" value="YCTTRB" />
    					<constructor-arg index="1">
    						<util:constant static-field="java.sql.Types.CHAR" />
    					</constructor-arg>
    				</bean>
    				<bean class="org.springframework.jdbc.core.SqlParameter">
    					<constructor-arg index="0" value="COAPAP" />
    					<constructor-arg index="1">
    						<util:constant static-field="java.sql.Types.CHAR" />
    					</constructor-arg>
    				</bean>
    				<bean class="org.springframework.jdbc.core.SqlParameter">
    					<constructor-arg index="0" value="YCOSRV" />
    					<constructor-arg index="1">
    						<util:constant static-field="java.sql.Types.CHAR" />
    					</constructor-arg>
    				</bean>
    			</list>
    		</property>
    		<property name="rowMapper">
    			<bean class="com.natixis.aws.personnemorale.mapping.PersonneMoraleRowMapper" />
    		</property>
    		<property name="preparedStatementSetter" ref="preparedStatementSetter" />
    	</bean>
    If we use the attached version of StoredProcedureItemReader erveything works fine.

    Should I open a JIRA issue ?

    Thanks !

    [1]
    Code:
    Caused by: org.springframework.jdbc.BadSqlGrammarException: Executing stored procedure; bad SQL grammar [{call M10K001(?, ?, ?, ?, ?, ?)}]; nested exception is com.ibm.db2.jcc.b.eo: [jcc][10143][10845][3.53.95] Paramètre non valide 1 : Le paramètre n'est pas défini ou n'est pas enregistré. ERRORCODE=-4461, SQLSTATE=42815
    	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:98)
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    	at org.springframework.batch.item.database.StoredProcedureItemReader.openCursor(StoredProcedureItemReader.java:221)
    	at org.springframework.batch.item.database.AbstractCursorItemReader.doOpen(AbstractCursorItemReader.java:401)
    	at org.springframework.batch.item.support.AbstractItemCountingItemStreamItemReader.open(AbstractItemCountingItemStreamItemReader.java:137)
    	... 44 more
    Caused by: com.ibm.db2.jcc.b.eo: [jcc][10143][10845][3.53.95] Paramètre non valide 1 : Le paramètre n'est pas défini ou n'est pas enregistré. ERRORCODE=-4461, SQLSTATE=42815
    	at com.ibm.db2.jcc.b.bd.a(bd.java:676)
    	at com.ibm.db2.jcc.b.bd.a(bd.java:60)
    	at com.ibm.db2.jcc.b.bd.a(bd.java:103)
    	at com.ibm.db2.jcc.b.hm.sc(hm.java:4276)
    	at com.ibm.db2.jcc.b.hm.e(hm.java:2989)
    	at com.ibm.db2.jcc.b.im.Ub(im.java:105)
    	at com.ibm.db2.jcc.b.im.execute(im.java:88)
    	at org.springframework.batch.item.database.StoredProcedureItemReader.openCursor(StoredProcedureItemReader.java:205)
    	... 46 more

  • #2
    You should make a jira issue out of this, the PreparedStatementSetter gets a PreparedStatement parameter which you can cast to a CallableStatement and set the OUT parameters yourself, but StoredProcedureItemReader itself registers the cursor as an OUT parameter, but not the other OUT parameters

    I guess it would be optimal if StoredProcedureItemReader itself would set all the OUT parameters in the CallableStatement, since it gets the SQLPArameter list anyhow...

    I used this to go around this:
    Code:
    public void setValues(PreparedStatement ps) throws SQLException {
            CallableStatement cs = (CallableStatement) ps;
            cs.setString(1, "constant");
            cs.setString(2, "constant");
            cs.setNull(3, OracleTypes.CURSOR);
            //param 4 is set by the StoredProcedureItemReader
            cs.registerOutParameter(5, Types.VARCHAR);
            cs.registerOutParameter(6, Types.INTEGER);
        }

    Comment


    • #3
      Created http://jira.springframework.org/browse/BATCH-1561

      Thanks !

      Comment


      • #4
        I am having the same issue using DB2 database. How do we use the attached storedprocedureItemreader? should I add it to the spring jar file? or use it as a class in my application?

        Comment


        • #5
          Hello,

          If you only have to register outputParameter (and don't need to get the output parameter value after the stored procedure is called), you don't have to use the attached storedprocedureItemreader.

          Just use Spring Batch StoredProcedureItemReader and write your own PreparedStatementSetter.

          In your PreparedStatementSetter, you'll need to cast PreparedStatement to CallableStatement and call registerOutParameter.

          Sample code :
          Code:
          public class PersonneMoralePreparedStatementSetter implements PreparedStatementSetter {
          
              public void setValues(PreparedStatement aPs) throws SQLException {
                  CallableStatement lCallableStatement = (CallableStatement) aPs
                  lCallableStatement .registerOutParameter(1, Types.CHAR);
                  lCallableStatement .registerOutParameter(2, Types.CHAR);
                  lCallableStatement .registerOutParameter(3, Types.CHAR);
                  lCallableStatement .setString(4, "value4");
                  lCallableStatement .setString(5, "value5");
                  lCallableStatement .setString(6, "value6");
              }
          }

          Comment


          • #6
            Thanks for the reply . I got it working and insteaded of having my own implementation I used the Listpreparedstatementsetter (org.springframework.batch.core.resource) and provided the input parameters as part of the config file itself.
            <beans:bean id="statementSetter"
            class="org.springframework.batch.core.resource.Lis tPreparedStatementSetter">
            <property name="parameters">
            <beans:list>
            <beans:value>1</beans:value>
            <beans:value>2</beans:value>
            <beans:value>3</beans:value>
            <beans:value>4</beans:value>
            </beans:list>
            </property>
            </beans:bean>

            Comment


            • #7
              i have a clarrificatiion..

              <property name="preparedStatementSetter" ref="preparedStatementSetter" />
              and

              public class PersonneMoralePreparedStatementSetter implements PreparedStatementSetter {

              public void setValues(PreparedStatement aPs) throws SQLException {
              CallableStatement lCallableStatement = (CallableStatement) aPs
              lCallableStatement .registerOutParameter(1, Types.CHAR);
              lCallableStatement .registerOutParameter(2, Types.CHAR);
              lCallableStatement .registerOutParameter(3, Types.CHAR);
              lCallableStatement .setString(4, "value4");
              lCallableStatement .setString(5, "value5");
              lCallableStatement .setString(6, "value6");
              }
              }
              ..but how to set the bean for the above class which refered as preparedStatementSetter...


              Please help asap....

              Comment


              • #8
                Hi All,

                I am using StoredProcedureItemReader reades StoreProcedure (?,?,?,?,?,?). The stored procedure returns 3 output parameters .First one is Cursor and remaining are varchars.

                I have used rowmapper and able to fetch values from result set for the cursor. Could you please help me how to get the value from two other output params. I have struggled for this for the last two days. Please help

                Comment


                • #9
                  Hi All,

                  I am using StoredProcedureItemReader reades StoreProcedure (?,?,?,?,?,?). The stored procedure returns 3 output parameters .First one is Cursor and remaining are varchars.

                  I have used rowmapper and able to fetch values from result set for the cursor. Could you please help me how to get the value from two other output params. I have struggled for this for the last two days. Please help

                  Comment


                  • #10
                    HI

                    Any update on ulaganathana question please?

                    Comment

                    Working...
                    X