Activity - sqlStoredProcedure

Activity Type: Relational

Description

Run a stored procedure of a relational data resource.

Examples

<sqlStoredProcedure name="statement">
  <storedProcedureName>STORED-PROCEDURE-NAME</storedProcedureName>
  <resultStream name="results"/>
</sqlStoredProcedure>

<sqlStoredProcedure name="statement">
  <!-- value of first input parameter -->
  <sqlParameter position="1" from="data" />
  <!-- register output parameter of type INTEGER -->
  <sqlOutParameter position="2" type="4" />
  <storedProcedureName>STORED-PROCEDURE-NAME</storedProcedureName>
  <resultStream name="results"/>
</sqlStoredProcedure>

Specification

Element sqlStoredProcedure:

• Attribute name - unique name for the activity within the scope of a request.
• Element sqlParameter (zero or more) - define a parameter.
  • Attribute position - position within the expression element.
  • Attribute from (optional) - name of the output from another activity which provides the value of this parameter.
  • If a from value is not specified then this element must contain a value for the parameter.
  • The sequence in which sqlParameter elements appear is important when the same activity will be supplying a value for more than one parameter. The sequence in which sqlParameters appear is the sequence in which they must be delivered from the other activity, for example:

    <sqlParameter position ="1" from="inputStream"/>
    <sqlParameter position ="3" from="inputStream"/>
    <sqlParameter position ="2" from="inputStream"/>
    

    This means inputStream must first deliver parameter 1 then parameter 3 then parameter 2.
• Element sqlOutParameter (zero or more) - register an output parameter.
  • Attribute position - position within the expression element.
  • Attribute type - type of the output parameter, an integer constant as defined in java.sql.Types.
• Element storedProcedureName (required) - the name of a stored procedure.
  • The procedure name is contained within the element.
• Element resultStream (required) - output stream for stored procedures which produces the output parameters, result sets and update counts, as applicable.
  • Attribute name - name of the activity's output stream.

Limitations

The default implementation of this activity does not work with Oracle databases.

Data Resource Accessor

This activity requires a data resource accessor that implements the following interface: uk.org.ogsadai.dataresource.JDBCConnectionProvider

Input and Output Types

• Input (optional): sqlParameter — java.lang.Object
• Output: resultStream — a block can be one of the following:
  • java.lang.ResultSet — result set
  • java.lang.String — XML element containing the number of modified rows.
  • java.lang.Map — maps the position of an output parameter (java.lang.Integer) to its value (java.lang.Object).

XML Schema

OGSA-DAI/schema/ogsadai/xsd/activities/sql_stored_procedure.xsd

Class

uk.org.ogsadai.activity.sql.SQLStoredProcedureActivity

Client Toolkit Class

uk.org.ogsadai.client.toolkit.activity.sql.SQLStoredProcedure