SQL Data Query Driver

Not counted towards your tag license limit.

The SQL Data Query driver provides a means for a VTScada application to capture data from an ODBC-compliant database and to place that data into the application's Analog Status, Digital Status, Analog Input, or Digital Input tags. The driver allows time-stamped historical data in the database, or current values, or both to be read into VTScada tags and sent to the VTScada historian.

To use the driver, do the following steps in order:

If you plan to use an ODBC data source name for the connection, create this using the Microsoft ODBC administration tools. Choose the 32-bit or 64-bit ODBC administrator as appropriate for the data source. VTScada can connect to either.
Create and configure one or more SQL Data Query Driver tags.
At least one tag will be required for each ODBC source used. Additional tags may be required if more than one SQL Query template is required.
Create the I/O tags that will receive data from the driver tags.
Use String I/O tags if retrieving text values.
If using Analog Status tags, ensure that you set the scaling parameters appropriately. In most cases, the Unscaled Data Process Max and the Scaled Data Process Max values should be identical so as to avoid scaling the retrieved value.
Draw the SQL Data Query Driver tag using the built-in drawing widgets.
The Show Stats button and a Communication Indicator widget are both recommended.

Error Handling

Connections to database servers via ODBC can provide a wide range of errors that are related to the type of database, the ODBC driver used, and the type of fault encountered. For some of these errors, the best course of action is to disconnect from the database and then reconnect to it to clear the fault. Because the cases where this needs to be done can vary widely and may be influenced by your server or network configuration, VTScada provides a means to instruct the SQL Data Query Driver tag to perform a disconnect and reconnect upon receiving specific error codes from the database.

To configure this feature, you must first determine what error codes require that the database be disconnected (this may take some trial and error) and then enumerate these error codes using a set of system settings that define both the database type and the error codes. In practice, these settings will take the following general format:

SSSODBCDisconnectErrorN = EEE

Where:

SSS defines the server type and can be one of the following. (Note that this must match the server type you selected when configuring the tag)

MS-SQL-Server
MS-Access
Oracle
MySQL
SyBASE
PolyHedra

N defines the error index, which must start with a value of 1 and increase in steps of 1 for each different error code. ODBCDisconnectError must always match this test for all settings

EEE defines the error code returned by the ODBC driver for the database

These codes can be configured using the advanced option Edit Properties dialog or by manually editing the Settings.Dynamic file, followed by an import.

Example #1:

If a connection to an MS SQL-Server database returns error codes 234, 895, and 4589 when it needs to be disconnected & re-connected, create the following three application settings:

MS-SQL-ServerODBCDisconnectError1 = 234
MS-SQL-ServerODBCDisconnectError2 = 895
MS-SQL-ServerODBCDisconnectError3 = 4589

Example #2:

If a connection to a MySQL database returns error codes 1111,222, 666, and 4321 when it needs to be disconnected & re-connected, create the following four application settings:

MySQLODBCDisconnectError1 = 1111
MySQLODBCDisconnectError2 = 222
MySQLODBCDisconnectError3 = 666
MySQLODBCDisconnectError4 = 4321

Related information:

SQL Data Query I/O Addressing

SQL Driver Error Codes

Best Practices for Tags

SQL Data Query Driver properties ID tab

The ID tab of every tag includes the same common elements: Name, Area, Description, and Help ID.

Name:

Uniquely identifies each tag in the application. If the tag is a child of another, the parent names will be displayed in a separate area before the name field.

You may right-click on the tag's name to add or remove a conditional start expression.

Area

The area field is used to group similar tags together. By defining an area, you make it possible to:

Filter for particular tag groups when searching in the tag browser
Link dial-out alarm rosters to Alarm tags having a particular area
Limit the number of tags loaded upon startup.
Filter the alarm display to show only certain areas.
Filter tag selection by area when building reports

When working with Parent-Child tag structures, the area property of all child tags will automatically match the configured area of a parent. Naturally, you can change any tag's area as required. In the case of a child tag, the field background will turn yellow to indicate that you have applied an override. (Orange in the case of user-defined types. Refer to Configuration Field Colors)

To use the area field effectively, you might consider setting the same Area for each I/O driver and its related I/O tags to group all the tags representing the equipment processes installed at each I/O device. You might also consider naming the Area property for the physical location of the tag (i.e. a station or name of a landmark near the location of the I/O device). For serial port or Roster tags, you might configure the Area property according to the purpose of each tag, such as System or Communications.

You may define as many areas as you wish and you may leave the area blank for some tags (note that for Modem tags that are to be used with the Alarm Notification System, it is actually required that the area field be left blank).

To define a new area, type the name in the field. It will immediately be added. To use an existing area, use the drop-down list feature. Re-typing an existing area name is not recommended since a typo or misspelling will result in a second area being created.

There is no tool to remove an area name from VTScada since such a tool is unnecessary. An area definition will exist as long as any tag uses it and will stop existing when no tag uses it (following the next re-start).

Description

Tag names tend to be brief. The description field provides a way to give each tag a human-friendly note describing its purpose. While not mandatory, the description is highly recommended.

Tag descriptions are displayed in the tag browser, in the list of tags to be selected for a report and also on-screen when the operator holds the pointer over the tag’s widget. For installations that use the Alarm Notification System, the description will be spoken when identifying the tag that caused the alarm.

The description field will store up to 65,500 characters, but this will exceed the practical limits of what can be displayed on-screen.

This note is relevant only to those with a multilingual user interface:
When editing any textual parameter (description, area, engineering units...) always work in the phrase editor. Any changes made directly to the textual parameter will result in a new phrase being created rather than the existing phrase being changed.
In a unilingual application this makes no difference, but in a multilingual application it is regarded as poor practice.

Help Search Key

Used only by those who have created their own CHM-format context sensitive help files to accompany their application. Refer to Custom Help Files

SQL Data Query properties Database Settings tab

The Database Settings tab holds the properties required to connect to the database that you will query.

Database DSN

The Data Source Name (DSN) is configured using the Microsoft ODBC Administrator tool. It is used in place of a connection string to access a database via an ODBC-compliant driver.

Database Type

In addition to the usual types supported by VTScada: Oracle, MySQL, and MS-SQL Server, you can select from Access, PolyHedra or SyBase.

Username and Password

Provide the credentials that are required to connect to your database program.

While the password is not displayed on this configuration screen, it is stored using plain text and will be visible if the tag database is exported.

While not a common configuration, you can set the SQL Data Query Driver's password to an expression. If you are using an optimized expression, you should use a Settings.Startup property because an optimized expression does not react to dynamic changes. (You will need to restart the application for the expression to react to the change.) If you use an unoptimized expression, note that those do react to dynamic changes, and will update if you change a Settings.Dynamic property that is referenced in the expression.

SQL Data Query properties Query Settings tab

Single Value SQL Query Template

Use this if either of the following is true:

The data will be queried in a format of one query per address (input tag) or
Only one tag address is supplied (attached) to the driver.

Templates are the SQL selection queries that you want to execute. The table name and where-clause values to query are written as replaceable parameters, with values coming from the I/O tags using this driver. For more information, refer to the section, "Creating Queries for the SQL Data Driver", found within this topic.

Multi Value SQL Query Template

The tag query template to use if multiple tag addresses will be returned by a single query.

Use Multi Value Query Template

Force the use of the "Multi Value SQL Query Template" if more than one tag is attached.

Use Single Quotes on Addresses

Select this box to apply single quotation marks to all tag addresses / where-clause parameters, when they are inserted into a query.

Select this option if querying based on text values. Deselect this option if querying based on numeric values.

Hold

Select this to have I/O tags attached to the driver hold their last value in the event of a communication failure. If not selected, tags will have their value set to invalid on a communication failure.

Query Result Timestamp Time Zone

Using these radio buttons you can display the timestamp within your results using either the local time zone or UTC. Note that the VTScada Historian stores all data using UTC, but this might not be the case for the database that you are querying.

Time-Out Limit

Time limit in seconds for queries to the database server. Queries that exceed this time will be declared as a communication failure and data for associated tags may be invalidated, depending on the state of the "Hold" setting.

Creating Queries for the SQL Data Driver

The SQL Data Query Driver relies on user-defined query templates in combination with parameters supplied by the address fields of the associated tags to create configurable queries that extract data as needed. Use this to create generic query template to be created at the driver level that are then modified by the contents of the attached input tag address fields through the use of replaceable tokens. This enables the driver tag to read a database table containing data for multiple tags with a single query definition.

A user defined query template can contain the following tokens:

^A - ID value token
^T - Table name token

Several examples following show how these can be used in conjunction with the tag addresses to create queries to extract database table values into VTScada tags. Note that query templates may contain modifiers as appropriate for the database being queried, such as "LIMIT" (MySQL, Oracle), "TOP" (Access, MS-SQL), "ORDER BY", etc.

Required Query Result Format

All queries returned from the database must be structured in a multi-field format following one or the other of the formats described following:

Option 1 - ID and Value

The data will be returned as two columns with the first field containing a text string or numeric value that matches the ID portion of the address field of the tag for which it is destined. The second column will contain the data value for the tags that match the address ID in the first field. For example, if an Analog Status tag is created with its address set to "ABCDE", a query resulting in the following returned data set would assign a value of 36.7 to the raw value of the tag:

Field 1 - ID Field 2 - Value

ABCDE 36.7

AAAAA 56.8

GHGHG 99999

If two other tags are created with their addresses set to "AAAAA" and "GHGHGH", their raw values would be set to 56.9 and 99999 respectively by the same query result. In this example, the timestamp of the Analog Status tags would be set to the current time as a result of the query result as there are no timestamps in the data set returned from the database.

Field 1 - ID	Field 2 - Value
ABCDE	36.7
AAAAA	56.8
GHGHG	99999

Option 2 - ID, Value, and Timestamp

An optional third timestamp field may also be returned from the database and if present will be automatically detected by the driver and used as the timestamp for the tags. For the above example, if the table to be queried was instead formatted as shown here:

Field 1 - ID Field 2 -Tag Value Field 3 - Timestamp

ABCDE 36.7 2014-06-02 12:34:56

AAAAA 56.8 2013-06-02 13:44:16

GHGHG 99999 2012-06-02 14:54:06

Then the Analog Status tags with addresses "ABCDE", "AAAAA", and "GHGHGH" would be assigned values 36.7, 56.8, 99999, and timestamps "2014-06-02 12:34:56", '2013-06-02 13:44:16", & "2012-06-02 14:54:06" respectively.

Field 1 - ID	Field 2 -Tag Value	Field 3 - Timestamp
ABCDE	36.7	2014-06-02 12:34:56
AAAAA	56.8	2013-06-02 13:44:16
GHGHG	99999	2012-06-02 14:54:06

Using Query Templates

The driver tag's query templates function by allowing the user to create a generic query that will have some of its content replaced by data extracted from the address fields of the associated I/O tags through the use of the ID token ^A or the table token ^T. The examples following show several ways in which data can be read from a database using the query templates in conjunction with the tokens.

Example #1 - Single Tag Value from a Table

This example reads a single value from a specific table.

Given a query template configured as:

SELECT TagName, TagValue FROM DataLogTable WHERE TagName = '^A'

And, an Analog Status tag with its address set to AIT0045,

Then the resulting query would be:

SELECT TagName, TagValue FROM DataLogTable WHERE TagName = 'AIT0045'

The result is a two-field response where the first field is a match for the address field of the attached Analog Status tag.

An additional setting of the driver that makes building queries of this type easier is the "Use Single Quotes on Addresses". If selected, then the tag addresses are automatically delimited by the single quotation marks so the query template can be changed to:

SELECT TagName, TagValue FROM DataLogTable WHERE TagName = ^A

And, the resulting query will be the same. This option should be used only when querying based on text values, not numbers.

Example #2 - Multiple Tag Values from a Table Using Multiple Queries

If multiple tags need to be extracted from a single table, then the template shown in example #1 will function by executing one query for each connected tag. For example, if three tags are connected to the driver with addresses, AIT0045, PT0679, and FIT0098, then three queries are generated by the driver:

SELECT TagName, TagValue FROM DataLogTable WHERE TagName = 'AIT0045'

SELECT TagName, TagValue FROM DataLogTable WHERE TagName = 'PT0679'

SELECT TagName, TagValue FROM DataLogTable WHERE TagName = 'FIT0098'

This example is contingent on the following settings being made in the driver:

The template is entered in the "Single Value SQL Query Template" setting
"Use Multi Value Query Template" is NOT selected

Note that the setting "Use Multi Value Query Template" enables the user to select which template to use. If not selected, the "Single Value SQL Query Template" is always used. If selected, the "Single Value SQL Query Template" is used only if there is 1 tag attached to the driver, while the "Multi Value SQL Query Template" is used if there are multiple tags attached to the driver.

Example #3 - Multiple Tag Values from a Table Using a Single Query

As noted above in Example #2, a query template can be used to query data for multiple tags in a single query, provided that the query result correctly correlates the tag addresses in the first field with the data in the second field. If, for the above example, the "Use Multi Value Query Template" setting is selected, then the following template entered in the Multi Value SQL Query Template field:

SELECT TagName, TagValue FROM DataLogTable WHERE TagName In (^A)

would result in a query structured as:

SELECT TagName, TagValue FROM DataLogTable WHERE TagName In ('AIT0045','PT0679','FIT0098')

The dataset returned from this query would contain multiple records and the driver will assign the data to the appropriate tags based on the contents of the TagName field returned as the ID.

Example #4 - Using Table Name in the Address

If each tag requires data to be extracted from a different table, then a table name may be added to a tag's address and from there used to populate the table name field of a query automatically. Using this template:

SELECT TagName, TagValue FROM ^T WHERE TagName = '^A'

Where used with tags having the following addresses:

AIT0098/DataHourly

PT1111/DataDaily

FIT0108/DataMonthly

Then, the resulting queries would be in the form:

SELECT TagName, TagValue FROM DataHourly WHERE TagName = 'AIT0098'

SELECT TagName, TagValue FROM DataDaily WHERE TagName = 'PT1111'

SELECT TagName, TagValue FROM DataWeekly WHERE TagName = 'FIT0108'

Note that this example assumes that all three tables contain the columns, 'TagName' and 'TagValue'.

Example #5 - ID Does Not Exist In Database

In some instances, there may be a need to extract data from a database table where no unique ID exists to identify the data other than the field names in the table. For example, if a database contains a table named "LastReadings" that contains only 1 record with the following fields:

ReadingTime
Temperature
Pressure
FlowRate

If you wish to extract the most recent of these three readings into three individual tags, then the following query template could be used:

SELECT '^A', ^A, ReadingTime FROM LastReadings

By setting the tag addresses to "Temperature", "Pressure", and "Flow", the following queries will be created:

SELECT 'Temperature', Temperature, ReadingTime FROM LastReadings

SELECT 'Pressure', Pressure, ReadingTime FROM LastReadings

SELECT 'FlowRate', FlowRate, ReadingTime FROM LastReadings

Note the use of the ID in two places in the list of fields to select. By placing the first of these inside of single quotation marks, the query result will return this field value as a text string, which will then meet the driver requirements to have the first field match the ID portion of the address set by the tag. The second field returned will contain the corresponding data required for the tag from the named field itself.

Example #6 - Multiple columns of readings per row

In many databases, there will be many values per row in a table. For example, consider the following table named FieldReadings which has temperature, pressure and flow, all identified by ID:

ID	Temperature	Pressure	Flow
1001	50.6	100.4	20.5
1002	53.7	120.3	15.5

One approach is to define three separate driver tags, with the following query templates:

SELECT ID, Temperature FROM FieldReadings WHERE ID IN (^A)

SELECT ID, Pressure FROM FieldReadings WHERE ID IN (^A)

SELECT ID, Flow FROM FieldReadings WHERE ID IN (^A)

This benefits from driver read coalescing, in that the driver can efficiently retrieve data for multiple tags with a minimum of calls to the database. However, this comes at the cost of more complex tag configuration, where I/O tags must be configured to point to different drivers that all reference the same table.

Another approach is to override the use of the ^T table placeholder to instead refer to the column from which to retrieve the data.

The query template for the single instance of the driver tag would look like the following:

SELECT ID, ^T FROM FieldReadings WHERE ID IN (^A)

The tag addressing would look as follows:

1001/Temperature

1001/Pressure

1001/Flow

This has the benefit of reducing the number of driver tags to one and reducing configuration complexity across the system. However, due to the addressing scheme used, the driver can not take advantage of read coalescing, which will result in a database query for each tag value requested. This approach would be of use in a smaller system with a limited amount of tags that wish to query a fast database platform at spaced intervals. This approach may not be suited for large-scale systems querying data at high rates.

Related information:

SQL Data Query I/O Addressing