# OPC UA to SQL Problem

**URL:** <https://discourse.nodered.org/t/opc-ua-to-sql-problem/63550>\
**Category:** General\
**Tags:** database, function-node\
**Created:** [7 June 2022 07:56 UTC](https://discourse.nodered.org/t/opc-ua-to-sql-problem/63550 "2022-06-07T07:56:54Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![DiveDeepNorway](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/divedeepnorway/32/62748_2.png) [@DiveDeepNorway](https://discourse.nodered.org/u/DiveDeepNorway)\
**Post date:** [7 June 2022 07:56 UTC](https://discourse.nodered.org/t/opc-ua-to-sql-problem/63550/1 "2022-06-07T07:56:54Z")

</div>

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:

```auto
[
    {
        "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
    }
]

```

---

<div class="post-metadata">

**Author:** ![DiveDeepNorway](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/divedeepnorway/32/62748_2.png) [@DiveDeepNorway](https://discourse.nodered.org/u/DiveDeepNorway)\
**Post date:** [7 June 2022 07:57 UTC](https://discourse.nodered.org/t/opc-ua-to-sql-problem/63550/2 "2022-06-07T07:57:36Z")

</div>

![error 2](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/c/1cfab9cb1cbcc8064c678f9f944382b2f5253fdd.png)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [7 June 2022 07:57 UTC](https://discourse.nodered.org/t/opc-ua-to-sql-problem/63550/3 "2022-06-07T07:57:41Z")

</div>

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 `````)

````auto
``` 
   code goes here 
```

````

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

See this post for more details - [How to share code or flow json](https://discourse.nodered.org/t/how-to-share-code-or-flow-json/506)

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [7 June 2022 08:02 UTC](https://discourse.nodered.org/t/opc-ua-to-sql-problem/63550/4 "2022-06-07T08:02:24Z")

</div>

> [@DiveDeepNorway](#):
>
> ![error 2](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/c/1cfab9cb1cbcc8064c678f9f944382b2f5253fdd.png)

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](https://discourse.nodered.org/t/dashboard-and-ms-sql-database-number-and-text-input-nodes/55696/2)

---

<div class="post-metadata">

**Author:** ![DiveDeepNorway](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/divedeepnorway/32/62748_2.png) [@DiveDeepNorway](https://discourse.nodered.org/u/DiveDeepNorway)\
**Post date:** [7 June 2022 08:10 UTC](https://discourse.nodered.org/t/opc-ua-to-sql-problem/63550/5 "2022-06-07T08:10:39Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [7 June 2022 08:18 UTC](https://discourse.nodered.org/t/opc-ua-to-sql-problem/63550/6 "2022-06-07T08:18:55Z")

</div>

See [this article in the cookbook](https://cookbook.nodered.org/basic/join-streams) for an example of how to join messages into one object.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [6 August 2022 08:19 UTC](https://discourse.nodered.org/t/opc-ua-to-sql-problem/63550/7 "2022-08-06T08:19:40Z")

</div>

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