Activity
An OpenSpirit Insert Activity creates and executes a query to insert
data via 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 Insert 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 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 INSERT query that
defines which columns to insert. The SQL is constructed using
the OpenSpirit
Insert 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. |
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) . |
<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 Insert activity. This class contains all output items for the activity. |
| NumRowsInserted | Integer | The number of rows inserted by this query (will be either 0 or 1). |
QueryParameters |
Complex | The input QueryParameters are echoed here so that subsequent activities may use the supplied values. |
OspDataKey |
string | The OpenSpirit datakey of the inserted item. This is returned if the insert succeeded. This is commonly used in subsequent Insert activities where children of this data item are to be inserted and they require the datakey of their parent. |
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) |
OpenSpirit Insert Query Builder
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 INSERT 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 insert into. Note that display names are used in the combo box. The actual table name will appear in the constructed SQL query.
3.
Select which columns, or attributes, you wish to insert. 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. The actual attribute name will appear in the constructed
SQL query. An input parameter will be constructed for every selected
attribute with the same name and type as the selected attribute.
Note that for some data stores (e.g. Kingdom and Petra) , after data is inserted, it will not be visible in the native application that is connecting to the same project unless the application refreshes its connection or is restarted. This is due to caching done in the vendor's API.