MySQL NODE-RED table

General
21

i want sen 3 messages, the values of Inductive sensor, LM35 sensor and LM335 sensor

22

I have this in the debug

image
image.png1045×712 45 KB

and my function to database is
image
image.png1405×231 11.1 KB

Sensores is the portuguese translation of Sensors

23

Hi @smalhao

as you have changed the Join node to generate a key/value object, the msg.payload[0] way of accessing its properties is no longer the right way to do it.

This page in the docs explains how you can use the Debug sidebar to identify how to access different parts of a message: https://nodered.org/docs/user-guide/messages

If you look at the Debug message, you can see msg.payload is no longer an array, but an Object. It has three properties:

{
   "ns=2;s=LM35": 23.0550....,
   "ns=2;s=SensorIndutivo": 7514.689.....,
   "ns=2;s=LM335": 22.2104...
}

So you would access those properties as:

msg.payload["ns=2;s=LM35"]
msg.payload["ns=2;s=SensorIndutivo"]
msg.payload["ns=2;s=LM335"]

in your Function node, in place of msg.payload[0], msg.payload[1] and msg.payload[2].

24

I was making a small program to test more easily and that was as follows:

image
image.png1188×356 31.1 KB

The configuration of each is :

image
image.png638×714 18.7 KB

The configuration of Joins is:

image
image.png623×715 15.5 KB

The function is :slight_smile:
var newmsg=new Date().toString();
msg.topic= "INSERT INTO testebase VALUES ('"+new Date().toString()+"','"+msg.payload[0].toFixed(2)+"','"+msg.payload[1].toFixed(2)+"','"+msg.payload[2].toFixed(2)+"')";
return msg;

The debug output of function is :
image

and send the data correctly to database. When i apply the same method in other program i want (yesterday )programming don´t show the array with the values correctly.

25

Why have you changed the Join back to array mode? You need it to be the key/value object.

In array mode, it will join each 3 messages it receives into the array - regardless of the order they arrive in.

So if you have three sensors, A, B and C, and you get a stream of messages:

A B C B A B B C A

You will end up with [A,B,C], [B,A,B], [B,C,A].

By using the key/value object join mode, it will use msg.topic to ensure each message has one message from each topic it receives.

26

The Join configuration I have
is as follows:

image
image.png637×690 15.7 KB

the output of Join is:

image
image.png439×727 24.4 KB

When i change to key/value Object

image
image.png640×684 19.6 KB

the output of Join is

image
image.png433×752 25 KB

and i need to send the value of sensor to database, but in database the values appears in that way

image
image.png860×585 63 KB

the values 1360.30.... are meters, 20, 00 are the temperature of sensor LM335 and 20.74 are the temperature of sensor LM35 and i need to put them in column correctly.

27

What does your function node that is generating the query look like?

Please don't screenshot it. Copy the code and paste it here - making sure you've read this post How to share code or flow json about how to properly share code on this forum.

28

in the function to send data to database i configured that way:

var newmsg=new Date().toString();
msg.topic="INSERT INTO Sensores VALUES ('"+ new Date().toString() +"','"+ msg.payload[0] +"','"+ msg.payload[1] +"','"+ msg.payload[2] +"')";
return msg;

i think i missing something in the function i think

29

Did you read this reply from me? MySQL NODE-RED table

It explained how you needed to change your query to access the properties of msg.payload after changing the Join node configuration.

1 Like
30

Put the query like this :

var newmsg=new Date().toString();
msg.topic="INSERT INTO Sensores VALUES ('"+ new Date().toString() +"','"+ msg.payload["ns=2;s=SensorIndutivo"] +"','"+ msg.payload["ns=2;s=LM335"] +"','"+ msg.payload["ns=2;s=LM35"] +"')";
return msg;
31

You need to understand the difference between an object and an array in javascript.
It is worth spending 30minutes reading one of the many online tutorials.
After that also read the following https://nodered.org/docs/user-guide/messages

32

Okay. And did that work? What output did that generate? Did you use the Debug node to see what the query it generated?

33

Its working, thanks for your help knolleary

06/02/2019, 11:27:15node: output of function
INSERT INTO Sensores VALUES ('Wed Feb 06 2019 11:27:01 GMT+0000 (GMT)','1899.0927590950298','20.44695023999293','21.19148479999994') : msg.payload : Object
{ ns=2;s=LM35: 21.19148479999994, ns=2;s=LM335: 20.44695023999293, ns=2;s=SensorIndutivo: 1899.0927590950298 }

the database is now correctly

image
image.png870×239 28.7 KB

1 Like
34

if i need to check each one of topics if they are sending the data, how i coud make that in a function of switch case ?