Package org.frankframework.jdbc

Class StoredProcedureQuerySender

java.lang.Object
org.frankframework.jdbc.JdbcFacade
org.frankframework.jdbc.AbstractJdbcSender<QueryExecutionContext>
org.frankframework.jdbc.AbstractJdbcQuerySender<QueryExecutionContext>
org.frankframework.jdbc.FixedQuerySender
org.frankframework.jdbc.StoredProcedureQuerySender
All Implemented Interfaces:
FrankElement, HasApplicationContext, HasName, HasPhysicalDestination, IBlockEnabledSender<QueryExecutionContext>, IConfigurable, IScopeProvider, ISender, ISenderWithParameters, IWithParameters, IXAEnabled, NameAware, ConfigurableLifecycle, org.springframework.beans.factory.Aware, org.springframework.context.ApplicationContextAware, org.springframework.context.Lifecycle, org.springframework.context.Phased, org.springframework.context.SmartLifecycle
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 IParameter 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>

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