StoredProcedureItemReader

[arun4]

Hi All,
I need to execute the StoredProcedure and write the output to the file. while calling the StoredProcedureItemReader i am getting the following exception.


<code>

org.springframework.batch.item.ItemStreamException : Failed to initialize the reader
at org.springframework.batch.item.support.AbstractIte mCountingItemStreamItemReader.open(AbstractItemCou ntingItemStreamItemReader.java:139)
at org.springframework.batch.item.support.CompositeIt emStream.open(CompositeItemStream.java:98)
at org.springframework.batch.core.step.tasklet.Taskle tStep.open(TaskletStep.java:288)
at org.springframework.batch.core.step.AbstractStep.e xecute(AbstractStep.java:193)
at org.springframework.batch.core.job.SimpleStepHandl er.handleStep(SimpleStepHandler.java:115)
at org.springframework.batch.core.job.flow.JobFlowExe cutor.executeStep(JobFlowExecutor.java:61)
at org.springframework.batch.core.job.flow.support.st ate.StepState.handle(StepState.java:60)
at org.springframework.batch.core.job.flow.support.Si mpleFlow.resume(SimpleFlow.java:144)
at org.springframework.batch.core.job.flow.support.Si mpleFlow.start(SimpleFlow.java:124)
at org.springframework.batch.core.job.flow.FlowJob.do Execute(FlowJob.java:99)
at org.springframework.batch.core.job.AbstractJob.exe cute(AbstractJob.java:281)
at org.springframework.batch.core.launch.support.Simp leJobLauncher$1.run(SimpleJobLauncher.java:120)
at org.springframework.core.task.SyncTaskExecutor.exe cute(SyncTaskExecutor.java:49)
at org.springframework.batch.core.launch.support.Simp leJobLauncher.run(SimpleJobLauncher.java:114)
at org.springframework.batch.core.launch.support.Simp leJobOperator.start(SimpleJobOperator.java:300)
at za.co.sanlam.epsilon.batch.core.tasklet.BatchExecu tor$2.run(BatchExecutor.java:884)
at java.lang.Thread.run(Thread.java:736)
Caused by: java.lang.ClassCastException: [B incompatible with java.sql.ResultSet
at org.springframework.batch.item.database.StoredProc edureItemReader.openCursor(StoredProcedureItemRead er.java:211)
at org.springframework.batch.item.database.AbstractCu rsorItemReader.doOpen(AbstractCursorItemReader.jav a:401)
at org.springframework.batch.item.support.AbstractIte mCountingItemStreamItemReader.open(AbstractItemCou ntingItemStreamItemReader.java:137)
... 16 more

</code>

My Reader:

<code>

<bean id="exportMISMovementsReader"
class="org.springframework.batch.item.database.Sto redProcedureItemReader">
<property name="dataSource" ref="dataSource" />
<property name="rowMapper">
<bean
class="com.test.batch.exports.mismovements.mapper. MISMovementsMapper" />
</property>
<property name="procedureName" value="XML_MISMovements" />
<property name="function" value="true" />

</bean>

</code>

My stored procedure is

<code>

CREATE PROCEDURE [dbo].[XML_MISMOVEMENTS] as

TRUNCATE TABLE XMLDEST

INSERT into XMLDEST

SELECT
.
.
.

FROM MisPlanMvts EVENT

SELECT
.
.
FROM XMLDest

FOR XML EXPLICIT

</code>

I missed any thing in the configuration?

Thanks for your reply in advance.

[arun4]

Hi all,

In this case, I have modified the StoredProcedureItemReader.

Now it is working fine.

Is it is a bug in StoredProcedureItemReader ?

<code> orginal StoredProcedureItemReader code

if (results) {
rs = callableStatement.getResultSet();
}
else {
if (function) {
rs = (ResultSet) callableStatement.getObject(1);
}
else {
rs = (ResultSet) callableStatement.getObject(refCursorPosition);
}
}

</code>


Modified code:

<code>

if (function) {
/* if my storedProcedure contain any insert statements? then use getUpdateCount() to retrieve the first result and
* while getMoreResults() method must be used to retrieve the second and subsequent results.*/
int count = callableStatement.getUpdateCount();
if (count != -1) {

boolean moreResult = callableStatement.getMoreResults();
if (moreResult) {
rs = callableStatement.getResultSet();
}
}
//rs = (ResultSet) callableStatement.getObject(1);
}
else {
rs = (ResultSet) callableStatement.getObject(refCursorPosition);
}
</code>