Package org.frankframework.jdbc

Class StoredProcedureQuerySender

java.lang.Object
org.frankframework.jndi.JndiBase
org.frankframework.jdbc.JdbcFacade
org.frankframework.jdbc.JdbcSenderBase<H>
org.frankframework.jdbc.JdbcQuerySenderBase<QueryExecutionContext>
org.frankframework.jdbc.FixedQuerySender
org.frankframework.jdbc.StoredProcedureQuerySender
All Implemented Interfaces:
AdapterAware, HasPhysicalDestination, IBlockEnabledSender<QueryExecutionContext>, IConfigurable, IConfigurationAware, INamedObject, IScopeProvider, ISender, ISenderWithParameters, IWithParameters, IXAEnabled, HasStatistics, org.springframework.beans.factory.Aware, org.springframework.context.ApplicationContextAware
public class StoredProcedureQuerySender extends FixedQuerySender
StoredProcedureQuerySender is used to send stored procedure queries and retrieve the result.

QueryType settings and OUTPUT parameters

The StoredProcedureQuerySender class has the following features:
  • It supports setting the output parameters of the stored procedure by setting 'mode' attribute of the corresponding 'Param' to 'OUTPUT' or 'INOUT'.
  • The queryType can only be 'SELECT' or 'OTHER'.
  • Use queryType 'SELECT' when the stored procedure only returns a set of rows, and you need the output to be the format as FixedQuerySender (see DB2XMLWriter).
  • Use queryType 'OTHER' if the stored procedure has one or more output parameters. With this query type, the stored procedure can return a result-set along with returning some values in output parameters. Depending on the database, the stored procedure can even returning multiple result sets or a combination of result sets as return values, and result sets as REF_CURSOR OUT parameters.

All stored procedure parameters that are not fixed, so specified in the query with a ?, should have a corresponding Parameter entry. Output parameters should have mode="OUTPUT", or mode="INOUT" depending on how the stored procedure is defined.

Sample Output for queryType=OTHER

Basic Example with Only Simple Output Parameters

        <resultset>
                <result param="r1" type="STRING">MESSAGE-CONTENTS</result>
                <result param="r2" type="STRING">E</result>
        </resultset>

Example with Resultset and Simple Output Parameters

         <resultset>
                 <result resultNr="1">
                         <fielddefinition>
                                <field name="FIELDNAME"
                                                  type="columnType"
                                                  columnDisplaySize=""
                                                  precision=""
                                                  scale=""
                                                  isCurrency=""
                                                  columnTypeName=""
                                                  columnClassName=""/>
                                 <field ...../>
                     </fielddefinition>
                         <rowset>
                                 <row number="0">
                                         <field name="TKEY">MSG-ID</field>
                                         <field name="TCHAR">E</field>
                                         <field name="TMESSAGE">MESSAGE-CONTENTS</field>
                                         <field name="TCLOB" null="true"/>
                                         <field name="TBLOB" null="true"/>
                                 </row>
                 <row number="1" ...../>
                         </rowset>
                 </result>
                 <result param="count" type="INTEGER">5</result>
         </resultset>

Example with Simple and Cursor Output Parameters

        <resultset>
                <result param="count" type="INTEGER">5</result>
                <result param="cursor1" type="LIST">
                         <fielddefinition>
                                <field name="FIELDNAME"
                                                  type="columnType"
                                                  columnDisplaySize=""
                                                  precision=""
                                                  scale=""
                                                  isCurrency=""
                                                  columnTypeName=""
                                                  columnClassName=""/>
                                 <field ...../>
                     </fielddefinition>
                        <rowset>
                                <row number="0">
                                        <field name="TKEY">MSG-ID</field>
                                        <field name="TCHAR">E</field>
                                        <field name="TMESSAGE">MESSAGE-CONTENTS</field>
                                        <field name="TCLOB" null="true"/>
                                        <field name="TBLOB" null="true"/>
                                </row>
                                <row number="1" ..... />
                        </rowset>
                </result>
        </resultset>

NOTE: Support for stored procedures is currently experimental and changes in the currently produced output-format are expected.

Since:
7.9
Parameters
All parameters present are applied to the query to be executed.