Linking a Variable to a Sql Read Query

A variable can be linked to the result of a Sql read query by choosing the Sql Variables source. See the topic Data Connections Overview. When a variable is linked in this way its real-time value, and optionally its timestamp and quality, are sourced from a third party database such as Microsoft's SQL Server. Read queries can be configured so that they are polled periodically and / or on event.

A Sql read query returns a list of rows or a scalar value. In the case of a list of rows, you must be able to select the row(s) and the column(s) to be mapped to the variable and its metadata. The configuration is in three steps (or four steps if the query contains parameters) each with its own configuration tab.

  1. Data selection - Selects the Sql Connection and its subordinate Sql Read Query.
  2. Row selection - Selection of a particular row or all rows from the table of results.
  3. Mapping - Selection within the row of the field that contains the value and, optionally, the field that contains the timestamp and the status of the variable.
  4. Parameters - Configuration of any parameters that the query contains. Parameters add flexibility to a Sql Query by allowing the value of one or more fields to be provided by the configuration of the linked variable.

See the topic Sql variables Read query mapping examples for a detailed explanation and examples.

Mapping variables to a Sql read query is subject to scope-related constraints:

  • The scope of the Sql read query and the scope of the mapped variable must match,

  • When using a variable to set the value of an input parameter, constraints related to scope apply to avoid user-specific data to be used as inputs in a context where the query results will be accessible to other users.

The following matrix summarizes the list of valid combinations of scopes:

Scope of the mapped variable Scope of the Sql read query Scope of the variables used to set input parameters
Local Local Local, Shared
Shared Shared Local, Shared
Session Session Local, Shared, Session
Client-context Client-context Local, Shared, Session, Client-context

How to configure the Data Selection

  1. Open the configuration dialog for the variable and select the Sources tab.
  2. Select Sql Variables as the Source. Sql Variables is only available if one or more Sql connections have been configured. A new tab Reading appears for configuration of the data selection. ClosedShow picture
  3. Select the Sql connection and the subordinate Sql query. The appearance of the Source tab is now modified to display three more sub-tabs, Row selection, Mapping and Parameters. ClosedShow picture
  4. Even though the configuration is only partially completed, it is possible to test the query using the Execute button. If the query requires any parameters, you are prompted to enter them. The results from the query are displayed in the result field. Testing the Sql Query has the advantage of discovering the Field Names that are returned by the query. The field names are used in the other configuration tabs. In the following screen shot the field name that has been discovered is BIT. It is followed, in parenthesis, by the field type. ClosedShow picture

The Traces button is used to enable trace information in the Event Viewer (F7) that can be useful when debugging a particular query.

If the variable has the command property set, a Writing tab also appears. See the topic Linking a variable to a Sql write query.

How to configure the Row selection

The row selection can be either by Position, Field value, or All rows. ClosedShow picture

  • By position - Typically used with wide table format where each data variable appears in a separate column. Selects a row by its actual position in the query results. You can enter a position (1 by default to select the first record), or select Last row to select the last returned row.
  • By field value - Typically used with a narrow table format where one column contain the values and other columns the metadata of the values. Selects a row by the value of a particular column (lookup). If the Sql read query has been tested using the Execute button, the Field name and Value list boxes are populated with the possible values. The selected field must be of type boolean, integer or string. Double, float, decimal, dates or structured data types cannot be used.
  • All rows - Typically used with a wide table format where the Sql read query has been configured to return several rows of the same value. Selects and processes all data rows.
  • Selection filter - Allows you to define an expression that will sort out returned rows you want to exclude. If for a given row, the result of the expression is True, the row is taken into account. If the result is False, the row is ignored. For example, if you want to exclude rows returning the value Null in the column called Temperature, you could add the following Selection Filter: =if(IsDbNull(FieldValue("Temperature")) == false, true, false)
  • When row selection is empty - Allows you to control how the variable VTQ is updated if the query does not return any row.
    • If set to Preserve variable state, the VTQ of the variable is maintained.
    • If set to Set variable as invalid, the variable quality is set to NS.
    • If set to Replace variable value by, the variable value is set to the specific value you enter.

When using the By position option, keep in mind that any ORDER BY clause in your Sql query affects the result you will obtain. In most cases, changing the ORDER BY clause in the query requires changing the value of Position in the variable mapping.

How to configure the Mapping

The Mapping tab is used to map the fields containing the value, and optionally the timestamp and the status (quality) to the variable. Mapping is achieved with an expression taking advantage of the FieldValue() function with the name of the column of interest as argument. The expression is entered using the Expression Editor dialog. See the topic Using the Expression Editor. If the Sql read query has been tested using the Execute button, clicking the button at the top left of the dialog displays a list of the available fields. ClosedShow picture

The expression must contain the function FieldValue(), which selects the field. In addition, it can also contain other expression elements should there be any requirement to manipulate the value before it is used to update the variable value (resp. timestamp or quality).

