Part 2 of the MQTT publish/subscribe communication tutorial using OpcDbGateway

Please see part 2 of the MQTT publish/subscribe communication tutorial using OpcDbGateway – Please see also the first part before –

Using OpcDbGateway for MQTT pub/sub communication


Visualization, which need not be configured.

At present, there is quantity of comfortable tools for the creation of visualization projects that allow you to create very fancy user interface with animated visuals, lots of different types of charts and stationery. All, however, have one common drawback – you need to learn to work with them really perfectly. Otherwise, the end result will not meet the possibilities they provide.

Real requirements for visualization are often not so high that it makes sense to devote time to learn perfect working with mentioned comfortable visualization tools. A worker at the test facility or in the control room does not need to watch the spectacular images and animations. After some time look at this beautiful picture ceases to be interesting for him.

What he really needs is to see data, even if only in the form of characters, that are appropriately structured and thus provide the possibility of very rapid orientate in them.

Tree views enable such a structured displaying of data. The data in the tree can be displayed according to various criteria such:

  • Placement of data points – the most important data from a single plant can be placed in one branch and every plant can have its own branch.
  • Data from the same types of equipment can be placed in one branch of the tree.
  • The current data may be in a different branch as data about history of the data points.

Another universal view of data is tabular display. Groups of  somehow interrelated data are placed in a row of the table. Individual rows of the table may represent the same view on data points (e.g. values ​​of operating parameters of any technological device) at different time points.

The tabular views of data can be created simply by using SQL commands. These SQL commands may be predefined and stored in the configuration (to avoid unduly burden for the ordinary users), or may be created or just modified from predefined SQL commands stored directly in visualization application.



Figure 1 Combination of tree view and table view to current data.


Figure 2 Table view of data from a database gained with modifiable SQL command.

The use of these two methods of visualization – the tree view and tabular view provides a way of really universal visualizing without need to configure visualization itself.

These universal visualization principles were used in the software product SAEAUT SCADA. This product represents a pair of two basic applications – server application that provides collection, data processing, and storing them in databases and the Web client application that provides visualization of the data from the server using the above described two ways to display – a tree data structure and tabular display of data.

Collection, processing and storage of data by the server is necessary to configure (customize) for different applications. In SAEAUT SCADA™ it is done using comfortable configuration application. However, the data visualization itself, by utilizing the above described universal ways of data visualizing, there is no need to configure. Visualization application uses data from the configuration of the server application.

What enables the visualization Web application of SAEAUT SCADA™:

  • View of current data from devices or from other external applications or data obtained by internal processing algorithms in the server of SAEAUT SCADA. For this, it uses a combination of tree and tabular views. The table displays data from one branch of the tree structure. For each data point shows not only the current value but also quality and timestamp – i.e.  whether the data is valid. This way you can not only view data but also write them and so to manage devices connected to the server.
  • View data stored in databases using table views – by that database tables from one or more connected databases contained within server configuration can be shown immediately without user intervention. Just select table from the list of available tables to see it. Preconfigured SQL commands, to show table can be modified by user and saved for later use. You can create and save even more complex SQL commands to create data view from multiple tables on one database.
  • Create interactive reports (ad-hoc)  with formats csv, pdf, xml, xls and doc from the current data tables and from data stored in databases connected to server application.
  • Displaying of current status and alarm history  – the operator can not only view and filter alarms resources by various criteria, but they also acknowledge an alarm and comment it.
  • View the contents of log files generated by the server application . Records of server functionality is saved to log file. Detail of the system records can be configured in advance or be influenced by operators on the fly . In addition, you can also configure creating new log files and storing the current values ​​of process variables together with a commentary to them.
  • Responsive web design ensures the correct display of data in various web browsers on desktop and mobile devices.

Of course, the need for a graphical display of data is sometimes unavoidable e.g. to view current or historical trend in the form of a graph.  This is also possible to provide easily without demanding visualization applications in several ways. E.g. , our application OPCAdapter allows within a few minutes to configure graph view of current data trends from SAEAUT SCADA server. Very rich visualization possibilities are offered by the Microsoft Excel, which can be connected the SAEAUT SCADA  server in several different ways.






Two alarm systems in OpcDbGateway

Industrial alarm systems provide logging and alerting to various unforeseen situations in the manufacturing process, machinery and equipment. They are often part of the control and monitoring systems to assess and manage values ​​of the process variables. If there is a deviation of one or more values ​​of the process variables from the desired “normal” state, then this state is evaluated as “alarm” and highlighted in some way, eg. colour or flashing at a glance the status of alarm sources.

