Getting Started – Database Tag
You can view the Getting Started with Database Data video to familiarize yourself with the following steps to setup a database interface.
- 00:00 – Introduction
- 01:01 – Example Data
- 01:38 – Configure Connection
- 03:53 – Save Configuration
- 04:27 – Configure Tags
- 05:12 – Read
- 05:54 – WHERE String
- 09:26 – Write
- 09:55 – Examples Tags
- 10:57 – Watch Window
- 11:34 – User Interface
- 13:53 – Data Route
- 16:03 – Modbus
- 19:44 – OPC UA
- 22:33 – Knowledge Base
- 24:30 – Remote Database
- 25:15 – Networking
- 25:48 – Device Drivers
- 26:10 – UIEngine
- 26:35 – Getting Started Guide
- 27:22 – Alarm Limits
- 28:18 – OpenAutomationSoftware.com
The following guide will demonstrate how to set up a local or remote database connection to enable OAS Tags to access values by column name with static or dynamic table name, WHERE statement, and ORDER BY query.
In the steps that follow a connection will be established to a SQL Server database engine to read and write data to a table called Orders that has the following structure.
The values in the example table Orders are as follows.
Step 1
Start the Configure OAS application if it is not already running.
Step 2
Select Configure-Drivers to define a database connection.
Define a Driver Interface Name that will be used to identify this database connection.
Set the Driver type to Database.
Set the database Provider to the desired selection.
Set the Server property to the local or remote database server. (Not required for SQLite)
Set the Database property to the name of the database to connect to. For SQLite this is set as the file path of the existing database.
If using SQL Server provider Use Windows Authentication or uncheck to specify User Name and Password for the connection.
To allow writing to the database when a tag is written to enable the property Allow Writing Values.
Note: When tags are written to with a Data Source of Database all records matching the WHERE string of the tag are update. This can result in multiple records being updated from a single write based on the value of the WHERE string of the tag.
Select ADD DRIVER at the left of the list of driver interfaces.
Driver interface is now active in the system and ready for tags to be assigned to the connection.
Select Save on the menu bar to save the configuration changes to the .Tags file.
Step 3
Select Configure-Tags to specify which tables and columns to read and update values.
Select ADD GROUP to optionally define a group where new tags will be added.
In this example we will use the new group name Orders.
Select ADD TAG to add the tag Pressure Setpoint which will be used to access the values from the PressureSetpoint column.
Set the tag Data Source to Database.
Set the Table or View property to Orders.
Set the Field Name property to PressureSetpoint.
If the field will be updated with a write to the tag set the Field Data Type to the data type of the database column.
Set the Where String property to WHERE BatchNumber = 1.
Note: The value of the Where String property determines which record will be obtained from the table or view. It also determines which record(s) will be updated when a write to the tag occurs.
Select the Apply Changes button to activate the changes to the tag.
The current value of the PressureSetpoint column in the Orders table where the BatchNumber equals 1 will now appear in the tag Value.
Select Save on the menu bar to save the configuration changes to the .Tags file.
Additional Tag Properties
The Order By String property is another property that can be used to determine what record is used to read the value from the database. It is not used in updating records on a write.
The Table or View, Where String, and Order By String properties can optionally be set dynamically from a string tag. The properties Set Table Name with Tag, Set Where String with Tag, and Set Order by String with Tag control the ability to set the respective property values dynamically from another string tag. These properties allow for the determination of which table and record are accessed for reading and updating from other tag values.
The Polling Rate determines the frequency that data is read from the database.
The Enable by Tag property allows control of polling by the value of a Boolean tag; true is enabled, and false is disabled.
The Device Read property enables reading from the database by event instead of continuously. When enabled the Boolean tag that is defined to the Device Read will trigger a read with a value transition from false to true.
