Modbus Device Integration
Iot | Modbus| Read and Write Modbus Coils | Edge Computer| JSON | SQL Database | Grafana | Real-time Dashboards
One Minute Modbus to SQL Integration with InDriver - Easy Tutorial
Join Jane from inanalytics.io as she demonstrates a swift and efficient integration of a Modbus Device with SQL, all in just one minute, leveraging the powerful capabilities of InDriver. This step-by-step tutorial is designed for both beginners and seasoned professionals looking to streamline their Modbus to SQL integration process.
Example 1: Modbus Device Read/Write, Microsoft Azure Logging, and Grafana Dashboard Integration.
In this solution, InDriver, installed on a Windows 11 Edge Computer, collects data from a Moxa IOLogic E1212 connected via Modbus TCP. The binary representation of the current timestamp's second value is stored in the Moxa device in an 8-byte coil output every second. Every 5 seconds, the device is read, and the data are stored in an SQL database in JSON format.
Description of an example Modbus Integration Algorithm
onStartup: Creating an SQL table if it doesn't exist and installing timer hooks.
The following lines of code:
-
Import ModbusApi from the InDriver Modbus Communication Object Library.
-
Create a table "public.modbus" using TSApi with columns: source (text), ts (timestamp), and data (JSON).
-
Delete all rows with data from the "public.modbus" table for example purposes.
-
Install hooks to trigger the onHook block every 1 second (1000ms) and 5 seconds (5000ms) synchronously with the computer clock.
-
Connect to the Modbus Device "Moxa" at the address 192.168.1.22.
InDriver.import('ModbusApi');
InDriver.sqlExecute("AzurePGSQL","select tsapicreatetable('public','modbus');");
InDriver.sqlExecute("AzurePGSQL","delete from public.modbus;");
InDriver.installHook(1000);
InDriver.installHook(5000);
ModbusApi.connectDevice( 'Moxa','{ "mode": "TCP", "networkAddress": "192.168.0.22"}');
onHook: Setting Modbus Device's Coils every 1s, reading and logging the Coils' values to an SQL table every 5s.
The following lines of code:
-
Generate an array with bit values representing the current time seconds value every 1s, synchronously with the clock (e.g., 00:00:01, 00:00:02, etc.) and write the generated array to the device using ModbusApi.writeDevice('Moxa', '{ "name": "coils", "type": "COILS", "address": 1, "data":' + JSON.stringify(array) + ' }'), which writes to coils from 1 to 8 bits.
-
Read coils' values from the device using ModbusApi.readDevice('Moxa', '{"name": "coils", "type": "COILS", "address": 1, "size": 8}') every 5 seconds, log the retrieved values in JSON format to an SQL table using InDriver.sqlExecute("AzurePGSQL", ["select tsapiinsert('public','modbus','IOLogicE1212', '", ts.toUTCString(), "','", ModbusApi.getDeviceData("Moxa"), "' );"]) and send a message to other InDriver tasks containing the acquired Modbus Coils Values.
if (InDriver.isHook(1000)) {
let seconds = ts.getSeconds()
InDriver.debug('write: ' + seconds)
let array = new Array(8)
for (var i = 0; i < 8; i++) {
array[i] = (seconds >> i) & 0x1
}
InDriver.debug('data: ' + JSON.stringify(array))
ModbusApi.writeDevice('Moxa','{ "name": "coils", "type": "COILS", "address": 1, "data":' + JSON.stringify(
array) + ' }')
}
if (InDriver.isHook(5000)) {
ModbusApi.readDevice( 'Moxa','{"name": "coils", "type": "COILS", "address":1, "size":8}')
let data = ModbusApi.getDeviceData("Moxa")
InDriver.debug("read: ",data)
let ts = InDriver.hookTs()
InDriver.sqlExecute("AzurePGSQL",["select tsapiinsert('public','modbus','IOLogicE1212', '", ts.toUTCString(), "','", data, "' );"])
InDriver.sendMessage(ts, '["device data","arch"]', data)
}
Modbus Data Visualization in Grafana
In Grafana, the process involves placing visualizations and configuring SQL queries, as demonstrated below.
In this example, both Table and Time Series Visualizations were employed. The Table visualization includes columns such as ts (timestamp), seconds (integer) representing seconds obtained from coils' bit representation, and individual columns for coils 1 to 8 values.
Similarly, the Time Series visualization utilizes a nearly identical SQL query, showcasing changes in coil values over time.
Below is the SQL query applied in Table:
select ts,
(((data->'Moxa'->'Read'->'coils'->>'1'='true')::integer << 0) |
((data->'Moxa'->'Read'->'coils'->>'2'='true')::integer << 1) |
((data->'Moxa'->'Read'->'coils'->>'3'='true')::integer << 2) |
((data->'Moxa'->'Read'->'coils'->>'4'='true')::integer << 3) |
((data->'Moxa'->'Read'->'coils'->>'5'='true')::integer << 4) |
((data->'Moxa'->'Read'->'coils'->>'6'='true')::integer << 5) |
((data->'Moxa'->'Read'->'coils'->>'7'='true')::integer << 6) |
((data->'Moxa'->'Read'->'coils'->>'8'='true')::integer << 7)
) as seconds,
(data->'Moxa'->'Read'->'coils'->>'8'='true')::integer as "coil8",
(data->'Moxa'->'Read'->'coils'->>'7'='true')::integer as "coil7",
(data->'Moxa'->'Read'->'coils'->>'6'='true')::integer as "coil6",
(data->'Moxa'->'Read'->'coils'->>'5'='true')::integer as "coil5",
(data->'Moxa'->'Read'->'coils'->>'4'='true')::integer as "coil4",
(data->'Moxa'->'Read'->'coils'->>'3'='true')::integer as "coil3",
(data->'Moxa'->'Read'->'coils'->>'2'='true')::integer as "coil2",
(data->'Moxa'->'Read'->'coils'->>'1'='true')::integer as "coil1"
from modbus where source = 'IOLogicE1212' order by ts desc;
Example 2: InDriver's Unique Feature - Simultaneous Readout of Multiple Modbus Devices
In this scenario, three Moxa IOLogic Ethernet I/O modules are linked to an Edge Computer with InDriver. The system requirement is to concurrently read the Discrete Inputs from all devices, ensuring simultaneous data retrieval rather than sequential scanning.
Description of an example Algorithm
onStartup: Connecting to three Modbus Devices and installing reading hook.
The following lines of code:
-
Import ModbusApi from the InDriver Modbus Communication Object Library.
-
Install a hook to trigger the onHook block every 5 seconds (5000ms) synchronously with the computer clock.
-
Connect to the three Modbus Devices "MoxaOne", "MoxaTwo" and "MoxaThree" at the address 192.168.1.22 to 192.168.1.24
InDriver.import('ModbusApi');
InDriver.installHook(5000);
ModbusApi.connectDevice( 'MoxaOne','{ "mode": "TCP", "networkAddress": "192.168.0.22"}');
ModbusApi.connectDevice( 'MoxaTwo','{ "mode": "TCP", "networkAddress": "192.168.0.23"}');
ModbusApi.connectDevice( 'MoxaThree,'{ "mode": "TCP", "networkAddress": "192.168.0.24"}');
onHook: Reading Discrete Inputs Simultaneously and Logg data to SQL
The following lines of code:
-
Similarly to SQL transactions, ModbusApi offers the begin() and commit() transaction blocks for Modbus read and write functions. All functions within such a block are executed simultaneously after the commit() or commitWait() operation. In this case, it involves concurrent execution of the following ModbusApi read functions: ModbusApi.readDevice ('MoxaOne'.....), ModbusApi.readDevice ('MoxaTwo'.....) and ModbusApi.readDevice ('MoxaThree'.....).
-
Verify the completion of the readout by utilizing ModbusApi.isSucceeded(). Once confirmed, retrieve all read data using ModbusApi.getAllData(). This data can be effortlessly logged into an SQL table, with the source specified in this example as '3xIOLogicE1212'.
if (InDriver.isHook(5000)) {
ModbusApi.begin()
ModbusApi.readDevice( 'MoxaOne','{"name": "inputs", "type": "DISCRETEINPUTS", "address":1, "size":8}')
ModbusApi.readDevice( 'MoxaTwo','{"name": "inputs", "type": "DISCRETEINPUTS", "address":1, "size":8}')
ModbusApi.readDevice( 'MoxaThree','{"name": "inputs", "type": "DISCRETEINPUTS", "address":1, "size":8}')
ModbusApi.commitWait()
if (ModbusApi.isSucceeded()) {
let data = ModbusApi.getAllData()
let ts = InDriver.hookTs()
InDriver.sqlExecute("AzurePGSQL",["select tsapiinsert('public','modbus','3xIOLogicE1212', '", ts.toUTCString(), "','", data, "' );"])
InDriver.sendMessage(ts, '["device data","arch"]', data)
}
}
The code block results in logged data in JSON format as depicted below.
The readout is simultaneous and synchronized with the computer clock, occurring at regular 5-second intervals, for example, at 00:00:00, 00:00:05, 00:00:10, and so forth.
{
"MoxaOne": { "Read": { "inputs": { "1": true, "2": false, "3": true, "4": false, "5": true, "6": false, "7": true, "8": false } } },
"MoxaTwo": { "Read": { "inputs": { "1": true, "2": false, "3": true, "4": false, "5": true, "6": false, "7": true, "8": false } } },
"MoxaThree": { "Read": { "inputs": { "1": true, "2": false, "3": true, "4": false, "5": true, "6": false, "7": true, "8": false } } }
}