Call stored procedure

[mistrz_7]

Hello,

Is it possible to call stored procedure inside jdbcItemReader/jdbcItemWriter xml block using Jberet?

I mean that examaple xml can look like bellow:

<reader ref="jdbcItemReader">

     <properties>

          <property name="dataSourceLookup" value="java:/myDb" />

               <property name="sql" value="{call myProc()}"/>                                                                                                                                                                                                                                       

                <property name="beanType" value="java.util.List" />                                                                                                                                                                                                

          </properties>

</reader>

Best Regards

sw

[cfang]

Please see the following issue:

1. JBERET-307

Implement database stored procedure item reader

[cfang]

The fix for the above issue (JBERET-307) has been pushed to github repo.  You may want to build it and give it a try.  It will be included in the next release from JBeret master branch, 1.3.0.Beta5.

I tested it with h2 database stored procedure (see commit or the test source), and it should work with other RDBMS stored procedures as well.

[cfang]

If your stored procedure just returns a ResultSet, much teh same way as a query, then you can use stored procedure with the current version of JdbcItemReader, without needing the fix in JBERET-307.

JBERET-307 addresses this issue in a more standarad and portabel way, and should work with all RDBMS and drivers.  As per CallableStatement javadoc, this is the interface used to execute SQL stored procedures.  But as CallableStatement inherits from  PreparedStatement, a query-like stored procedure without additional features should work with current version of JdbcItemReader, which uses  PreparedStatement.

[mistrz_7]

Hello,

You are right. I have tested procedure returnig result set on mssql - it works well. My example is:

<reader ref="jdbcItemReader">

             <properties>

              <property name="dataSourceLookup" value="java:/myDb" />

                <property name="sql" value="{call test.p_test()}"/> 

  <property name="beanType" value="java.util.List" />

                <property name="resultSetProperties" value="fetchSize=10000" />

  </properties>

</reader>

But I'm waiting for fix, when will be possible to call procedure that don't return result set. It is ofter required eq. long runnig procedure just save result inside database.

Best Regards

sw

[cfang]

The reader expects the sql or stored procedure to return a result set.  Otherwise, the reader won't be able to read anything.

If your stored procedure doesn't return a result set, you can call it in a separate batchlet step using org.jberet.support.io.JdbcBatchlet.  Then in your chunk step, the reader retrieves the result set.

org.jberet.support.io.JdbcBatchlet will be available in the next release (1.3.0.Beta5).

[mistrz_7]

Will be posible to use OUT parameter in stored procedure?

Regards

że

[cfang]

As part of the fix to JBERET-307, I added a executeStoredProcedure method.  You can subclass JdbcItemReader and override this method to register the OUT parameter before, and to get the OUT parameter after.  JBeret jdbcItemReader just retrieves the result set from executing the stored procedure, without doing anything about IN or OUT parameters.