NodeRed MySQL if record not found in database

684 views
Skip to first unread message

Dj Jastrzębowski

unread,
Oct 29, 2017, 6:20:40 AM10/29/17
to Node-RED
Hi, 

I have questions about get record from  mysql database.

Have simple query

"data = (msg.payload);
var m = {
topic : "SELECT * FROM `users1` WHERE `id` = " + data + ""} ;
return m; "

i get result if not found:

SELECT * FROM `users1` WHERE `id` = 801095 : msg.payload : array[0]


If found record im redirect to flow use function:

msg.payload = msg.payload[0].id;
return msg;


How to redirect to the appropriate  flow if record not found and have empty array ?

Regards,

Darek

Zenofmud

unread,
Oct 29, 2017, 6:30:17 AM10/29/17
to node...@googlegroups.com
According to the Info tab for node-red-contrib-mysql:
If nothing is found for the key then null is returned,

so you could connect a switch to the output and check for ‘is null’ and ‘is not null’ and process from there.


--
http://nodered.org
 
Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.
To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/44731a72-10f5-4bbb-a7cf-9f611f6ead02%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Dj Jastrzębowski

unread,
Oct 29, 2017, 6:44:41 AM10/29/17
to Node-RED

Thanks for you replay Paul,
Unfortunately it does not work and I do not know why :-(



Colin Law

unread,
Oct 29, 2017, 6:49:43 AM10/29/17
to node...@googlegroups.com
What doesn't work? Are not getting null (send it to a debug node to check) or can't you get the next bit to work in which case post the flow so we can have a look. Saying it doesn't work does not give us any clues as to what might be wrong.

Colin

--
http://nodered.org
 
Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+unsubscribe@googlegroups.com.

To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

Dj Jastrzębowski

unread,
Oct 29, 2017, 7:00:14 AM10/29/17
to Node-RED
Debug directly connected to database say
 



Regards,

Darek

Colin Law

unread,
Oct 29, 2017, 7:11:32 AM10/29/17
to node...@googlegroups.com
So it looks like the info tab is wrong (or what is meant by 'nothing found for key' is not the same as 'the select query returns no records). In fact what is happening is that it is returning an array with nothing in it, which makes sense. If it finds 5 records it would return an array with 5 entries, so if it finds no entries it returns an empty array. So you need to test for an empty array rather than null.

Colin

--
http://nodered.org
 
Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+unsubscribe@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

Dj Jastrzębowski

unread,
Oct 29, 2017, 7:22:17 AM10/29/17
to Node-RED


I tried to make a condition for an empty array, unfortunately without success :-( 


Zenofmud

unread,
Oct 29, 2017, 8:42:06 AM10/29/17
to node...@googlegroups.com
Interesting because I get 
10/29/2017, 8:39:06 AMnode: 79839c62.a4ced4select * from wp_users where 'user_login' = 'xxx' : msg : Object
object
payloadarray[0]
topic"select * from wp_users where 'user_login' = 'xxx'"
_msgid"d78c8679.ca7678”from the output of the mysql node.
back to basics:
What version of Node-Red?
what version of Node.js?
what version of NPM?
What mysql node are you using and what version?
Please export your flow and attach it to a reply


--
http://nodered.org
 
Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.

Dj Jastrzębowski

unread,
Oct 29, 2017, 9:15:33 AM10/29/17
to Node-RED
My flow


[{"id":"b7553f16.eefea","type":"function","z":"7500ab43.037454","name":"","func":"msg.payload = msg.payload[0].allow;\nreturn msg;","outputs":1,"noerr":0,"x":330,"y":320,"wires":[["646fb220.19880c"]]},{"id":"5e375ff3.93fc3","type":"function","z":"7500ab43.037454","name":"","func":"msg.payload = msg.payload[0].id;\nreturn msg;","outputs":1,"noerr":0,"x":350,"y":360,"wires":[[]]},{"id":"83a34ff4.dc4fc","type":"switch","z":"7500ab43.037454","name":"","property":"payload","propertyType":"msg","rules":[{"t":"nnull"},{"t":"regex","v":"[0]","vt":"str","case":false}],"checkall":"true","outputs":2,"x":190,"y":600,"wires":[["b7553f16.eefea","5e375ff3.93fc3"],[]]},{"id":"defc76ae.96c208","type":"mysql","z":"7500ab43.037454","mydb":"56dc1bd.3183de4","name":"OVH","x":90,"y":400,"wires":[["83a34ff4.dc4fc"]]},{"id":"c8229ca9.3e316","type":"function","z":"7500ab43.037454","name":"Check from database","func":"data = (msg.payload);\nvar m = {\ntopic : \"SELECT * FROM `users1` WHERE `id` = \" + data + \"\"} ;\nreturn m;","outputs":1,"noerr":0,"x":180,"y":180,"wires":[["defc76ae.96c208"]]},{"id":"1af66d56.c03e23","type":"inject","z":"7500ab43.037454","name":"","topic":"","payload":"400211","payloadType":"str","repeat":"","crontab":"","once":false,"x":90,"y":100,"wires":[["c8229ca9.3e316"]]},{"id":"56dc1bd.3183de4","type":"MySQLdatabase","z":"","host":"127.0.0.0","port":"3306","db":"1","tz":""}]

Zenofmud

unread,
Oct 29, 2017, 9:17:18 AM10/29/17
to node...@googlegroups.com
And the answers to the other questions??

On Oct 29, 2017, at 9:15 AM, Dj Jastrzębowski <dariuszjas...@gmail.com> wrote:

My flow


[{"id":"b7553f16.eefea","type":"function","z":"7500ab43.037454","name":"","func":"msg.payload = msg.payload[0].allow;\nreturn msg;","outputs":1,"noerr":0,"x":330,"y":320,"wires":[["646fb220.19880c"]]},{"id":"5e375ff3.93fc3","type":"function","z":"7500ab43.037454","name":"","func":"msg.payload = msg.payload[0].id;\nreturn msg;","outputs":1,"noerr":0,"x":350,"y":360,"wires":[[]]},{"id":"83a34ff4.dc4fc","type":"switch","z":"7500ab43.037454","name":"","property":"payload","propertyType":"msg","rules":[{"t":"nnull"},{"t":"regex","v":"[0]","vt":"str","case":false}],"checkall":"true","outputs":2,"x":190,"y":600,"wires":[["b7553f16.eefea","5e375ff3.93fc3"],[]]},{"id":"defc76ae.96c208","type":"mysql","z":"7500ab43.037454","mydb":"56dc1bd.3183de4","name":"OVH","x":90,"y":400,"wires":[["83a34ff4.dc4fc"]]},{"id":"c8229ca9.3e316","type":"function","z":"7500ab43.037454","name":"Check from database","func":"data = (msg.payload);\nvar m = {\ntopic : \"SELECT * FROM `users1` WHERE `id` = \" + data + \"\"} ;\nreturn m;","outputs":1,"noerr":0,"x":180,"y":180,"wires":[["defc76ae.96c208"]]},{"id":"1af66d56.c03e23","type":"inject","z":"7500ab43.037454","name":"","topic":"","payload":"400211","payloadType":"str","repeat":"","crontab":"","once":false,"x":90,"y":100,"wires":[["c8229ca9.3e316"]]},{"id":"56dc1bd.3183de4","type":"MySQLdatabase","z":"","host":"127.0.0.0","port":"3306","db":"1","tz":""}]


--
http://nodered.org
 
Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+u...@googlegroups.com.
To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

Dj Jastrzębowski

unread,
Oct 29, 2017, 10:55:51 AM10/29/17
to Node-RED

What version of Node-Red?- 
what version of Node.js?
what version of NPM?
What mysql node are you using and what version?


29 Oct 15:53:07 - [info] Node-RED version: v0.17.4
29 Oct 15:53:07 - [info] Node.js  version: v4.8.2
npm --version 1.4.21
node-red-node-mysql 0.0.16


Dj Jastrzębowski

unread,
Oct 29, 2017, 11:33:08 AM10/29/17
to Node-RED
It is bug ? Should by "null"

Regards,
Dariusz

Nick O'Leary

unread,
Oct 29, 2017, 1:05:43 PM10/29/17
to Node-RED Mailing List
Hi Dj,

can you confirm which MySQL nodes you are using? Everyone on this thread has assumed you are using node-red-contrib-mysql, whose help says it returns null for an empty result.

But there is also node-red-node-mysql which provides the type of node the flow you've shared actually uses.

So lets assume that is what you're using. In which case an empty array would be the expected result of a query that returned no results. The question is then how to test for that.

In the Switch node, for the "property" field, select 'Expression' from the dropdown list, and set its value to $count(payload)
That will be the number of results msg.payload contains. You can then add rules for  == 0 and > 0 to branch your flow accordingly.

Nick

--
http://nodered.org
 
Join us on Slack to continue the conversation: http://nodered.org/slack
---
You received this message because you are subscribed to the Google Groups "Node-RED" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-red+unsubscribe@googlegroups.com.

To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.

Dj Jastrzębowski

unread,
Oct 29, 2017, 1:19:50 PM10/29/17
to Node-RED

I use this 


Dj Jastrzębowski

unread,
Oct 29, 2017, 3:11:19 PM10/29/17
to Node-RED


Thanks Nick,

Its Working  :-) 

steve rickus

unread,
Oct 30, 2017, 10:07:40 AM10/30/17
to Node-RED
Dj, I don't see how this could be working for all cases... msg.payload is an "array", which cannot be compared directly to a number. You need to compare the length of the array, which is what Nick suggested.

To follow Nick's suggestion, on the "Property" type pulldown select the "J:" option (to use a JSONata expression), and put "$count(payload)" in the box to the right of it. Then change your rules to check "==" 0 and ">" 0 (you have the a/z option shown, which is the string "0" -- so change that to be 0/9 for a number)
Reply all
Reply to author
Forward
0 new messages