Problem getting Value from SQL SELECT

[Chorum]

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:

Here the part of the flow:

[{"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:

Here is the output of the mysql node:

And here is the output of the last Payload Weitergabe node:

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

[bakman2]

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

msg.payload[0].Getriebe_Betriebsstunden

[Chorum]

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

[bakman2]

It does give you the value.

What does your output look like ?

[Chorum]

if i use :
msg.payload.Zeitausgabe = msg.payload[0].Getriebe_Betriebsstunden;

i get :

so Nothing changed…

My hope was for:
Zeitausgabe = 00:01

[bakman2]

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.

[Chorum]

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

what i can give is a bigger overview:

[bakman2]

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.

[Colin]

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

[Chorum]

So, i used :

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!!!

[Colin]

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.

[Chorum]

ok i will Change that.

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

[Chorum]

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:

The actual part of the Flow:

[{"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:

part of the original payload (i cutted most values out of it, because of the length)

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 ?

[zenofmud]

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

[Chorum]

So i do the joining in the wrong way?

[zenofmud]

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

[Chorum]

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.

[Chorum]

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….

[Chorum]

Problem solved,

Here are the Join Node Options that helped: