7 Replies Latest reply on Feb 24, 2017 6:49 PM by cfang

    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

        • 1. Re: Call stored procedure
          cfang

          Please see the following issue:

          1. JBERET-307

          Implement database stored procedure item reader

          • 2. Re: Call stored procedure
            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.

            • 3. Re: Call stored procedure
              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.

              • 4. Re: Call stored procedure
                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

                • 5. Re: Call stored procedure
                  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).

                  • 6. Re: Call stored procedure
                    mistrz_7

                    Will be posible to use OUT parameter in stored procedure?

                     

                    Regards

                    że

                    • 7. Re: Call stored procedure
                      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.