MySQL COUNT with Node-Red

[PhilLu1]

Hello,
I try to use COUNT with Node-Red on a MySQL DB.
Query is perfectly working on MySQL PHP My Admin...

SELECT COUNT(*) AS nombre FROM `loinc_be_fr_linguisticvariant` WHERE `SYSTEM` LIKE 'Isolat' AND `CLASS` LIKE 'Microbiologie' AND `SCALE_TYP` LIKE 'Nominal' AND `TIME_ASPCT` LIKE 'Temps ponctuel';

return:
nombre 96

... but not with Node-Red

This is the code:

var value1 = msg.payload[0].SYSTEM; 
var value2 = msg.payload[1].CLASS; 
var value3 = msg.payload[2].SCALE_TYP; 
var value4 = msg.payload[3].TIME_ASPCT; 
msg.topic = 'SELECT COUNT(*) AS nombre FROM `loinc_be_fr_linguisticvariant` WHERE `SYSTEM` LIKE "' + value1 + '" AND `CLASS` LIKE "' + value2 + '"AND `SCALE_TYP` LIKE "' + value3 + '"AND `TIME_ASPCT` LIKE "' + value4 + '"; '; 
return msg;

Returned message is an Array[4] and not a number of records !
Thanks for your help !
Phil.

[ralphwetzel]

@PhilLu1

msg.topic = 'SELECT COUNT(*) AS nombre FROM `loinc_be_fr_linguisticvariant` WHERE `SYSTEM` LIKE "' + value1 + '" AND `CLASS` LIKE "' + value2 + '"AND `SCALE_TYP` LIKE "' + value3 + '"AND `TIME_ASPCT` LIKE "' + value4 + '"; '; 

Edit: Following the Node-RED standard, I'm pretty sure, you have to put your query string into msg.payload. Never stop learning! Thank you @jbudd ...

What you received - as an Array[4] - most probably are your values of [SYSTEM, CLASS, SCALE_TYP, TIME_ASPCT].

[jbudd]

@ralphwetzel, for node-red-node-mysql, the query has to be in msg.topic.

Hi @PhilLu1, welcome to the forum.

Can you use a debug node to show us the msg.topic passed to MySQL, also the contents of the returned array?

I would expect the result from MySQL to look like this

[PhilLu1]

... I have to receive COUNT result into a Dashboard TEXT

{{msg.topic.nombre}}

... but not work

[PhilLu1]

yes... I receive SYSTEM, CLASS, SCALE_TYP, TIME_ASPCT
How receive 'nombre' from "AS nombre" ?

[jbudd]

@PhilLu1

I have to receive COUNT result into a Dashboard TEXT

{{msg.topic.nombre}}

You didn't provide the data I asked for but nevertheless I'll point out that the mysql node returns the query in msg.topic and the result in msg.payload.

[PhilLu1]

This is what the debug contains...

SELECT COUNT() FROM `loinc_be_fr_linguisticvariant` WHERE `SYSTEM` LIKE "Urine" AND `CLASS` LIKE "Chimie"AND `SCALE_TYP` LIKE "Quantitatif"AND `TIME_ASPCT` LIKE "Temps ponctuel"; : msg.payload : array[4]
array[4]
0: object
SYSTEM: "Urine"
1: object
CLASS: "Chimie"
2: object
SCALE_TYP: "Quantitatif"
3: object
TIME_ASPCT: "Temps ponctuel"

with msg.topic =

[PhilLu1]

... and

"SELECT COUNT() FROM `loinc_be_fr_linguisticvariant` WHERE `SYSTEM` LIKE "Urine" AND `CLASS` LIKE "Chimie"AND `SCALE_TYP` LIKE "Quantitatif"AND `TIME_ASPCT` LIKE "Temps ponctuel"; "

... with msg.payload =

[jbudd]

@PhilLu1

