Activity
An OpenSpirit Delete Activity creates and executes a query to delete
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, 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 Delete Query builder. |
| SQL Query | No | No | The SQL DELETE query that
defines which rows to delete. The SQL is constructed using the
OpenSpirit
Delete Query builder.
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 | Contains the list of prepared parameters, the values of the attributes to be inserted, 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. |
| NumRowsDeleted | Integer | The number of rows deleted by this query. |
| QueryParameters | Complex | Contains the list of prepared parameters, the values of the attributes to be inserted, 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 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 Delete 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 DELETE 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 delete from. 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 base your delete selection upon by clicking on the add button next to the desired attribute.
4. Then 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.
5. As you are adding query constraints you may see the updated SQL by clicking on the Update SQL button.
Note that the behavior of the Delete activity will vary across data stores. Some data stores will perform a cascading delete if a parent object is deleted whereas other data stores will require you to delete all children objects before a parent may be deleted. So before using this activity you should have a good understanding of the specifics of the data store that you are deleting from.