Data Logger Suite: universal solution

for Data Logging and Monitoring

For Windows 2000 - Windows 11 (2022) (incl. Server, x86 and x64)

Latest version: 3.0.3 build 1111. November 11, 2024.

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:

• Data Logger Suite Professional, Enterprise, or a trial version.
• 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