Dear Friends,
we are trying to connect PLC with MySQL Database.
we could able to connect PLC and read the current value of the memory address.
To read each tags, using individual nodes but requirement is to introduce function node between Read node and MySQL and write the insert command on the same.
Please help.
Kindly refer the following Image with Node and Table structure
messages are async and NEVER arrive at the same time. So your function will run 5 (different times). This is key to understanding node-red.
What you can do is use a join node to group the 5 reads into one key/val object.
However, your design is inefficient.
Multiple reads from PLC can result in inconsistent data and is 5x slower than reading 1 LOT of data in one go.
If you decide to do this the "better way" my suggestion is to read all values in one go & use something like the buffer-parser node to arrange the collected array of data into nice typed values, ready for insert into the database.
In order to get all the data into the function node at the same time, you need to use a join node. I suggest you read the documentation "Working with messages" and pay attention to the section on "Message sequences".
After that, if you still have questions, please ask them and show us what you have tried.
Can you read all of the data in one fetch from the plc? If so do that, then you will have it all in one message.
Thanks a lot. Let me try with JOIN Node
Yes. I can read all tags in one message.
Dear Steve,
Thanks for your swift reply and suggestion.
I shall try with JOIN node.
Regarding consistent reading and storing the data to table, will it be ok if we use different tables ? or any other database you suggest ?
Thanks again
Depends on the driver. I didn't write this one but if I were to guess, no, reading from different PLC areas is possibly achieved by separate transmissions (therefore not consistent). But I am speculating based on experience.
To pretty much guarantee consistency of data you should aim for contiguous data memory address. Even if that means moving the various values in the PLC into another contiguous "collection" area.
Edit...
After a stretch and a coffee and a reread, I will try again 
...
If you read data in a consistent manner from the PLC, you can write it to 1 or 2 or X tables in 1 or 2 or X databases without issue. Basically, once the data is captured, PLC scan is no longer a consideration.
Thanks steve.
If you can read them all at once then do that, and then you don't need to use the Join node to put them back together. That will be more efficient both in the PLC and in node-red.
Dear Steve,
Regarding Buffer-Parser node, i would like to have some document if any (Step-by-step) configuration. As per my query, data's are coming from PLC node and that has to go to MySQL with insert command and how can we use parser Buffer-node to fulfill the requirement?
-
Have you read the read.me?
-
Have you viewed the built in help for the node?
-
Have you imported any of the examples built in (press ctrl+i, chose examples, then expand buffer-parser?
I am willing to help you get going if you provide the following...
-
a copy of your PLC data output (attach a debug node set to output the "complete message") and use the
copy buttonthat appears when you hover over the debug message in the sidebar - then paste the complete message into a reply
-
what you expect to get from this data e.g...
- bytes 0,1,2,3 are a 32bit value and should be 12345
- bytes 4,5 are a signed integer and should contain -100
- byte 12 contains 8 status bits (bit0: ready, bit1: error, bit15:disabled)
- bytes 20,21,22,23 are a 32 bit float and should be 512.623001
Dear Steve,
Thanks for your reply.
Enclosed Flow for your reference. I had gone through the read me file and communication with PLC is through TCP/IP.
I am trying to explain my requirement in this flow which has
- PLC node sending the data to function node.
- SQL Insert query written in function node which is again connected to MySQL Node
[{"id":"1f9c7e1a.6fad92","type":"tab","label":"Flow 3","disabled":false,"info":""},{"id":"345d7424.01d28c","type":"s7 in","z":"1f9c7e1a.6fad92","endpoint":"43b13a21.e1edd4","mode":"all","variable":"DB1,WORD0","diff":true,"name":"PLC Tags","x":280,"y":200,"wires":[["46e8f59a.125eec"]]},{"id":"46e8f59a.125eec","type":"buffer-parser","z":"1f9c7e1a.6fad92","name":"","data":"payload","dataType":"msg","specification":"spec","specificationType":"ui","items":[{"type":"bigint64be","name":"DB1,WORD0","offset":0,"length":1,"offsetbit":0,"scale":"1","mask":""},{"type":"bigint64be","name":"DB1,WORD2","offset":8,"length":1,"offsetbit":0,"scale":"1","mask":""},{"type":"bigint64be","name":"DB1,WORD6","offset":16,"length":1,"offsetbit":0,"scale":"1","mask":""},{"type":"bigint64be","name":"item4DB1,WORD10","offset":24,"length":1,"offsetbit":0,"scale":"1","mask":""},{"type":"bigint64be","name":"DB1,WORD12","offset":32,"length":1,"offsetbit":0,"scale":"1","mask":""},{"type":"bigint64be","name":"DB1,WORD16","offset":40,"length":1,"offsetbit":0,"scale":"1","mask":""}],"swap1":"","swap2":"","swap3":"","swap1Type":"swap","swap2Type":"swap","swap3Type":"swap","msgProperty":"payload","msgPropertyType":"str","resultType":"value","resultTypeType":"output","multipleResult":true,"fanOutMultipleResult":true,"setTopic":true,"outputs":6,"x":470,"y":200,"wires":[["f3bd0fc8.e6873"],["f3bd0fc8.e6873"],["f3bd0fc8.e6873"],["f3bd0fc8.e6873"],["f3bd0fc8.e6873"],["f3bd0fc8.e6873"]]},{"id":"f3bd0fc8.e6873","type":"function","z":"1f9c7e1a.6fad92","name":"","func":"var num = msg.payload\nmsg.topic = \"insert into tbltAGS values (SLNo,'DB1.WORD0','DB1.WORD2','DB1.WORD4','DB1.WORD10','DB1.WORD12',\"+num+\",Now(),'Admin')\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":680,"y":200,"wires":[["6713d333.a54abc","e324ccef.318fe"]]},{"id":"6713d333.a54abc","type":"mysql","z":"1f9c7e1a.6fad92","mydb":"9721b43b.2be718","name":"","x":830,"y":200,"wires":[[]]},{"id":"e324ccef.318fe","type":"debug","z":"1f9c7e1a.6fad92","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":990,"y":200,"wires":[]},{"id":"43b13a21.e1edd4","type":"s7 endpoint","transport":"iso-on-tcp","address":"192.168.43.2","port":"102","rack":"0","slot":"1","localtsaphi":"01","localtsaplo":"00","remotetsaphi":"01","remotetsaplo":"00","connmode":"rack-slot","adapter":"","busaddr":"2","cycletime":"1000","timeout":"2000","name":"","vartable":[{"addr":"DB1,WORD0","name":"DB1,WORD0"},{"addr":"DB1,WORD2","name":"DB1,WORD2"},{"addr":"DB1,WORD10","name":"DB1,WORD10"},{"addr":"DB1,WORD6","name":"DB1,WORD6"},{"addr":"DB1,WORD12","name":"DB1,WORD12"}]},{"id":"9721b43b.2be718","type":"MySQLdatabase","name":"","host":"127.0.0.1","port":"3306","db":"Plant","tz":"","charset":"UTF8"}]
You didnt include the data
You didnt include what types you expect
You didnt include what values to expect
I cannot help if you dont provide info...
First observation...
If you turn off "fan out" and you change this to key/val output, you get all values in one message...
now you can simply copy the path and use the value...
demo flow....
[{"id":"46e8f59a.125eec","type":"buffer-parser","z":"1f9c7e1a.6fad92","name":"","data":"payload","dataType":"msg","specification":"spec","specificationType":"ui","items":[{"type":"bigint64be","name":"DB1_WORD0","offset":0,"length":1,"offsetbit":0,"scale":"1","mask":""},{"type":"bigint64be","name":"DB1_WORD2","offset":8,"length":1,"offsetbit":0,"scale":"1","mask":""},{"type":"bigint64be","name":"DB1_WORD6","offset":16,"length":1,"offsetbit":0,"scale":"1","mask":""},{"type":"bigint64be","name":"DB1_WORD10","offset":24,"length":1,"offsetbit":0,"scale":"1","mask":""},{"type":"bigint64be","name":"DB1_WORD12","offset":32,"length":1,"offsetbit":0,"scale":"1","mask":""},{"type":"bigint64be","name":"DB1_WORD16","offset":40,"length":1,"offsetbit":0,"scale":"1","mask":""}],"swap1":"","swap2":"","swap3":"","swap1Type":"swap","swap2Type":"swap","swap3Type":"swap","msgProperty":"payload","msgPropertyType":"str","resultType":"keyvalue","resultTypeType":"output","multipleResult":false,"fanOutMultipleResult":false,"setTopic":true,"outputs":1,"x":1430,"y":120,"wires":[["f3bd0fc8.e6873","b56294ff.2dac58"]]},{"id":"f3bd0fc8.e6873","type":"function","z":"1f9c7e1a.6fad92","name":"","func":"\nvar SLNo = 1;\n\nmsg.topic = \"insert into tbltAGS values (?,?,?,?,?,?,?,Now(),'Admin')\";\nmsg.payload = [\n msg.payload.DB1_WORD0,\n msg.payload.DB1_WORD2,\n msg.payload.DB1_WORD6,\n msg.payload.DB1_WORD10,\n msg.payload.DB1_WORD12,\n msg.payload.DB1_WORD16,\n]\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1260,"y":180,"wires":[["d0aa03b8.33037"]]},{"id":"8cd48cf2.ba3ad","type":"inject","z":"1f9c7e1a.6fad92","name":"fake PLC data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[91,49,50,44,51,50,44,54,52,44,53,52,44,50,50,44,52,56,44,55,54,44,49,53,50,44,50,49,49,44,49,50,44,55,52,44,49,53,56,44,49,55,44,49,57,51,44,50,53,53,44,49,50,44,48,44,49,50,44,50,53,53,44,49,44,48,44,50,48,48,44,50,50,44,57,52,44,50,53,52,44,44,53,52,44,50,50,44,52,56,44,55,54,44,49,53,50,44,50,49,49,44,49,50,44,55,52,44,49,53,56,44,49,93]","payloadType":"bin","x":1270,"y":120,"wires":[["46e8f59a.125eec"]]},{"id":"b56294ff.2dac58","type":"debug","z":"1f9c7e1a.6fad92","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":1610,"y":120,"wires":[]},{"id":"d0aa03b8.33037","type":"debug","z":"1f9c7e1a.6fad92","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":1430,"y":180,"wires":[]}]
Other observations...
- You call your variables
WORDbut you have chosen big int 64bit values in buffer parser! - bigint64 values probably do not work with mySQL
- try not to put odd characters in names (e.g. DB1_WORD12 is better than DB1,WORD12)
Firstly, thank you so much for your effort on this. Here I would like to input my clarification.
Data Communication between PLC and RPI is through Ethernet (TCP/IP)
Your question:
You didn’t include the data
Ans: Yes. Data not included here because it was dummy nodes and its not configured for any communication. Just to show you, FLOW has been created.
You didn’t include what types you expect
Ans: Normally PLC Tag output we expect here and same needs to be added to the MySQL or any other RDBMS.
Sometime Tag Output will be BOOL (Bit), INT, DOUBLE INT(WORD), FLOAT DECEMIAL (10,5), STRING, DATETIME.
You didn’t include what values to expect
Ans:
| Data Type | Expected Value from PLC |
|---|---|
| BOOL | 0 or 1 |
| INT | XXXXXXXX |
| Double | XXXXXXXXXXXXXXX |
| FLOAT | XXX.XXXXXXXXXXX |
| STRING | “WORD10” |
| DATETIME | 2021.03.28 10:20:25 AM |
Your Suggestion:
Thanks for your suggestion. In this I need few changes.
Instead of fake PLC data (Inject Node), SIEMENS PLC Node (node-red-contrib-s7) needs to be configured. (Please refer the below FLOW)
[{"id":"ebf6c102.e1a38","type":"tab","label":"Flow 3","disabled":false,"info":""},{"id":"46e8f59a.125eec","type":"buffer-parser","z":"ebf6c102.e1a38","name":"","data":"payload","dataType":"msg","specification":"spec","specificationType":"ui","items":[{"type":"bigint64be","name":"DB1_WORD0","offset":0,"length":1,"offsetbit":0,"scale":"1","mask":""},{"type":"bigint64be","name":"DB1_WORD2","offset":8,"length":1,"offsetbit":0,"scale":"1","mask":""},{"type":"bigint64be","name":"DB1_WORD6","offset":16,"length":1,"offsetbit":0,"scale":"1","mask":""},{"type":"bigint64be","name":"DB1_WORD10","offset":24,"length":1,"offsetbit":0,"scale":"1","mask":""},{"type":"bigint64be","name":"DB1_WORD12","offset":32,"length":1,"offsetbit":0,"scale":"1","mask":""},{"type":"bigint64be","name":"DB1_WORD16","offset":40,"length":1,"offsetbit":0,"scale":"1","mask":""}],"swap1":"","swap2":"","swap3":"","swap1Type":"swap","swap2Type":"swap","swap3Type":"swap","msgProperty":"payload","msgPropertyType":"str","resultType":"keyvalue","resultTypeType":"output","multipleResult":false,"fanOutMultipleResult":false,"setTopic":true,"outputs":1,"x":450,"y":280,"wires":[["f3bd0fc8.e6873","b56294ff.2dac58"]]},{"id":"f3bd0fc8.e6873","type":"function","z":"ebf6c102.e1a38","name":"","func":"\nvar SLNo = 1;\n\nmsg.topic = \"insert into tbltAGS values (?,?,?,?,?,?,?,Now(),'Admin')\";\nmsg.payload = [\n msg.payload.DB1_WORD0,\n msg.payload.DB1_WORD2,\n msg.payload.DB1_WORD6,\n msg.payload.DB1_WORD10,\n msg.payload.DB1_WORD12,\n msg.payload.DB1_WORD16,\n]\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":280,"y":340,"wires":[["d0aa03b8.33037"]]},{"id":"b56294ff.2dac58","type":"debug","z":"ebf6c102.e1a38","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":630,"y":280,"wires":[]},{"id":"d0aa03b8.33037","type":"debug","z":"ebf6c102.e1a38","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"true","targetType":"full","statusVal":"payload","statusType":"auto","x":430,"y":340,"wires":[]},{"id":"2acc4b9c.2a2174","type":"s7 in","z":"ebf6c102.e1a38","endpoint":"d6429ddd.49b4f","mode":"all","variable":"","diff":true,"name":"PLC Data","x":280,"y":280,"wires":[["46e8f59a.125eec"]]},{"id":"d6429ddd.49b4f","type":"s7 endpoint","transport":"iso-on-tcp","address":"192.168.100.2","port":"102","rack":"0","slot":"1","localtsaphi":"01","localtsaplo":"00","remotetsaphi":"01","remotetsaplo":"00","connmode":"rack-slot","adapter":"","busaddr":"2","cycletime":"1000","timeout":"2000","name":"s7 in","vartable":[{"addr":"DB3,WORD0","name":"Word0"},{"addr":"DB3,WORD2","name":"Word2"},{"addr":"DB3,WORD4","name":"Word4"},{"addr":"DB3,WORD6","name":"Word6"},{"addr":"DB3,WORD8","name":"Word8"}]}]
This node consists of All the Tags coming from PLC with the scan rate of X seconds hence each tag will provide present value of the memory address.
As explained above, Tags needs to be configured here and these tags provide the data based on the scan cycle. (May be in 1 sec, 5 sec, 1 Min etc…)
For data logging, Database and Tables are configured in MySQL within RPI.
Your demo flow is excellent however please add the S7 node instead fake PLC data node and show the configuration refer to the above explanations.
Please note:
-
Added the demo flow node to my screen and getting following error.
axios error: Error: socket hang up -
For S7 Contrib Data presentation Please refer the following link
node-red-contrib-s7 (node) - Node-RED
If you are interested to see the demo through remote session, I will be available to showcase.
Please let me know
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
