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.