OPC UA to SQL Problem

General
,
1

I have problem sending data from OPC UA to MS SQL database.

I have 3 tags that i read from my PLC (seconds, minutes and houre). This works find. But when I try to extract the value and put it into my SQL database, I get ann error; Incorrect syntax near '1'.
1 = The second value that counts every second. So other times I have another value in the erroor.

Here is my flow:

[
    {
        "id": "e072a13164d32272",
        "type": "tab",
        "label": "Flow 1",
        "disabled": false,
        "info": "",
        "env": []
    },
    {
        "id": "f772969f9b56c808",
        "type": "OpcUa-Client",
        "z": "e072a13164d32272",
        "endpoint": "e0da43123b30a3ac",
        "action": "subscribe",
        "deadbandtype": "a",
        "deadbandvalue": 1,
        "time": "1",
        "timeUnit": "s",
        "certificate": "n",
        "localfile": "",
        "localkeyfile": "",
        "securitymode": "None",
        "securitypolicy": "None",
        "folderName4PKI": "",
        "name": "OPC",
        "x": 450,
        "y": 300,
        "wires": [
            [
                "f469b7079c7c2d9a"
            ]
        ]
    },
    {
        "id": "5b1c84e5049f5606",
        "type": "debug",
        "z": "e072a13164d32272",
        "name": "Seconds",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 920,
        "y": 180,
        "wires": []
    },
    {
        "id": "b811ba42b33a817c",
        "type": "OpcUa-Browser",
        "z": "e072a13164d32272",
        "d": true,
        "endpoint": "e0da43123b30a3ac",
        "item": "",
        "datatype": "",
        "topic": "ns=2;i=1",
        "items": [],
        "name": "",
        "x": 370,
        "y": 60,
        "wires": [
            [
                "cc52d22e5926e7db"
            ]
        ]
    },
    {
        "id": "cc52d22e5926e7db",
        "type": "ui_template",
        "z": "e072a13164d32272",
        "group": "932a107191ea39cc",
        "name": "",
        "order": 0,
        "width": "6",
        "height": "10",
        "format": "<div layout=\"row\" layout-align=\"space-between\">\n\t<select ng-model=\"item\" ng-options=\"items.item.displayName.text for items in msg.payload\">\n\t\t<option value=\"\">-- Objects Root --</option>\n\t</select>\n\t<button ng-click=\"send({payload: {actiontype: 'browse', root: item}})\"> Browse </button>\n</div>\n\n<div layout=\"row\" layout-align=\"space-between\">\n    <ul> <li ng-repeat=\"items in msg.payload\"> {{items.item.displayName.text}}: {{items.item.nodeId}} </li>\n    </ul>\n</div>",
        "storeOutMessages": false,
        "fwdInMessages": false,
        "resendOnRefresh": true,
        "templateScope": "local",
        "className": "",
        "x": 340,
        "y": 160,
        "wires": [
            [
                "b811ba42b33a817c"
            ]
        ]
    },
    {
        "id": "bcb00b7938665c1d",
        "type": "ui_gauge",
        "z": "e072a13164d32272",
        "name": "RTD",
        "group": "d89550f79297ced2",
        "order": 1,
        "width": 0,
        "height": 0,
        "gtype": "gage",
        "title": "gauge",
        "label": "units",
        "format": "{{'%.1f'|sprintf:value}}sec",
        "min": "30",
        "max": "60",
        "colors": [
            "#00b500",
            "#e6e600",
            "#ca3838"
        ],
        "seg1": "",
        "seg2": "",
        "className": "",
        "x": 910,
        "y": 140,
        "wires": []
    },
    {
        "id": "0191e60408c8da17",
        "type": "debug",
        "z": "e072a13164d32272",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload3",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 860,
        "y": 620,
        "wires": []
    },
    {
        "id": "e67a0330f5ad1248",
        "type": "debug",
        "z": "e072a13164d32272",
        "name": "Minutes",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 920,
        "y": 320,
        "wires": []
    },
    {
        "id": "b405cec568d0e11e",
        "type": "debug",
        "z": "e072a13164d32272",
        "name": "Hours",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 910,
        "y": 480,
        "wires": []
    },
    {
        "id": "2d92449439e3dd97",
        "type": "ui_gauge",
        "z": "e072a13164d32272",
        "name": "RTD1",
        "group": "d89550f79297ced2",
        "order": 1,
        "width": 0,
        "height": 0,
        "gtype": "gage",
        "title": "gauge",
        "label": "units",
        "format": "{{'%.1f'|sprintf:value}}min",
        "min": "30",
        "max": "60",
        "colors": [
            "#00b500",
            "#e6e600",
            "#ca3838"
        ],
        "seg1": "",
        "seg2": "",
        "className": "",
        "x": 910,
        "y": 360,
        "wires": []
    },
    {
        "id": "94de1e01392db6f3",
        "type": "ui_gauge",
        "z": "e072a13164d32272",
        "name": "RTD2",
        "group": "d89550f79297ced2",
        "order": 1,
        "width": 0,
        "height": 0,
        "gtype": "gage",
        "title": "gauge",
        "label": "units",
        "format": "{{'%.1f'|sprintf:value}}hr",
        "min": "30",
        "max": "60",
        "colors": [
            "#00b500",
            "#e6e600",
            "#ca3838"
        ],
        "seg1": "",
        "seg2": "",
        "className": "",
        "x": 910,
        "y": 520,
        "wires": []
    },
    {
        "id": "1eb7b92207767668",
        "type": "OpcUa-Item",
        "z": "e072a13164d32272",
        "item": "ns=2;s=PersistentVars.Seconds",
        "datatype": "UInt32",
        "value": "",
        "name": "OPC1",
        "x": 270,
        "y": 240,
        "wires": [
            [
                "f772969f9b56c808"
            ]
        ]
    },
    {
        "id": "de7ac73c4b5021f1",
        "type": "OpcUa-Item",
        "z": "e072a13164d32272",
        "item": "PersistentVars.Minutes: ns=2;s=PersistentVars.Minutes",
        "datatype": "UInt32",
        "value": "",
        "name": "OPC2",
        "x": 270,
        "y": 300,
        "wires": [
            [
                "f772969f9b56c808"
            ]
        ]
    },
    {
        "id": "310308c9cd61bdf2",
        "type": "OpcUa-Item",
        "z": "e072a13164d32272",
        "item": "PersistentVars.Hours: ns=2;s=PersistentVars.Hours",
        "datatype": "UInt32",
        "value": "",
        "name": "OPC3",
        "x": 270,
        "y": 360,
        "wires": [
            [
                "f772969f9b56c808"
            ]
        ]
    },
    {
        "id": "26e0991f16c80806",
        "type": "inject",
        "z": "e072a13164d32272",
        "name": "",
        "props": [
            {
                "p": "topic",
                "vt": "str"
            }
        ],
        "repeat": "10",
        "crontab": "",
        "once": false,
        "onceDelay": "10",
        "topic": "",
        "x": 90,
        "y": 300,
        "wires": [
            [
                "1eb7b92207767668",
                "310308c9cd61bdf2",
                "de7ac73c4b5021f1"
            ]
        ]
    },
    {
        "id": "f469b7079c7c2d9a",
        "type": "switch",
        "z": "e072a13164d32272",
        "name": "",
        "property": "browseName",
        "propertyType": "msg",
        "rules": [
            {
                "t": "cont",
                "v": "OPC1",
                "vt": "str"
            },
            {
                "t": "cont",
                "v": "OPC2",
                "vt": "str"
            },
            {
                "t": "cont",
                "v": "OPC3",
                "vt": "str"
            }
        ],
        "checkall": "false",
        "repair": false,
        "outputs": 3,
        "x": 630,
        "y": 300,
        "wires": [
            [
                "bcb00b7938665c1d",
                "5b1c84e5049f5606",
                "02f752b4aa3d7243",
                "80ed8c3cabb27555"
            ],
            [
                "2d92449439e3dd97",
                "e67a0330f5ad1248",
                "7eb859783b73588c",
                "80ed8c3cabb27555"
            ],
            [
                "94de1e01392db6f3",
                "b405cec568d0e11e",
                "42e9e12116d6f7c7",
                "80ed8c3cabb27555"
            ]
        ]
    },
    {
        "id": "80ed8c3cabb27555",
        "type": "function",
        "z": "e072a13164d32272",
        "name": "INSERT statement",
        "func": "if (msg.browseName == 'OPC1') {\n    Seconds = msg.payload\n} else if (msg.browseName == 'OPC2') {\n    Minutes = msg.payload\n} else if (msg.browseName == 'OPC3') {\n    Hours = msg.payload\n}\n\nmsg.topic = \"INSERT INTO 'VS2' ('Val1, Val2, Val3') VALUES ( \"+ Hours +\", \"+ Minutes +\", \" + Seconds +\")\"; \nreturn msg;\n",
        "outputs": 1,
        "noerr": 0,
        "initialize": "\n",
        "finalize": "",
        "libs": [],
        "x": 430,
        "y": 640,
        "wires": [
            [
                "dc66cec0c8dea93e",
                "0191e60408c8da17"
            ]
        ]
    },
    {
        "id": "02f752b4aa3d7243",
        "type": "debug",
        "z": "e072a13164d32272",
        "d": true,
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 910,
        "y": 220,
        "wires": []
    },
    {
        "id": "7eb859783b73588c",
        "type": "debug",
        "z": "e072a13164d32272",
        "d": true,
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 910,
        "y": 400,
        "wires": []
    },
    {
        "id": "42e9e12116d6f7c7",
        "type": "debug",
        "z": "e072a13164d32272",
        "d": true,
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 910,
        "y": 560,
        "wires": []
    },
    {
        "id": "dc66cec0c8dea93e",
        "type": "MSSQL",
        "z": "e072a13164d32272",
        "mssqlCN": "aa9808ada67af497",
        "name": "MSSQL",
        "query": "",
        "outField": "payload10",
        "x": 680,
        "y": 700,
        "wires": [
            []
        ]
    },
    {
        "id": "e0da43123b30a3ac",
        "type": "OpcUa-Endpoint",
        "endpoint": "opc.tcp://192.168.84.20:4840",
        "secpol": "None",
        "secmode": "None",
        "none": false,
        "login": false,
        "usercert": false,
        "usercertificate": "",
        "userprivatekey": ""
    },
    {
        "id": "932a107191ea39cc",
        "type": "ui_group",
        "name": "Default",
        "tab": "317c384db903f671",
        "order": 1,
        "disp": true,
        "width": "6",
        "collapse": false,
        "className": ""
    },
    {
        "id": "d89550f79297ced2",
        "type": "ui_group",
        "name": "Data",
        "tab": "317c384db903f671",
        "order": 2,
        "disp": true,
        "width": "6",
        "collapse": false,
        "className": ""
    },
    {
        "id": "aa9808ada67af497",
        "type": "MSSQL-CN",
        "name": "Mandals",
        "server": "fem-prodsq01.database.windows.net",
        "encyption": true,
        "database": "prod-sql01"
    },
    {
        "id": "317c384db903f671",
        "type": "ui_tab",
        "name": "Home",
        "icon": "dashboard",
        "disabled": false,
        "hidden": false
    }
]
2

error 2

3

In order to make code readable and usable it is necessary to surround your code with three backticks (also known as a left quote or backquote ```)

``` 
   code goes here 
```

You can edit and correct your post by clicking the pencil :pencil2: icon.

See this post for more details - How to share code or flow json

1 Like
4

As you can see from the image, that is NOT valid SQL.

Also, I would strongly recommend using parameters to both simplify this and avoid SQL Injection

Here is a demo I did on another thread: Dashboard and MS SQL database - number and text input nodes - #2 by Steve-Mcl

1 Like
5

Thanks for quick reply.
How do I do this when I have 3 msg.payloads thats not a part of the same object?

6

See this article in the cookbook for an example of how to join messages into one object.

7

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