Activity
An OpenSpirit Query Activity creates and executes a query to retrieve
data from an OpenSpirit Data Connection. The query is constructed at design
time using the OpenSpirit query builder. If you want to specify
the SQL query at execution time or by hand coding the SQL then use the
OpenSpirit
SQL Direct Activity.
See
the Query
Builder section regarding an OpenSpirit connection and launching the
query builder.
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) |
| Output Type | No | No | Either output a result set ( an xml document with a schema) or a formatted result as a string. If "Formatted Result" is chosen then one may choose XML, JSON, or HTML (an html table is made from the result set) . A formatted result is often used if the output from the Query activity is to be uses in an http response. |
| Query Scope Type | No | No | Indicates whether the data source to query will be defined by an OpenSpirit Data Connection, by a set of OpenSpirit datakeys supplied as an input (the datakeys in turn reference one or more data sources), or an OpenSpirit data source supplied as an input. |
| OpenSpirit Data Connection | No | Required if Query Scope= Selected Datasource | A shared resource that
defines a project or data source. See OpenSpirit
Data Connection.
Clicking on the browse button will prompt you to connect to OpenSpirit if a connection has not previously been made. |
| SQL Model | No | Read Only | Displays the data model used in the SQL query. The model is specified in the OpenSpirit Query builder. |
| 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. If you want to hand edit the SQL generated
by the Query Builder
you can copy and paste it into an OpenSpirit SQL Direct Activity.
Clicking on the edit button will prompt you to connect to OpenSpirit if a connection has not previously been made. |
| Maximum Rows | No | Yes | The maximum number of rows to return. |
Add SQL Aliases |
No | Yes | If checked the SQL aliases will be added to the selected column names in the output result set schema. |
Process in Subsets |
No | Yes | If checked then set the SubsetSize in the input to determine how many rows will be returned by the activity in each group iteration. If checked, an output parameter, LastSubset, will be set to true when the last row is encountered in that iteration. This is used to process a large number of rows without having to hold them all in memory. |
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 | Root element for all input elements |
| OspUser | String | If supplied and useSharedAcount=false
then the OspUser account will specify the OpenSpirit user account
to use to determine where to run the data connector and under
what account .
If supplied and useSharedAcount=true then OspUser will be used to determine whether the user has access to the datasource/project but the account used to run Designer (at design time) or Hawk (if run from within a deployed EAR file) will specify the OpenSpirit user account to use to determine where to run the data connector and under what account . Note that if OspUser is specified then Designer (at design time) or Hawk (if run from within a deployed EAR file) must be run under an account that has been assigned OpenSpirit admin privileges (done in the OpenSpirit desktop User Manager) |
| UseSharedAccount | Boolean | Only used if OspUser is non-null. Determines whether the query will be executed using the OspUser account or the account running Designer (at design time) or Hawk (if run from within a deployed EAR file) . |
| QueryContext
UnitCatalog UnitCatalogVersion UnitSystem |
Complex
String String String |
Optionally specify the unit system to use to indicate the preferred units for columns that have a measurement type. If supplied then this will override any unit system that may have been specified on the OpenSpirit Data Connection. |
| QueryContext
CRS |
OspCRS | Optionally specify the coordinate reference system to use to indicate the desired CRS for columns that are a geometry type. If supplied then this will override any CRS that may have been specified on the OpenSpirit Data Connection. If not specified then geometry is returned in the data source's CRS. Note: when a CRS is specified a datum shift is also specified so datum shifts may be performed if required and a Vertical Unit may be defined to create a 3d (compound) system. |
| MaxRows | Integer | The maximum number of rows to return. Overrides any value that may have been supplied in the configuration panel. |
| SubsetSize | Integer | Only used if "Process in Subsets" is checked in the configuration panel. Determines how many rows will be returned by the activity in each group iteration. |
<variable name> |
<varies> | One variable for each prepared parameter 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. |
QueryParameters |
Complex | The input QueryParameters are echoed here so that subsequent activities may use the supplied values. |
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 msg msgCode |
The query could not be
successfully executed.
The msg element should contain some indication of the reason. The error code (see list of possible codes) |
If
a connection to OpenSpirit has not previously been made, launching the
query builder will prompt the user if they want to connect to OpenSpirit.
If “Yes” is clicked, a connection to OpenSpirit will be made and the query
builder will then be launched. If “No” is clicked, the query builder will
not be displayed. A connection to OpenSpirit only needs to be made once
after Designer is started. So, if a connection was previously made the
query builder will launch without any prompts.
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.
