Hello everyone,
i’m trying to log some values from modbus devices to the mySQL node. the flow is not very clear to me as functionality and setup, so i’m posting here what i’m trying to do and asking how is possible to achieve something like this.
I’m familiar with the modbus read and buffer parser nodes individually, but since are coming from different devices, here question 1 arises.
- how is recommended to merge the outputs of 3 different buffer parsers to merge the values before going to database? i’m using key/value pairs as output, but it can be any type if more convenient. Is there any other way to merge all the values from 3 devices?
- on the examples for mysql, i saw using template node and mustache syntax for creating the query (topic and payload , or topic and get the payload from previous node). Assuming i have all my values from the previous step in key/value pairs, where each key coming from buffer parsers are the column name, and each value is the record value for each column, how this can be done dynamically in the template for a successful sql insert into table ?
This procedure i got it by checking out the documentation and recommendations. i don’t know if is the easiest way or the suggested way (i guess it is that’s why i have it as starting point), but i’m not fixated on that, if another method is easier i’m willing to change methodology and flow.
Thanks for any suggestions.
[
{
"id": "f20a075846751b87",
"type": "modbus-read",
"z": "4e7505dfcf1c08de",
"name": "modbus read device 1",
"topic": "",
"showStatusActivities": false,
"logIOActivities": false,
"showErrors": false,
"showWarnings": true,
"unitid": "",
"dataType": "",
"adr": "",
"quantity": "",
"rate": "",
"rateUnit": "",
"delayOnStart": false,
"startDelayTime": "",
"server": "",
"useIOFile": false,
"ioFile": "",
"useIOForPayload": false,
"emptyMsgOnFail": false,
"x": 300,
"y": 380,
"wires": [
[
"4d06079ace179c40"
],
[]
]
},
{
"id": "7811dd6a8dce739f",
"type": "modbus-read",
"z": "4e7505dfcf1c08de",
"name": "modbus read device 2",
"topic": "",
"showStatusActivities": false,
"logIOActivities": false,
"showErrors": false,
"showWarnings": true,
"unitid": "",
"dataType": "",
"adr": "",
"quantity": "",
"rate": "",
"rateUnit": "",
"delayOnStart": false,
"startDelayTime": "",
"server": "",
"useIOFile": false,
"ioFile": "",
"useIOForPayload": false,
"emptyMsgOnFail": false,
"x": 300,
"y": 440,
"wires": [
[
"0572ea4d6dc4c8d6"
],
[]
]
},
{
"id": "066dcff404d48e58",
"type": "modbus-read",
"z": "4e7505dfcf1c08de",
"name": "modbus read device 3",
"topic": "",
"showStatusActivities": false,
"logIOActivities": false,
"showErrors": false,
"showWarnings": true,
"unitid": "",
"dataType": "",
"adr": "",
"quantity": "",
"rate": "",
"rateUnit": "",
"delayOnStart": false,
"startDelayTime": "",
"server": "",
"useIOFile": false,
"ioFile": "",
"useIOForPayload": false,
"emptyMsgOnFail": false,
"x": 300,
"y": 500,
"wires": [
[
"ea0e4736537ce6cd"
],
[]
]
},
{
"id": "4d06079ace179c40",
"type": "buffer-parser",
"z": "4e7505dfcf1c08de",
"name": "",
"data": "payload",
"dataType": "msg",
"specification": "spec",
"specificationType": "ui",
"items": [
{
"type": "int16be",
"name": "bom",
"offset": 4,
"length": 1,
"offsetbit": 0,
"scale": "0.1",
"mask": ""
},
{
"type": "int16be",
"name": "plane",
"offset": 24,
"length": 1,
"offsetbit": 0,
"scale": "0.1",
"mask": ""
},
{
"type": "int16be",
"name": "noneed",
"offset": 26,
"length": 1,
"offsetbit": 0,
"scale": "0.1",
"mask": ""
},
{
"type": "int16be",
"name": "ambient",
"offset": 56,
"length": 1,
"offsetbit": 0,
"scale": "0.1",
"mask": ""
},
{
"type": "int16be",
"name": "horizontal",
"offset": 58,
"length": 1,
"offsetbit": 0,
"scale": "0.1",
"mask": ""
},
{
"type": "int16be",
"name": "item4",
"offset": 76,
"length": 1,
"offsetbit": 0,
"scale": "0.1",
"mask": ""
},
{
"type": "int16be",
"name": "wind",
"offset": 200,
"length": 1,
"offsetbit": 0,
"scale": "0.1",
"mask": ""
}
],
"swap1": "",
"swap2": "",
"swap3": "",
"swap1Type": "swap",
"swap2Type": "swap",
"swap3Type": "swap",
"msgProperty": "payload",
"msgPropertyType": "str",
"resultType": "keyvalue",
"resultTypeType": "return",
"multipleResult": false,
"fanOutMultipleResult": false,
"setTopic": false,
"outputs": 1,
"x": 530,
"y": 380,
"wires": [
[
"f60288d0239696bf"
]
]
},
{
"id": "0572ea4d6dc4c8d6",
"type": "buffer-parser",
"z": "4e7505dfcf1c08de",
"name": "",
"data": "payload",
"dataType": "msg",
"specification": "spec",
"specificationType": "ui",
"items": [
{
"type": "int16be",
"name": "bom",
"offset": 4,
"length": 1,
"offsetbit": 0,
"scale": "0.1",
"mask": ""
},
{
"type": "int16be",
"name": "plane",
"offset": 24,
"length": 1,
"offsetbit": 0,
"scale": "0.1",
"mask": ""
},
{
"type": "int16be",
"name": "noneed",
"offset": 26,
"length": 1,
"offsetbit": 0,
"scale": "0.1",
"mask": ""
},
{
"type": "int16be",
"name": "ambient",
"offset": 56,
"length": 1,
"offsetbit": 0,
"scale": "0.1",
"mask": ""
},
{
"type": "int16be",
"name": "horizontal",
"offset": 58,
"length": 1,
"offsetbit": 0,
"scale": "0.1",
"mask": ""
},
{
"type": "int16be",
"name": "item4",
"offset": 76,
"length": 1,
"offsetbit": 0,
"scale": "0.1",
"mask": ""
},
{
"type": "int16be",
"name": "wind",
"offset": 200,
"length": 1,
"offsetbit": 0,
"scale": "0.1",
"mask": ""
}
],
"swap1": "",
"swap2": "",
"swap3": "",
"swap1Type": "swap",
"swap2Type": "swap",
"swap3Type": "swap",
"msgProperty": "payload",
"msgPropertyType": "str",
"resultType": "keyvalue",
"resultTypeType": "return",
"multipleResult": false,
"fanOutMultipleResult": false,
"setTopic": false,
"outputs": 1,
"x": 530,
"y": 440,
"wires": [
[
"f60288d0239696bf"
]
]
},
{
"id": "ea0e4736537ce6cd",
"type": "buffer-parser",
"z": "4e7505dfcf1c08de",
"name": "",
"data": "payload",
"dataType": "msg",
"specification": "spec",
"specificationType": "ui",
"items": [
{
"type": "int16be",
"name": "bom",
"offset": 4,
"length": 1,
"offsetbit": 0,
"scale": "0.1",
"mask": ""
},
{
"type": "int16be",
"name": "plane",
"offset": 24,
"length": 1,
"offsetbit": 0,
"scale": "0.1",
"mask": ""
},
{
"type": "int16be",
"name": "noneed",
"offset": 26,
"length": 1,
"offsetbit": 0,
"scale": "0.1",
"mask": ""
},
{
"type": "int16be",
"name": "ambient",
"offset": 56,
"length": 1,
"offsetbit": 0,
"scale": "0.1",
"mask": ""
},
{
"type": "int16be",
"name": "horizontal",
"offset": 58,
"length": 1,
"offsetbit": 0,
"scale": "0.1",
"mask": ""
},
{
"type": "int16be",
"name": "item4",
"offset": 76,
"length": 1,
"offsetbit": 0,
"scale": "0.1",
"mask": ""
},
{
"type": "int16be",
"name": "wind",
"offset": 200,
"length": 1,
"offsetbit": 0,
"scale": "0.1",
"mask": ""
}
],
"swap1": "",
"swap2": "",
"swap3": "",
"swap1Type": "swap",
"swap2Type": "swap",
"swap3Type": "swap",
"msgProperty": "payload",
"msgPropertyType": "str",
"resultType": "keyvalue",
"resultTypeType": "return",
"multipleResult": false,
"fanOutMultipleResult": false,
"setTopic": false,
"outputs": 1,
"x": 530,
"y": 500,
"wires": [
[
"f60288d0239696bf"
]
]
},
{
"id": "f60288d0239696bf",
"type": "join",
"z": "4e7505dfcf1c08de",
"name": "",
"mode": "auto",
"build": "object",
"property": "payload",
"propertyType": "msg",
"key": "topic",
"joiner": "\\n",
"joinerType": "str",
"useparts": false,
"accumulate": "false",
"timeout": "",
"count": "",
"reduceRight": false,
"x": 750,
"y": 440,
"wires": [
[
"a0a3268dedeb52d8"
]
]
},
{
"id": "a0a3268dedeb52d8",
"type": "template",
"z": "4e7505dfcf1c08de",
"name": "",
"field": "payload",
"fieldType": "msg",
"format": "handlebars",
"syntax": "mustache",
"template": "This is the payload: {{payload}} !",
"output": "str",
"x": 900,
"y": 440,
"wires": [
[
"b0b1a401ed52004f"
]
]
},
{
"id": "b0b1a401ed52004f",
"type": "mysql",
"z": "4e7505dfcf1c08de",
"name": "",
"x": 1070,
"y": 440,
"wires": [
[
"096f763aa136ee75"
]
]
},
{
"id": "096f763aa136ee75",
"type": "debug",
"z": "4e7505dfcf1c08de",
"name": "debug sql",
"active": false,
"tosidebar": true,
"console": false,
"tostatus": true,
"complete": "payload",
"targetType": "msg",
"statusVal": "payload",
"statusType": "auto",
"x": 1220,
"y": 440,
"wires": []
}
]