OpenSpiritSQLDirect

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. The SQL Editor is used if the query is supplied on configuration.
See the SQL Editor section regarding the SQL Editor.

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 Supplied On No Indicates whether the SQL will be provided in the configuration panel or as an input to the activity. Parameterized SQL statement is not allowd when supplied on input
SQL Model 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) No 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 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 The maximum number of rows to return.
Process in Subsets No 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.
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 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.
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".
LengthUnit String Optionally specify the preferred unit for length measurements. Ignored if the SQL is not a SELECT statement.
TimeUnit String Optionally specify the preferred unit for time measurements. Ignored if the SQL is not a SELECT statement.
MaxRows Integer The maximum number of rows to return. Overrides any value that may have been supplied in the configuration panel. Ignored if the SQL is not a SELECT statement.
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. Ignored if the SQL is not a SELECT statement.
InputParameters Complex (One or more parameter elements) One variable for each prepared parameter supplied in the 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. Only visible when the SQL is supplied on Configuration and has one or more parameters.
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. Only visible when the SQL is supplied on Configuration and has one or more WHERE clause parameters.
SQL String The SQL statement to be executed. Only visible when the SQL is supplied on Input.
Model String The name of the SQL model. If the name is null or an empty string, the OpenSpirit model is assumed. Only visible when the SQL is supplied on Input.
ModelVersion String The version of the SQL model. Null version is not accepted unless the model is the OpenSpirit model in which case version 2.9 is assumed. Only visible when the SQL is supplied on Input.

Output

The following is the output for the activity.

Output Item Data Type 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.
resultSet Complex The returned results from the SELECT query. Not used for other query types. The resultSet takes two forms:
1. If the SQL query is defined at design time then a sequence of result rows containing named column values are returned like this:

                                         


2. If the SQL query is defined at runtime then metadata (defining the columns) and a sequence of result rows containing generic column values are returned like this:

                                         

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...

SQL Editor

The SQL Editor let you enter/edit the SQL to execute. It also uses a generic SQL parser to fetch parameter and result set metadata (i.e. column names and data types). The metadata is used to build the input/output schemas for the activity.

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. Type in the SQL query or copy/paste from some OpenSpirit Query Builder and edit it as needed.
3. Click the "Fetch" button to parse the SQL to fetch parameter and result set metadata.
4. If the SQL cannot be parsed an error dialog will show the reason. Click OK and use the "Fetch" button on the Configuration panel to fetch the metadata using an OpenSpirit data connection.
5. If the SQL can be parsed but contains errors the errors will be highlighted. Hover on the errors to see the reasons and fix them.

NOTE: The generic parser does not support NATURAL JOIN and JOIN with USING clause. Use the JOIN with ON clause instead.
For example:
Instead of
"SELECT * FROM EpiWell_WellBore a NATURAL JOIN EpiWell_Well b"
or "SELECT * FROM EpiWell_WellBore a JOIN EpiWell_Well b USING(a.Well)"
use "SELECT * FROM EpiWell_WellBore a JOIN EpiWell_Well b ON a.Well = b.PrimaryKey$"