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
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.
- 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.
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 an user in your database and grant the "write" access rights to tables for your user.
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:
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.
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. 6).
Fig. 9 A message about data being successfully written
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?
- 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.