Insert multiple sensors data into mysql

General
1

image

I need help to insert correctly.

Function node code:

image
image415Ă—583 10.9 KB

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}}");>

2

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)

3

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"
            ]
        ]
    }
]
4

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:

  • Guess what your data looks like.
  • Guess what processing you apply to it.
  • Guess what the result is.
  • Guess what result you want.

I can't see any way to assist you.

5

Sorry if I didn't explain right.

My data are:

Captura de ecrĂŁ 2024-03-05 104426
Captura de ecrĂŁ 2024-03-05 104426373Ă—576 12.6 KB

The processing applied to insert data into mysql it's not working, it's not inserting the number values in the columns:

Captura de ecrĂŁ 2024-03-05 104614
Captura de ecrĂŁ 2024-03-05 104614200Ă—680 31.7 KB

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": []
    }
]
6

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.

7

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

image

8

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.

image
image418Ă—585 30.4 KB

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.

image
image1037Ă—573 36.5 KB

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;
9

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": []
    }
]
10

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.

Better solution, using Prepared Query

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;
1 Like
11

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!

1 Like
12

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.