InSQL Connector

This document will discuss the unique functionality in the Wonderware® IndustrialSQL Server™ (InSQL) Connector.

Configuration

When configuring InSQL for use with SAP xApp Manufacturing Integration and Intelligence (SAP xMII) insure that the Microsoft SQL Server is configured to listen on TCP/IP as the transfer protocol.  Typically, InSQL will be configured to listen on port 1434 (port 1433 is the default port assigned to Microsoft SQL Server itself).  While named pipes can be used, it can require a fairly complicated security configuration and makes data access in distributed networks more complicated.  Enabling SQL Server to listen on a TCP/IP link is the optimal approach and can result in a modest performance increase in many cases.

The following table shows the specific server parameters settings needed to configure InSQL to use either the default TCP/IP protocol or the OLEDB method.  The additional server parameters listed in the System Administrator editor are common to both connection types.

Name

Description

Type

Usage

URL

Driver-specific string defining how to access the physical database

String

Default: jdbc:inetdae:hammer9:1434?database=Runtime&sql7=true

OLEDB: jdbc:inetdae:hammer9:1433?database=Runtime&sql7=true

User Name

Database-specific User ID

String

Default: wwUser

OLEDB: a user account with appropriate permissions to query the Runtime database

Password

Database-specific password

String

Default: wwUser

OLEDB: the password corresponding to the User Name setting

UseOLEDB

Determines what mode the InSQL data is queried

Boolean

Default: false (default for InSQL version 7.x and earlier)

OLEDB: true (default for InSQL version 8.x and later)

Supported Modes

GroupList Mode

The GroupList mode enables the discovery of the tag groups defined within the connector.  In the case of InSQL, these would include the system-defined groups (All Analog Tags, All Discrete Tags, etc.) as well as user-defined public and private groups.  The user ID and password used to connect to the database (as configured for each server) will control which "private" groups are displayed in the results of this query.  The GroupList mode can be used in tandem with the Mask parameter. This enables the user to filter only the groups that match the mask pattern.

TagList Mode

The TagList mode enables the discovery of the tags defined within the connector.  This mode can be used in tandem with both the Group and the Mask parameters.  If no value is provided for the Group or Mask parameter, all tags will be returned in the query.  If a value is provided for the Group parameter, only tags for that group will be displayed.  The Mask parameter can be applied to either of the prior two examples to provide additional filtering, which will return only tags that match the mask pattern.

Data Retrieval Behavior

History Mode

The connector can provide historical values in an interpolated manner for analog and discrete tags.  Interpolation can be accomplished by specifying either the # of rows desired or the retrieval resolution.  If the mode is "History" and a value is provided for the Resolution parameter (which is in seconds), the connector will retrieval evenly-spaced values starting at the beginning of the time interval, up to the maximum # of rows specified in the RowCount parameter.  If no value is provided for the Resolution parameter, the connector will return an evenly-spaced number of values based on the value of the RowCount parameter.

For example, if the time interval is 1 hour, Resolution is 15, and RowCount is 240, the connector will return evenly spaced values each 15 seconds, up to 240 values (which would span the entire hour).

If the time interval is 1 hour, Resolution is not provided or is set to zero, and RowCount is 120, the connector would return 120 evenly spaced values, at an effective interval of 30 seconds.

HistoryEvent Mode

The connector can provide historical values "as they were stored" by InSQL for analog and discrete tags.  This mode provides no interpolation of values.

Statistics Mode

When retrieving data for statistical calculations, the connector utilizes the same techniques as in the "HistoryEvent"  mode.  This ensures precision of statistical measurements, particularly time-weighted average, by using the exact storage time and values from the historical database.

Current Mode

The connector can provide current values for discrete and analog tags with all versions of InSQL.  However, if the InSQL version is 7.1 or newer, the connector can also provide live values for string tags.

Accessing Special InSQL Tables

There is no need for special modes to access the StringHistory, summary, and event tables of InSQL, as these are standard Microsoft SQL Server tables and are more easily accessed via the IDBC connector.  However, this does require that the IDBC connector be licensed and configured for use.  Simply create a server in the IDBC connector identically configured to the server in the InSQL connector, and use the Template Editor to create your queries.