MODBUS to a database: Write data to two different tables

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.

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.

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

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

Fig. 9 A message about data being successfully written

Related articles: MODBUS to a database: Write data to two different tables

MODBUS RTU, MODBUS ASCII, MODBUS/TCP

• MODBUS power meter data logging (easy method)
• Sunspec-compatible MODBUS power meters, inverters (easy method)
• MODBUS RTU/TCP polling: Configuring master station (MODBUS RTU, MODBUS TCP, requests, response items).
• MODBUS poll: How to make sure that the application sends requests and receives responses?
• MODBUS poll: How to view register values, not raw MODBUS packets?
• MODBUS polling: How to make sure that the application correctly interprets the responses received from the device?
• MODBUS polling: How to view MODBUS register values in a more easy-to-grasp form (graphs, indicators, etc.)?
• MODBUS: How to combine the data of two requests?
• MODBUS: What is the right way to poll multiple devices?
• Copy settings from Simply MODBUS RTU Master to our Modbus Data Logger.
• Copy settings from the MODBUS Poll utility.
• Controlling PLC coil registers status using MODBUS TCP (MODBUS data parser, custom scripts, events generating, and handling).
• MODBUS to MSSQL: Write MODBUS registers to separate columns
• MODBUS to MySQL: Write MODBUS values to the MySQL database
• MODBUS to a database: Writing MODBUS RTU/TCP values to a database
• MODBUS to a database: Write data to two different tables.
• MODBUS to a database: Write data to two different databases, making a complete copy.
• Sentron PAC 3200: MODBUS TCP Data Logging
• Write data to a MODBUS device
• SQL to MODBUS: Send data from a SQL database to MODBUS.
• MODBUS TCP ↔ MODBUS RTU real-time conversion.

BACNET/IP

• Bacnet/IP power meter data logging