NodeRed MySQL if record not found in database

[Dj Jastrzębowski]

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]

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]

Thanks for you replay Paul,

Unfortunately it does not work and I do not know why :-(

[Colin Law]

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.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/fcafa73f-3d5d-4a29-9580-972f5a26b199%40googlegroups.com.

[Dj Jastrzębowski]

Debug directly connected to database say

 

Regards,

Darek

[Colin Law]

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.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/bd867031-5a70-484d-90d5-290185aaee0a%40googlegroups.com.

[Dj Jastrzębowski]

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

[Zenofmud]

Interesting because I get 

10/29/2017, 8:39:06 AMnode: 79839c62.a4ced4select * from wp_users where 'user_login' = 'xxx' : msg : Object

object

payload: array[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]

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]

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.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/58fb26c8-5959-4a84-b8ca-bed17707df38%40googlegroups.com.

[Dj Jastrzębowski]

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]

It is bug ? Should by "null"

Regards,

Dariusz

[Nick O'Leary]

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.

To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/caf17ac7-5aa9-407c-b754-2737420a216c%40googlegroups.com.

[Dj Jastrzębowski]

I use this 

[Dj Jastrzębowski]

Thanks Nick,

Its Working  :-) 

[steve rickus]

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)