Getting Started – Database

Setting an OAS Tag Data Source to Database makes it easy to read values from database tables or views or write to database tables based on the column name set in the tag. This allows for the sharing and transferring database values to any OAS feature. Using the Data Route feature, these values can also be transferred automatically to OAS device interfaces like Modbus, Allen Bradley, Siemens, OPC UA, OPC DA, MQTT, and Sparkplug B. Database Tag values are most commonly made available to the Open UIEngine for user interface read and write access.

The Database Data Source can connect to SQL Server, Oracle, mySQL, Cassandra, PostgreSQL, MariaDB, and SQLite.

For transactional recording to a database see Getting Started – Data Logging.

For transactional processing from a database see Getting Started – Recipes.

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.

Production Data Example Design

The values in the example table Orders are as follows.

Example Database Values

Step 1

OAS

Start the Configure OAS application if it is not already running.

Step 2

Select Configure-Drivers to define a database connection.

Configure Drivers

Define a Driver Interface Name that will be used to identify this database connection.

Driver Interface Name

Set the Driver type to Database.

Database Driver

Set the database Provider to the desired selection.

Database Provider

Set the Server property to the local or remote database server. (Not required for SQLite)

Database Server

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.

Database Property

If using SQL Server provider Use Windows Authentication or uncheck to specify User Name and Password for the connection.

Database Authentication

To allow writing to the database when a tag is written to enable the property Allow Writing Values.

Allow Database Write

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.

Add Driver

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.

Save Button

Step 3

Select Configure-Tags to specify which tables and columns to read and update values.

Menu Configure Tags

Select ADD GROUP to optionally define a group where new tags will be added.

Add Group

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.

Add Tag Button
Add Pressure Setpoint

Set the tag Data Source to Database.

Database Data Source

Set the Table or View property to Orders.

Database Table or View

Set the Field Name property to PressureSetpoint.

Database Field Name

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.

Database Data Type

Set the Where String property to WHERE BatchNumber = 1.

Database WHERE String

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.

Apply Changes

The current value of the PressureSetpoint column in the Orders table where the BatchNumber equals 1 will now appear in the tag Value.

Database Tag Value

Select Save on the menu bar to save the configuration changes to the .Tags file.

Save Button

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.

Order By String

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.

Set Table Name with Tag
Set Where String with Tag
Set Order by String with Tag

The Polling Rate determines the frequency that data is read from the database.

Polling Rate

The Enable by Tag property allows control of polling by the value of a Boolean tag; true is enabled, and false is disabled.

Enable by Tag

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.

Device Read