Using payload from Modbus Flex Getter to query mySQL database on phpmyadmin

General
1

I am trying to use the payload coming in from a PLC (part number is a combination of numbers and text) through modbus flex getter and use the buffer parser to convert the hex to string.

However, using that payload to query does not return a value. If I use an inject with the actual string it returns the value- so I know the sql works.

Any thoughts?

/28/2021, 12:16:30 PMnode: 6cfe691a.485c68SELECT Pleats FROM pleatco WHERE Part_Number = 'PWW10' : msg.payload : array[0]

[ empty ]

5/28/2021, 12:17:27 PMnode: 6cfe691a.485c68SELECT Pleats FROM pleatco WHERE Part_Number = 'PWW10' : msg.payload : array[1]

array[1]

0: object

Pleats: 180

2

Can you capture the whole msg that goes into the buffer parser (use a debug node set to show complete message then click the copy value button that appears under you mouse cursor when you hover of the msg in the debug window).

Also, select the buffer parser node & the node that builds your SQL & export (ctrl+e) them.

3

Sure this is what I see when I debug Modbus flex getter that goes in the buffer parser. It says new users cannot upload files ;(

{"_msgid":"d1bf9110.d196d","payload":[22352,12631,48,0,0,0,0,0,0,0],"topic":"886f87b5.5a0bb8","messageId":"60b1288c36ee442146f90792","modbusRequest":{"unitid":1,"fc":3,"address":36866,"quantity":10,"emptyMsgOnFail":true,"keepMsgProperties":true,"messageId":"60b1288c36ee442146f90792"},"responseBuffer":{"data":[22352,12631,48,0,0,0,0,0,0,0],"buffer":[87,80,49,87,0,48,0,0,0,0,0,0,0,0,0,0,0,0,0,0]}}

[{"id":"89974332.ca228","type":"function","z":"9f77f1c1.87b7a","name":"","func":"msg.topic = \"SELECT `Pleats` FROM `pleatco` WHERE Part_Number = '\"+msg.payload+\"'\"\nreturn msg;\n\n\n\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":560,"y":300,"wires":[["59133cce.3422a4"]]},{"id":"76dcb548.47d34c","type":"function","z":"9f77f1c1.87b7a","name":"","func":"msg.payload = Buffer.from(msg.payload, 'hex').toString();\n//return msg;\n\n//msg.payload = msg.payload.toString('utf8');\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":900,"y":80,"wires":[["932ea846.4474c8","89974332.ca228"]]},{"id":"5b7f246e.777b8c","type":"buffer-parser","z":"9f77f1c1.87b7a","name":"","data":"payload","dataType":"msg","specification":"spec","specificationType":"ui","items":[{"type":"buffer","name":"item1","offset":0,"length":5,"offsetbit":0,"scale":"1","mask":""}],"swap1":"swap16","swap2":"swap16","swap3":"swap16","swap1Type":"swap","swap2Type":"swap","swap3Type":"swap","msgProperty":"payload","msgPropertyType":"str","resultType":"buffer","resultTypeType":"output","multipleResult":false,"fanOutMultipleResult":false,"setTopic":true,"outputs":1,"x":710,"y":120,"wires":[["76dcb548.47d34c"]]}]
4

You just copy the flow to clipboard then paste it into your reply.

Please note - in order to make code more readable and importable it is important to surround your code with three backticks
```
like this
```

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

5

Thanks Steve - I am very new and learning the code as well as usage! etc...

6

Does this look correct?
image

[{"id":"89974332.ca228","type":"function","z":"1bd2b382.4dd4f4","name":"make SQL query","func":"msg.topic = \"SELECT `Pleats` FROM `pleatco` WHERE Part_Number = '\"+msg.payload+\"'\"\nreturn msg;\n\n\n\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1082,"y":384,"wires":[["408b7445.0fa55c"]]},{"id":"1d11cbeb.371494","type":"inject","z":"1bd2b382.4dd4f4","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":714,"y":320,"wires":[["59e9f4ae.6f6acc"]]},{"id":"59e9f4ae.6f6acc","type":"function","z":"1bd2b382.4dd4f4","name":"simulate modbus","func":"return {\n    \"_msgid\":\"d1bf9110.d196d\",\n    \"payload\":[22352,12631,48,0,0,0,0,0,0,0],\n    \"topic\":\"886f87b5.5a0bb8\",\n    \"messageId\":\"60b1288c36ee442146f90792\",\n    \"modbusRequest\":{\"unitid\":1,\"fc\":3,\"address\":36866,\"quantity\":10,\"emptyMsgOnFail\":true,\"keepMsgProperties\":true,\"messageId\":\"60b1288c36ee442146f90792\"},\n    \"responseBuffer\":{\n        \"data\":[22352,12631,48,0,0,0,0,0,0,0],\n        \"buffer\":Buffer.from([87,80,49,87,0,48,0,0,0,0,0,0,0,0,0,0,0,0,0,0])\n    }\n}\n ","outputs":1,"noerr":0,"initialize":"","finalize":"","x":884,"y":320,"wires":[["acb8d7b.5c34d28"]]},{"id":"408b7445.0fa55c","type":"debug","z":"1bd2b382.4dd4f4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"topic","targetType":"msg","statusVal":"","statusType":"auto","x":1260,"y":384,"wires":[]},{"id":"acb8d7b.5c34d28","type":"buffer-parser","z":"1bd2b382.4dd4f4","name":"","data":"responseBuffer.buffer","dataType":"msg","specification":"spec","specificationType":"ui","items":[{"type":"hex","name":"item1","offset":0,"length":5,"offsetbit":0,"scale":"1","mask":""}],"swap1":"swap16","swap2":"","swap3":"","swap1Type":"swap","swap2Type":"swap","swap3Type":"swap","msgProperty":"payload","msgPropertyType":"str","resultType":"value","resultTypeType":"output","multipleResult":false,"fanOutMultipleResult":false,"setTopic":true,"outputs":1,"x":886,"y":384,"wires":[["89974332.ca228"]]}]
7

The Part_Number = PWW10. That's I have to use to query the SQL

8

ok, so set buffer parser to return string

image
image1111×185 15.9 KB

Demo flow...

[{"id":"89974332.ca228","type":"function","z":"1bd2b382.4dd4f4","name":"make SQL query","func":"msg.topic = \"SELECT `Pleats` FROM `pleatco` WHERE Part_Number = '\"+msg.payload+\"'\"\nreturn msg;\n\n\n\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":1082,"y":384,"wires":[["408b7445.0fa55c"]]},{"id":"1d11cbeb.371494","type":"inject","z":"1bd2b382.4dd4f4","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":714,"y":320,"wires":[["59e9f4ae.6f6acc"]]},{"id":"59e9f4ae.6f6acc","type":"function","z":"1bd2b382.4dd4f4","name":"simulate modbus","func":"return {\n    \"_msgid\":\"d1bf9110.d196d\",\n    \"payload\":[22352,12631,48,0,0,0,0,0,0,0],\n    \"topic\":\"886f87b5.5a0bb8\",\n    \"messageId\":\"60b1288c36ee442146f90792\",\n    \"modbusRequest\":{\"unitid\":1,\"fc\":3,\"address\":36866,\"quantity\":10,\"emptyMsgOnFail\":true,\"keepMsgProperties\":true,\"messageId\":\"60b1288c36ee442146f90792\"},\n    \"responseBuffer\":{\n        \"data\":[22352,12631,48,0,0,0,0,0,0,0],\n        \"buffer\":Buffer.from([87,80,49,87,0,48,0,0,0,0,0,0,0,0,0,0,0,0,0,0])\n    }\n}\n ","outputs":1,"noerr":0,"initialize":"","finalize":"","x":884,"y":320,"wires":[["acb8d7b.5c34d28"]]},{"id":"408b7445.0fa55c","type":"debug","z":"1bd2b382.4dd4f4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"topic","targetType":"msg","statusVal":"","statusType":"auto","x":1260,"y":384,"wires":[]},{"id":"acb8d7b.5c34d28","type":"buffer-parser","z":"1bd2b382.4dd4f4","name":"","data":"responseBuffer.buffer","dataType":"msg","specification":"spec","specificationType":"ui","items":[{"type":"ascii","name":"item1","offset":0,"length":5,"offsetbit":0,"scale":"1","mask":""}],"swap1":"swap16","swap2":"","swap3":"","swap1Type":"swap","swap2Type":"swap","swap3Type":"swap","msgProperty":"payload","msgPropertyType":"str","resultType":"value","resultTypeType":"output","multipleResult":false,"fanOutMultipleResult":false,"setTopic":true,"outputs":1,"x":886,"y":384,"wires":[["89974332.ca228"]]}]
9

Hey, it works - but here is the problem. The part numbers that come in from the modbus is always not 5 characters. I sent a 6 character part number from my PLC and it is not able to query because it sends only 5 characters to SQL.

Any way to combat that?

10

Yes, so long as the string is zero terminated. Use -1 for the length parameter in the buffer-parser item.

You might need to add msg.payload[0] = msg.payload[0].trim(): in the make SQL function node to remove any non printable characters.

11

image
image775×394 19.4 KB

Unfortunately some of the data I push through will have the 0 at the end. If i do -1 in length then this is the output I get. I tried different techniques to get rid of the \x00 but kind of lost there. even if I did, the byte is still present.

12

Try...

msg.payload[0] = msg.payload[0].replace(/[^\x00-\x7F]/g, "")

In your SQL function at the top of the function node before any other statements.

13

image
image775×342 19.4 KB

I am not really having any luck with this. I attached the screenshot. buffer length is set to -1.

[{"id":"9f77f1c1.87b7a","type":"tab","label":"PLEATER #1","disabled":false,"info":""},{"id":"3a3ae9c0.e18b36","type":"inject","z":"9f77f1c1.87b7a","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"str","x":110,"y":60,"wires":[["22897c4d.c6db04"]]},{"id":"89974332.ca228","type":"function","z":"9f77f1c1.87b7a","name":"","func":"//msg.payload[0] = msg.payload[0].trim();\nmsg.payload[0] = msg.payload[0].replace(/[^\\x00-\\x7F]/g, \"\")\nmsg.topic = \"SELECT `Pleats` FROM `pleatco` WHERE Part_Number = '\"+msg.payload+\"'\"\nreturn msg;\n\n\n\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":740,"y":200,"wires":[["59133cce.3422a4","595a4b63.160064"]]},{"id":"886f87b5.5a0bb8","type":"modbus-flex-getter","z":"9f77f1c1.87b7a","name":"","showStatusActivities":true,"showErrors":true,"logIOActivities":false,"server":"daef988e.6eb638","useIOFile":false,"ioFile":"","useIOForPayload":false,"emptyMsgOnFail":true,"keepMsgProperties":true,"x":450,"y":60,"wires":[["5b7f246e.777b8c"],[]]},{"id":"22897c4d.c6db04","type":"function","z":"9f77f1c1.87b7a","name":"","func":"msg.payload = { value: msg.payload, 'fc': 3, 'unitid': 1, 'address': 36866 , 'quantity': 10 } \nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":260,"y":60,"wires":[["886f87b5.5a0bb8"]]},{"id":"2fb32ca4.c5afa4","type":"inject","z":"9f77f1c1.87b7a","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"PWW10","payloadType":"str","x":460,"y":420,"wires":[["89974332.ca228"]]},{"id":"6cfe691a.485c68","type":"debug","z":"9f77f1c1.87b7a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":1170,"y":200,"wires":[]},{"id":"59133cce.3422a4","type":"mysql","z":"9f77f1c1.87b7a","mydb":"7ce0fe.a4dcbf04","name":"","x":980,"y":200,"wires":[["6cfe691a.485c68"]]},{"id":"5b7f246e.777b8c","type":"buffer-parser","z":"9f77f1c1.87b7a","name":"","data":"payload","dataType":"msg","specification":"spec","specificationType":"ui","items":[{"type":"ascii","name":"item2","offset":0,"length":-1,"offsetbit":0,"scale":"1","mask":""}],"swap1":"swap16","swap2":"","swap3":"swap16","swap1Type":"swap","swap2Type":"swap","swap3Type":"swap","msgProperty":"payload","msgPropertyType":"str","resultType":"value","resultTypeType":"output","multipleResult":false,"fanOutMultipleResult":false,"setTopic":true,"outputs":1,"x":630,"y":60,"wires":[["89974332.ca228","5e82cd3c.185cf4"]]},{"id":"5e82cd3c.185cf4","type":"debug","z":"9f77f1c1.87b7a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":1110,"y":80,"wires":[]},{"id":"595a4b63.160064","type":"debug","z":"9f77f1c1.87b7a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":930,"y":320,"wires":[]},{"id":"daef988e.6eb638","type":"modbus-client","name":"CLICK","clienttype":"tcp","bufferCommands":true,"stateLogEnabled":false,"queueLogEnabled":false,"tcpHost":"192.168.5.199","tcpPort":"502","tcpType":"DEFAULT","serialPort":"/dev/ttyUSB","serialType":"RTU-BUFFERD","serialBaudrate":"9600","serialDatabits":"8","serialStopbits":"1","serialParity":"none","serialConnectionDelay":"100","unit_id":"1","commandDelay":"2","clientTimeout":"1000","reconnectOnTimeout":true,"reconnectTimeout":"2000","parallelUnitIdsAllowed":true},{"id":"7ce0fe.a4dcbf04","type":"MySQLdatabase","name":"","host":"127.0.0.1","port":"3306","db":"node-red-temp","tz":"","charset":"UTF8"}]
14

Try this...

[{"id":"5b494e09.c29e5","type":"function","z":"b8d87de8.2e416","name":"","func":"msg.payload[0] = msg.payload[0].replace(/[\\u{0000}-\\u{0003}]/gu,\"\");\nmsg.topic = \"SELECT `Pleats` FROM `pleatco` WHERE Part_Number = ?\";\nreturn msg;\n\n\n\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":700,"y":200,"wires":[["afda14e9.6cf2b8"]]},{"id":"4987d1a5.1256b","type":"inject","z":"b8d87de8.2e416","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[\"PWW10\"]","payloadType":"json","x":540,"y":240,"wires":[["5b494e09.c29e5"]]},{"id":"afda14e9.6cf2b8","type":"debug","z":"b8d87de8.2e416","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":870,"y":200,"wires":[]},{"id":"f3b592d4.57f68","type":"buffer-parser","z":"b8d87de8.2e416","name":"","data":"responseBuffer.buffer","dataType":"msg","specification":"spec","specificationType":"ui","items":[{"type":"ascii","name":"item1","offset":0,"length":-1,"offsetbit":0,"scale":"1","mask":""}],"swap1":"swap16","swap2":"","swap3":"swap16","swap1Type":"swap","swap2Type":"swap","swap3Type":"swap","msgProperty":"payload","msgPropertyType":"str","resultType":"value","resultTypeType":"output","multipleResult":false,"fanOutMultipleResult":false,"setTopic":true,"outputs":1,"x":530,"y":200,"wires":[["5b494e09.c29e5"]]},{"id":"e42a451b.0778c8","type":"function","z":"b8d87de8.2e416","name":"test data 1 PWW10","func":"return {\n    \"_msgid\":\"d1bf9110.d196d\",\n    \"payload\":[22352,12631,48,0,0,0,0,0,0,0],\n    \"topic\":\"886f87b5.5a0bb8\",\n    \"messageId\":\"60b1288c36ee442146f90792\",\n    \"modbusRequest\":{\"unitid\":1,\"fc\":3,\"address\":36866,\"quantity\":10,\"emptyMsgOnFail\":true,\"keepMsgProperties\":true,\"messageId\":\"60b1288c36ee442146f90792\"},\n    \"responseBuffer\":{\n        \"data\":[22352,12631,48,0,0,0,0,0,0,0],\n        \"buffer\":Buffer.from([87,80,49,87,0,48,0,0,0,0,0,0,0,0,0,0,0,0,0,0])\n    }\n}\n ","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":290,"y":100,"wires":[["f3b592d4.57f68"]]},{"id":"e2f99c6c.d1208","type":"inject","z":"b8d87de8.2e416","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":120,"y":100,"wires":[["e42a451b.0778c8"]]},{"id":"162569d7.e92c26","type":"function","z":"b8d87de8.2e416","name":"test data 1 PWW100","func":"return {\n    \"_msgid\":\"d1bf9110.d196d\",\n    \"payload\":[22352,12631,48,0,0,0,0,0,0,0],\n    \"topic\":\"886f87b5.5a0bb8\",\n    \"messageId\":\"60b1288c36ee442146f90792\",\n    \"modbusRequest\":{\"unitid\":1,\"fc\":3,\"address\":36866,\"quantity\":10,\"emptyMsgOnFail\":true,\"keepMsgProperties\":true,\"messageId\":\"60b1288c36ee442146f90792\"},\n    \"responseBuffer\":{\n        \"data\":[22352,12631,48,0,0,0,0,0,0,0],\n        \"buffer\":Buffer.from([87,80,49,87,0,48,0,48,0,0,0,0,0,0,0,0,0,0,0,0])\n    }\n}\n ","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":300,"y":140,"wires":[["f3b592d4.57f68"]]},{"id":"92503463.70b918","type":"inject","z":"b8d87de8.2e416","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":120,"y":140,"wires":[["162569d7.e92c26"]]},{"id":"d831086f.41a548","type":"inject","z":"b8d87de8.2e416","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":120,"y":180,"wires":[["bca0c39a.d26c5"]]},{"id":"bca0c39a.d26c5","type":"function","z":"b8d87de8.2e416","name":"test data 1 PWW1111","func":"return {\n    \"_msgid\":\"d1bf9110.d196d\",\n    \"payload\":[22352,12631,48,0,0,0,0,0,0,0],\n    \"topic\":\"886f87b5.5a0bb8\",\n    \"messageId\":\"60b1288c36ee442146f90792\",\n    \"modbusRequest\":{\"unitid\":1,\"fc\":3,\"address\":36866,\"quantity\":10,\"emptyMsgOnFail\":true,\"keepMsgProperties\":true,\"messageId\":\"60b1288c36ee442146f90792\"},\n    \"responseBuffer\":{\n        \"data\":[22352,12631,48,0,0,0,0,0,0,0],\n        \"buffer\":Buffer.from([87,80,49,87,0,49,0,49,0,49,0,0,0,0,0,0,0,0,0,0])\n    }\n}\n ","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":300,"y":180,"wires":[["f3b592d4.57f68"]]}]

NOTE:
I changed your query to use prepared statement (safer than building a string query) but if it doesnt work with your database, change it back to

msg.topic = "SELECT `Pleats` FROM `pleatco` WHERE Part_Number = `" + msg.payload[0] + "`";`
15

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