Sending an array payload values into MySQL dB

General
1

Hi
As it is visible in the image below I have an array of 26 values which I want to send it into MySQL dB , Under voltage Colum I have 26 rows , one value at each row , and incoming data is from ignition edge and every time the value is updated the same should be updated in dB
can anybody help me with this

value_read
value_read1155×391 29.3 KB

db_table_img
PFA Flow

[
    {
        "id": "d9f3645e862cc528",
        "type": "tab",
        "label": "Flow 4",
        "disabled": false,
        "info": "",
        "env": []
    },
    {
        "id": "4a9707e1946312fe",
        "type": "inject",
        "z": "d9f3645e862cc528",
        "name": "",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "",
        "payloadType": "date",
        "x": 180,
        "y": 100,
        "wires": [
            [
                "ddb651b2d17aee2d"
            ]
        ]
    },
    {
        "id": "33d158d4f77b8056",
        "type": "debug",
        "z": "d9f3645e862cc528",
        "name": "#0C416B",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 640,
        "y": 160,
        "wires": []
    },
    {
        "id": "ddb651b2d17aee2d",
        "type": "ignition-tag-read",
        "z": "d9f3645e862cc528",
        "server": "fbd184d1ad1deaf1",
        "value": "",
        "valueType": "msg.payload",
        "tagPath": "[edge]1/Devices/1/LPC1/PSUI26",
        "name": "",
        "x": 260,
        "y": 200,
        "wires": [
            [
                "3fb8f86d2606db16"
            ]
        ]
    },
    {
        "id": "3fb8f86d2606db16",
        "type": "function",
        "z": "d9f3645e862cc528",
        "name": "",
        "func": "msg.payload = msg.payload.ignitionResult.value;\nreturn msg;",
        "outputs": 1,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 420,
        "y": 180,
        "wires": [
            [
                "3c68006.976b7",
                "33d158d4f77b8056"
            ]
        ]
    },
    {
        "id": "3c68006.976b7",
        "type": "template",
        "z": "d9f3645e862cc528",
        "name": "build SQL",
        "field": "topic",
        "fieldType": "msg",
        "format": "sql",
        "syntax": "mustache",
        "template": "update lpc set voltage = +msg.payload[0]+ , current =15 where powersupply = 'PSU-01'\n",
        "output": "str",
        "x": 120,
        "y": 320,
        "wires": [
            [
                "821e4c644258ca35"
            ]
        ]
    },
    {
        "id": "821e4c644258ca35",
        "type": "mysql",
        "z": "d9f3645e862cc528",
        "mydb": "687ec5a6f3f680be",
        "name": "",
        "x": 300,
        "y": 320,
        "wires": [
            []
        ]
    },
    {
        "id": "871339a6bdeec81d",
        "type": "debug",
        "z": "d9f3645e862cc528",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 650,
        "y": 220,
        "wires": []
    },
    {
        "id": "3a1229cc4f2c6d63",
        "type": "debug",
        "z": "d9f3645e862cc528",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "false",
        "statusVal": "",
        "statusType": "auto",
        "x": 650,
        "y": 280,
        "wires": []
    },
    {
        "id": "bf4c86b5516a9a0a",
        "type": "inject",
        "z": "d9f3645e862cc528",
        "name": "Payload value",
        "props": [
            {
                "p": "payload"
            },
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "[0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]",
        "payloadType": "json",
        "x": 130,
        "y": 40,
        "wires": [
            []
        ]
    },
    {
        "id": "fbd184d1ad1deaf1",
        "type": "ignition-server",
        "hostname": "192.168.1.21",
        "port": "8088",
        "ssl": false,
        "tls": "",
        "defaultTagProvider": "edge",
        "name": ""
    },
    {
        "id": "687ec5a6f3f680be",
        "type": "MySQLdatabase",
        "name": "",
        "host": "192.168.1.147",
        "port": "3306",
        "db": "ohmiumnodered",
        "tz": "",
        "charset": "UTF8"
    }
]
2

example of using template to make multiple updates

[{"id":"4a9707e1946312fe","type":"inject","z":"bf9e1e33.030598","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[1,2,3,4,5]","payloadType":"json","x":130,"y":1980,"wires":[["3c68006.976b7"]]},{"id":"3c68006.976b7","type":"template","z":"bf9e1e33.030598","name":"build SQL","field":"topic","fieldType":"msg","format":"sql","syntax":"mustache","template":"update lpc set voltage = {{{payload.0}}} , current =15 where powersupply = 'PSU-01';\nupdate lpc set voltage = {{{payload.1}}} , current =15 where powersupply = 'PSU-02';\nupdate lpc set voltage = {{{payload.2}}} , current =15 where powersupply = 'PSU-03';\nupdate lpc set voltage = {{{payload.3}}} , current =15 where powersupply = 'PSU-04';\nupdate lpc set voltage = {{{payload.4}}} , current =15 where powersupply = 'PSU-05';\n","output":"str","x":170,"y":2100,"wires":[["3a1229cc4f2c6d63"]]},{"id":"3a1229cc4f2c6d63","type":"debug","z":"bf9e1e33.030598","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":390,"y":2100,"wires":[]}]
3

You have not shown us the data coming from the ignition tag read function, but I suspect your function node discards the information linking the readings to the powersupply id.

You need an array of objects, something like this. Maybe the data already arrives in this format?

[
{"powersupply":"PSU-01", "voltage":14, "current":15},
{"powersupply":"PSU-02", "voltage":52, "current":45},
{"powersupply":"PSU-03", "voltage":55, "current":64}
etc
]

Feed this array into a Split node (to create one message per reading) and build your SQL UPDATE statement from that.

4

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