OpenSpiritDelete

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 OpenSpirit Delete Query Builder section regarding an OpenSpirit connection and launching the query builder.

General

The General tab contains the following fields.

Field Module Property? Description
Name No The name to be displayed as the label for the activity in the process.
Query Scope Type 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 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. Required if Query Scope= Selected Datasource.
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 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.
Activity Timeout No The number of minutes to wait before the activity is canceled. 0 means never cancel.

Description

Provide a short description for the activity.

Input

The following is the input for the activity.

Input Item Data Type 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.
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 Connection Properties Complex (Name/Value Pair) Optionally specify the properties to be used when creating OpenSpirit connection. If not specified and the query scope type is Selected Data Source then the properties if any in the configuration of the input Data Source will be used.
DataSourceType String Type of DataSource registered with OpenSpirit. Only visible and required when the query scope type is "Input Datasource".
DataSourceTypeVersion String Version of the Data source type. Only visible and required when the query scope type is "Input Datasource".
DataSource String Name of the Data source. Only visible and required when the query scope type is "Input Datasource".
Project String Name of the Project in the DataSource. Only visible when the query scope type is "Input Datasource". Required when the data source type supports projects.
OspDataKeyArray String array One or more OpenSpirit datakeys that define the query scope. Only visible when the query scope type is "Input Datakeys".
WhereClauseParameters Complex (One or more parameter elements) One variable for each prepared parameter supplied in the WHERE clause of 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.

Output

The following is the output for the activity.

Output Item Data Type Description
OspOutput Complex The root element for the output of the OpenSpirit Delete activity. This element contains all output items for the activity.
NumRowsDeleted Integer The number of rows deleted by this query.
QueryParameters Complex The input QueryParameters are echoed here so that subsequent activities may use the supplied values.
SQLInput String The SQL string for the query.

Fault

The Fault tab lists exceptions that are thrown by this activity.

Error Schema Element Datatype Description
msg string The error message returned by the plug-in.
msgCode string The error code returned by the plug-in
Fault Thrown When...

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.