Activity - sqlQueryStatement

Activity Type: Relational

Description

Run an SQL query on a relational data resource. Note that output from this activity must be converted into a string representation, e.g. using the SQLResultsToXML activity, before it can be delivered in the response or to an output stream.

Example

<sqlQueryStatement name="statement">
  <!-- value of first parameter -->
  <sqlParameter position="1" from="idvalue"/>
  <!-- value of second parameter -->
  <sqlParameter position="2">321</sqlParameter>
  <expression>
    select * from littleblackbook where id <= ? and id >= ?
  </expression>
  <resultStream name="statementResponse"/>
</sqlQueryStatement>

Specification

Element sqlQueryStatement:

• 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 expression (required) - SQL query statement.
  • The SQL query statement is contained within the element.
  • In the SQL expression ? can be used as a place-holder for any parameters that have been defined in sqlParameter elements. The first parameter corresponds to the first place-holder, the second to the second etc.
• Element resultStream (required) - output stream.
  • Attribute name - name of the activity's output stream.

Warning to Service Deployers

Note that providing an SQL update statement will cause an exception to be thrown. If the database driver used by OGSA-DAI to communicate with the database supports read-only connections then the database will not be modified. If however, the driver does not support read-only connections then the database will be modified.

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 — java.sql.ResultSet or EngineConstants.NO_MORE_DATA as the last block

XML Schema

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

Class

uk.org.ogsadai.activity.sql.SQLQueryStatementActivity

Client Toolkit Class

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