I need help to insert correctly.
Function node code:
Template node code:
<INSERT INTO digitalizacao.Milacron(temperatureZone0,temperatureZone1,temperatureZone2,temperatureZone3,temperatureZone4,temperatureZone5) VALUES ("{{sensorsvalues.temperatureZone0}}","{{sensorsvalues.temperatureZone1}}","{{sensorsvalues.temperatureZone2}}","{{sensorsvalues.temperatureZone3}}","{{sensorsvalues.temperatureZone4}}","{{sensorsvalues.temperatureZone5}}");>
This is a bit of a guess since you have not shared any of the data arriving in Node-red.
Your if(msg.topic === "ns=2;s=@blahblahblah") stuff implies that you are only getting the sensor value for one zone per incoming message. Nevertheless it looks like you are attempting to insert a record for each message.
You cannot just build up var sensorvalues with zone0, zone1 etc as each message arrives. It's content is not retained from one message to the next.
You could use context storage to keep the value from one message to the next.
If you do this, how will you know that all 6 values have arrived and it's time to store the record in the database?
Why is your database designed with 6 temperature values rather than a zone number and single temperature field?
eg create table Milacron (timestamp, zone, temperature)
I'm using an injection node with timestamp, 6 opcua item nodes and OpcUa Client node to read the values from that ID ns=2;s=@blablabla. So I used a join node to combine each msg.payload and then a function node and template node that I shared the code before.
I can see the data values in my database with timestamp but the 6 values are storing together in each column, I mean each column have the 6 values instead of 1 per column.
[
{
"id": "788edd9e.90d464",
"type": "tab",
"label": "Flow 2",
"disabled": false,
"info": ""
},
{
"id": "6b44502a.129e7",
"type": "mysql",
"z": "788edd9e.90d464",
"mydb": "",
"name": "Mysql",
"x": 690,
"y": 320,
"wires": [
[
"2ac5c830e053c98b"
]
]
},
{
"id": "b95eecf0ea19f418",
"type": "OpcUa-Client",
"z": "788edd9e.90d464",
"endpoint": "",
"action": "read",
"deadbandtype": "a",
"deadbandvalue": 1,
"time": 10,
"timeUnit": "s",
"certificate": "n",
"localfile": "",
"localkeyfile": "",
"folderName4PKI": "",
"useTransport": false,
"maxChunkCount": "",
"maxMessageSize": "",
"receiveBufferSize": "",
"sendBufferSize": "",
"name": "",
"x": 400,
"y": 120,
"wires": [
[
"3e5dba4292a1de0d"
]
]
},
{
"id": "968e722e7e67d33c",
"type": "template",
"z": "788edd9e.90d464",
"name": "",
"field": "topic",
"fieldType": "msg",
"format": "sql",
"syntax": "mustache",
"template": "INSERT INTO `digitalizacao`.`Milacron`(`temperatureZone0`,`temperatureZone1`,`temperatureZone2`,`temperatureZone3`,`temperatureZone4`,`temperatureZone5`) VALUES (\"{{Msg}}\",\"{{Msg}}\",\"{{Msg}}\",\"{{Msg}}\",\"{{Msg}}\",\"{{Msg}}\");\n",
"output": "str",
"x": 540,
"y": 320,
"wires": [
[
"822dd7607299a65c"
]
]
},
{
"id": "429467aece67eaa1",
"type": "function",
"z": "788edd9e.90d464",
"name": "function 1",
"func": "//ObtĂ©m o valor de msg.payload proveniente dos sensores\nvar sensorvalue = msg.payload;\n\n//Cria um objeto para armazenar os valores de todos os sensores\nvar sensorsvalues = {};\n\n//Verifica qual sensor está enviando a mensagem e define a saĂda correspondente\nif(msg.topic === \"ns=2;s=@ActTmpBrlFeed[1]\"){\n sensorsvalues.temperatureZone0 = sensorvalue;\n}\nelse if(msg.topic === \"ns=2;s=@ActTmpBrlZone[1,1]\"){\n sensorsvalues.temperatureZone1 = sensorvalue;\n}\nelse if(msg.topic === \"ns=2;s=@ActTmpBrlZone[1,2]\"){\n sensorsvalues.temperatureZone2 = sensorvalue;\n}\nelse if(msg.topic === \"ns=2;s=@ActTmpBrlZone[1,3]\"){\n sensorsvalues.temperatureZone3 = sensorvalue;\n}\nelse if(msg.topic === \"ns=2;s=@ActTmpBrlZone[1,4]\"){\n sensorsvalues.temperatureZone4 = sensorvalue;\n}\nelse if(msg.topic === \"ns=2;s=@ActTmpBrlNozzle[1]\"){\n sensorsvalues.temperatureZone5 = sensorvalue;\n}\n\n//Adiciona os valores dos sensores ao objeto msg\nmsg.sensorsvalues = sensorsvalues;\n\n//Retorna a mensagem modificada\nreturn msg;",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 480,
"y": 200,
"wires": [
[
"e7cbaa78094d3a68",
"b2e29cbf80e7a1b5"
]
]
},
{
"id": "2ac5c830e053c98b",
"type": "debug",
"z": "788edd9e.90d464",
"name": "debug 1",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 680,
"y": 380,
"wires": []
},
{
"id": "822dd7607299a65c",
"type": "debug",
"z": "788edd9e.90d464",
"name": "debug 2",
"active": false,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"statusVal": "",
"statusType": "auto",
"x": 680,
"y": 220,
"wires": []
},
{
"id": "e7cbaa78094d3a68",
"type": "debug",
"z": "788edd9e.90d464",
"name": "debug 3",
"active": false,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"statusVal": "",
"statusType": "auto",
"x": 680,
"y": 180,
"wires": []
},
{
"id": "dbf668c30bd7dfbf",
"type": "OpcUa-Item",
"z": "788edd9e.90d464",
"item": "ns=2;s=@ActTmpBrlFeed[1]",
"datatype": "Double",
"value": "",
"name": "temperatureZone0",
"x": 270,
"y": 300,
"wires": [
[
"b95eecf0ea19f418"
]
]
},
{
"id": "87f6136bcf09973a",
"type": "inject",
"z": "788edd9e.90d464",
"name": "",
"props": [
{
"p": "payload"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 80,
"y": 300,
"wires": [
[
"dbf668c30bd7dfbf",
"ace44828f9018116",
"cdea5bec5218fd40",
"146c50872e7b3ca8",
"2ad69ea8e45aab5f",
"894f5867a3128a9c"
]
]
},
{
"id": "ace44828f9018116",
"type": "OpcUa-Item",
"z": "788edd9e.90d464",
"item": "ns=2;s=@ActTmpBrlZone[1,1]",
"datatype": "Double",
"value": "",
"name": "temperatureZone1",
"x": 270,
"y": 340,
"wires": [
[
"b95eecf0ea19f418"
]
]
},
{
"id": "cdea5bec5218fd40",
"type": "OpcUa-Item",
"z": "788edd9e.90d464",
"item": "ns=2;s=@ActTmpBrlZone[1,2]",
"datatype": "Double",
"value": "",
"name": "temperatureZone2",
"x": 270,
"y": 380,
"wires": [
[
"b95eecf0ea19f418"
]
]
},
{
"id": "146c50872e7b3ca8",
"type": "OpcUa-Item",
"z": "788edd9e.90d464",
"item": "ns=2;s=@ActTmpBrlZone[1,3]",
"datatype": "Double",
"value": "",
"name": "temperatureZone3",
"x": 270,
"y": 420,
"wires": [
[
"b95eecf0ea19f418"
]
]
},
{
"id": "2ad69ea8e45aab5f",
"type": "OpcUa-Item",
"z": "788edd9e.90d464",
"item": "ns=2;s=@ActTmpBrlZone[1,4]",
"datatype": "Double",
"value": "",
"name": "temperatureZone4",
"x": 270,
"y": 460,
"wires": [
[
"b95eecf0ea19f418"
]
]
},
{
"id": "894f5867a3128a9c",
"type": "OpcUa-Item",
"z": "788edd9e.90d464",
"item": "ns=2;s=@ActTmpBrlNozzle[1]",
"datatype": "Double",
"value": "",
"name": "temperatureZone5",
"x": 270,
"y": 500,
"wires": [
[
"b95eecf0ea19f418"
]
]
},
{
"id": "b2e29cbf80e7a1b5",
"type": "change",
"z": "788edd9e.90d464",
"name": "",
"rules": [
{
"t": "set",
"p": "Msg",
"pt": "msg",
"to": "payload",
"tot": "msg"
},
{
"t": "delete",
"p": "payload",
"pt": "msg"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 500,
"y": 260,
"wires": [
[
"968e722e7e67d33c"
]
]
},
{
"id": "5a26fcff.2a60d4",
"type": "inject",
"z": "788edd9e.90d464",
"name": "temperatureZone0",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "1",
"crontab": "",
"once": false,
"onceDelay": "1",
"topic": "ns=2;s=@ActTmpBrlFeed[1]",
"payload": "",
"payloadType": "date",
"x": 130,
"y": 20,
"wires": [
[]
]
},
{
"id": "dbf96360ca7e3141",
"type": "inject",
"z": "788edd9e.90d464",
"name": "temperatureZone1",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "1",
"crontab": "",
"once": false,
"onceDelay": "1",
"topic": "ns=2;s=@ActTmpBrlZone[1,1]",
"payload": "",
"payloadType": "date",
"x": 130,
"y": 60,
"wires": [
[]
]
},
{
"id": "4b88a0c117223198",
"type": "inject",
"z": "788edd9e.90d464",
"name": "temperatureZone2",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "1",
"crontab": "",
"once": false,
"onceDelay": "1",
"topic": "ns=2;s=@ActTmpBrlZone[1,2]",
"payload": "",
"payloadType": "date",
"x": 130,
"y": 100,
"wires": [
[]
]
},
{
"id": "02c82d8601ae0c9d",
"type": "inject",
"z": "788edd9e.90d464",
"name": "temperatureZone3",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "1",
"crontab": "",
"once": false,
"onceDelay": "1",
"topic": "ns=2;s=@ActTmpBrlZone[1,3]",
"payload": "",
"payloadType": "date",
"x": 130,
"y": 140,
"wires": [
[]
]
},
{
"id": "edb8ff25ddf6bd76",
"type": "inject",
"z": "788edd9e.90d464",
"name": "temperatureZone4",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "1",
"crontab": "",
"once": false,
"onceDelay": "1",
"topic": "ns=2;s=@ActTmpBrlZone[1,4]",
"payload": "",
"payloadType": "date",
"x": 130,
"y": 180,
"wires": [
[]
]
},
{
"id": "188d14ba012c441f",
"type": "inject",
"z": "788edd9e.90d464",
"name": "temperatureZone5",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "1",
"crontab": "",
"once": false,
"onceDelay": "1",
"topic": "ns=2;s=@ActTmpBrlNozzle[1]",
"payload": "",
"payloadType": "date",
"x": 130,
"y": 220,
"wires": [
[]
]
},
{
"id": "e36c5490823880c7",
"type": "function",
"z": "788edd9e.90d464",
"name": "function 2",
"func": "//ObtĂ©m o valor de msg.payload proveniente dos sensores\n/*var temperatureZone0 = msg.payload.temperatureZone0;\nvar temperatureZone1 = msg.payload.temperatureZone1;\nvar temperatureZone2 = msg.payload.temperatureZone2;\nvar temperatureZone3 = msg.payload.temperatureZone3;\nvar temperatureZone4 = msg.payload.temperatureZone4;\nvar temperatureZone5 = msg.payload.temperatureZone5;\n\nvar sql = \"INSERT INTO digitalizacao.Milacron (temperatureZone0,temperatureZone1,temperatureZone2,temperatureZone3,temperatureZone4,temperatureZone5) VALUES (\"+temperatureZone0+\",\"+temperatureZone1+\",\"+temperatureZone2+\",\"+temperatureZone3+\",\"+temperatureZone4+\",\"+temperatureZone5+\")\";\nmsg.payload = sql;\n\nreturn msg;*/\n\n/*let {temperatureZone0,temperatureZone1,temperatureZone2,temperatureZone3,temperatureZone4,temperatureZone5} = msg.payload;\n\nlet sql = `INSERT INTO digitalizacao.Milacron(temperatureZone0,temperatureZone1,temperatureZone2,temperatureZone3,temperatureZone4,temperatureZone5) VALUES (${temperatureZone0},${temperatureZone1},${temperatureZone2},${temperatureZone3},${temperatureZone4},${temperatureZone5},)`;\n\nmsg.payload = sql;\nreturn msg;*/\n\n//ObtĂ©m o valor de msg.payload proveniente dos sensores\nvar sensorvalue = msg.payload;\n\n\n\n//Verifica qual sensor está enviando a mensagem e define a saĂda correspondente\nif (msg.topic === \"ns=2;s=@ActTmpBrlFeed[1]\") {\n msg.payload.temperatureZone0 = sensorvalue;\n}\nelse if (msg.topic === \"ns=2;s=@ActTmpBrlZone[1,1]\") {\n msg.payload.temperatureZone1 = sensorvalue;\n}\nelse if (msg.topic === \"ns=2;s=@ActTmpBrlZone[1,2]\") {\n msg.payload.temperatureZone2 = sensorvalue;\n}\nelse if (msg.topic === \"ns=2;s=@ActTmpBrlZone[1,3]\") {\n msg.payload.temperatureZone3 = sensorvalue;\n}\nelse if (msg.topic === \"ns=2;s=@ActTmpBrlZone[1,4]\") {\n msg.payload.temperatureZone4 = sensorvalue;\n}\nelse if (msg.topic === \"ns=2;s=@ActTmpBrlNozzle[1]\") {\n msg.payload.temperatureZone5 = sensorvalue;\n}\n\nreturn msg;",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 500,
"y": 500,
"wires": [
[
"b34f4dbe0d916aeb"
]
]
},
{
"id": "b34f4dbe0d916aeb",
"type": "template",
"z": "788edd9e.90d464",
"name": "",
"field": "topic",
"fieldType": "msg",
"format": "sql",
"syntax": "mustache",
"template": "INSERT INTO `digitalizacao`.`Milacron`(`temperatureZone0`,`temperatureZone1`,`temperatureZone2`,`temperatureZone3`,`temperatureZone4`,`temperatureZone5`) VALUES ('{{payload.temperatureZone0}}','{{payload.temperatureZone1}}','{{payload.temperatureZone2}}','{{payload.temperatureZone3}}','{{payload.temperatureZone4}}','{{payload.temperatureZone5}}');\n",
"output": "str",
"x": 640,
"y": 500,
"wires": [
[]
]
},
{
"id": "6ddb1ac547f559dc",
"type": "function",
"z": "788edd9e.90d464",
"name": "function 3",
"func": "//ObtĂ©m o valor de msg.payload proveniente dos sensores\nvar sensorvalue = msg.payload;\n\n//Verifica qual sensor está enviando a mensagem e define a saĂda correspondente\nif(msg.topic === \"ns=2;s=@ActTmpBrlFeed[1]\"){\n msg.temperatureZone0 = parseFloat(sensorvalue);\n}\n\n//Retorna a mensagem modificada\nreturn msg;",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 160,
"y": 560,
"wires": [
[
"4fe27f79a428f3f1"
]
]
},
{
"id": "4b4ff719e1f9862f",
"type": "function",
"z": "788edd9e.90d464",
"name": "function 4",
"func": "//Obtém o valor de msg.payload proveniente dos sensores\nvar sensorvalue = msg.payload;\n\nif(msg.topic === \"ns=2;s=@ActTmpBrlZone[1,1]\"){\n msg.temperatureZone1 = parseFloat(sensorvalue);\n}\n\n//Retorna a mensagem modificada\nreturn msg;",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 160,
"y": 600,
"wires": [
[
"cfafcd2ca38e9e8e"
]
]
},
{
"id": "1efc1ef2c9176ba5",
"type": "template",
"z": "788edd9e.90d464",
"name": "",
"field": "topic",
"fieldType": "msg",
"format": "sql",
"syntax": "mustache",
"template": "INSERT INTO `digitalizacao`.`Milacron`(`temperatureZone0`) VALUES ({{Msg0}});\n",
"output": "str",
"x": 500,
"y": 560,
"wires": [
[
"6dc7d1928bbdee2e"
]
]
},
{
"id": "ca15650a118aa040",
"type": "template",
"z": "788edd9e.90d464",
"name": "",
"field": "topic",
"fieldType": "msg",
"format": "sql",
"syntax": "mustache",
"template": "INSERT INTO `digitalizacao`.`Milacron`(`temperatureZone1`) VALUES ({{Msg1}});\n",
"output": "str",
"x": 500,
"y": 600,
"wires": [
[
"6dc7d1928bbdee2e"
]
]
},
{
"id": "24f176abbce316d0",
"type": "function",
"z": "788edd9e.90d464",
"name": "function 5",
"func": "//Obtém o valor de msg.payload proveniente dos sensores\nvar sensorvalue = msg.payload;\n\nif(msg.topic === \"ns=2;s=@ActTmpBrlZone[1,2]\"){\n msg.temperatureZone2 = parseFloat(sensorvalue);\n}\n\n//Retorna a mensagem modificada\nreturn msg;",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 160,
"y": 640,
"wires": [
[
"3365f1a13e7c2a96"
]
]
},
{
"id": "e8b3f747c6d0872a",
"type": "function",
"z": "788edd9e.90d464",
"name": "function 6",
"func": "//Obtém o valor de msg.payload proveniente dos sensores\nvar sensorvalue = msg.payload;\n\nif(msg.topic === \"ns=2;s=@ActTmpBrlZone[1,3]\"){\n msg.temperatureZone3 = parseFloat(sensorvalue);\n}\n\n//Retorna a mensagem modificada\nreturn msg;",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 160,
"y": 680,
"wires": [
[
"37dd688af68d8b20"
]
]
},
{
"id": "d6c8be535a70ba3a",
"type": "function",
"z": "788edd9e.90d464",
"name": "function 7",
"func": "//Obtém o valor de msg.payload proveniente dos sensores\nvar sensorvalue = msg.payload;\n\nif(msg.topic === \"ns=2;s=@ActTmpBrlZone[1,4]\"){\n msg.temperatureZone4 = parseFloat(sensorvalue);\n}\n\n//Retorna a mensagem modificada\nreturn msg;",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 160,
"y": 720,
"wires": [
[
"199718568d191f8b"
]
]
},
{
"id": "bd4123f4bfc2132e",
"type": "function",
"z": "788edd9e.90d464",
"name": "function 8",
"func": "//Obtém o valor de msg.payload proveniente dos sensores\nvar sensorvalue = msg.payload;\n\nif(msg.topic === \"ns=2;s=@ActTmpBrlZone[1,1]\"){\n msg.temperatureZone1 = parseFloat(sensorvalue);\n}\n\n//Retorna a mensagem modificada\nreturn msg;",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 160,
"y": 760,
"wires": [
[
"17f73edf67b2b937"
]
]
},
{
"id": "540053aab91cb16d",
"type": "template",
"z": "788edd9e.90d464",
"name": "",
"field": "topic",
"fieldType": "msg",
"format": "sql",
"syntax": "mustache",
"template": "INSERT INTO `digitalizacao`.`Milacron`(`temperatureZone2`) VALUES ({{Msg2}});\n",
"output": "str",
"x": 500,
"y": 640,
"wires": [
[
"6dc7d1928bbdee2e"
]
]
},
{
"id": "90ef15840b06b993",
"type": "template",
"z": "788edd9e.90d464",
"name": "",
"field": "topic",
"fieldType": "msg",
"format": "sql",
"syntax": "mustache",
"template": "INSERT INTO `digitalizacao`.`Milacron`(`temperatureZone3`) VALUES ({{Msg3}});\n",
"output": "str",
"x": 500,
"y": 680,
"wires": [
[
"6dc7d1928bbdee2e"
]
]
},
{
"id": "21e965406c3ead57",
"type": "template",
"z": "788edd9e.90d464",
"name": "",
"field": "topic",
"fieldType": "msg",
"format": "sql",
"syntax": "mustache",
"template": "INSERT INTO `digitalizacao`.`Milacron`(`temperatureZone4`) VALUES ({{Msg4}});\n",
"output": "str",
"x": 500,
"y": 720,
"wires": [
[
"6dc7d1928bbdee2e"
]
]
},
{
"id": "5cc98767cc4b23d9",
"type": "template",
"z": "788edd9e.90d464",
"name": "",
"field": "topic",
"fieldType": "msg",
"format": "sql",
"syntax": "mustache",
"template": "INSERT INTO `digitalizacao`.`Milacron`(`temperatureZone5`) VALUES ({{Msg5}});\n",
"output": "str",
"x": 500,
"y": 760,
"wires": [
[
"6dc7d1928bbdee2e"
]
]
},
{
"id": "6dc7d1928bbdee2e",
"type": "debug",
"z": "788edd9e.90d464",
"name": "debug 4",
"active": false,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"statusVal": "",
"statusType": "auto",
"x": 680,
"y": 580,
"wires": []
},
{
"id": "3e5dba4292a1de0d",
"type": "join",
"z": "788edd9e.90d464",
"name": "",
"mode": "custom",
"build": "object",
"property": "payload",
"propertyType": "msg",
"key": "topic",
"joiner": "\\n",
"joinerType": "str",
"accumulate": true,
"timeout": "",
"count": "6",
"reduceRight": false,
"reduceExp": "",
"reduceInit": "",
"reduceInitType": "",
"reduceFixup": "",
"x": 90,
"y": 480,
"wires": [
[
"6ddb1ac547f559dc",
"4b4ff719e1f9862f",
"24f176abbce316d0",
"e8b3f747c6d0872a",
"d6c8be535a70ba3a",
"bd4123f4bfc2132e",
"8b08096ed7226546"
]
]
},
{
"id": "4fe27f79a428f3f1",
"type": "change",
"z": "788edd9e.90d464",
"name": "",
"rules": [
{
"t": "set",
"p": "Msg0",
"pt": "msg",
"to": "payload",
"tot": "msg"
},
{
"t": "delete",
"p": "payload",
"pt": "msg"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 320,
"y": 560,
"wires": [
[
"1efc1ef2c9176ba5"
]
]
},
{
"id": "cfafcd2ca38e9e8e",
"type": "change",
"z": "788edd9e.90d464",
"name": "",
"rules": [
{
"t": "set",
"p": "Msg1",
"pt": "msg",
"to": "payload",
"tot": "msg"
},
{
"t": "delete",
"p": "payload",
"pt": "msg"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 320,
"y": 600,
"wires": [
[
"ca15650a118aa040"
]
]
},
{
"id": "3365f1a13e7c2a96",
"type": "change",
"z": "788edd9e.90d464",
"name": "",
"rules": [
{
"t": "set",
"p": "Msg2",
"pt": "msg",
"to": "payload",
"tot": "msg"
},
{
"t": "delete",
"p": "payload",
"pt": "msg"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 320,
"y": 640,
"wires": [
[
"540053aab91cb16d"
]
]
},
{
"id": "37dd688af68d8b20",
"type": "change",
"z": "788edd9e.90d464",
"name": "",
"rules": [
{
"t": "set",
"p": "Msg3",
"pt": "msg",
"to": "payload",
"tot": "msg"
},
{
"t": "delete",
"p": "payload",
"pt": "msg"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 340,
"y": 680,
"wires": [
[
"90ef15840b06b993"
]
]
},
{
"id": "199718568d191f8b",
"type": "change",
"z": "788edd9e.90d464",
"name": "",
"rules": [
{
"t": "set",
"p": "Msg4",
"pt": "msg",
"to": "payload",
"tot": "msg"
},
{
"t": "delete",
"p": "payload",
"pt": "msg"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 320,
"y": 720,
"wires": [
[
"21e965406c3ead57"
]
]
},
{
"id": "17f73edf67b2b937",
"type": "change",
"z": "788edd9e.90d464",
"name": "",
"rules": [
{
"t": "set",
"p": "Msg5",
"pt": "msg",
"to": "payload",
"tot": "msg"
},
{
"t": "delete",
"p": "payload",
"pt": "msg"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 320,
"y": 760,
"wires": [
[
"5cc98767cc4b23d9"
]
]
},
{
"id": "22fe35b54015744f",
"type": "debug",
"z": "788edd9e.90d464",
"name": "debug 5",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"statusVal": "",
"statusType": "auto",
"x": 620,
"y": 820,
"wires": []
},
{
"id": "63f66d4fe275d318",
"type": "function",
"z": "788edd9e.90d464",
"name": "function 9",
"func": "if (typeof msg.payload !== 'object' || msg.payload === null){\n msg.payload={};\n}\n\nswitch(msg.topic){\n case \"ns=2;s=@ActTmpBrlFeed[1]\":msg.payload.temperatureZone0=msg.value;\n break;\n case \"ns=2;s=@ActTmpBrlZone[1,1]\": msg.payload.temperatureZone1 = msg.value;\n break;\n case \"ns=2;s=@ActTmpBrlZone[1,2]\": msg.payload.temperatureZone2 = msg.value;\n break;\n case \"ns=2;s=@ActTmpBrlZone[1,3]\": msg.payload.temperatureZone3 = msg.value;\n break;\n case \"ns=2;s=@ActTmpBrlZone[1,4]\": msg.payload.temperatureZone4 = msg.value;\n break;\n case \"ns=2;s=@ActTmpBrlNozzle[1]\": msg.payload.temperatureZone5 = msg.value;\n break;\n}\n\nreturn msg;",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 260,
"y": 860,
"wires": [
[]
]
},
{
"id": "a6a95cc7073657cb",
"type": "template",
"z": "788edd9e.90d464",
"name": "",
"field": "topic",
"fieldType": "msg",
"format": "sql",
"syntax": "mustache",
"template": "INSERT INTO `digitalizacao`.`Milacron`(`temperatureZone0`,`temperatureZone1`,`temperatureZone2`,`temperatureZone3`,`temperatureZone4`,`temperatureZone5`) VALUES (\"{{sensorsvalues.temperatureZone0}}\",\"{{sensorsvalues.temperatureZone1}}\",\"{{sensorsvalues.temperatureZone2}}\",\"{{sensorsvalues.temperatureZone3}}\",\"{{sensorsvalues.temperatureZone4}}\",\"{{sensorsvalues.temperatureZone5}}\");\n",
"output": "str",
"x": 440,
"y": 860,
"wires": [
[
"22fe35b54015744f"
]
]
},
{
"id": "8b08096ed7226546",
"type": "function",
"z": "788edd9e.90d464",
"name": "function 10",
"func": "//ObtĂ©m o valor de msg.payload proveniente dos sensores\nvar sensorvalue = msg.payload;\n\n//Cria um objeto para armazenar os valores de todos os sensores\nvar sensorsvalues = {};\n\n//Verifica qual sensor está enviando a mensagem e define a saĂda correspondente\nif(msg.topic === \"ns=2;s=@ActTmpBrlFeed[1]\"){\n sensorsvalues.temperatureZone0 = sensorvalue;\n}\nelse if(msg.topic === \"ns=2;s=@ActTmpBrlZone[1,1]\"){\n sensorsvalues.temperatureZone1 = sensorvalue;\n}\nelse if(msg.topic === \"ns=2;s=@ActTmpBrlZone[1,2]\"){\n sensorsvalues.temperatureZone2 = sensorvalue;\n}\nelse if(msg.topic === \"ns=2;s=@ActTmpBrlZone[1,3]\"){\n sensorsvalues.temperatureZone3 = sensorvalue;\n}\nelse if(msg.topic === \"ns=2;s=@ActTmpBrlZone[1,4]\"){\n sensorsvalues.temperatureZone4 = sensorvalue;\n}\nelse if(msg.topic === \"ns=2;s=@ActTmpBrlNozzle[1]\"){\n sensorsvalues.temperatureZone5 = sensorvalue;\n}\n\n//Adiciona os valores dos sensores ao objeto msg\nmsg.sensorsvalues = sensorsvalues;\n\n//Retorna a mensagem modificada\nreturn msg;",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 250,
"y": 920,
"wires": [
[
"a6a95cc7073657cb"
]
]
}
]
You have posted a flow in which no data source is connected to the MySQL node.
This flow cannot possibly result in the problem you describe above.
There are 9 function node / template node combinations in this flow. How can we tell which is relevant to the issue?
You have vaguely described your input data, you have vaguely described the result in your database but you have not shown us any data.
It seems to me that you are asking us to more or less:
I can't see any way to assist you.
Sorry if I didn't explain right.
My data are:
The processing applied to insert data into mysql it's not working, it's not inserting the number values in the columns:
And it's the flow corrected. I'm trying to put each temperature sensor value in each column described like temperatureZoneX.
[
{
"id": "96767a24f66ad306",
"type": "tab",
"label": "Flow 7",
"disabled": false,
"info": "",
"env": []
},
{
"id": "1d1e4b6a37a33636",
"type": "inject",
"z": "96767a24f66ad306",
"name": "",
"props": [
{
"p": "payload"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 80,
"y": 300,
"wires": [
[
"dd7e8db1fa80b4ca",
"311cbede7c350149",
"11bc22b89ba91d97",
"7714747921cc3d29",
"6ccc92e5c8cad379",
"6beececf42babfb4"
]
]
},
{
"id": "0691e2d3de26c929",
"type": "OpcUa-Client",
"z": "96767a24f66ad306",
"endpoint": "",
"action": "read",
"deadbandtype": "a",
"deadbandvalue": 1,
"time": 10,
"timeUnit": "s",
"certificate": "n",
"localfile": "",
"localkeyfile": "",
"folderName4PKI": "",
"useTransport": false,
"maxChunkCount": "",
"maxMessageSize": "",
"receiveBufferSize": "",
"sendBufferSize": "",
"name": "",
"x": 520,
"y": 320,
"wires": [
[
"07eaea7962ed8deb"
]
]
},
{
"id": "dd7e8db1fa80b4ca",
"type": "OpcUa-Item",
"z": "96767a24f66ad306",
"item": "ns=2;s=@ActTmpBrlFeed[1]",
"datatype": "Double",
"value": "",
"name": "temperatureZone0",
"x": 290,
"y": 220,
"wires": [
[
"0691e2d3de26c929"
]
]
},
{
"id": "311cbede7c350149",
"type": "OpcUa-Item",
"z": "96767a24f66ad306",
"item": "ns=2;s=@ActTmpBrlZone[1,1]",
"datatype": "Double",
"value": "",
"name": "temperatureZone1",
"x": 290,
"y": 260,
"wires": [
[
"0691e2d3de26c929"
]
]
},
{
"id": "11bc22b89ba91d97",
"type": "OpcUa-Item",
"z": "96767a24f66ad306",
"item": "ns=2;s=@ActTmpBrlZone[1,2]",
"datatype": "Double",
"value": "",
"name": "temperatureZone2",
"x": 290,
"y": 300,
"wires": [
[
"0691e2d3de26c929"
]
]
},
{
"id": "7714747921cc3d29",
"type": "OpcUa-Item",
"z": "96767a24f66ad306",
"item": "ns=2;s=@ActTmpBrlZone[1,3]",
"datatype": "Double",
"value": "",
"name": "temperatureZone3",
"x": 290,
"y": 340,
"wires": [
[
"0691e2d3de26c929"
]
]
},
{
"id": "6ccc92e5c8cad379",
"type": "OpcUa-Item",
"z": "96767a24f66ad306",
"item": "ns=2;s=@ActTmpBrlZone[1,4]",
"datatype": "Double",
"value": "",
"name": "temperatureZone4",
"x": 290,
"y": 380,
"wires": [
[
"0691e2d3de26c929"
]
]
},
{
"id": "6beececf42babfb4",
"type": "OpcUa-Item",
"z": "96767a24f66ad306",
"item": "ns=2;s=@ActTmpBrlNozzle[1]",
"datatype": "Double",
"value": "",
"name": "temperatureZone5",
"x": 290,
"y": 420,
"wires": [
[
"0691e2d3de26c929"
]
]
},
{
"id": "07eaea7962ed8deb",
"type": "function",
"z": "96767a24f66ad306",
"name": "function 11",
"func": "//ObtĂ©m o valor de msg.payload proveniente dos sensores\nvar sensorvalue = msg.payload;\n\n//Cria um objeto para armazenar os valores de todos os sensores\nvar sensorsvalues = {};\n\n\n//Verifica qual sensor está enviando a mensagem e define a saĂda correspondente\nif(msg.topic === \"ns=2;s=@ActTmpBrlFeed[1]\"){\n sensorsvalues.temperatureZone0 = Object.values(sensorvalue);\n}\nelse if(msg.topic === \"ns=2;s=@ActTmpBrlZone[1,1]\"){\n sensorsvalues.temperatureZone1 = Object.values(sensorvalue);\n}\nelse if(msg.topic === \"ns=2;s=@ActTmpBrlZone[1,2]\"){\n sensorsvalues.temperatureZone2 = Object.values(sensorvalue);\n}\nelse if(msg.topic === \"ns=2;s=@ActTmpBrlZone[1,3]\"){\n sensorsvalues.temperatureZone3 = Object.values(sensorvalue);\n}\nelse if(msg.topic === \"ns=2;s=@ActTmpBrlZone[1,4]\"){\n sensorsvalues.temperatureZone4 = Object.values(sensorvalue);\n}\nelse if(msg.topic === \"ns=2;s=@ActTmpBrlNozzle[1]\"){\n sensorsvalues.temperatureZone5 = Object.values(sensorvalue);\n}\n\n//Adiciona os valores dos sensores ao objeto msg\nmsg.sensorsvalues = sensorsvalues;\n\n//Retorna a mensagem modificada\nreturn msg;",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 550,
"y": 400,
"wires": [
[
"f758a6d243246532"
]
]
},
{
"id": "f758a6d243246532",
"type": "template",
"z": "96767a24f66ad306",
"name": "",
"field": "topic",
"fieldType": "msg",
"format": "sql",
"syntax": "mustache",
"template": "INSERT INTO `digitalizacao`.`Milacron`(`temperatureZone0`,`temperatureZone1`,`temperatureZone2`,`temperatureZone3`,`temperatureZone4`,`temperatureZone5`) VALUES ({{sensorsvalues.temperatureZone0}},{{sensorsvalues.temperatureZone1}},{{sensorsvalues.temperatureZone2}},{{sensorsvalues.temperatureZone3}},{{sensorsvalues.temperatureZone4}},{{sensorsvalues.temperatureZone5}});\n",
"output": "str",
"x": 720,
"y": 360,
"wires": [
[
"b63507ca79f626da"
]
]
},
{
"id": "b63507ca79f626da",
"type": "mysql",
"z": "96767a24f66ad306",
"mydb": "",
"name": "Mysql",
"x": 730,
"y": 280,
"wires": [
[
"0bab0e20822352fc"
]
]
},
{
"id": "0bab0e20822352fc",
"type": "debug",
"z": "96767a24f66ad306",
"name": "debug 7",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"statusVal": "",
"statusType": "auto",
"x": 760,
"y": 200,
"wires": []
}
]
Your function node will never end up with all the data in one message. As each msg arrives, it is examined and depending on which if statement matches, that data will be what is sent out from the function node.
You need to join the six msgs together and send that to the function node. Then in the function node create the outgoing msg with all six data items you need.
Then the template node will have the data to create a valid sql statement.
I used a join node and changed the function node:
var values = msg.payload;
var query = "INSERT INTO digitalizacao.Milacron(temperatureZone0,temperatureZone1,temperatureZone2,temperatureZone3,temperatureZone4,temperatureZone5) VALUES (";
query += values.temperatureZone0 + ",";
query += values.temperatureZone1 + ",";
query += values.temperatureZone2 + ",";
query += values.temperatureZone3 + ",";
query += values.temperatureZone4 + ",";
query += values.temperatureZone5 + ")";
msg.payload = query;
return msg;
But the values displayed are undefined
Your values are undefined because you are testing different topic names. I suspect as you have already joined the data into a a key/value payload, you do not actually need to test the topic.
It might be you simply need to apply the values without the if(topic === stuff
Also, it is strongly advised to NOT use SQL strings, but instead use Prepared Querys as demonstrated in the readme
Here is a demo.
Demo flow
[{"id":"5e4d52fe211cf160","type":"function","z":"3642c7ee286f9c17","name":"faking your data","func":"\nmsg.payload = {\n \"ns=2;s=@ActTmpBrlFeed[1]\": 15,\n \"ns=2;s=@ActTmpBrlZone[1,1]\": 14,\n \"ns=2;s=@ActTmpBrlZone[1,2]\": 14,\n \"ns=2;s=@ActTmpBrlZone[1,3]\": 13,\n \"ns=2;s=@ActTmpBrlZone[1,4]\": 14,\n \"ns=2;s=@ActTmpBrlNozzle[1]\": 14\n}\nreturn msg;","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":680,"y":280,"wires":[["fe40b7f106ebb275","61a8ed405a5812ce"]]},{"id":"fed8e64139153c46","type":"inject","z":"3642c7ee286f9c17","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":640,"y":220,"wires":[["5e4d52fe211cf160"]]},{"id":"fe40b7f106ebb275","type":"function","z":"3642c7ee286f9c17","name":"prepare params","func":"msg.payload = {\n ActTmpBrlFeed1: msg.payload[\"ns=2;s=@ActTmpBrlFeed[1]\"],\n ActTmpBrlZone1_1: msg.payload[\"ns=2;s=@ActTmpBrlZone[1,1]\"],\n ActTmpBrlZone1_2: msg.payload[\"ns=2;s=@ActTmpBrlZone[1,2]\"],\n ActTmpBrlZone1_3: msg.payload[\"ns=2;s=@ActTmpBrlZone[1,3]\"],\n ActTmpBrlZone1_4: msg.payload[\"ns=2;s=@ActTmpBrlZone[1,4]\"],\n ActTmpBrlNozzle_1: msg.payload[\"ns=2;s=@ActTmpBrlNozzle[1]\"]\n}\n\nmsg.topic = \"INSERT INTO mytable (`col1`, `col2`, `col3`, `col4`, `col5`, `col6`) VALUES (:ActTmpBrlFeed1, :ActTmpBrlZone1_1, :ActTmpBrlZone1_2, :ActTmpBrlZone1_3, :ActTmpBrlZone1_4, :ActTmpBrlNozzle_1);\"\nreturn msg;\n","outputs":1,"timeout":0,"noerr":0,"initialize":"","finalize":"","libs":[],"x":680,"y":340,"wires":[["3c9072c368727840"]]},{"id":"3c9072c368727840","type":"debug","z":"3642c7ee286f9c17","name":"to DB -->","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":880,"y":340,"wires":[]},{"id":"61a8ed405a5812ce","type":"debug","z":"3642c7ee286f9c17","name":"the data from join node","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":920,"y":280,"wires":[]}]
The function only:
msg.payload = {
ActTmpBrlFeed1: msg.payload["ns=2;s=@ActTmpBrlFeed[1]"],
ActTmpBrlZone1_1: msg.payload["ns=2;s=@ActTmpBrlZone[1,1]"],
ActTmpBrlZone1_2: msg.payload["ns=2;s=@ActTmpBrlZone[1,2]"],
ActTmpBrlZone1_3: msg.payload["ns=2;s=@ActTmpBrlZone[1,3]"],
ActTmpBrlZone1_4: msg.payload["ns=2;s=@ActTmpBrlZone[1,4]"],
ActTmpBrlNozzle_1: msg.payload["ns=2;s=@ActTmpBrlNozzle[1]"]
}
msg.topic = "INSERT INTO mytable (`col1`, `col2`, `col3`, `col4`, `col5`, `col6`) VALUES (:ActTmpBrlFeed1, :ActTmpBrlZone1_1, :ActTmpBrlZone1_2, :ActTmpBrlZone1_3, :ActTmpBrlZone1_4, :ActTmpBrlNozzle_1);"
return msg;
Thank you for your help, I solved the issue:
[
{
"id": "788edd9e.90d464",
"type": "tab",
"label": "Flow 2",
"disabled": false,
"info": ""
},
{
"id": "6b44502a.129e7",
"type": "mysql",
"z": "788edd9e.90d464",
"mydb": "",
"name": "Mysql",
"x": 810,
"y": 280,
"wires": [
[
"2ac5c830e053c98b"
]
]
},
{
"id": "b95eecf0ea19f418",
"type": "OpcUa-Client",
"z": "788edd9e.90d464",
"endpoint": "",
"action": "read",
"deadbandtype": "a",
"deadbandvalue": 1,
"time": 10,
"timeUnit": "s",
"certificate": "n",
"localfile": "",
"localkeyfile": "",
"folderName4PKI": "",
"useTransport": false,
"maxChunkCount": "",
"maxMessageSize": "",
"receiveBufferSize": "",
"sendBufferSize": "",
"name": "",
"x": 460,
"y": 140,
"wires": [
[
"649d6f6c21ed3ab0"
]
]
},
{
"id": "dbf668c30bd7dfbf",
"type": "OpcUa-Item",
"z": "788edd9e.90d464",
"item": "ns=2;s=@ActTmpBrlFeed[1]",
"datatype": "Double",
"value": "",
"name": "temperatureZone0",
"x": 330,
"y": 300,
"wires": [
[
"b95eecf0ea19f418"
]
]
},
{
"id": "87f6136bcf09973a",
"type": "inject",
"z": "788edd9e.90d464",
"name": "",
"props": [
{
"p": "payload"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 100,
"y": 300,
"wires": [
[
"dbf668c30bd7dfbf",
"ace44828f9018116",
"cdea5bec5218fd40",
"146c50872e7b3ca8",
"2ad69ea8e45aab5f",
"894f5867a3128a9c"
]
]
},
{
"id": "ace44828f9018116",
"type": "OpcUa-Item",
"z": "788edd9e.90d464",
"item": "ns=2;s=@ActTmpBrlZone[1,1]",
"datatype": "Double",
"value": "",
"name": "temperatureZone1",
"x": 330,
"y": 340,
"wires": [
[
"b95eecf0ea19f418"
]
]
},
{
"id": "cdea5bec5218fd40",
"type": "OpcUa-Item",
"z": "788edd9e.90d464",
"item": "ns=2;s=@ActTmpBrlZone[1,2]",
"datatype": "Double",
"value": "",
"name": "temperatureZone2",
"x": 330,
"y": 380,
"wires": [
[
"b95eecf0ea19f418"
]
]
},
{
"id": "146c50872e7b3ca8",
"type": "OpcUa-Item",
"z": "788edd9e.90d464",
"item": "ns=2;s=@ActTmpBrlZone[1,3]",
"datatype": "Double",
"value": "",
"name": "temperatureZone3",
"x": 330,
"y": 420,
"wires": [
[
"b95eecf0ea19f418"
]
]
},
{
"id": "2ad69ea8e45aab5f",
"type": "OpcUa-Item",
"z": "788edd9e.90d464",
"item": "ns=2;s=@ActTmpBrlZone[1,4]",
"datatype": "Double",
"value": "",
"name": "temperatureZone4",
"x": 330,
"y": 460,
"wires": [
[
"b95eecf0ea19f418"
]
]
},
{
"id": "894f5867a3128a9c",
"type": "OpcUa-Item",
"z": "788edd9e.90d464",
"item": "ns=2;s=@ActTmpBrlNozzle[1]",
"datatype": "Double",
"value": "",
"name": "temperatureZone5",
"x": 330,
"y": 500,
"wires": [
[
"b95eecf0ea19f418"
]
]
},
{
"id": "3083fbf31559bdd3",
"type": "debug",
"z": "788edd9e.90d464",
"name": "debug 6",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"statusVal": "",
"statusType": "auto",
"x": 640,
"y": 380,
"wires": []
},
{
"id": "649d6f6c21ed3ab0",
"type": "join",
"z": "788edd9e.90d464",
"name": "",
"mode": "custom",
"build": "object",
"property": "payload",
"propertyType": "msg",
"key": "topic",
"joiner": "\\n",
"joinerType": "str",
"accumulate": true,
"timeout": "",
"count": "6",
"reduceRight": false,
"reduceExp": "",
"reduceInit": "",
"reduceInitType": "",
"reduceFixup": "",
"x": 630,
"y": 180,
"wires": [
[
"c2f9454fa0174a2b"
]
]
},
{
"id": "c2f9454fa0174a2b",
"type": "function",
"z": "788edd9e.90d464",
"name": "function 11",
"func": "var Zone0 = msg.payload[\"ns=2;s=@ActTmpBrlFeed[1]\"];\nvar Zone1 = msg.payload[\"ns=2;s=@ActTmpBrlZone[1,1]\"];\nvar Zone2 = msg.payload[\"ns=2;s=@ActTmpBrlZone[1,2]\"];\nvar Zone3 = msg.payload[\"ns=2;s=@ActTmpBrlZone[1,3]\"];\nvar Zone4 = msg.payload[\"ns=2;s=@ActTmpBrlZone[1,4]\"];\nvar Zone5 = msg.payload[\"ns=2;s=@ActTmpBrlNozzle[1]\"];\n\nvar query = \"INSERT INTO `digitalizacao`.`Milacron`(`temperatureZone0`,`temperatureZone1`,`temperatureZone2`,`temperatureZone3`,`temperatureZone4`,`temperatureZone5`) VALUES (\";\nquery += Zone0 + \",\";\nquery += Zone1 + \",\";\nquery += Zone2 + \",\";\nquery += Zone3 + \",\";\nquery += Zone4 + \",\";\nquery += Zone5 + \")\";\n\nmsg.topic = query;\n\nreturn msg;",
"outputs": 1,
"timeout": 0,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 610,
"y": 280,
"wires": [
[
"3083fbf31559bdd3",
"6b44502a.129e7"
]
]
},
{
"id": "2ac5c830e053c98b",
"type": "debug",
"z": "788edd9e.90d464",
"name": "debug 1",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 840,
"y": 380,
"wires": []
}
]
As I stated before, it is not recommended to generate SQL strings but to use Prepared Queries instead
Your function is NOT making a prepared query and is at risk from SQL Injection hacks
var Zone0 = msg.payload["ns=2;s=@ActTmpBrlFeed[1]"];
var Zone1 = msg.payload["ns=2;s=@ActTmpBrlZone[1,1]"];
var Zone2 = msg.payload["ns=2;s=@ActTmpBrlZone[1,2]"];
var Zone3 = msg.payload["ns=2;s=@ActTmpBrlZone[1,3]"];
var Zone4 = msg.payload["ns=2;s=@ActTmpBrlZone[1,4]"];
var Zone5 = msg.payload["ns=2;s=@ActTmpBrlNozzle[1]"];
var query = "INSERT INTO `digitalizacao`.`Milacron`(`temperatureZone0`,`temperatureZone1`,`temperatureZone2`,`temperatureZone3`,`temperatureZone4`,`temperatureZone5`) VALUES (";
query += Zone0 + ",";
query += Zone1 + ",";
query += Zone2 + ",";
query += Zone3 + ",";
query += Zone4 + ",";
query += Zone5 + ")";
msg.topic = query;
return msg;
and while it is likely not a huge concern in your local environment, using Prepared Queries is not only "best practice" and "safer" it is actually easier and less error prone.
msg.topic = "INSERT INTO `digitalizacao`.`Milacron`(`temperatureZone0`,`temperatureZone1`,`temperatureZone2`,`temperatureZone3`,`temperatureZone4`,`temperatureZone5`) VALUES (?, ?, ?, ?, ?, ?)"
const data = []
data.push(msg.payload["ns=2;s=@ActTmpBrlFeed[1]"])
data.push(msg.payload["ns=2;s=@ActTmpBrlZone[1,1]"])
data.push(msg.payload["ns=2;s=@ActTmpBrlZone[1,2]"])
data.push(msg.payload["ns=2;s=@ActTmpBrlZone[1,3]"])
data.push(msg.payload["ns=2;s=@ActTmpBrlZone[1,4]"])
data.push(msg.payload["ns=2;s=@ActTmpBrlNozzle[1]"])
msg.payload = data
return msg;
Ok, I understood. I'm just starting to develop any solution with sql so I haven't this knowledge about the risks and the best practice to use prepared queries. I will change, thank you for your attention!
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.
Copyright OpenJS Foundation and Node-RED contributors. All rights reserved. The OpenJS Foundation has registered trademarks and uses trademarks. For a list of trademarks of the OpenJS Foundation, please see our Trademark Policy and Trademark List. Trademarks and logos not indicated on the list of OpenJS Foundation trademarks are trademarks™ or registered® trademarks of their respective holders. Use of them does not imply any affiliation with or endorsement by them.
The OpenJS Foundation | Terms of Use | Privacy Policy | OpenJS Foundation Bylaws | Trademark Policy | Trademark List | Cookie Policy