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.
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.
"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"; "
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?
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
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.
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.
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.
