Store and retrieve an array from database

General
1

Hi,

I have a flow that retrieves data (numeric) values (code to put on or off a light by rf commands and store these codes into a databse mysql and retrieve them when needed, for example to turn a device on/off.

I have all parts working accept when retrieving the code from the database and make an array of it.Problem is that my code makes every array element a string, and each element must be an numeric value. I had this working in an older version with the split function, but cant get it working anymore...

Here my code and my script/flow

screendump data
(data2 = data stored as varchar string in database
data is my conversion to an array, but each value is string instead of numeric)

Naamloos
Naamloos3063×1800 150 KB

flow

[{"id":"5f7be13b.16abb","type":"tab","label":"Flow 1","disabled":false,"info":""},{"id":"113487c.10c8a78","type":"debug","z":"5f7be13b.16abb","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":730,"y":140,"wires":[]},{"id":"37c5079.617fb78","type":"function","z":"5f7be13b.16abb","name":"select statement","func":"\nmsg.topic = \"Select * from usblamp where optieid=0;\"\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":360,"y":220,"wires":[["2d58922.ed60e6e"]]},{"id":"c074d809.96f8b8","type":"function","z":"5f7be13b.16abb","name":"uitpakken device","func":"\nmsg.payload={\n    //\"mac\":\"MAC address of your broadlink rm\",\n    //\"host\":\"IP address of your broadlink rm\",\n    \"action\":\"send\",\n    \"data\":msg.payload[0].data.split(','),\n    \"data2\":msg.payload[0].data,\n    \"repeat\":2\n};\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":730,"y":220,"wires":[["113487c.10c8a78","3e8662f4.d700be"]]},{"id":"2d58922.ed60e6e","type":"mysql","z":"5f7be13b.16abb","mydb":"cde7dc1.620b42","name":"database","x":520,"y":220,"wires":[["c074d809.96f8b8"]]},{"id":"3e8662f4.d700be","type":"function","z":"5f7be13b.16abb","name":"usb aan","func":"msg.payload={\n    //\"mac\":\"MAC address of your broadlink rm\",\n    //\"host\":\"IP address of your broadlink rm\",\n    \"action\":\"send\",\n    \"data\":msg.data,\n    \"repeat\":2\n};\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":920,"y":220,"wires":[[]]},{"id":"cd4c0e29.fcf08","type":"inject","z":"5f7be13b.16abb","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":190,"y":220,"wires":[["37c5079.617fb78"]]},{"id":"cde7dc1.620b42","type":"MySQLdatabase","name":"pvknew","host":"192.168.20.38","port":"6603","db":"pvknew","tz":"","charset":"UTF8"}]
2
  1. what version of NR and node.js? Check startup log
  2. what version of the mysql Are you using
  3. please provide the schema for the table.
  4. you say it was working. What changed?

Note: you can build an sql statement in a inject node or template node - you don’t need to use a function node in this case.

3

I received the solution for converting my string array to a number array from another forum. I would like to share this for "others"

var data = msg.payload[0].data.split(',');
for(var i=0, len=data.length; i<len; i++){
data[i] = parseInt(data[i], 10);

@zenofmud
for defining the query for my sql i now use the function node as part of a flow that starts with a dashboard.
I will take a look at the template node, i think indeed this is a better option for defining the queries! thanks!

function node usage
msg.topic = "Select * from usblamp where optieid=" + flow.get("usblamp_levelid") + " and device='" + flow.get("usblamp_device") + "';"

return msg;

4

If the column is of number type in the database then I would have expected it to returned as a number. Is that not happening?

5

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.