# Problem getting Value from SQL SELECT

**URL:** <https://discourse.nodered.org/t/problem-getting-value-from-sql-select/31761>\
**Category:** Dashboard\
**Created:** [20 August 2020 07:54 UTC](https://discourse.nodered.org/t/problem-getting-value-from-sql-select/31761 "2020-08-20T07:54:29Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Chorum](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/chorum/32/47644_2.png) [@Chorum](https://discourse.nodered.org/u/Chorum)\
**Post date:** [20 August 2020 07:54 UTC](https://discourse.nodered.org/t/problem-getting-value-from-sql-select/31761/1 "2020-08-20T07:54:30Z")

</div>

Hello,

i would like to read a value from a SQL database and use the value in my flow. its my first attempt to do that and i have a Problem, Maybe based on msg structure misunterdstanding.

Here is a Picture of the part of the Flow:

![flow](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/7/27c499486f274149d825cc05d258e2a79d8357c2.png)

Here the part of the flow:

```auto
[{"id":"17fd4fda.6273f","type":"function","z":"af61c022.bc96e","name":"SQL Read","func":"var sql1 = \"SELECT Getriebe_Betriebsstunden \";\nvar sql2 = \"FROM aktuelle_alex_daten \";\nvar sql3 = \"WHERE Name_Pruefstand =\";\nvar sql4 = \"'\"+\"W03\"+\"'\"\n\n\n\n\nmsg.topic = sql1 + sql2 + sql3 + sql4;\nreturn msg;","outputs":1,"noerr":0,"x":1030,"y":1020,"wires":[["6853a388.430adc"]]},{"id":"e4debac5.f4a8e8","type":"function","z":"af61c022.bc96e","name":"Payload Weitergabe","func":"msg.topic = \"ATESTEO/Wolfsburg/W03\";\nmsg.payload.Zeitausgabe = msg.payload.Getriebe_Betriebsstunden;\nreturn msg;","outputs":1,"noerr":0,"x":1510,"y":1020,"wires":[[]]},{"id":"6853a388.430adc","type":"mysql","z":"af61c022.bc96e","name":"","x":1290,"y":1020,"wires":[["e4debac5.f4a8e8"]]}]

```

i changed the mysql node, because i dont want to post the real one.

Here is the output of the SQL Read function node:  
 ![sql read output](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/6/869a8e59507018d07cbc7c07e0401e7db89be259.png)

Here is the output of the mysql node:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/3/3372c90d9b6ce500f6ead8d71f48b3b841547ddf.png)

And here is the output of the last Payload Weitergabe node:  
 ![payload Weitergabe output](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/1/3188e3b68208dfa59ff05c0cd77c3f093cd62144.png)

I have problems to get the value of Getriebe\_Betriebsstunden in a variable.  
I tried a Change node and some other things but Nothing works...

Maybe someone has a solution for this ?

Chorum

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [20 August 2020 08:03 UTC](https://discourse.nodered.org/t/problem-getting-value-from-sql-select/31761/2 "2020-08-20T08:03:07Z")

</div>

The payload is an array with an object, you can retrieve it like:

```auto
msg.payload[0].Getriebe_Betriebsstunden

```

---

<div class="post-metadata">

**Author:** ![Chorum](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/chorum/32/47644_2.png) [@Chorum](https://discourse.nodered.org/u/Chorum)\
**Post date:** [20 August 2020 08:06 UTC](https://discourse.nodered.org/t/problem-getting-value-from-sql-select/31761/3 "2020-08-20T08:06:53Z")

</div>

Hi,

on of my attempts was msg.payload.Zeitausgabe = msg.payload.0.Getriebe\_Betriebsstunden; but didnt work 🙂

sadly msg.payload.Zeitausgabe = msg.payload[0].Getriebe\_Betriebsstunden; doesnt give me the value ☹

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [20 August 2020 08:14 UTC](https://discourse.nodered.org/t/problem-getting-value-from-sql-select/31761/4 "2020-08-20T08:14:18Z")

</div>

It does give you the value.

 ![Screenshot 2020-08-20 at 10.13.22](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/8/48384269d908e598c70ebf61b7ef6029ec00ecbb.jpeg)

What does your output look like ?

---

<div class="post-metadata">

**Author:** ![Chorum](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/chorum/32/47644_2.png) [@Chorum](https://discourse.nodered.org/u/Chorum)\
**Post date:** [20 August 2020 08:17 UTC](https://discourse.nodered.org/t/problem-getting-value-from-sql-select/31761/5 "2020-08-20T08:17:03Z")

</div>

if i use :  
msg.payload.Zeitausgabe = msg.payload[0].Getriebe\_Betriebsstunden;

i get :  
 ![payload Weitergabe output2](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/0/8035e0cca0b9b3a0e8c8053a115126013529a70e.png)

so Nothing changed…

My hope was for:  
Zeitausgabe = 00:01

🙂

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [20 August 2020 08:20 UTC](https://discourse.nodered.org/t/problem-getting-value-from-sql-select/31761/6 "2020-08-20T08:20:01Z")

</div>

Can you post you full flow with some dummy input ? (ie. dont use the mysql nodes as we cannot replicate).

ah no you cannot set it to:

msg.payload.Zeitausgabe because msg.payload is already an array. If you set it to msg.Zeitausgabe or remove msg.payload first.

---

<div class="post-metadata">

**Author:** ![Chorum](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/chorum/32/47644_2.png) [@Chorum](https://discourse.nodered.org/u/Chorum)\
**Post date:** [20 August 2020 08:24 UTC](https://discourse.nodered.org/t/problem-getting-value-from-sql-select/31761/7 "2020-08-20T08:24:59Z")

</div>

sorry but its to big to post, so i have to post that part.

what i can give is a bigger overview:

 ![überblick](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/a/1a0219553d659da4bbdf655bb070645cf939b0a3.png)

---

<div class="post-metadata">

**Author:** ![bakman2](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/bakman2/32/6207_2.png) [@bakman2](https://discourse.nodered.org/u/bakman2)\
**Post date:** [20 August 2020 08:25 UTC](https://discourse.nodered.org/t/problem-getting-value-from-sql-select/31761/8 "2020-08-20T08:25:48Z")

</div>

See previous response:

You cannot set it to msg.payload.Zeitausgabe because msg.payload is already an array.  
Set it msg.Zeitausgabe or remove msg.payload first.

---

<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:** [20 August 2020 08:33 UTC](https://discourse.nodered.org/t/problem-getting-value-from-sql-select/31761/9 "2020-08-20T08:33:06Z")

</div>

You should be able to use  
`msg.payload = {Zeitausgabe: msg.payload[0].Getriebe_Betriebsstunden}`

---

<div class="post-metadata">

**Author:** ![Chorum](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/chorum/32/47644_2.png) [@Chorum](https://discourse.nodered.org/u/Chorum)\
**Post date:** [20 August 2020 08:38 UTC](https://discourse.nodered.org/t/problem-getting-value-from-sql-select/31761/10 "2020-08-20T08:38:50Z")

</div>

So, i used :

```auto
msg.topic = "ATESTEO/Wolfsburg/W03";
var Buffer;

Buffer = msg.payload[0].Getriebe_Betriebsstunden;
delete msg.payload
msg.Zeitausgabe = Buffer;

return msg;

```

so i get rid of the Array too..

Thank you both for your help!!!

---

<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:** [20 August 2020 08:50 UTC](https://discourse.nodered.org/t/problem-getting-value-from-sql-select/31761/11 "2020-08-20T08:50:27Z")

</div>

I advise against using a variable called Buffer, buffer is a keyword in javascript. In this case what you have will work ok, but it may cause confusion in the future. Also the convention in javascript is not to start variables with a capital letter, again it will not stop it working but the code will look strange to other developers.

---

<div class="post-metadata">

**Author:** ![Chorum](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/chorum/32/47644_2.png) [@Chorum](https://discourse.nodered.org/u/Chorum)\
**Post date:** [20 August 2020 08:53 UTC](https://discourse.nodered.org/t/problem-getting-value-from-sql-select/31761/12 "2020-08-20T08:53:18Z")

</div>

ok i will Change that.

i think i stumbled in an other problem but i will think about that first 🙂

---

<div class="post-metadata">

**Author:** ![Chorum](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/chorum/32/47644_2.png) [@Chorum](https://discourse.nodered.org/u/Chorum)\
**Post date:** [20 August 2020 11:34 UTC](https://discourse.nodered.org/t/problem-getting-value-from-sql-select/31761/13 "2020-08-20T11:34:05Z")

</div>

So here is my next Problem:  
i want to join my value from the SQL database with my original payload.  
i tried to build up the Payload like my original but it dont match exactly:

Screen of the actual part of the Flow:

 ![flowpic](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/f/ff6ef7405a6818b3871fdb0242178fa21306a459.png)

The actual part of the Flow:

```auto
[{"id":"6853a388.430adc","type":"mysql","z":"af61c022.bc96e","name":"","x":1290,"y":1020,"wires":[["3adb48a1.0c2aa8"]]},{"id":"3adb48a1.0c2aa8","type":"function","z":"af61c022.bc96e","name":"Payload Weitergabe","func":"msg.topic = \"ATESTEO/Wolfsburg/W03.1\";\nvar x;\n/*\nx = msg.payload[0].Getriebe_Betriebsstunden;\ndelete msg.payload\nmsg.topic = \"ATESTEO/Wolfburg/W03.1\";\nmsg.Zeitausgabe = x;\n*/\nx = msg.payload[0].Getriebe_Betriebsstunden;\ndelete msg.payload\n//msg.Zeitausgabe = x;\n\n\nmsg.x = x\n\nreturn msg;","outputs":1,"noerr":0,"x":1480,"y":1020,"wires":[["71c7bd7c.5a5e04"]]},{"id":"71c7bd7c.5a5e04","type":"change","z":"af61c022.bc96e","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"{}","tot":"json"},{"t":"set","p":"payload.Ausgabe","pt":"msg","to":"x","tot":"msg"},{"t":"delete","p":"x","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":1700,"y":1020,"wires":[["149c1737.f84a29"]]},{"id":"c9b6d92c.aa4b48","type":"function","z":"af61c022.bc96e","name":"original payload flow path","func":"\nreturn msg;","outputs":1,"noerr":0,"x":1670,"y":1050,"wires":[["149c1737.f84a29"]]},{"id":"149c1737.f84a29","type":"join","z":"af61c022.bc96e","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":true,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":1850,"y":1020,"wires":[[]]},{"id":"d95faa9a.23aec8","type":"function","z":"af61c022.bc96e","name":"SQL Read W03/W03.1","func":"var sql1 = \"SELECT Getriebe_Betriebsstunden \";\nvar sql2 = \"FROM aktuelle_alex_daten \";\nvar sql3 = \"WHERE Name_Pruefstand =\";\nvar sql4 = \"'\"+\"W03\"+\"'\"\n\n\n\n\nmsg.topic = sql1 + sql2 + sql3 + sql4;\nreturn msg;","outputs":1,"noerr":0,"x":1090,"y":1020,"wires":[["6853a388.430adc"]]}]

```

Created Payload:  
 ![eigenpayload](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/2/42db5fa0a311d439c95534805ed515d396e716e5.png)

part of the original payload (i cutted most values out of it, because of the length)  
 ![originalpayload1](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/b/ebb3a92521be8db6a7c45929e862d03f940a5132.png)  
 ![originalpayload2](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/b/ab744cb9de547bcfdf12d065fd88bb54f8ebffce.png)

What i can see is that the payload property has a different position in both msg's.

I would be happy if i could get help with that again 🙂 or is there a easier way to join my value to a msg ?

---

<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:** [20 August 2020 12:33 UTC](https://discourse.nodered.org/t/problem-getting-value-from-sql-select/31761/14 "2020-08-20T12:33:09Z")

</div>

The order of msg structure doesn’t matter. In both cases you have a msg.payload.

---

<div class="post-metadata">

**Author:** ![Chorum](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/chorum/32/47644_2.png) [@Chorum](https://discourse.nodered.org/u/Chorum)\
**Post date:** [20 August 2020 12:34 UTC](https://discourse.nodered.org/t/problem-getting-value-from-sql-select/31761/15 "2020-08-20T12:34:02Z")

</div>

So i do the joining in the wrong way?

---

<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:** [20 August 2020 12:39 UTC](https://discourse.nodered.org/t/problem-getting-value-from-sql-select/31761/16 "2020-08-20T12:39:27Z")

</div>

The `join1st node you show has a `function`node connected to it, but the`function` node has nothing connected to its input so it will never run

---

<div class="post-metadata">

**Author:** ![Chorum](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/chorum/32/47644_2.png) [@Chorum](https://discourse.nodered.org/u/Chorum)\
**Post date:** [20 August 2020 12:41 UTC](https://discourse.nodered.org/t/problem-getting-value-from-sql-select/31761/17 "2020-08-20T12:41:07Z")

</div>

I cannot post the complete flow, it is to big.  
so i cut out a part and posted the msg wich is coming from that "function node" -\> original msg.

---

<div class="post-metadata">

**Author:** ![Chorum](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/chorum/32/47644_2.png) [@Chorum](https://discourse.nodered.org/u/Chorum)\
**Post date:** [20 August 2020 13:12 UTC](https://discourse.nodered.org/t/problem-getting-value-from-sql-select/31761/18 "2020-08-20T13:12:33Z")

</div>

Update: After the join node, now i get 2 Messages with the same structure and order. the only difference is the payload. the single value is in one message and the original value pack is in the other.

i dont understand why i dont get a single message….

---

<div class="post-metadata">

**Author:** ![Chorum](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/chorum/32/47644_2.png) [@Chorum](https://discourse.nodered.org/u/Chorum)\
**Post date:** [21 August 2020 05:58 UTC](https://discourse.nodered.org/t/problem-getting-value-from-sql-select/31761/19 "2020-08-21T05:58:44Z")

</div>

Problem solved,

Here are the Join Node Options that helped:  
 ![joinsetup](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/e/ce490c49631c53270524033ba078b887a7b86fff.png)

---

<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:** [4 September 2020 05:58 UTC](https://discourse.nodered.org/t/problem-getting-value-from-sql-select/31761/20 "2020-09-04T05:58:46Z")

</div>

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