Read values from S7 component to mysql query

General
1

Hi, I am new at Node-Red.
I make a project but, I can read values for write to mysql.
Every thing is working only I can't find correct way.

Now: I am using S7 component to read data from PLC, I can watch on dashboard,
how can I get data to mysql query.

S7 read data = DB1,Real4.

//I try to write qurey;
var str1 = msg.payload;
str1 = str1[0]['DB1,Real4'];
msg.payload = str1;
msg.topic = "INSERT into Temps(Temp1) VALUES ('str1')"; //
return msg;

Now Node-red giveing error =
[error] [function:SQL Query] TyperError: Cannot read property 'DB1,Real4' of undefined

2

and what does an example of msg.payload look like?

3

msg.payload = str1;

4

if msg.payload really does equal str1
then it isn't an array so your line
str1 = str1[0]['DB1,Real4']; makes no sense

5

how, can I solve?

6

I'd start by reading the docs especially the "working with messages"

When you have done that, then add debug nodes to your flow to understand what message you are actually sending through your flow, as it doesn't look to be what you think it is.

7

[
{
"id": "e5a6eef7.11771",
"type": "tab",
"label": "Flow 1",
"disabled": false,
"info": ""
},
{
"id": "1a3ac55b.d95c2b",
"type": "ui_text_input",
"z": "e5a6eef7.11771",
"name": "",
"label": "Havuz 3 Sıcaklık",
"tooltip": "",
"group": "4d86b472.71234c",
"order": 3,
"width": 0,
"height": 0,
"passthru": true,
"mode": "number",
"delay": 300,
"topic": "",
"x": 410,
"y": 120,
"wires": [
[
"e6ced9bd.45c368"
]
]
},
{
"id": "fcca8334.20aa3",
"type": "ui_text_input",
"z": "e5a6eef7.11771",
"name": "",
"label": "Havuz 1 Sıcaklık",
"tooltip": "",
"group": "4d86b472.71234c",
"order": 1,
"width": 0,
"height": 0,
"passthru": true,
"mode": "number",
"delay": 300,
"topic": "",
"x": 410,
"y": 40,
"wires": [
[
"19eaee6e.2b5192"
]
]
},
{
"id": "e83cde1d.b1998",
"type": "ui_text_input",
"z": "e5a6eef7.11771",
"name": "",
"label": "Havuz 2 Sıcaklık",
"tooltip": "",
"group": "4d86b472.71234c",
"order": 2,
"width": 0,
"height": 0,
"passthru": true,
"mode": "number",
"delay": 300,
"topic": "",
"x": 410,
"y": 80,
"wires": [
[
"21b262d4.1348ee"
]
]
},
{
"id": "e34c47ef.90d4b8",
"type": "s7 in",
"z": "e5a6eef7.11771",
"endpoint": "cbbd9acd.931358",
"mode": "single",
"variable": "Sicaklık 1",
"diff": false,
"name": "",
"x": 100,
"y": 40,
"wires": [
[
"fcca8334.20aa3",
"81bfdf.a5c8602"
]
]
},
{
"id": "db61f3a0.477db",
"type": "s7 in",
"z": "e5a6eef7.11771",
"endpoint": "cbbd9acd.931358",
"mode": "single",
"variable": "Sicaklık 2",
"diff": false,
"name": "",
"x": 100,
"y": 80,
"wires": [
[
"e83cde1d.b1998"
]
]
},
{
"id": "74177299.019e6c",
"type": "s7 in",
"z": "e5a6eef7.11771",
"endpoint": "cbbd9acd.931358",
"mode": "single",
"variable": "Sicaklık 3",
"diff": false,
"name": "",
"x": 100,
"y": 120,
"wires": [
[
"1a3ac55b.d95c2b"
]
]
},
{
"id": "9c6cea66.980d08",
"type": "s7 out",
"z": "e5a6eef7.11771",
"endpoint": "cbbd9acd.931358",
"variable": "Ust Pozisyon",
"name": "",
"x": 650,
"y": 580,
"wires":
},
{
"id": "ae4f2e8c.bb35a",
"type": "ui_switch",
"z": "e5a6eef7.11771",
"name": "",
"label": "Üst Pozisyon",
"tooltip": "",
"group": "4d86b472.71234c",
"order": 5,
"width": "0",
"height": "0",
"passthru": true,
"decouple": "false",
"topic": "",
"style": "",
"onvalue": "true",
"onvalueType": "bool",
"onicon": "",
"oncolor": "",
"offvalue": "false",
"offvalueType": "bool",
"officon": "",
"offcolor": "",
"x": 330,
"y": 580,
"wires": [
[
"9c6cea66.980d08"
]
]
},
{
"id": "7e8ab299.d453dc",
"type": "ui_switch",
"z": "e5a6eef7.11771",
"name": "",
"label": "Alt Pozisyon",
"tooltip": "",
"group": "4d86b472.71234c",
"order": 6,
"width": "0",
"height": "0",
"passthru": true,
"decouple": "false",
"topic": "",
"style": "",
"onvalue": "true",
"onvalueType": "bool",
"onicon": "",
"oncolor": "",
"offvalue": "false",
"offvalueType": "bool",
"officon": "",
"offcolor": "",
"x": 330,
"y": 620,
"wires": [
[
"51c2d502.bc7ffc"
]
]
},
{
"id": "51c2d502.bc7ffc",
"type": "s7 out",
"z": "e5a6eef7.11771",
"endpoint": "cbbd9acd.931358",
"variable": "Alt Pozisyon",
"name": "",
"x": 650,
"y": 620,
"wires":
},
{
"id": "19eaee6e.2b5192",
"type": "s7 out",
"z": "e5a6eef7.11771",
"endpoint": "cbbd9acd.931358",
"variable": "Sicaklık 1",
"name": "",
"x": 680,
"y": 40,
"wires":
},
{
"id": "21b262d4.1348ee",
"type": "s7 out",
"z": "e5a6eef7.11771",
"endpoint": "cbbd9acd.931358",
"variable": "Sicaklık 2",
"name": "",
"x": 690,
"y": 80,
"wires":
},
{
"id": "e6ced9bd.45c368",
"type": "s7 out",
"z": "e5a6eef7.11771",
"endpoint": "cbbd9acd.931358",
"variable": "Sicaklık 3",
"name": "",
"x": 680,
"y": 120,
"wires":
},
{
"id": "54010d27.a7aa84",
"type": "s7 in",
"z": "e5a6eef7.11771",
"endpoint": "cbbd9acd.931358",
"mode": "single",
"variable": "Sicaklık 1",
"diff": false,
"name": "",
"x": 420,
"y": 460,
"wires": [
[
"c23bcae2.265248"
]
]
},
{
"id": "c23bcae2.265248",
"type": "ui_chart",
"z": "e5a6eef7.11771",
"name": "",
"group": "4d86b472.71234c",
"order": 9,
"width": 0,
"height": 0,
"label": "Sıcaklık1/Sıcaklık2",
"chartType": "pie",
"legend": "true",
"xformat": "HH:mm:ss",
"interpolate": "linear",
"nodata": "",
"dot": true,
"ymin": "",
"ymax": "",
"removeOlder": "2",
"removeOlderPoints": "",
"removeOlderUnit": "1",
"cutout": "50",
"useOneColor": false,
"colors": [
"#0080ff",
"#ff0000",
"#ff7f0e",
"#2ca02c",
"#98df8a",
"#d62728",
"#ff9896",
"#9467bd",
"#c5b0d5"
],
"useOldStyle": true,
"outputs": 1,
"x": 650,
"y": 480,
"wires": [

]
},
{
"id": "3fa40b11.416ba4",
"type": "s7 in",
"z": "e5a6eef7.11771",
"endpoint": "cbbd9acd.931358",
"mode": "single",
"variable": "Ust Pozisyon",
"diff": false,
"name": "",
"x": 140,
"y": 580,
"wires": [
[
"ae4f2e8c.bb35a"
]
]
},
{
"id": "d4f41d3d.49699",
"type": "s7 in",
"z": "e5a6eef7.11771",
"endpoint": "cbbd9acd.931358",
"mode": "single",
"variable": "Alt Pozisyon",
"diff": false,
"name": "",
"x": 140,
"y": 640,
"wires": [
[
"7e8ab299.d453dc"
]
]
},
{
"id": "35ca352c.f2927a",
"type": "s7 in",
"z": "e5a6eef7.11771",
"endpoint": "cbbd9acd.931358",
"mode": "single",
"variable": "Sicaklık 3",
"diff": false,
"name": "",
"x": 420,
"y": 500,
"wires": [
[
"c23bcae2.265248"
]
]
},
{
"id": "3288ffab.4b723",
"type": "mysql",
"z": "e5a6eef7.11771",
"mydb": "884b10b0.746d7",
"name": "",
"x": 640,
"y": 360,
"wires": [
[
"adeb306d.754ed"
]
]
},
{
"id": "adeb306d.754ed",
"type": "debug",
"z": "e5a6eef7.11771",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"x": 650,
"y": 400,
"wires":
},
{
"id": "45b211b6.5d71c",
"type": "function",
"z": "e5a6eef7.11771",
"name": "SQL Query",
"func": "msg.topic = "INSERT into Temps(Temp1) VALUES ('"+srt0+"')";\nreturn msg;",
"outputs": 1,
"noerr": 0,
"x": 650,
"y": 320,
"wires": [
[
"3288ffab.4b723"
]
]
},
{
"id": "34f7799f.c8efa6",
"type": "inject",
"z": "e5a6eef7.11771",
"name": "",
"topic": "",
"payload": "",
"payloadType": "date",
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"x": 360,
"y": 280,
"wires": [
[
"45b211b6.5d71c"
]
]
},
{
"id": "81bfdf.a5c8602",
"type": "change",
"z": "e5a6eef7.11771",
"name": "",
"rules": [
{
"t": "set",
"p": "deneme",
"pt": "global",
"to": "srt0",
"tot": "str"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 630,
"y": 260,
"wires": [
[
"45b211b6.5d71c"
]
]
},
{
"id": "4d86b472.71234c",
"type": "ui_group",
"z": "",
"name": "HAT-1 Kontrol",
"tab": "7cfecf52.cfe63",
"order": 1,
"disp": true,
"width": "6",
"collapse": true
},
{
"id": "cbbd9acd.931358",
"type": "s7 endpoint",
"z": "",
"transport": "iso-on-tcp",
"address": "192.168.0.1",
"port": "102",
"rack": "0",
"slot": "1",
"localtsaphi": "01",
"localtsaplo": "00",
"remotetsaphi": "01",
"remotetsaplo": "00",
"connmode": "rack-slot",
"adapterauto": true,
"adapterport": "",
"busaddr": "",
"adapteraddr": "0",
"cycletime": "2000",
"timeout": "1500",
"verbose": "off",
"name": "",
"vartable": [
{
"addr": "M223.0",
"name": "Ust Pozisyon"
},
{
"addr": "DB1,Real4",
"name": "Sicaklık 1"
},
{
"addr": "DB1,Real12",
"name": "Sicaklık 2"
},
{
"addr": "DB1,Real20",
"name": "Sicaklık 3"
},
{
"addr": "M223.2",
"name": "Alt Pozisyon"
}
]
},
{
"id": "884b10b0.746d7",
"type": "MySQLdatabase",
"z": "",
"host": "192.168.0.250",
"port": "3306",
"db": "TrOtom",
"tz": ""
},
{
"id": "7cfecf52.cfe63",
"type": "ui_tab",
"z": "",
"name": "SCADA",
"icon": "dashboard",
"disabled": false,
"hidden": false
}
]

8

Please export your flow again, read this post and edit your post.

First determine the debug output of your node.

This line:
msg.topic = "INSERT into Temps(Temp1) VALUES ('str1')"; //

str1 inserted as a string, not the data/object/array that you are looking for.

It should be something like

VALUES ('"+str1+"')"

9

yes I know, I already fix it, and I write my known methods but not working,
I'd export all flow to here.
Thanks for your help.

10

Node1
Node1.jpg1019×657 144 KB

I need to help an Urgent please.

split function not working,
I need to get all datas values and split as data1, data2, data3....
so, write by sql query to data rows.

Thank you.

11

First determine what the input is.

Next it states: m.split, which is not shown in your screenshots, where does m.split come from?

12

I am trying in function node, and try every thing wrote at internet from pages.
I have only one function node and 5 data input,
I need to sperate datas and write diffrent rows.

this is my trys;

let m = msg.payload
let s = m.split(';')
let out =[ ]
for(x=0;x<s.length;x++){
parts = s[ x].split(':')
out.push({payload:{[parts[0]]:parseFloat(parts[1])}})
}

return out

var myVars=msg.payload.split(";");
var result={};
myVars.forEach(function(v){
var parts=v.split(":");
result [parts[0]]=parts[1];
})
msg.payload=result;

var keys=Object.keys(msg.payload);
var msgs=keys.map(function(key){
return {topic: key , payload: msg.payload[key]};
})

var output=msg.payload[0].split(";");
var msg1={payload:(output[0])};
var msg2={payload:(ouput[1])};
var msg3={payload:(output[2])};

return [msg1,msg2,msg3];

//var outputMsgs = [ ];
//var words = msg.payload.split(" ");
// for (var w in words) {
// outputMsgs.push({payload:words[w]});
// }
// return [ outputMsgs ];

//doSomeAsyncWork(msg, function(result) {
// node.send({payload:result});
//});
//return;

//var temp = msg.payload.value[0];
//msg.topic = "INSERT into Temps(Temp1) VALUES ('"+temp+"')";
//return msg;

//var newMsg = msg.payload.split(',');
// temp1 = { payload: newMsg[0]};
//var temp2 = { payload: newMsg[1]};

//msg.topic = "INSERT into Temps(Temp1,Temp2) VALUES ('"+temp1+","+temp2+"')";
//return msg;

13

what is going into the function ? can you add a debug node to show us the msg.payload (and its type) going in ?

14

Node1%20(2)
Node1 (2).jpg1071×726 143 KB

Node2
Node2.jpg1031×806 131 KB

15

So you are receiving an object with several properties, one of which is a numeric value, so a simple string split isn't going to work. Maybe read this post https://nodered.org/docs/user-guide/messages then you can work out how to access the part(s) you want.