Updating mysql values

General
1

This works but should be a better way. Kinda new to node red but I have tried a Function node, a change node, and a Template node. Probably wrong but couldn't get any of them to work. Any help is greatly appreciated.

[{"id":"af0e674c.7dafb8","type":"tab","label":"OAH","disabled":false,"info":""},{"id":"ed43ccaa.6d226","type":"debug","z":"af0e674c.7dafb8","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1310,"y":240,"wires":[]},{"id":"ac7768bd.e702e8","type":"mysql","z":"af0e674c.7dafb8","mydb":"6a95d814.94c7d8","name":"","x":1150,"y":240,"wires":[["ed43ccaa.6d226"]]},{"id":"53708e25.34a47","type":"inject","z":"af0e674c.7dafb8","name":"","topic":"FLUSH HOSTS","payload":"FLUSH HOSTS","payloadType":"str","repeat":"3600","crontab":"","once":false,"onceDelay":0.1,"x":340,"y":40,"wires":[["ac873573.731738"]]},{"id":"8c4abce7.0e011","type":"ui_slider","z":"af0e674c.7dafb8","name":"","label":"OAH","tooltip":"","group":"a1fc7b2.127ea88","order":0,"width":0,"height":0,"passthru":false,"outs":"all","topic":"","min":0,"max":"100","step":1,"x":130,"y":300,"wires":[["35ec0ea.3111af2","a05f6187.80d9f"]]},{"id":"60ab3ae6.f5bf84","type":"inject","z":"af0e674c.7dafb8","name":"Clear","topic":"UPDATE `temps` SET `OAh`=0","payload":"SET 0","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":90,"y":40,"wires":[["41f64111.0cbab"]]},{"id":"176ff9d1.049c36","type":"change","z":"af0e674c.7dafb8","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"UPDATE `temps` SET `OAh`=2","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":630,"y":260,"wires":[["be4590c1.a7e28"]]},{"id":"35ec0ea.3111af2","type":"switch","z":"af0e674c.7dafb8","name":"","property":"payload","propertyType":"msg","rules":[{"t":"btwn","v":"0","vt":"num","v2":"1.0","v2t":"num"},{"t":"btwn","v":"1.1","vt":"num","v2":"2.0","v2t":"num"},{"t":"btwn","v":"2.1","vt":"num","v2":"3.0","v2t":"num"},{"t":"btwn","v":"3.1","vt":"num","v2":"4.0","v2t":"num"},{"t":"btwn","v":"4.1","vt":"num","v2":"5.0","v2t":"num"},{"t":"btwn","v":"5.1","vt":"num","v2":"6.0","v2t":"num"},{"t":"btwn","v":"6.1","vt":"num","v2":"7.0","v2t":"num"},{"t":"btwn","v":"7.1","vt":"num","v2":"8.0","v2t":"num"},{"t":"btwn","v":"8.1","vt":"num","v2":"9.0","v2t":"num"},{"t":"btwn","v":"9.1","vt":"num","v2":"10.0","v2t":"num"}],"checkall":"true","repair":false,"outputs":10,"x":390,"y":400,"wires":[["1d02826a.0a487e"],["176ff9d1.049c36"],["b5095d42.bbd36"],["7c738314.4104dc"],["a5d01027.69dd"],["e33fb7e7.eaa5c8"],["a04ff5cd.a09288"],["55e2b02d.cfc47"],["54be4f3a.1981c"],["d50b8969.2065c8"]]},{"id":"b5095d42.bbd36","type":"change","z":"af0e674c.7dafb8","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"UPDATE `temps` SET `OAh`=3","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":630,"y":300,"wires":[["2496f345.c7c22c"]]},{"id":"a5d01027.69dd","type":"change","z":"af0e674c.7dafb8","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"UPDATE `temps` SET `OAh`=5","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":630,"y":380,"wires":[["9c9972.5427469"]]},{"id":"e33fb7e7.eaa5c8","type":"change","z":"af0e674c.7dafb8","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"UPDATE `temps` SET `OAh`=6","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":630,"y":420,"wires":[["d537a436.b99f28"]]},{"id":"a04ff5cd.a09288","type":"change","z":"af0e674c.7dafb8","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"UPDATE `temps` SET `OAh`=7","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":630,"y":460,"wires":[["9cde3cd4.ce448"]]},{"id":"55e2b02d.cfc47","type":"change","z":"af0e674c.7dafb8","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"UPDATE `temps` SET `OAh`=8","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":630,"y":500,"wires":[["eb0c12f4.0d961"]]},{"id":"7c738314.4104dc","type":"change","z":"af0e674c.7dafb8","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"UPDATE `temps` SET `OAh`=4","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":630,"y":340,"wires":[["5efe1d39.e16f94"]]},{"id":"1d02826a.0a487e","type":"change","z":"af0e674c.7dafb8","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"UPDATE `temps` SET `OAh`=1","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":630,"y":220,"wires":[["48ae4629.6e08d8"]]},{"id":"d50b8969.2065c8","type":"change","z":"af0e674c.7dafb8","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"UPDATE `temps` SET `OAh`=10","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":630,"y":580,"wires":[["141c5185.a7164e"]]},{"id":"54be4f3a.1981c","type":"change","z":"af0e674c.7dafb8","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"UPDATE `temps` SET `OAh`=9","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":630,"y":540,"wires":[["a68d0a14.e732f8"]]},{"id":"48ae4629.6e08d8","type":"link out","z":"af0e674c.7dafb8","name":"","links":["72adf94c.144b78"],"x":735,"y":220,"wires":[]},{"id":"be4590c1.a7e28","type":"link out","z":"af0e674c.7dafb8","name":"","links":["72adf94c.144b78"],"x":735,"y":260,"wires":[]},{"id":"9cde3cd4.ce448","type":"link out","z":"af0e674c.7dafb8","name":"","links":["72adf94c.144b78"],"x":735,"y":460,"wires":[]},{"id":"2496f345.c7c22c","type":"link out","z":"af0e674c.7dafb8","name":"","links":["72adf94c.144b78"],"x":735,"y":300,"wires":[]},{"id":"5efe1d39.e16f94","type":"link out","z":"af0e674c.7dafb8","name":"","links":["72adf94c.144b78"],"x":735,"y":340,"wires":[]},{"id":"9c9972.5427469","type":"link out","z":"af0e674c.7dafb8","name":"","links":["72adf94c.144b78"],"x":735,"y":380,"wires":[]},{"id":"d537a436.b99f28","type":"link out","z":"af0e674c.7dafb8","name":"","links":["72adf94c.144b78"],"x":735,"y":420,"wires":[]},{"id":"eb0c12f4.0d961","type":"link out","z":"af0e674c.7dafb8","name":"","links":["72adf94c.144b78"],"x":735,"y":500,"wires":[]},{"id":"a68d0a14.e732f8","type":"link out","z":"af0e674c.7dafb8","name":"","links":["72adf94c.144b78"],"x":735,"y":540,"wires":[]},{"id":"141c5185.a7164e","type":"link out","z":"af0e674c.7dafb8","name":"","links":["72adf94c.144b78"],"x":735,"y":580,"wires":[]},{"id":"72adf94c.144b78","type":"link in","z":"af0e674c.7dafb8","name":"SQL In","links":["48ae4629.6e08d8","be4590c1.a7e28","2496f345.c7c22c","5efe1d39.e16f94","9c9972.5427469","d537a436.b99f28","9cde3cd4.ce448","eb0c12f4.0d961","a68d0a14.e732f8","141c5185.a7164e","3f647aa1.e99946","7d3e546.b1e79ac","f797739e.4a48d","d54e1c4a.4096f","40f19cc5.10d2f4","2953b97a.af92b6","2f2cae8d.ee5322","f7b03de3.a6a2","4e51a92e.4c5ab8","1ada0947.5ca8c7","b5613d8a.60d3f","876a9b0.4764868","642f03b3.0d632c","1d72d2e3.392abd","70280e13.7b9fc","43f96d3.c6b9894","9f843dd7.87b9f","1a8bd35.250ed2d","7d63e1c7.20f88","d285495c.c9d258","dcf004e6.510768","b8ddaed0.b9b4d","13dc9081.88882f","4ff237ca.96c5f8","1b5b2bc.34c22d4","950ce7b7.017ed8","d0eee088.df111","df7a3d35.50b75","602c2ae0.11b504","3b763309.05806c","2848a4e6.fcd87c","e6263fea.4b1ce","ec2c8a44.98bff8","115e5237.9866ee","2ca38ba4.75b744","cadc61ee.7870b","ba870eba.36a8e","1d6d12db.4f1dfd","45469bc6.146204","f2522155.00b67","d5d53cc5.ade68","fe83a9b2.0acbf8","b72db739.3f41f8","108094.7d653f6d","9fd63c52.36ca8","5eee9f82.f1ea6","eef1fa5f.a38008","1ccead5a.8652b3","219795a8.2fdfda","8e7e6966.c02008","3fe296f.bb9cc6a","fe64238e.8d1e9","8d9d78e0.807c68","5b446c52.1ed954","9c448295.9fb63","e212b64a.426398","54ea6856.23dcc8","14454d46.674b13","b2f8d7e4.fcac68","a5d7d5a.b70b928","addc442b.458938","9183426c.72cea","86e83975.e75a38","8dc21d35.c5195","a8ea5347.d7fb1","6b19fa9c.f832a4","1ed0adbb.27fa52","1060aea.9dc8451","a38d8579.12cdf8","79e29bb7.58a7f4","a82c55e6.27f938","617015d5.9478bc","690ca141.37fee","9820f921.207228","208047a4.384e98","a8bef220.9363","733ff5f6.8ec88c","9487429e.77c9e","c57a6445.bfc738","b5dd4fc8.8aa66","96bfe7cc.9b2888","762694ab.1233dc","4558854c.3db46c","c7c5dadd.9d49e8","d66c84fb.440288","e0f8c7e2.c22978","5e2e3cd2.169b34","7a17458a.09ac3c","37051027.41f1e","e7918155.20537","41f64111.0cbab","ac873573.731738"],"x":1035,"y":240,"wires":[["ac7768bd.e702e8","992af664.e9d3a8"]]},{"id":"41f64111.0cbab","type":"link out","z":"af0e674c.7dafb8","name":"","links":["72adf94c.144b78"],"x":180,"y":100,"wires":[]},{"id":"ac873573.731738","type":"link out","z":"af0e674c.7dafb8","name":"","links":["72adf94c.144b78"],"x":540,"y":40,"wires":[]},{"id":"a05f6187.80d9f","type":"ui_gauge","z":"af0e674c.7dafb8","name":"","group":"a1fc7b2.127ea88","order":1,"width":0,"height":0,"gtype":"gage","title":"OAH","label":"units","format":"{{value}}","min":0,"max":"100","colors":["#00b500","#e6e600","#ca3838"],"seg1":"33","seg2":"66","x":380,"y":260,"wires":[]},{"id":"992af664.e9d3a8","type":"debug","z":"af0e674c.7dafb8","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":1130,"y":200,"wires":[]},{"id":"6a95d814.94c7d8","type":"MySQLdatabase","z":"","name":"Master SQL","host":"10.10.5.31","port":"3306","db":"hvac","tz":"","charset":"UTF8"},{"id":"a1fc7b2.127ea88","type":"ui_group","z":"","name":"hvac","tab":"66d5f7ba.b8dfe8","order":1,"disp":true,"width":"6","collapse":false},{"id":"66d5f7ba.b8dfe8","type":"ui_tab","z":"","name":"hvac","icon":"dashboard","disabled":false,"hidden":false}]
2

here is an example of change node and template node.

[{"id":"1d1d0aa7.669a25","type":"inject","z":"af0e674c.7dafb8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"$round($random()*9,1)","payloadType":"jsonata","x":140,"y":380,"wires":[["3e672320.3025f4","e2672bab.81b7e","8e149c17.61f83"]]},{"id":"3e672320.3025f4","type":"debug","z":"af0e674c.7dafb8","name":"","active":false,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"payload","statusType":"auto","x":340,"y":340,"wires":[]},{"id":"e2672bab.81b7e","type":"change","z":"af0e674c.7dafb8","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"\"UPDATE `temps` SET `OAh`= \" & $floor(payload)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":470,"y":400,"wires":[["90b89fb2.d4f168"]]},{"id":"8e149c17.61f83","type":"change","z":"af0e674c.7dafb8","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"$floor(payload)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":330,"y":440,"wires":[["2a13f8ba.bdb9d8"]]},{"id":"90b89fb2.d4f168","type":"debug","z":"af0e674c.7dafb8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":720,"y":400,"wires":[]},{"id":"2a13f8ba.bdb9d8","type":"template","z":"af0e674c.7dafb8","name":"","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"UPDATE `temps` SET `OAh`= {{topic}}","output":"str","x":530,"y":440,"wires":[["90b89fb2.d4f168"]]}]

[edit you can use $ceil() instead of $floor() if you wish the values 0 - 1 to be 1 rather than 0, etc]

3

This is all a bit ... unorthodox & very very long handed.

As far as I can see, all this code does is take a slider value from 0.0 ~ 10.0 and rounds up to the next integer.

i.e. 7.7 would go to the change node and do UPDATE `temps` SET `OAh`=8

image



You can achieve the same thing with Math.round in a function node...

msg.topic = "UPDATE `temps` SET `OAh` = " + Math.round(msg.payload + 0.5)
return msg;


Better still, to avoid any SQL injection...

msg.topic = "UPDATE `temps` SET `OAh` = ?";
msg.payload =  [ Math.round(msg.payload + 0.5) ];
return msg;

(see "Preparing Queries" in node-red-node-mysql readme )

your flow now looks like this...

image

[{"id":"ed43ccaa.6d226","type":"debug","z":"af0e674c.7dafb8","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":450,"y":272,"wires":[]},{"id":"ac7768bd.e702e8","type":"mysql","z":"af0e674c.7dafb8","mydb":"6a95d814.94c7d8","name":"","x":278,"y":272,"wires":[["ed43ccaa.6d226"]]},{"id":"53708e25.34a47","type":"inject","z":"af0e674c.7dafb8","name":"","repeat":"3600","crontab":"","once":false,"onceDelay":0.1,"topic":"FLUSH HOSTS","payload":"FLUSH HOSTS","payloadType":"str","x":204,"y":80,"wires":[["ac873573.731738"]]},{"id":"8c4abce7.0e011","type":"ui_slider","z":"af0e674c.7dafb8","name":"","label":"OAH","tooltip":"","group":"a1fc7b2.127ea88","order":0,"width":0,"height":0,"passthru":false,"outs":"all","topic":"","min":0,"max":"100","step":1,"x":114,"y":144,"wires":[["a05f6187.80d9f","fd901859.2c83e8"]]},{"id":"60ab3ae6.f5bf84","type":"inject","z":"af0e674c.7dafb8","name":"Clear","repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"UPDATE `temps` SET `OAh`=0","payload":"SET 0","payloadType":"str","x":114,"y":32,"wires":[["41f64111.0cbab"]]},{"id":"72adf94c.144b78","type":"link in","z":"af0e674c.7dafb8","name":"SQL In","links":["48ae4629.6e08d8","be4590c1.a7e28","2496f345.c7c22c","5efe1d39.e16f94","9c9972.5427469","d537a436.b99f28","9cde3cd4.ce448","eb0c12f4.0d961","a68d0a14.e732f8","141c5185.a7164e","3f647aa1.e99946","7d3e546.b1e79ac","f797739e.4a48d","d54e1c4a.4096f","40f19cc5.10d2f4","2953b97a.af92b6","2f2cae8d.ee5322","f7b03de3.a6a2","4e51a92e.4c5ab8","1ada0947.5ca8c7","b5613d8a.60d3f","876a9b0.4764868","642f03b3.0d632c","1d72d2e3.392abd","70280e13.7b9fc","43f96d3.c6b9894","9f843dd7.87b9f","1a8bd35.250ed2d","7d63e1c7.20f88","d285495c.c9d258","dcf004e6.510768","b8ddaed0.b9b4d","13dc9081.88882f","4ff237ca.96c5f8","1b5b2bc.34c22d4","950ce7b7.017ed8","d0eee088.df111","df7a3d35.50b75","602c2ae0.11b504","3b763309.05806c","2848a4e6.fcd87c","e6263fea.4b1ce","ec2c8a44.98bff8","115e5237.9866ee","2ca38ba4.75b744","cadc61ee.7870b","ba870eba.36a8e","1d6d12db.4f1dfd","45469bc6.146204","f2522155.00b67","d5d53cc5.ade68","fe83a9b2.0acbf8","b72db739.3f41f8","108094.7d653f6d","9fd63c52.36ca8","5eee9f82.f1ea6","eef1fa5f.a38008","1ccead5a.8652b3","219795a8.2fdfda","8e7e6966.c02008","3fe296f.bb9cc6a","fe64238e.8d1e9","8d9d78e0.807c68","5b446c52.1ed954","9c448295.9fb63","e212b64a.426398","54ea6856.23dcc8","14454d46.674b13","b2f8d7e4.fcac68","a5d7d5a.b70b928","addc442b.458938","9183426c.72cea","86e83975.e75a38","8dc21d35.c5195","a8ea5347.d7fb1","6b19fa9c.f832a4","1ed0adbb.27fa52","1060aea.9dc8451","a38d8579.12cdf8","79e29bb7.58a7f4","a82c55e6.27f938","617015d5.9478bc","690ca141.37fee","9820f921.207228","208047a4.384e98","a8bef220.9363","733ff5f6.8ec88c","9487429e.77c9e","c57a6445.bfc738","b5dd4fc8.8aa66","96bfe7cc.9b2888","762694ab.1233dc","4558854c.3db46c","c7c5dadd.9d49e8","d66c84fb.440288","e0f8c7e2.c22978","5e2e3cd2.169b34","7a17458a.09ac3c","37051027.41f1e","e7918155.20537","41f64111.0cbab","ac873573.731738","db47edcd.4fa7a"],"x":79,"y":272,"wires":[["ac7768bd.e702e8","992af664.e9d3a8"]]},{"id":"41f64111.0cbab","type":"link out","z":"af0e674c.7dafb8","name":"","links":["72adf94c.144b78"],"x":495,"y":32,"wires":[]},{"id":"ac873573.731738","type":"link out","z":"af0e674c.7dafb8","name":"","links":["72adf94c.144b78"],"x":495,"y":80,"wires":[]},{"id":"a05f6187.80d9f","type":"ui_gauge","z":"af0e674c.7dafb8","name":"","group":"a1fc7b2.127ea88","order":1,"width":0,"height":0,"gtype":"gage","title":"OAH","label":"units","format":"{{value}}","min":0,"max":"100","colors":["#00b500","#e6e600","#ca3838"],"seg1":"33","seg2":"66","x":226,"y":192,"wires":[]},{"id":"992af664.e9d3a8","type":"debug","z":"af0e674c.7dafb8","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":178,"y":336,"wires":[]},{"id":"fd901859.2c83e8","type":"function","z":"af0e674c.7dafb8","name":"Prepare Query","func":"msg.topic = \"UPDATE `temps` SET `OAh` = ?\";\nmsg.payload =  [ Math.round(msg.payload + 0.5) ];\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":352,"y":144,"wires":[["db47edcd.4fa7a"]]},{"id":"db47edcd.4fa7a","type":"link out","z":"af0e674c.7dafb8","name":"","links":["72adf94c.144b78"],"x":495,"y":144,"wires":[]},{"id":"6a95d814.94c7d8","type":"MySQLdatabase","name":"Master SQL","host":"10.10.5.31","port":"3306","db":"hvac","tz":"","charset":"UTF8"},{"id":"a1fc7b2.127ea88","type":"ui_group","name":"hvac","tab":"66d5f7ba.b8dfe8","order":1,"disp":true,"width":"6","collapse":false},{"id":"66d5f7ba.b8dfe8","type":"ui_tab","name":"hvac","icon":"dashboard","disabled":false,"hidden":false}]
4

Thank you very much. I couldn't figure it out the only purpose of the switch was to change the the data from msg.payload to msg.topic.

5

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