Configuring of static/dynamic alarm in configuration application of the OpcDbGateway
Configuring of static/dynamic alarm in configuration application of the OpcDbGateway


There are two basic types of alarms: static and dynamic. Static alarms have two states – active / inactive. Dynamic alarm uses two variables – a source of alarm variable and source of acknowledgment. The confirmation (acknowledgment) usually ensures a human operator. He confirms the information about the alarm situation. A pair of variables provides four possible states of the alarm entity:  inactive, active, acknowledged and gone (resolved). To the state acknowledged the alarm entity gets if the operator or any downstream device confirms that the alarm situation is known.

OpcDbGateway can be used not only as a control and monitoring system but also as an alarm system. It even offers two alarm systems – (1) according to the OPC Foundation standard –  OPC AE and (2) a proprietary alarm system, which provides several advantages which you can inquire in the document Alarms in OpcDbGateway.

In an earlier post – Mapping database tables in OpcDbGateway , it was explained how the variables from different devices and data sources are mapped to memory operands. They can be used in the proprietary alarm system as alarm sources and acknowledgment sources.  Since configurable functionality OpcDbGateway enables numerous functions exercised over the memory operands, the proprietary alarm system in OpcDbGateway is extremely flexible.

OpcDbGateway – How does it work

Linking of data sources

For connecting external devices and various software applications in integrated application, it is ideal if communication software drivers – OPC servers for them are available. OpcDbGateway configurator can automatically import data items of such OPC servers in application configuration. This feature is particularly important when you consider that there can be thousands of such data items in more complex applications. Similar import functionality is provided to the structure of the database tables for connected process databases, and also for applications containing a DDE server such as MS Excel. In case that for a device an OPC server is not available, the communication driver can be included to user software module – enhancing DLL. Data links from external data sources are mapped to the so-called memory operands.


Configuring and programming of application logic

Application logic can be implemented either by using configurable commands that work with mentioned memory operands, or can be programmed in enhancing DLL. Programming is particularly useful when it comes to the implementation of complex and fast algorithms. To work with databases, you can use either the aforementioned configurable commands, SQL commands that can be inserted into the configuration, or procedures stored on external databases. It is also possible to configure and parameterize to start external programs and scripts.

Execution of integrated application

Runtime application without user interface provides an environment for the running of integrated applications while debugging of the applications is done using configuration application with user interface.  The configuration application has built-in OPC client that can connect to the internal OPC server of the runtime application. In the same way, during normal operation, various custom client applications with OPC client can be connected. An example of such an application is also together delivered application SAEAUT OPC WebView. It is a web application programmed in ASP.NET providing standard data views in the tree or list. The user has the option to extend it as needed to complete visualization application or use another application with built-in OPC client. There is also another easy way to create visualisation application – connecting of MS Excel to the runtime application over DDE interface.

New generation of OpcDbGateway

The version 5 of the tool for software applications integrationOpcDbGateway offers number of improvements in runtime, configuration and also in the auxiliary jointly distributed applications. One of the most appealing features also in previous versions was automatic mapping of address spaces of external OPC servers to the OpcDbGateway address space. The new generation of the product enables alike feature also for process databases – automatic mapping of database tables or vice versa – creating of tables according to the OpcDbGateway configuration.

The latest OpcDbGateway version is available, and already tested for the latest versions of operating systems, Windows 8 and Windows Server 2012. Still it is not obvious that the products tested for operating systems for the x86 platform operate reliably also on x64 hardware platform. Putting new line of products for all kinds of platforms and versions of older and newer Windows operating systems was therefore quite time-consuming.

OpcDbGateway provides functionality of OPC DA, AE, UA, XML DA server, OPC client, multidatabase client, DDE client, SCADA server, platform for cyclic dalata processing, platform for event oriented processing, SOA applications,
OpcDbGateway -what is it,

Increased efficiency of runtime application in new version opens opportunity to use OpcDbGateway in more demanding applications with thousands of interconnected data points than ever.

Better keeping of synchronous controller period means that OpcDbGateway can be seen as a soft real-time operating system. Possibility to use it in demanding applications is enhanced by the implementation of the new OPC client in OpcDbGateway. For each external OPC server in the version 5, multiple OPC groups can be defined in internal OPC client. It reduces unnecessary data transfers and thus increases the number of connectable data points and decreases demands on the hardware used.

