The Relational Adaptor allows data from Oracle, SQL Server, and ODBC databases to be transformed into time series data.
Relational Adaptor Settings
The following table lists the adaptor's parameters, along with the name and type to be used in the Import/Export spreadsheet.
| Parameter | Description and example | Name | Type |
| Connection Type | The type of connection to use to connect to the target relational database. Options: Microsoft SQL Server, Oracle, ODBC, OLEDB. | ConnectionType | String |
| Provider | The OLEDB provider to use for connecting to the target database when the connection type is set to OLEDB. Applies to OLEDB only. | Provider | String |
| Server | Name of the server to connect to. For Oracle connections, this value can contain the relevant TNS. For ODBC connections, this value is expected to specify the DSN to use. | Server | String |
| Database | Name of the database to connect to. This parameter is not required for all database connection types. | Database | String |
| Use Trusted Connection | When this option is selected, a trusted connection will be used to connect to the relational database. Otherwise, the user ID and password must be provided for the connection. Options: True, False. | UseTrustedConnection | Boolean |
| User ID | User ID to use to connect to the target relational database. If trusted connection is used, this value will be ignored and may be left blank. | UserId | String |
| Password | Password to use to connect to the target relational database. If trusted connection is used, this value will be ignored and may be left blank. | Password | EncryptedString |
| Allow Duplicate Timestamps | Whether this data source supports returning multiple values for a given timestamp. If this value is set to false and the query returns 2 or more values with the same timestamps, the adaptor will return only 1 of the values and discard other values with the same timestamp. If set to true, the adaptor will return all values even if they have the same timestamp. Options: True, False. | AllowDuplicateTimestamps | Boolean |
| Allow Write | Whether this data source should allow P2 Server client applications to write data to tags in the relational database. This must be set to true if you want the datasource to handle Put requests to save values in the database. Options: True, False. | AllowWrite | Boolean |
| Tag Name Column | Name of the column containing the name for associated tags. Used by the available tags query and also by single point and historical queries. | TagNameColumn | String |
| Tag Name Column Type | Type of the tag name column. This setting is used to determine what type of query parameter should be generated for filtering tag names. Options: Unicode, NonUnicode | TagNameColumnType | String |
| Wide Tag Name Link | For wide queries, tag names are generated by concatenating the row name and column name of the tag’s value, with a joining string between. This parameter specifies the joining string to use when creating wide tag names. This option was added in version 4.5.3. | WideTagNameLink | String |
| Tag Description Column | For narrow queries, the name of the column containing the description for associated tags. For wide queries, the columns whose name ends with this text will be treated as the columns containing the descriptions, if the remainder matches another column’s name. If not specified, no description will be displayed in the user interface. Used only by tag discovery query. | TagDescriptionColumn | String |
| Tag Unit Column | For narrow queries, the name of the column containing the units for associated tags. For wide queries, the columns whose names end with this text will be treated as the columns containing the units, if the remainder matches another column’s name. If not specified, no units will be displayed in the user interface. Used only by tag discovery query. | TagUnitColumn | String |
| Tag Maximum Value Column | For narrow queries, the name of the column containing the maximum value for associated tags. For wide queries, the columns whose names end with this text will be treated as the columns containing the maximum values, if the remainder matches another column’s name. If not specified, no maximum value will be displayed in the user interface. Used only by tag discovery query. | TagMaximumValueColumn | String |
| Tag Minimum Value Column | For narrow queries, the name of the column containing the minimum value for associated tags. For wide queries, the columns whose names end with this text will be treated as the columns containing the minimum values, if the remainder matches another column’s name. If not specified, no minimum value will be displayed in the user interface. Used only by tag discovery query. | TagMinimumValueColumn | String |
| Data Timestamp Column | Name of the column containing the timestamp of fetched data points. Used by single point and historical queries. | DataTimestampColumn | String |
| Data Value Column | Name of the column containing the value of fetched data points. Used by single point and historical Narrow queries; Wide queries ignore this value. | DataValueColumn | String |
| Data Confidence Column | Name of the column containing the confidence of fetched data points. Used by single point and historical queries. | DataConfidenceColumn | String |
| Query Type | Type of the configured queries which determines how the adaptor will handle the configured queries and statements. This value also determines what kind of keywords will be available. Options: Narrow, Wide. | QueryType | String |
| Available Tags Query | SQL query which returns the list of available tags. | AvailableTagsQuery | String |
| Single Point Raw Query | SQL query which returns the raw values for the given tags at a timestamp. This query should be written to return, at minimum, the single value on or immediately before the given timestamp or no rows. | SinglePointRawQuery | String |
| Use Single Point Last Known Value Query | When this option is selected, the configured Single Point Last Known Value Query will be used for resolving single-point requests with the Last Known Value sampling method. Otherwise, the Single Point Raw Query will be used and additional, built-in calculations will be performed to produce last known values. Options: True, False. | UseSinglePointLastKnownValueQuery | Boolean |
| Single Point Last Known Value Query | The SQL query which returns the last known value for the given tags at a timestamp. If this query is not defined, raw queries will be executed and sampling will be done by the adaptor. | SinglePointLastKnownValueQuery | String |
| Use Single Point Average Query | When this option is selected, the configured Single Point Average Query will be used for resolving single-point requests with the Average sampling method. Otherwise, the Single Point Raw Query will be used and additional, built-in calculations will be performed to produce average values. Options: True, False. | UseSinglePointAverageQuery | Boolean |
| Single Point Average Query | The SQL query which returns the calculated average values for the given tags at a timestamp. If this query is not defined, raw queries will be executed and sampling will be done by the adaptor. | SinglePointAverageQuery | String |
| Use Single Point Linear Interpolate Query | When this option is selected, the configured Single Point Linear Interpolate Query will be used for resolving single-point requests with the Linear Interpolate sampling method. Otherwise, the Single Point Raw Query will be used and additional, built-in calculations will be performed to produce linear interpolate values. Options: True, False. | UseSinglePointLinearInterpolateQuery | Boolean |
| Single Point Linear Interpolate Query | The SQL query which returns a calculated linear interpolated value for the given tags at a timestamp. If this query is not defined, raw queries will be executed and sampling will be done by the adaptor. | SinglePointLinearInterpolateQuery | String |
| Historical Raw Query | The SQL query which returns the raw values for the given tags within the time range. | HistoricalRawQuery | String |
| Use Historical Last Known Value Query | When this option is selected, the configured Historical Last Known Value Query will be used for resolving historical requests with the Last Known Value sampling method. Otherwise, the Historical Raw Query will be used and additional, built-in calculations will be performed to produce last known values. Options: True, False. | UseHistoricalLastKnownValueQuery | Boolean |
| Historical Last Known Value Query | SQL query which returns the last known values for the given tags within the time range. If this query is not defined then the raw queries will be executed and sampling will be done by the adaptor. | HistoricalLastKnownValueQuery | String |
| Use Historical Average Query | When this option is selected, the configured Historical Average Query will be used for resolving historical requests with the Average sampling method. Otherwise, the Historical Raw Query will be used and additional, built-in calculations will be performed to produce average values. Options: True, False. | UseHistoricalAverageQuery | Boolean |
| Historical Average Query | The SQL query which returns the calculated average values for the given tags within the time range. If this query is not defined, the raw queries will be executed and sampling will be done by the adaptor. | HistoricalAverageQuery | String |
| Use Historical Linear Interpolate Query | When this option is selected, the configured Historical Linear Interpolate Query will be used for resolving historical requests with the Linear Interpolate sampling method. Otherwise, the Historical Raw Query will be used and additional, built-in calculations will be performed to produce linear interpolate values. Options: True, False. | UseHistoricalLinearInterpolateQuery | Boolean |
| Historical Linear Interpolate Query | The SQL query which returns the calculated linear interpolated values for the given tags within the time range. If this query is not defined, raw queries will be executed and sampling will be done by the adaptor. | HistoricalLinearInterpolateQuery | String |
| Put Select Query | When Allow Write is enabled, this SQL statement will be executed to determine whether a row for the given timestamp already exists in the target database. If this query does not return any rows, an insert operation will be performed; otherwise an update operation will be done. | PutSelectQuery | String |
| Put Insert Statement | When Allow Write is enabled, this SQL statement will be executed to insert new rows in the target database. | PutInsertStatement | String |
| Put Update Statement | When Allow Write is enabled, this SQL statement will be executed to update the value of existing rows in the target database. | PutUpdateStatement | String |