An OpenSpirit Query Activity creates and executes a query to retrieve
data from an OpenSpirit Data Connection.Activity
An OpenSpirit Query Activity creates and executes a query to retrieve
data from an OpenSpirit Data Connection.
OpenSpirit Query Configuration Fields
| Field | Global Var? | Optional? | Description |
| Name | No | No | User assigned name given to query activity |
| Description | No | Yes | User assigned description (optional) |
| Query Scope Type | No | No | Indicates whether the data source to query will be defined by an OpenSpirit Data Connection or by a set of OpenSpirit datakeys supplied as an input (the datakeys in turn reference one or more data sources) |
| OpenSpirit Data Connection | No | No (if Query Scope= Selected Datasource) | A shared resource that defines a project or data source. See OpenSpirit Data Connection. |
| SQL Query | No | No | The SQL SELECT query that defines which columns to query and specifies optional prepared statement parameters. The SQL is constructed using the OpenSpirit Query builder. |
| Model | No | Read Only | Displays the data model used in the SQL query. The model is specified in the OpenSpirit Query builder. |
See TIBCO ActiveMatrix BusinessWorks Process Design Guide for more information about mapping and transforming input data.
The input for the activity is the following.
| Input Item | Datatype | Description |
| QueryParameters | Complex | Contains the list of prepared parameters supplied in the SQL statement. Prepared parameters allow you to substitute process data for items in the SQL statement. The types and names of the prepared parameters are based on the SQL query. |
The output for the activity is the following.
| Output Item | Datatype | Description |
OspOutput |
Complex | The root class for the output of the OpenSpirit Update activity. This class contains all output items for the activity. |
| NumRowsReturned | Integer | The number of rows returned by this query. |
resultSet |
Complex | The returned results from the query. |
result |
Complex | One row from the results. The contents of the result element depends upon the columns returned by the query. There are zero or more result elements contained in the resultSet. |
The Error Output tab lists the possible exceptions that can be thrown by this activity.
| Exception | Thrown When ... |
OspException |
The query could not be successfully executed. The msg element should contain some indication of the reason. |
The SELECT query builder allows you to construct a SQL query statement without having to type any SQL. Follow the steps illustrated below:
1. Select the data model you wish to use. All OpenSpirit data connectors expose the OpenSpirit common data model and many also expose the native model of the underlying data store. If you chose a native model make sure it matches the data source type.
2. Select which table you wish to query. Note that display names are used in the combo box. These names may differ from the display names used in the OpenSpirit DataSelector. The actual table names will appear in the constructed SQL query.
3. Select which columns,
or attributes, you wish to query. The 
icon
may be used to display the description, if available, for each column.
Note that display names are used in the listing of attributes. These
names may differ from the display names used in the OpenSpirit DataSelector.
The actual attribute name will appear in the constructed SQL query.
4. If you wish to query for attributes from related tables simply click on the Add Attributes button and select the desired attributes. An appropriate JOIN clause will be added.
5. If you do not want to query all the rows in the table you may specify constraints by clicking on the Query Constraint tab.
6. In the Query Constraint tab you may add constraints on selected attributes by clicking on the add button next to the desired attribute.
7. Select the desired operator and enter a constraint value. If you wish to set the constraint value at run time then type a "?" in the value field and an input parameter will be generated. The input parameter name will be the name of the attribute and its xml element type will be based on the attribute data type.
8. As you are adding query constraints you may see the updated SQL by clicking on the Update SQL button.
