StoredProcedureItemReader error when using OUT parameters

[wriggle]

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 :

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 :

	<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]

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

[deebo]

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:

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);
    }

[wriggle]

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

Thanks !

[senorita]

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?

[gonzalad]

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 :

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");
    }
}

[senorita]

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>

[ulaganathana]

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....

[ulaganathana]

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

[ulaganathana]

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