Write OPC UA Variables to MySQL

[jrlion]

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:

[
    {
        "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

[Colin]

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?

[zenofmud]

While yu can use a function node to build the query, I find using a template node is mch cleaner. Here is an example

[jrlion]

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.

[jrlion]

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!

[jrlion]

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.

[Steve-Mcl]

put a debug after "INSERT Statement" node - cant help without seeing the SQL

[jrlion]

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

[Steve-Mcl]

@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

[jrlion]

Steve, I'm very thankful for this hint!
Here is what I changed:

it now works perfectly fine

[Steve-Mcl]

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...

msg.topic = `INSERT INTO tbl (col1,col2) VALUES(${num1},'${str1}')`;

Its called template literals