SELECT COUNT() FROM `loinc_be_fr_lingui...

You are missing an asterisk in the query.

[ralphwetzel]

@PhilLu1

@jbudd

You didn't provide the data I asked for

We need to guess too much to be efficient in supporting you.
Why don't you share your flow - at least the relevant part of it - to allow us to examine the details?

[E1cid]

I would think it would be more like this

Select count(*)
From
(
   SELECT * 
   FROM `loinc_be_fr_linguisticvariant` 
   WHERE    `SYSTEM` LIKE "Urine" AND 
            `CLASS` LIKE "Chimie" AND 
            `SCALE_TYP` LIKE "Quantitatif" AND 
            `TIME_ASPCT` LIKE "Temps ponctuel"
) as nombre

But not a sql expert.

[jbudd]

Ummm. Don't think so, sorry!

The OP has already pointed out that his query works in phpMyAdmin.

The problem in Node-red appears to be the missing asterisk in
select count(*) from ...

[PhilLu1]

I try with and without...

[jbudd]

@PhilLu1

I try with and without

If you try the version without the asterisk you should get an error.

Show us what you do get?

Try running this flow. Obviously you will need to change the db node to match your database.
Check that the three queries are all benign SELECTs before touching your live database of course.

[{"id":"bf05578e63864ed4","type":"tab","label":"Test MySQL Select","disabled":false,"info":"","env":[]},{"id":"b51cf4e87553bbce","type":"inject","z":"bf05578e63864ed4","name":"","props":[{"p":"foo","v":"select count(*) as nombre FROM loinc_be_fr_linguisticvariant","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":110,"y":40,"wires":[["7a595d649a8e609a"]]},{"id":"fcaa1d889fa0ce64","type":"mysql","z":"bf05578e63864ed4","mydb":"2381cb15.b563bc","name":"","x":670,"y":40,"wires":[["a66b312990a2ccbd"]]},{"id":"7a595d649a8e609a","type":"change","z":"bf05578e63864ed4","name":"select count(*) from","rules":[{"t":"set","p":"topic","pt":"msg","to":"select count(*) as nombre1 FROM loinc_be_fr_linguisticvariant","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":130,"y":100,"wires":[["01b3fb4a4a4596f9"]]},{"id":"db427d1ddc4a46d9","type":"change","z":"bf05578e63864ed4","name":"select count(*) from where","rules":[{"t":"set","p":"topic","pt":"msg","to":"select count(*) as nombre2 FROM loinc_be_fr_linguisticvariant WHERE SYSTEM LIKE \"Urine\"","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":150,"y":160,"wires":[["ccbce48b144b7ad1"]]},{"id":"75f8d08fc4cbd614","type":"change","z":"bf05578e63864ed4","name":"select count(*) from ... where ... AND ... AND ... AND","rules":[{"t":"set","p":"topic","pt":"msg","to":"select count(*) as nombre3 FROM loinc_be_fr_linguisticvariant WHERE SYSTEM LIKE \"Urine\" AND CLASS LIKE \"Chimie\" AND SCALE_TYP LIKE \"Quantitatif\" AND TIME_ASPC LIKE \"Temps ponctuel\"","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":240,"y":220,"wires":[["7b2a8af716894eb7"]]},{"id":"01b3fb4a4a4596f9","type":"link call","z":"bf05578e63864ed4","name":"","links":["accc1509a0245556"],"linkType":"static","timeout":"30","x":310,"y":100,"wires":[["db427d1ddc4a46d9","1b956ce9d671afe6"]]},{"id":"accc1509a0245556","type":"link in","z":"bf05578e63864ed4","name":"Run Query","links":[],"x":565,"y":40,"wires":[["fcaa1d889fa0ce64"]]},{"id":"a66b312990a2ccbd","type":"link out","z":"bf05578e63864ed4","name":"link out 9","mode":"return","links":[],"x":795,"y":40,"wires":[]},{"id":"ccbce48b144b7ad1","type":"link call","z":"bf05578e63864ed4","name":"","links":["accc1509a0245556"],"linkType":"static","timeout":"30","x":350,"y":160,"wires":[["75f8d08fc4cbd614","d270134f5d9dd223"]]},{"id":"7b2a8af716894eb7","type":"link call","z":"bf05578e63864ed4","name":"","links":["accc1509a0245556"],"linkType":"static","timeout":"30","x":530,"y":220,"wires":[["2a5db5fb41997a69"]]},{"id":"1b956ce9d671afe6","type":"debug","z":"bf05578e63864ed4","name":"nombre1","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":480,"y":100,"wires":[]},{"id":"d270134f5d9dd223","type":"debug","z":"bf05578e63864ed4","name":"nombre2","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":520,"y":160,"wires":[]},{"id":"2a5db5fb41997a69","type":"debug","z":"bf05578e63864ed4","name":"nombre3","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":700,"y":220,"wires":[]},{"id":"2381cb15.b563bc","type":"MySQLdatabase","name":"db","host":"127.0.0.1","port":"3306","db":"espresso","tz":"","charset":"UTF8"}]

Expand all 3 debug outputs to look something like this, and show us. (My example queries access different fields, table and database). Also show us any errors please.

[shrickus]

IIRC, the SQL nodes always return an array of "results" -- usually representing rows of object data...

Since you requested just a count() of the rows, your answer is the number 4 in an array of 1 resulting row. This is working as intended -- in fact, even the SQL command line returns a record count of 1 when you ask for a simple row count.

[jbudd]

Hmm. If @PhilLu1 was prepared to show us what result he/she gets we could be more certain, but msg.payload ought to contain an array with one element {nombre: nnn}

I suspect they have stopped looking at this thread.

[PhilLu1]

no, I'm there

[PhilLu1]

I try also with COUNT(*)...

[jw_te_r]

If I use this statement for MyData:

var newMsg = {
"topic" : "SELECT COUNT(*) as aantal FROM `MyData`;"
}
return newMsg;

I can read my result from payload[0].aantal.

[anka001]

Don't know if it is the root cause, but your querystring lacks some blanks. Instead of

'"AND `SCALE_TYP` LIKE "'

it should be

'" AND `SCALE_TYP` LIKE "'

same thing here

'"AND `TIME_ASPCT` LIKE "'