Can not return results of POST HTTP Request

[d0ublei]

Hi,
Been a node red user for some time, but I have finally ran into an issue that seems relatively simple, but I can not find a solution for.

I am running an application on several Raspberry Pi's with Node Red installed on the devices. The application is using a MySQL database connection which is not a good idea on a Raspberry Pi for anything other than development. To get around this I am going to be doing a POST with the ID of the device to a webserver hosting a database. The POST will take the id, convert that into a MYSQL query and return the results in the response. Now I have been able to POST the id, query the DB and return a JSON string, but I can not have that respond to the POST request with the data.

I feel I'm so close to getting this but just cant find the solution. Any help would be wonderful!

Happy to post my flow if it helps. I imagine its something silly im missing.

[dceejay]

you can only have one response to each request so the wire that goes from in the http in to the {page node will be causing a problem... Like wise you need to ensure that the other path does maintain the msg.res property all the way down it's path (so it's not lost).

[d0ublei]

That must be the root cause of my issue. I am taking the message, converting it to a SQL statement, converting the output to JSON, and then trying to send back the JSON response. In this process I am certainly loosing the msg.res.

Am I going about this the wrong way? Perhaps a post response is not the best way to return this type of data?

[{"id":"9cd3bf3f.6b81d","type":"debug","z":"460beebe.2c9c28","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1030,"y":580,"wires":[]},{"id":"cc647e86.924df8","type":"http in","z":"460beebe.2c9c28","name":"","url":"config","method":"post","upload":true,"swaggerDoc":"","x":130,"y":500,"wires":[["bb35c0fa.ffdf28"]]},{"id":"7da382b1.405fbc","type":"comment","z":"460beebe.2c9c28","name":"Web Listener to return config JSON string","info":"","x":200,"y":460,"wires":[]},{"id":"bb35c0fa.ffdf28","type":"function","z":"460beebe.2c9c28","name":"Record Lookup","func":"/*data = msg.payload.id;*/\ndata = msg.payload.id;\nvar ret = \"data-123\".replace('data-','');\nvar m = {\n topic : \"Select * FROM Perams Where Id = \"  + data +\";\"\n /*topic : \"Select * FROM Perams Where Id =  ('\" + ret + \"');\" */\n };\nreturn m;","outputs":1,"noerr":0,"x":320,"y":500,"wires":[["841c429.6b18f4"]]},{"id":"841c429.6b18f4","type":"mysql","z":"460beebe.2c9c28","mydb":"a1c59403.f234a8","name":"DataGrow DB","x":500,"y":500,"wires":[["1f7ec81.2e4ad38"]]},{"id":"79d540f3.e083a8","type":"change","z":"460beebe.2c9c28","name":"Set Headers","rules":[{"t":"set","p":"headers","pt":"msg","to":"{}","tot":"json"},{"t":"set","p":"headers.content-type","pt":"msg","to":"application/json","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":950,"y":500,"wires":[["c2020b42.36aa4","9cd3bf3f.6b81d"]]},{"id":"c2020b42.36aa4","type":"http response","z":"460beebe.2c9c28","name":"","x":1130,"y":500,"wires":[]},{"id":"1f7ec81.2e4ad38","type":"json","z":"460beebe.2c9c28","name":"","property":"payload","action":"str","pretty":true,"x":670,"y":500,"wires":[["79d540f3.e083a8"]]},{"id":"a1c59403.f234a8","type":"MySQLdatabase","z":"","name":"datagrow","host":"127.0.0.1","port":"3306","db":"datagrow","tz":""}]

[dceejay]

a quick look at your first function reveals you are creating a new object (m) to return instead of re-using the existing msg... thus immediately losing msg.res.

[d0ublei]

Makes sense. However, when I was originally trying to do this i could not sanitize the output to an exact sql statement without loosing the message properties. See below:

If i took out topic: the statement became invalid. If i tried to return msg.topic I also ran into problems.

[dceejay]

just add/set the msg.topic as the correct sql you want - and return the msg.

[d0ublei]

Awesome! I got it working. Thank-you so much for you help. I have attached the finished flow, Also, i found another helpful post:

Node-RED Design Patterns

[{"id":"9cd3bf3f.6b81d","type":"debug","z":"460beebe.2c9c28","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":910,"y":580,"wires":[]},{"id":"cc647e86.924df8","type":"http in","z":"460beebe.2c9c28","name":"","url":"config","method":"post","upload":true,"swaggerDoc":"","x":130,"y":500,"wires":[["c795047a.f77748"]]},{"id":"7da382b1.405fbc","type":"comment","z":"460beebe.2c9c28","name":"Web Listener to return config JSON string","info":"","x":200,"y":460,"wires":[]},{"id":"841c429.6b18f4","type":"mysql","z":"460beebe.2c9c28","mydb":"a1c59403.f234a8","name":"DataGrow DB","x":520,"y":500,"wires":[["1f7ec81.2e4ad38"]]},{"id":"79d540f3.e083a8","type":"change","z":"460beebe.2c9c28","name":"Set Headers","rules":[{"t":"set","p":"headers","pt":"msg","to":"{}","tot":"json"},{"t":"set","p":"headers.content-type","pt":"msg","to":"application/json","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":830,"y":500,"wires":[["9cd3bf3f.6b81d","2319c3af.56e9d4"]]},{"id":"c2020b42.36aa4","type":"http response","z":"460beebe.2c9c28","name":"","statusCode":"","headers":{},"x":1190,"y":500,"wires":[]},{"id":"1f7ec81.2e4ad38","type":"json","z":"460beebe.2c9c28","name":"","property":"payload","action":"str","pretty":true,"x":690,"y":500,"wires":[["79d540f3.e083a8"]]},{"id":"c795047a.f77748","type":"function","z":"460beebe.2c9c28","name":"Record Lookup","func":"data = msg.payload.id;\nmsg.topic = \n\"Select * FROM Perams Where Id = \"  + data +\";\"\nreturn msg;","outputs":1,"noerr":0,"x":300,"y":500,"wires":[["841c429.6b18f4"]]},{"id":"2319c3af.56e9d4","type":"template","z":"460beebe.2c9c28","name":"Format HTML","field":"payload","fieldType":"msg","format":"html","syntax":"mustache","template":"\n{{payload}}\n","x":1020,"y":500,"wires":[["c2020b42.36aa4"]]},{"id":"a1c59403.f234a8","type":"MySQLdatabase","z":"","name":"datagrow","host":"127.0.0.1","port":"3306","db":"datagrow","tz":""}]