MODBUS to a database: Write data to two different tables

Download a Free Trial Version.It allows you to try all features!

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 Professional, Enterprise, or a trial version for MODBUS RTU.
Advanced TCP/IP Data Logger Professional, 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.

Fig. 1 The MODBUS plugin

Fig. 2 MODBUS request #1

Fig. 3 MODBUS request #2

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

MySQL


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;

MS SQL


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.

Fig. 4 The Expressions plugin

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 your attention to the "Execute query" and "Event ID" fields in the settings.

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).

Fig. 7 SQL statement for the CURRENT event