# Write OPC UA Variables to MySQL
**URL:** https://discourse.nodered.org/t/write-opc-ua-variables-to-mysql/25820
**Category:** General
**Created:** [30 April 2020 14:49 UTC](https://discourse.nodered.org/t/write-opc-ua-variables-to-mysql/25820 "2020-04-30T14:49:30Z")
**Posts on this page:** 12
**Page:** 1
### Author:  [@jrlion](https://discourse.nodered.org/u/jrlion)
#### Post date: [30 April 2020 14:49 UTC](https://discourse.nodered.org/t/write-opc-ua-variables-to-mysql/25820/1 "2020-04-30T14:49:31Z")
Hello Forum,
I'm currently doing a project on reading out OPC UA Signals and writing them into a MySQL Database. There have been already a lot of threads helping me with writing a single variable into MySQL but right now I'm stuck with writing several variables into the db. I'm quite sure this is due to my lack of Java and errors in the INSERT statement within the function-node. The code is as follows:
```auto
[
{
"id": "f5e7bec3.0a4108",
"type": "tab",
"label": "Flow 4",
"disabled": false,
"info": ""
},
{
"id": "4387a3dc.f76fb4",
"type": "OpcUa-Client",
"z": "f5e7bec3.0a4108",
"endpoint": "6dfb4ae1.a93eec",
"action": "read",
"deadbandtype": "a",
"deadbandvalue": 1,
"time": 10,
"timeUnit": "s",
"certificate": "n",
"localfile": "",
"name": "OPC UA Client",
"x": 580,
"y": 260,
"wires": [
[
"70a999d8.9cc8c",
"f969b97b.55f9d"
]
]
},
{
"id": "70a999d8.9cc8c",
"type": "debug",
"z": "f5e7bec3.0a4108",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"x": 810,
"y": 260,
"wires": []
},
{
"id": "1ea56b48.61e43d",
"type": "mysql",
"z": "f5e7bec3.0a4108",
"mydb": "ae1976a4.2714e",
"name": "trialdb",
"x": 1230,
"y": 360,
"wires": [
[]
]
},
{
"id": "be93f795.04c478",
"type": "function",
"z": "f5e7bec3.0a4108",
"name": "INSERT statement",
"func": "V1_Vdc_smooth_r0026 = msg.payload.V1_Vdc_smooth_r0026\nV1_Mot_temp_r0035 = msg.payload.V1_Mot_temp_r0035\nmsg.topic = \"INSERT INTO trialtab (`V1_Vdc_smooth_r0026`,`V1_Mot_temp_r0035`) VALUES (\"+V1_Vdc_smooth_r0026+\",\"+V1_Mot_temp_r0035+\")\";\nreturn msg;\n\n",
"outputs": 1,
"noerr": 0,
"x": 1010,
"y": 360,
"wires": [
[
"1ea56b48.61e43d",
"675dec37.9e8fd4"
]
]
},
{
"id": "7bbfb622.3d5aa8",
"type": "OpcUa-Client",
"z": "f5e7bec3.0a4108",
"endpoint": "6dfb4ae1.a93eec",
"action": "read",
"deadbandtype": "a",
"deadbandvalue": 1,
"time": 10,
"timeUnit": "s",
"certificate": "n",
"localfile": "",
"name": "OPC UA Client",
"x": 580,
"y": 460,
"wires": [
[
"87b96419.b8496",
"f969b97b.55f9d"
]
]
},
{
"id": "87b96419.b8496",
"type": "debug",
"z": "f5e7bec3.0a4108",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"x": 810,
"y": 460,
"wires": []
},
{
"id": "f969b97b.55f9d",
"type": "join",
"z": "f5e7bec3.0a4108",
"name": "",
"mode": "custom",
"build": "object",
"property": "payload",
"propertyType": "msg",
"key": "topic",
"joiner": "\\n",
"joinerType": "str",
"accumulate": false,
"timeout": "",
"count": "2",
"reduceRight": false,
"reduceExp": "",
"reduceInit": "",
"reduceInitType": "",
"reduceFixup": "",
"x": 810,
"y": 360,
"wires": [
[
"be93f795.04c478"
]
]
},
{
"id": "6f36a762.a08448",
"type": "OpcUa-Item",
"z": "f5e7bec3.0a4108",
"item": "ns=2;s=840D.Drive1.V1_Vdc_smooth_r0026",
"datatype": "Float",
"value": "",
"name": "V1_Vdc_smooth_r0026",
"x": 350,
"y": 260,
"wires": [
[
"4387a3dc.f76fb4"
]
]
},
{
"id": "32821747.c54b8",
"type": "OpcUa-Item",
"z": "f5e7bec3.0a4108",
"item": "ns=2;s=840D.Drive1.V1_Mot_temp_r0035",
"datatype": "Float",
"value": "",
"name": "V1_Mot_temp_r0035",
"x": 340,
"y": 460,
"wires": [
[
"7bbfb622.3d5aa8"
]
]
},
{
"id": "675dec37.9e8fd4",
"type": "debug",
"z": "f5e7bec3.0a4108",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"x": 1110,
"y": 260,
"wires": []
},
{
"id": "e0f0e841.1db82",
"type": "inject",
"z": "f5e7bec3.0a4108",
"name": "",
"topic": "",
"payload": "",
"payloadType": "date",
"repeat": "60",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"x": 130,
"y": 360,
"wires": [
[
"6f36a762.a08448",
"32821747.c54b8"
]
]
},
{
"id": "6dfb4ae1.a93eec",
"type": "OpcUa-Endpoint",
"z": "",
"endpoint": "opc.tcp://192.168.1.3:4840",
"secpol": "None",
"secmode": "NONE",
"login": false
},
{
"id": "ae1976a4.2714e",
"type": "MySQLdatabase",
"z": "",
"name": "",
"host": "127.0.0.1",
"port": "3306",
"db": "trialdb",
"tz": ""
}
]
```
I receive the following error:
I already tried several ways of suggested code in other threads but I couldn't manage to solve the error
Thank you for your support!
kr
jrlion
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [30 April 2020 16:53 UTC](https://discourse.nodered.org/t/write-opc-ua-variables-to-mysql/25820/3 "2020-04-30T16:53:26Z")
I can't look at your flow at the moment, but is one of the debug outputs showing the intpu to the function. It appears that the payload does not contain the fields you expect. I see there is a join node, have you configured it to key/value mode?
---
### Author:  [@zenofmud](https://discourse.nodered.org/u/zenofmud)
#### Post date: [30 April 2020 18:10 UTC](https://discourse.nodered.org/t/write-opc-ua-variables-to-mysql/25820/4 "2020-04-30T18:10:26Z")
While yu can use a function node to build the query, I find using a template node is mch cleaner. Here is an example
---
### Author:  [@jrlion](https://discourse.nodered.org/u/jrlion)
#### Post date: [1 May 2020 11:02 UTC](https://discourse.nodered.org/t/write-opc-ua-variables-to-mysql/25820/5 "2020-05-01T11:02:31Z")
Thank you for the fast reply.
The join node is set to key/value-object, yes.
I will add a debug node of the function inputs on monday when my test rig with the OPC UA Server is running again and post a picture of its payload.
---
### Author:  [@jrlion](https://discourse.nodered.org/u/jrlion)
#### Post date: [1 May 2020 11:15 UTC](https://discourse.nodered.org/t/write-opc-ua-variables-to-mysql/25820/6 "2020-05-01T11:15:02Z")
Thank you for the hint, I will give it a try on monday when the OPC UA Server of my test rig is running again!
---
### Author:  [@jrlion](https://discourse.nodered.org/u/jrlion)
#### Post date: [7 May 2020 13:58 UTC](https://discourse.nodered.org/t/write-opc-ua-variables-to-mysql/25820/7 "2020-05-07T13:58:40Z")
This is my current setup, delivering the added error message as output of the join node:
The json is still the same as uploaded above.
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [7 May 2020 14:02 UTC](https://discourse.nodered.org/t/write-opc-ua-variables-to-mysql/25820/8 "2020-05-07T14:02:28Z")
put a debug after "INSERT Statement" node - cant help without seeing the SQL
---
### Author:  [@jrlion](https://discourse.nodered.org/u/jrlion)
#### Post date: [7 May 2020 14:03 UTC](https://discourse.nodered.org/t/write-opc-ua-variables-to-mysql/25820/9 "2020-05-07T14:03:02Z")
This is what the code of my newly created template looks like:
Unfortunately it delivers the following error:
I don't see exactly where there might be an Error in line 6
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [7 May 2020 14:05 UTC](https://discourse.nodered.org/t/write-opc-ua-variables-to-mysql/25820/10 "2020-05-07T14:05:29Z")
> [@jrlion](#):
>
> I don't see exactly where there might be an Error in line 6
because you are not looking at the SQL it tried to execute. you are looking at the data from BEFORE the "INSERT Statement" node
Here is a clue - youre attempting to put `payload.V1_vcd_smooth_r0026` into the SQL but that isnt a property in the msg.
Use the "copy" button from the debug output to get EXACT path to property.
As I said...
> [@Steve-Mcl](#):
>
> put a debug after "INSERT Statement" node
---
### Author:  [@jrlion](https://discourse.nodered.org/u/jrlion)
#### Post date: [8 May 2020 11:32 UTC](https://discourse.nodered.org/t/write-opc-ua-variables-to-mysql/25820/12 "2020-05-08T11:32:47Z")
Steve, I'm very thankful for this hint!
Here is what I changed:
it now works perfectly fine 🙌
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [8 May 2020 11:36 UTC](https://discourse.nodered.org/t/write-opc-ua-variables-to-mysql/25820/13 "2020-05-08T11:36:48Z")
Yeah, I could have just told you - but where is the fun in that?
Glad its working & you learned something too - win win 👍
If you wanna learn something else here is how I format strings like SQL...
```auto
msg.topic = `INSERT INTO tbl (col1,col2) VALUES(${num1},'${str1}')`;
```
Its called [template literals](https://medium.com/javascript-in-plain-english/es6-template-literals-5ffe0e7a692a)
---
### Author:  [@system](https://discourse.nodered.org/u/system)
#### Post date: [22 May 2020 11:37 UTC](https://discourse.nodered.org/t/write-opc-ua-variables-to-mysql/25820/14 "2020-05-22T11:37:24Z")
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.