Very important aid for productivity increasing and configuration errors reducing in applications integration presented already in previous versions the automatic mapping of address space of external OPC servers and the configuration of external DDE servers to the configuration of the OpcDbGateway. Similar functionality for automatic mapping of database tables to the OpcDbGateway configuration was implemented in the new version of the product. It should enhance the desirability of OpcDbGateway for integrating real-time data with data from databases in business applications. Except of this, the OpcDbGateway configuring application allows now also the opposite process – the creation of a database table configured in OpcDbGateway configurator in the connected database.

OpcDbGateway can work with many different databases using different database drivers now.

Product combines sequential functionality (implementation of a sequence programmed or configured commands) and event-driven functionality. Events are initiated in OpcDbGateway by triggers. Triggering can be based on time or on an internal variable – memory operand value. Given the importance of this feature, extending functionality and more transparent user interface for configuring of triggers and events is a significant benefit. Triggers with period in milliseconds or in weeks and years. can be now configured in a single transparent manner.

The monitor view in the configurator enabled in previous versions an option only to read OPC items by testing of a configuration. Currently, you can already also write OPC items, which eliminates the need for external OPC client for testing.

Configuration application was accompanied by a separate output window with three tabs where you can watch status of alarm sources, read the contents of the attached databases by freely definable SQL queries and to monitor logging files in real-time.
Users of OpcDbGateway can chose between two alarm systems. The first one is implemented according to the OPC AE specification. Alarming is there boundled with OPC items of the internal OPC server. It is usable together with OPC AE alarm client applications from different vendors. The second one is boundled with values of shared memory operands. Source as of the alarm in this case as well as a variable for alarm quitting is a memory operand. In regard of mapping of different external data sources as external OPC servers, OPC clients, databases, devices communicating using different protocols implemented within enhancing DLL‘s to the shared memory operands, this alarm system is very flexible. Except of this, it provides alarm history saved in process database and also in alarm log files. An alarm client in configuring application is implemented as an ActiveX. Customers can use this ActiveX in own alarm client applications as well.

Sometimes small changes greatly simplify usability. Such simple improvement is structuring of system variables that enable powerful monitoring of runtime application through OPC server interface, according to the different categories in the directory structure of the internal OPC server in the OpcDbGateway.

We believe that the new generation of product brings new application possibilities and satisfaction to users.

Mapping database tables in OpcDbGateway

In the previous post, it was shown that OpcDbGateway can be perceived as a funnel where real time data from OPC and DDE servers, data intermediated trough other communication drivers and data from different databases flow.

Mapping of OPC server address spaces as well as items from the DDE server configuration to the OpcDbGateway configuration means a substantial increase in applications integration productivity. Configuring sometimes thousands of data points for address spaces of external OPC servers can take weeks. If we had to configure the same data in OpcDbGateway it would be not only a waste of time but also a huge potential for false entries in configuration. This problem, since the first versions of OpcDbGateway configuration application, was solved using software wizards.

If you need to integrate corporate database applications, it is equally important to be able to mapping functionality from database tables to the OpcDbGateway configuration. In version 5 of OpcDbGateway was implemented software wizard for this purpose.


How it functions

After defining a connection string to the database is possible in the OpcDbGateway configurator choose the table from the database that we use in the configuration. In the next step, you can select the table columns and assign them to the database operands that are used to access either of the individual cells in a table column or the entire column. Using configurable or programmable functionality OpcDbGateway we can use database operands to interconnect data different data sources as shown in the figure below.

Mapping also works in the opposite direction. If we define the table first in the configurator OpcDbGateway can be connected to create a database with a single click.

With the tables in the configuration OpcDbGateway we can work easily either using configurable commands or by using SQL language. The content of the table we can also view at runtime in the configurator.

The next post will explain how it is possible for the content of linked database tables accessed via the OPC interface.

OpcDbGateway – a data funnel

OpcDbGateway – a data funel
OpcDbGateway can be understood as a kind of data funnel into which data from different data sources flow and where they are processed and passed to external applications.


Data can come from the following sources:

  • Data from OPC servers and DA2x 3.x – mapping of address space
  • Data from a DDE server – mapping of DDE servers
  • Data from database tables – mapping database tables to the OpcDbGateway address space
  • Data obtained through other communication drivers implemented as extension DLL

Data processing:

  • Configured or programmed (within external Dll’s)software or data processing
  • Define multiple OPC groups for each external OPC server
  • Data calculations at several levels

Passing of processed data:

  • Storage in databases and files
  • Alarms generated on the processed data
  • Data transfer and e-mail as SMS
  • Generate events
  • Generation of reports

Providing processed data via: