Hello Sir,
I stuck in a mysql query. I want to pick two dates and want to display the data in tabular form on dashboard after fetching data from database. I ran the query in mysql workbench and it was successfull. But, while running the same query in msg.topic in function node the result shows msg.payload[0]. The flows is exported here as:
[{"id":"ea171992.c2c538","type":"function","z":"99e51cc7.c42cb","name":"","func":"var start = msg.payload.from;\nvar end = msg.payload.to;\nmsg.topic =\"SELECT * FROM random where data_date between \"+start+\" and \"+end+\";\";\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":330,"y":80,"wires":[["4a583b85.12a814"]]},{"id":"4a583b85.12a814","type":"mysql","z":"99e51cc7.c42cb","mydb":"4408c380.b7cffc","name":"","x":500,"y":80,"wires":[["cae47677.83b378","4e657f46.64fb7"]]},{"id":"cae47677.83b378","type":"debug","z":"99e51cc7.c42cb","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":670,"y":80,"wires":[]},{"id":"4e657f46.64fb7","type":"ui_table","z":"99e51cc7.c42cb","group":"4d5e7557.929f7c","name":"","order":3,"width":0,"height":0,"columns":[],"outputs":0,"cts":false,"x":640,"y":160,"wires":[]},{"id":"a8ca02ab.c91c6","type":"ui_template","z":"99e51cc7.c42cb","group":"4d5e7557.929f7c","name":"form_date","order":4,"width":0,"height":0,"format":"<p>From: <input type=\"text\" name=\"from\" value=\"from\" ng-model=\"from\" placeholder=\"YYYY-MM-DD\" /></p>\n<p>To: <input type=\"text\" name=\"to\" value=\"to\" ng-model=\"to\" placeholder=\"YYYY-MM-DD\"/></p>\n<md-button ng-click=\"send({payload:{from:from, to:to}})\">Submit</md-button>\n","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","x":120,"y":80,"wires":[["ea171992.c2c538"]]},{"id":"4408c380.b7cffc","type":"MySQLdatabase","z":"","name":"dischargeDB","host":"localhost","port":"3306","db":"nathpa","tz":"GMT + 5:30"},{"id":"4d5e7557.929f7c","type":"ui_group","z":"","name":"Sem","tab":"3871e724.85cf98","order":1,"disp":true,"width":"4","collapse":false},{"id":"3871e724.85cf98","type":"ui_tab","z":"","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]
Please suggest me where i am wrong?
Thanks in advance.
If you put a debug node on the output of the function node - set to display the Complete msg object - what does it show?
Look at the msg.topic and how does it compare with the query that works in mysql workbench?
Thanks for prompt reply sir.
I put the debug node and the result is " 8/7/2020, 8:10:40 PMnode: b8d50e4d.1403fSELECT * FROM random where data_date between 2020-07-01 and 2020-07-29; : msg : Object
object
payload: object
socketid: "bWaTyjxjRMIMTg7wAAAC"
_msgid: "62e54e5d.4da2e"
topic: "SELECT * FROM random where data_date between 2020-07-01 and 2020-07-29;"". The flow is as:
[{"id":"ea171992.c2c538","type":"function","z":"99e51cc7.c42cb","name":"","func":"var start = msg.payload.from;\nvar end = msg.payload.to;\nmsg.topic =\"SELECT * FROM random where data_date between \"+start+\" and \"+end+\";\";\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":330,"y":80,"wires":[["4a583b85.12a814","b8d50e4d.1403f"]]},{"id":"4a583b85.12a814","type":"mysql","z":"99e51cc7.c42cb","mydb":"4408c380.b7cffc","name":"","x":500,"y":80,"wires":[["cae47677.83b378","4e657f46.64fb7"]]},{"id":"cae47677.83b378","type":"debug","z":"99e51cc7.c42cb","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":670,"y":80,"wires":[]},{"id":"4e657f46.64fb7","type":"ui_table","z":"99e51cc7.c42cb","group":"4d5e7557.929f7c","name":"","order":3,"width":0,"height":0,"columns":[],"outputs":0,"cts":false,"x":640,"y":160,"wires":[]},{"id":"a8ca02ab.c91c6","type":"ui_template","z":"99e51cc7.c42cb","group":"4d5e7557.929f7c","name":"form_date","order":4,"width":0,"height":0,"format":"<p>From: <input type=\"text\" name=\"from\" value=\"from\" ng-model=\"from\" placeholder=\"YYYY-MM-DD\" /></p>\n<p>To: <input type=\"text\" name=\"to\" value=\"to\" ng-model=\"to\" placeholder=\"YYYY-MM-DD\"/></p>\n<md-button ng-click=\"send({payload:{from:from, to:to}})\">Submit</md-button>\n","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","x":120,"y":80,"wires":[["ea171992.c2c538"]]},{"id":"b8d50e4d.1403f","type":"debug","z":"99e51cc7.c42cb","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":460,"y":160,"wires":[]},{"id":"4408c380.b7cffc","type":"MySQLdatabase","z":"","name":"dischargeDB","host":"localhost","port":"3306","db":"nathpa","tz":"GMT + 5:30"},{"id":"4d5e7557.929f7c","type":"ui_group","z":"","name":"Sem","tab":"3871e724.85cf98","order":1,"disp":true,"width":"4","collapse":false},{"id":"3871e724.85cf98","type":"ui_tab","z":"","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]
You don't need to provide the flow a second time. So the debug shows the query is:
SELECT * FROM random where data_date between 2020-07-01 and 2020-07-29;
now what does the query you used in mysql workbench look like?
Compare the two...are there any differences?
Yes Sir,
There is difference in date value. In mysql workbench
SELECT * FROM random where data_date between '2020-07-01' and '2020-07-29';
date values are withing quotes while node red function node these are without quotes. I think this might be reason. So, please suggest me how to include quotes as these are values received from user interface node.
Try changing the statement:
msg.topic ="SELECT * FROM random where data_date between '"+start+"' and '"+end+"';";
Thank you so much sir.
It worked.
Thank you once again.
This is why I had you add the debug node - so you could see the difference and then realize that you were not running the same query. Using the debug node is a great way to see what you are actually doing vrs what you think you are doing 
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
