# Store and retrieve an array from database

**URL:** <https://discourse.nodered.org/t/store-and-retrieve-an-array-from-database/46189>\
**Category:** General\
**Created:** [25 May 2021 07:03 UTC](https://discourse.nodered.org/t/store-and-retrieve-an-array-from-database/46189 "2021-05-25T07:03:01Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![pvklink](https://avatars.discourse-cdn.com/v4/letter/p/46a35a/32.png) [@pvklink](https://discourse.nodered.org/u/pvklink)\
**Post date:** [25 May 2021 07:03 UTC](https://discourse.nodered.org/t/store-and-retrieve-an-array-from-database/46189/1 "2021-05-25T07:03:01Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/9/398f6f6c18d4ba9c71967c09037811f203ec1955.jpeg)

flow

```auto
[{"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"}]

```

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [25 May 2021 08:01 UTC](https://discourse.nodered.org/t/store-and-retrieve-an-array-from-database/46189/2 "2021-05-25T08:01:32Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![pvklink](https://avatars.discourse-cdn.com/v4/letter/p/46a35a/32.png) [@pvklink](https://discourse.nodered.org/u/pvklink)\
**Post date:** [25 May 2021 10:31 UTC](https://discourse.nodered.org/t/store-and-retrieve-an-array-from-database/46189/3 "2021-05-25T10:31:49Z")

</div>

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;

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [27 May 2021 07:58 UTC](https://discourse.nodered.org/t/store-and-retrieve-an-array-from-database/46189/4 "2021-05-27T07:58:13Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [10 June 2021 07:58 UTC](https://discourse.nodered.org/t/store-and-retrieve-an-array-from-database/46189/5 "2021-06-10T07:58:42Z")

</div>

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