Mapping the status (quality) of the variable can be achieved by using any of the following constants:

  • VAR_QUALITY_GOOD - To set the variable status to valid
  • VAR_QUALITY_NS - To set the variable status to invalid
  • VAR_QUALITY_NSCOM - To set the variable status to invalid due to communication error

As an example, the typical expression you will use for mapping the quality is:

if(FieldValue(“col_Quality”) == 192, VAR_QUALITY_GOOD, VAR_QUALITY_NS)

where col_Quality is the name of the field where the quality is stored in the data source.
The value 192 is an example if the data source stores qualities with values similar to OPC.

If the Timestamp variable property is not mapped to a field from the query result, it will be set by PcVue and managed accordingly. The same goes for the Quality property.

Because there is a huge variety of timestamp formats, mapping the timestamp will eventually requires a conversion.

See the topic Expression functions for timestamp manipulation for more information about the expression functions available for date and time conversions.

  • Preserve variable's state on error - If enabled, the VTQ of the variable will be maintained if the mapping cannot be performed. For example if an expected field is missing in the returned rows, if a data type conversion error occurs or if the query fails. If not enabled, the variable is set invalid in such a situation.

The function IsDbNull() can be used to accommodate the fact that a returned field could be null for some rows.

For example to control how the quality property of the variable will be set if the col_Quality field is null, you could use an expression such as:

If(IsDbNull(FieldValue("col_Quality")), VAR_QUALITY_NS, FieldValue("col_Quality"))

You can also use the function IsFieldExists() to test if a field exists in the requested table.

How to configure the Parameters

If one or more parameters are found in the Sql read query, they are listed in the Parameters tab and must be substituted using an expression. A parameter can only be substituted once. If the Sql read query is subsequently used in another variable, the parameter substitution is pre-configured and the field is dimmed indicating it is read only.

A parameter defined by an expression can reference a property of the variable (domain, Extended attribute etc.) and use the functions and operators of the expression engine. The button at the top left of the expression editor dialog displays a list of the variable properties that can be substituted. ClosedShow picture

Parameters can only be set once - the first variable in which a Read Query is used will set the value of the parameter.

For example, Register01 has the domain Security. Register01 uses a Read Query containing ?Param01 configured to use the Domain. The value substituted for ?Param01 will be Security. Register02 uses the same Read Query. Irrespective of the domain of Register02, Security will be used as the value for ?Param01.

If a parameter is mapped with an indirection on a Text extended attribute so that it is set with a variable value, the adequacy of the scope of the variable with regards to the scope of the Sql query is only verified at run-time. It is not checked at configuration time, nor at start-up time.

Expression functions exclusively for Sql query variable mapping

Function and syntax Operation
FieldValue(QueryResultFieldName)

Return the value of the field QueryResultFieldName that belong to the result of a Sql Read Query. The format of the returned value is the same as that of the field.
Example: FieldValue("Temperature")
IsDbNull Function to evaluate a returned field value against NULL.
Row_Num Return the number of the row in the dataset returned by the query.

Functions exclusively for Sql query substitution parameters

The following functions all return a particular property of the concerned variable.

Function and syntax Variable property
AlarmLevel

Alarm level. Type VT_I2.
Example: 1
AssocLabel Value of the variable in terms of its associated label. Type VT_BSTR
Example: "Running"
BinAttr Complete extended binary attribute. Type VT_UI4.
Example: 128
BinAttrXX (01 to 30) The value of the specific bit of the extended binary attribute. Type VT_BOOL.
Example: BinAttr01 > 0
Description Description. Type VT_BSTR.
Example: "Room 101 measured temperature"
Domain Domain. Type VT_BSTR.
Example: "BUILDING01"
Name Name. Type VT_BSTR.
Example: "Building01.Floor01.Room01.Temperature"
Nature Nature. Type VT_BSTR.
Example: "HEATING"
Quality Quality. Type VT_UI2.
Example: 192
TextAttrXX (03 to 16) Extended text attribute 03 to 16. Type VT_BSTR.
Example 1: TextAttr05 > "Building01.Floor01.Room01.Temperature"
Example 2: TextAttr06 > "Help.PDF"
TextIndAttrXX (03 to 16) Deferred extended text attribute 03 to 16. Type VT_BSTR.
Example: "21.75"
TS Timestamp. Type VT_R8.
Example: 43895.647743055553
Units Units. Type VT_BSTR.
Example: "Deg °C"
UserName User name. Type VT_BSTR.
Value Value. The type is either VT_BSTR (Text variable) or VT_R8 (Register or Bit variable).
Example: 21.75

Traces

The Traces button allows you to enable diagnostic trace messages. Each trace generates specific messages that are logged in the Trace Files and displayed in the Event Viewer dialog (F7).

  • Trace 1: All drivers - General - Enable general traces related to the activity between the Variable Manager and the Sql variable driver.
  • Trace 2: All drivers - Details - Enable detailed traces related to the activity between the Variable Manager and the Sql variable driver.
  • Trace 3: All drivers - Performances - Enable traces related to performances of the Sql variable driver.
  • Trace 4: Sql variable driver - Performances - Enable traces related to performances of the Sql query results processing.