query in a function node-red

0
votes

By executing a Query in a functional node of Node-Red, how can I refer the rusult of the Query inside the same node? We are attaching our node. Or objectives is to create an automatic library, with this problem, we can not insert more than one equal book, with different ID.

OUR NODE

[{"id":"893d2899.cc8238","type":"function","z":"16cd4688.876b29","name":"CICLO FOR MAGAZZINO","func":"var prova = null;\nvar length = msg.payload.length;\nfor(var i=0; i< length; i++){\n var libroInAnalisi = msg.payload[i].idlibro;\n var magazzino = msg.magazzino;\n msg.topic = \"SELECT idlibro FROM magazzino WHERE idmagazzino = \" + msg.magazzino + \" AND idlibro = \" + msg.payload[i].idlibro;\n prova = msg.paylod; /*we can not processed this msg.payload in our for cycle again. This result is not processed from the for but is our enter value; we need that this value will be the result of our query */\n if (prova.length > 0 ) {\n
i = length;\n return msg;\n }\n}\nreturn msg;\n
\n\n","outputs":1,"noerr":0,"x":1960.3333740234375,"y":3691.333251953125,"wires":[[]]}]

1
mysqlnode.js
I recomment to modify the layout of your json, by using the { } button and using the return button. - Ole EH Dufour
Thank you for your answear, can you explain better how the json can resolve our problem? we have created one already, but we did not understand how to resolve the problem in your way. If you want we can show you all our flow to better understand the process. - JacopoB

1 Answers

0
votes

OUR NODE [{"id":"6c2611b5.7557c","type":"mqtt in","z":"16cd4688.876b29","name":"","topic":"InvioSedeTitolo","qos":"2","broker":"ef4521ef.a299c8","x":208.5001220703125,"y":3506.1668090820312,"wires":[["1a471c90.0a42db","2c52854d.e1abea"]]},{"id":"2c52854d.e1abea","type":"json","z":"16cd4688.876b29","name":"","property":"payload","action":"","pretty":false,"x":397.2620544433594,"y":3505.4763793945312,"wires":[["af4c5cbd.736df8"]]},{"id":"af4c5cbd.736df8","type":"change","z":"16cd4688.876b29","name":"","rules":[{"t":"set","p":"magazzino","pt":"msg","to":"payload.0","tot":"msg"},{"t":"set","p":"titolo","pt":"msg","to":"payload.1","tot":"msg"},{"t":"set","p":"autore","pt":"msg","to":"payload.2","tot":"msg"},{"t":"set","p":"chatId","pt":"msg","to":"payload.3","tot":"msg"},{"t":"set","p":"data","pt":"msg","to":"payload.4","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":606.5001220703125,"y":3504.1668090820312,"wires":[["2e07cb45.b7ccdc","9e50c718.365d58"]]},{"id":"9e50c718.365d58","type":"change","z":"16cd4688.876b29","name":"SELECTidLibro","rules":[{"t":"set","p":"topic","pt":"msg","to":"SELECT idlibro FROM Libri WHERE titolo = ? AND autore = ? ","tot":"str"},{"t":"set","p":"payload","pt":"msg","to":"[\tmsg.titolo,\tmsg.autore\t\t]","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":839.6668090820312,"y":3505.1670532226562,"wires":[["95314a01.d2eb58","ff75b9d0.a297c"]]},{"id":"95314a01.d2eb58","type":"mysql","z":"16cd4688.876b29","mydb":"ac96c738.c838b","name":"libri","x":1069.6671142578125,"y":3506.988067626953,"wires":[["50780f2e.e31f8","930fd299.160f08","95a2d410.fbe098","2a1ea1a8.88ba3e"]]},{"id":"2a1ea1a8.88ba3e","type":"function","z":"16cd4688.876b29","name":"EMPTY","func":"var empty;\nif ( msg.payload.length > 0 ){\n empty=0;\n} else {\n empty = 1;\n}\nmsg.empty = empty;\nreturn msg;","outputs":1,"noerr":0,"x":1227.1668701171875,"y":3516.1668090820312,"wires":[["314d3f64.3dcbd8"]]},{"id":"314d3f64.3dcbd8","type":"switch","z":"16cd4688.876b29","name":"","property":"empty","propertyType":"msg","rules":[{"t":"eq","v":"0","vt":"num"},{"t":"eq","v":"1","vt":"num"}],"checkall":"true","repair":false,"outputs":2,"x":1370.9447021484375,"y":3514.8334350585938,"wires":[["893d2899.cc8238"],[]]},{"id":"893d2899.cc8238","type":"function","z":"16cd4688.876b29","name":"CICLO FOR MAGAZZINO","func":"var prova = null;\nvar length = msg.payload.length;\nfor(var i=0; i< length; i++){\n var libroInAnalisi = msg.payload[i].idlibro;\n var magazzino = msg.magazzino;\n msg.topic = \"SELECT idlibro FROM magazzino WHERE idmagazzino = \" + msg.magazzino + \" AND idlibro = \" + msg.payload[i].idlibro;\n prova = msg.paylod; /*we can not processed this msg.payload in our for cycle again. This result is not processed from the for but is our enter value; we need that this value will be the result of our query */\n if (prova.length > 0 ) {\n i = length;\n return msg;\n }\n}\nreturn msg;\n \n\n","outputs":1,"noerr":0,"x":1598.6668701171875,"y":3506.6106567382812,"wires":[["de18a0fd.f963e"]]},{"id":"de18a0fd.f963e","type":"mysql","z":"16cd4688.876b29","mydb":"ac96c738.c838b","name":"magazzino","x":1841.613037109375,"y":3495.2676391601562,"wires":[["7baa967.2cb4468","d10e16d.0469e68"]]},{"id":"7baa967.2cb4468","type":"debug","z":"16cd4688.876b29","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1925.7799682617188,"y":3383.613235473633,"wires":[]},{"id":"ef4521ef.a299c8","type":"mqtt-broker","z":"","name":"","broker":"iot.eclipse.org","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"willTopic":"","willQos":"0","willPayload":"","birthTopic":"","birthQos":"0","birthPayload":""},{"id":"ac96c738.c838b","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"Biblioteca","tz":""}]