An OpenSpirit SQL
Direct Activity executes a SQL query (select, insert, update, or delete)
to read or write data from an OpenSpirit enabled data store.Activity
An OpenSpirit SQL
Direct Activity executes a SQL query (select, insert, update, or delete)
to read or write data from an OpenSpirit enabled data store.
OpenSpirit SQL Direct 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 Supplied On | No | No | Indicates whether the SQL will be provided in the configuration panel or as an input to the activity. |
| SQL Model | No | No | Specifies the data model used in the SQL query field. The combo box lists the possible models to choose from. If a connection to OpenSpirit has not been made, then the combo box will be read-only and the "Connect to OpenSpirit" button in front of the combo box will be red. Click on the "Connect to OpenSpirit" button to populate the combo box. |
| Fetch (button) | If the data connection and SQL are defined at design time then clicking the "Fetch" button will create an output schema that reflects the defined query. Otherwise a generic output schema will be used. (see output examples) | ||
| 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. |
| Maximum Rows | No | Yes | The maximum number of rows to return. |
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 visible if "Process in Subsets" is checked in the configuration panel. Determines how many rows will be returned by the activity in each group iteration. |
| NullValue | String | Only visible if "SQL Supplied on Input" is selected in the configuration panel. If the SQL supplied on the input contains "?", then the values supplied in the Param* element will be set on the PreparedStatement. If any of those values equals the value supplied in the NullValue element, then a NULL will be set on the PreparedStatement. |
| Param | String* | Only visible if "SQL Supplied on Input" is selected in the configuration panel. If the SQL supplied on the input contains "?", then the values supplied in this repeating element will be set on the PreparedStatement. |
<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. |
| NumOperations | Integer | For a SELECT query = number
of rows returned.
For a DELETE query= number of rows deleted For a UPDATE query= number of rows updated For a INSERT query= number of rows inserted |
QueryParameters |
Complex | The input QueryParameters are echoed here so that subsequent activities may use the supplied values. |
| Complex | The returned results from
the SELECT query. Not used for other query types. The resultSet
takes two forms:
|
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) |
