Mastering Industrial Data Collection: A Beginner's Guide to Omron Controllers and Database Functionality

In this article, we will demonstrate how Omron’s Sysmac Studio and an NX machine controller can directly access a relational database without a separate computer or middleware.

In this article, we have a Sysmac Studio program running on an NX Machine Controller.

We use Sysmac Studio to move and use data from IoT sources and store some of this data in the NX Controller Database.

What are we going to store in the database?

We’re going to move and use data from a wind turbine and an outdoor weather station.

We’ll show you how to easily perform insert and select operations on this database.

But, before we get to the database functions, we’ll show you how Sysmac Studio can communicate with the sources providing the data.

In this demonstration, we are going to use MQTT to capture the wind turbine and weather station data.

MQTT communication

MQTT is a very popular publish/subscribe messaging communication protocol for IoT devices allowing the exchange of data via Cloud services.

We delve deeper into these protocols and how they interact with Omron controllers in our free course ‘Omron PLC Basics: Fast-Track Training in NX & NJ Series‘.

MQTT uses an MQTT Server or Broker intermediary entity that enables MQTT clients to communicate.

The Server collects Published messages from MQTT clients and delivers the messages addressed to receivers that were Subscribed.

An MQTT Client can work simultaneously as a publisher or a subscriber on several topics.

In our example, the NX controller is an MQTT Client.

OPC UA communication

Sysmac Studio and the NX machine controller can also speak using OPC UA which is another mechanism for transferring information between servers and clients.

Omron controllers can accomplish MQTT via Sysmac Studio communication Function Blocks.

For example, the Function Block MQTTClient controls the connection with the MQTT server.

Sysmac Studio has an MQTT Library with simple-to-use FBs to Connect, Ping, Publish & Subscribe to an MQTT server.

JSON data format

OK…so far we’ve discussed how Sysmac Studio facilitates the movement of messages, but we haven’t discussed the message content or format.

In our Sysmac Studio program, we use JSON for the data format.

JSON (JavaScript Object Notation) is a very popular data format with the IoT and can be used to exchange data via MQTT.

OMRON has created a JSON library to convert Sysmac variables to JSON string and to extract variables from a JSON string.

Messages can be encoded with JSON at the source, communicated via MQTT, and decoded at the receiver using JSON.

Accessing the database

Ok…let’s talk about the NX machine controller Database and how to access and use it.

Unlike most other PLCs or control systems, OMRON controllers do not need any additional software, hardware, or Server Middleware.

Connecting with an SQL DB is easily accomplished.

In our example, we chose MS SQL Server to interact with our controller DB. As you can see, there are several possible relational database-type servers to choose from.

To complete the connection, we add the Database server address and the Database Username/Password.

After assigning the DB Connection settings, the DB_Connect Function block allows the NX Machine Controller to directly access the SQL Database.

SQLServerConn is entered as the DBConnentName input on the function block.

As we mentioned earlier, with some simple coding, the Function Block MQTTClient controls the connection with the NX Controller MQTT server.

Ok…so…now we’ve got MS SQL working with our controller database and we’ve connected our MQTT server with the NX Machine Controller as an MQTT Client.

We’re ready to demonstrate the program operation.

Here we go…

Wind turbine and weather station data

JSON-encoded wind turbine data is being published to an MQTT Server every second.

The program also subscribes to a weather station. This real-time JSON-encoded data is updated every 5 minutes.

The weather station data includes temperature, pressure, humidity, wind speed, and other information.

We use JSONdecoder function blocks to decode the specific data from the JSON message. For example, the Get_Temperature FB produces an extTemperature value.

OK…now we insert the data into the database with DB_Insert.

We have 2 tables in the database – WindTurbine001 and MeteoStation.

How can we see what’s in the Database?

There are lots of free SQL client software applications and database administration tools available.

We use DBeaver to view the 2 tables and the data in each, in the database named turbines.

Before we proceed further if you’re finding this content valuable and wish to gain a more comprehensive understanding of Omron Controllers, consider signing up for our free course ‘Omron PLC Basics: Fast-Track Training in NX & NJ Series‘.

Database queries

Alright…now we’ll perform a database query using the Sysmac Studio DB_Select function.

With some simple coding, we select the top 5 temperatures from the Windturbine001 table and set them in descending order.

We can view the top 5 temps selected by clicking on the Sysmac Studio Watch Tab Page (Table) icon in the Toolbar.

We’d like to show you the DB_Delete feature of the Sysmac Studio DB functions.

Users may want to clear old records from a database, and this can be easily accomplished with the DB_Delete function.

In our program, we’re using a normally open contact that we can operate to Execute the DB_Delete function block action.

Summary

Alright…that’s all we wanted to show you for now.

Let’s recap a few key points.

Omron’s Sysmac Studio working together with an NX Machine Controller can directly access a relational database without a separate computer or middleware.

Sysmac Studio has several DB Function blocks to perform functions such as insert, select, and delete.

Omron’s Sysmac Studio incorporates MQTT for communication via a comprehensive MQTT Function Block library.

Sysmac Studio can also speak using OPC UA.

Sysmac Studio also incorporates encoding and decoding JSON libraries to convert Sysmac variables to JSON string and to extract variables from a JSON string.

Connecting with one of several possible relational database types servers to choose from is easily accomplished.

‍

If you’re finding this content valuable and wish to gain a more comprehensive understanding of Omron Controllers, consider signing up for our free course Omron PLC Basics: Fast-Track Training in NX & NJ Series.

‍