Advanced Serial Data Logger

Trust In Confidence!

For Windows 2000 - Windows 10 (2019) (incl. Server, x86 and x64). Latest version: 4.5.8 build 512. .


MODBUS to a database: Write data to two different tables

Download a Free Trial Version. It allows you to try all features! Plugins can be downloaded separately here

Problem scenario:

I have a database with two different tables. One table should store current data, and another table should save kilowatts per hour.

Note: you may implement the same method and send data to multiple tables.

Requirements:

  • Advanced Serial Data Logger Enterprise or a trial version for MODBUS RTU.
  • Advanced TCP/IP Data Logger Enterprise or a trial version for MODBUS/TCP.
  • SQL Database Pro data export plugin (the full or trial version). It is bundled with the standard installation package.
  • Expressions data filter plugin. It presents in the installation package too.

It is assumed that:

You have configured the communication settings on the device:

  • MODBUS TCP - IP address, Subnet, Gateway. You must assign a static IP address for the device.
  • MODBUS RTU - baud rate and the number of data bits.

Solution:

1. You should create two MODBUS requests in a queue for the necessary data (fig. 1-3). One request should return data with current, and another request should return kilowatts. You may use different schedules for both requests. You may find more about how to configure the MODBUS queue here.

The MODBUS plugin
Fig. 1 The MODBUS plugin

MODBUS request #1
Fig. 2 MODBUS request #1

MODBUS request #2
Fig. 3 MODBUS request #2

2. Create two tables in your database. We omit steps to create an user in your database and grant the "write" access rights to tables for your user.


CREATE TABLE IF NOT EXISTS modbus_phase_current (
 DATE_TIME_STAMP DATETIME,
 PHASE_1_CURENT double DEFAULT NULL,
 PHASE_2_CURENT double DEFAULT NULL,
 PHASE_3_CURENT double DEFAULT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS modbus_kwh (
 DATE_TIME_STAMP DATETIME,
 KWH double DEFAULT NULL
) ENGINE=InnoDB;


CREATE TABLE [dbo].[modbus_phase_current] (
 [DATE_TIME_STAMP] datetime,
 [PHASE_1_CURENT] float NULL,
 [PHASE_2_CURENT] float NULL,
 [PHASE_3_CURENT] float NULL
)  ON [PRIMARY];

CREATE TABLE [dbo].[modbus_kwh] (
 [DATE_TIME_STAMP] datetime,
 [KWH] float NULL
) ON [PRIMARY];
 

PHASE_1_CURENT - the column stores the current of the corresponding phase.

KWH - the column stores kilowatts.

DATE_TIME_STAMP - the time stamp when the program processes a response.

3. Enable and configure the Expressions plugin (fig. 4-5). The plugin will control an address of a MODBUS response and generate the corresponding event. The plugin will append all data from the MODBUS response to a generated event. Later, the SQL plugin will handle that event and execute a SQL statement.

The Expressions plugin
Fig. 4 The Expressions plugin

Expressions
Fig. 5 Expressions

Here is the text copy of the expressions above:

SEND_EVENT_IF(MODBUS_ADDRESS=100, 'KWH')
SEND_EVENT_IF(MODBUS_ADDRESS=0, 'CURRENT')

4. Enable and configure the SQL Database Pro plugin (fig. 6-8). We omit connection settings here. You may find more info about it here.

You should add two SQL statements for two generated events. Please, pay attention to the "Execute query" and "Event ID" fields in the settings.

The SQL Database Pro plugin
Fig. 6 The SQL Database Pro plugin

Please, check a data type when you bind the parser item name to a SQL parameter. It should match the data type of your column. Some data types can be converter automatically (e.g., integer → float or double).

SQL statement for the CURRENT event
Fig. 7 SQL statement for the CURRENT event

SQL statement for the KWH event
Fig. 8 SQL statement for the KWH event.

5. Click "OK" to save the changes.

6. Check the status bar to ensure the data is being successfully processed (fig. 6).

Message in log
Fig. 9 A message about data being successfully written

Related articles:

MODBUS RTU, MODBUS ASCII, MODBUS/TCP

IEC 62056-21