SQL Logger Group Tags

Not counted towards your tag license limit.

SQL Logging will record, in an ODBC-compatible database, the values from any tag that could normally be logged.

The SQL Logger Group tag type works in combination with the SQL Logger tag. You use the SQL Logger Group tag to define a database and the frequency of data logging for all the SQL Logger tags associated with it. The SQL Logger tag is used to select which values are to be logged to that database. You may link one or more SQL Logger tags to one SQL Logger Group, recording one or more values into a single database.

Before You Begin:

Before you can log values to a database, you will need to have 1) a database containing two tables created using the template in the VTScada examples folder, and 2) an ODBC DSN configured for your database.

  1. Setting up a database:

You can use any ODBC compatible database such as MS-Access, MS-SQL, MySQL, Oracle, etc. Instructions for how to install and configure a database program are beyond the scope of this documentation.

After you have created a database, you will need two tables within it to hold the data that VTScada will export. In the Example folder under VTScada (normally found as C:\VTScada\Examples) there are four files to help you with this task.

  • SQLLoggerAccess.mdb
  • SQLLoggerMySQL.SQL
  • SQLLoggerOracle.SQL
  • SQLLoggerSQLServer.SQL

The three files which end with the extension ".SQL", contain SQL commands to create the tables you will need in your database.

If you do not have a database and do not own a database program, you can still use this feature of VTScada by following these steps:

  1. From the examples folder of VTScada, copy the file named SQLLoggerAccess.mdb to your application folder.
  2. Rename it to match the data you plan to collect, being sure to keep the .MDB extension on the name

You are now ready to proceed.

  1. Configure an ODBC data source name (DSN)

This is created using the Windows™ Open Data Base Connectivity (ODBC) tool. A 64-bit version of this utility can be found in the Administrative Tools section the Windows™ configuration menu. A 32-bit version is available as "C:\Windows\Syswow64\odbcad32.exe". Instructions for using the Microsoft utility are beyond the scope of this topic.

This must be done on all potential servers for a networked VTScada application. The DSN must be identical on each machine.

Related information:

Best Practices for Tags

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 Logger Group properties Settings tab

The Settings tab holds the properties required to connect this tag to a database.

Database DSN

As described in the introduction to this topic, you must create and configure a database before using the SQL Logger Group tag.

The DSN (Data Source Name) must already exist on your system and be pointing to the database you created for this tag.

Database Type

Tell VTScada what type of database you are writing to.

CAUTION: The Database Type selection MUST match your database. Choosing an incompatible type will result in the tag failing to write values to your database due to differences in data format.

Auto Delete Records Older Than (Days)

Limit the size of the database by automatically deleting records older than (n) days.

You may choose to set this value to 0 to turn off the auto-delete feature.

Username, Password

The Username and Password fields should match whatever is required to connect to your database. If you are using MS Access and did not configure a user name and password, then you may leave these fields blank.

Due to the method by which the password is encrypted, it is not possible configure it using a parameter expression.

Tag ID Table Name, Tag Data Table Name

The two table name fields should not be changed under ordinary circumstances. The database configuration utility supplied with VTScada created these table names for you by default. (see SQL Logger Group Tag: Before you begin)

SQL Logger Group properties Log Settings tab

Contains options to configure the timing for when data will be written to the database.

There are three choices of Log Type: Interval, Triggered and On Data Change. Note that all the tags attached to a particular SQL Logger Group through the associated SQL Logger tags will use these settings.

Log Type

  • "Interval" - Record values every (n) seconds. Use this when you want continuous monitoring of the system, setting the interval to the number of seconds you want between each data output.
  • "Triggered" - Write values only when some condition changes. The write could be triggered by a tag changing value, or you can create an expression to define precise conditions that will cause a value to be written. Note that the trigger does not need to be activated by the same tag whose value you are recording. You could potentially record the level of a holding tank every time a pump switches on or off.

When logging is to be triggered, you must also choose what the trigger will be. For testing purposes you can choose Constant, setting the value to 1 or 0. You can also write an expression that will evaluate to TRUE or FALSE (1 or 0), or you can select a tag that will have a value of 1 or 0.

Trigger On

If you have chosen Triggered as the Log Type, you also have the choice of whether the trigger happens on a Value Change, a Rising Edge (from FALSE to TRUE) or a Falling Edge (from TRUE to FALSE. This applies to Constants, Expressions and Tags.

Log Type: On Data Change

This option for log triggering passes more control over to the Logger tags attached to this Logger Group. Essentially, you are indicating that log values are to be written when the value of the tag you are monitoring changes. See the section on the SQL Logger tags for more information.

If using this log type, please read the following section: Log Invalids for an important note.

Log Invalids

You have the option of whether to record "invalids" as part of the data being written. An invalid data value is essentially a NULL.

Note that if you select On Data Change as your trigger and you have not chosen to log invalids, then if the data changes from a valid value to invalid (or vice versa), no value will be written. You must enable Log Invalids to capture these data changes.

Enable

Where triggering controls the timing of data output, Enable sets whether data is to be output at all. Where the two controls offer the same options of "Constant", "Expression" and "Tag", the difference may seem subtle, but it is fundamentally different. Enabling the logger group means that logging is switched on or off. Triggering means that (if logging is enabled) data values are written when the trigger signals them to be.

If a SQL Logger Group is disabled, then all the SQL Logger tags attached to that group are automatically disabled.

Use Coordinated Universal Time (UTC)

A switch giving you the option to record the time stamp using either local time or UTC.

SQL Logger Group properties SQL Logger tab

Each Logger Group Tag must have one or more SQL Logger tags contributing data to it before it will be useful. As noted, the SQL Logger Group Tag creates a link to a database and sets a frequency of logging - it does not actually select what data is being logged. That job is assigned to the SQL Logger tag as described in the following section.

Only one widget is available to SQL Logger Group tags:

Show Stats Widget (ODBC)