How to insert in a database?

i just created a new database and i'd like to insert data coming from a "http Request "that output is a parsed json object but i d on't know how to doCan you help me insert these data ?
i created my sql node and connect is ok. but after i dont know what to do

Assuming that it's a MySQL or Mariadb database, you need something like this, maybe in a function.

msg.payload = {temp:[0].temp, hum:[0].hum};
msg.topic = "INSERT INTO tablename (temp, hum) VALUES(:temp, :hum)";
return msg; 

Obviously use the proper table and field names.
You can get the right path for each data value from the debug panel "Copy path" button.

Hello it work perfectly. Thanks a lot :wink:
one more question . i would like to modiffy this value before insert in database because temperature is in farenheit and i would like it in celsius . i have to do something like ("value"-32)/1.8
Can you say me how to do ?

So at the top of the function node that sets up your SQL insert statement, define a function like this:

const FtoC = fahrenheit => (fahrenheit - 32) * 5 / 9;

And you use it by (for example) FtoC([0].temp)

(I don't really understand the syntax, I just copied from )

I think I can explain the maths.

The centigrade scale goes from 0 to 100 degrees C (freezing to boiling point)
Obviously other values are available.
The fahrenheit scale goes from 32 to 212 degrees F (freezing to boiling point).
So there are 100 increments in the centigrade scale and 180 increments (i.e. 212 - 32) in the F scale.
So if you have a value in F and want to convert it to C, you need to subtract 32 and then multiply by 100/180 (which is 5/9 when reduced to its lowest common denominator).

It's not so much the maths I don't grasp, it's the => operator !

i have a result but 2 troubles.
First how can i round it ?
second wich payload must i set because i ve an error


VALUES(:temp) assumes that your temperature value is in msg.payload.temp.

You need msg.payload.temp = FtoC( etc )

There was a thread in the forum very recently about rounding values.

For round i found a solution: in database i set the round;

i d like to insert also time that i format with a node , formatting is working well and can be send in database but when i want to add it with the current node 'temp' it doesn't work.
how can i add more sensors and data because i can't add it with the FtoC const ?

I am not sure I understand the question. However this bit of your screen capture
Untitled 2
looks wrong to me.

What you are doing is constructing a "Prepared Query". msg.payload contains an object whose elements are named in the INSERT statement. msg.topic contains the INSERT statement.
So an example

msg.payload = {temperature: FtoC([0].temp), humidity: 92};  // NB this replaces the previous contents of msg.payload
msg.payload.windspeed = 18;             // Since msg.payload is an object, you can create a new element like this
msg.payload.winddirection = "NNE";
msg.topic = "INSERT INTO observations (temperature, humidity, wind_speed, wind_direction) VALUES (:temperature, :humidity: :windspeed, :winddirection)"

Sorry, I do not understand this question. Can you explain more please?
It would help if you share the structure of your database table, an export of your flow and the actual data that you get from your weather station. It's difficult when all we have to go on is pictures.

1 Like

i don't understand but it ddoesn' work

each one separately it work but not both

I think you might have missed a comma after :humidity ??
There seems to be a : at the end of humidity rather than a ,

this was just an exemple for mine i have comma

If you find that syntax tricky to understand (like many of us) then I recommend sticking to the more simple syntax, which is easier to understand. So instead of the above, put this at the end of the function node (after return msg)

function FtoC( fahrenheit ) {
  return (fahrenheit - 32) * 5 / 9

Then use it exactly the same way as with the more compact syntax.

1 Like

Yes. It's the heat! :joy:

1 Like

Same here - my fingers keep sticking to the keys.

Untitled 3

This is a number: 14.7
This is a Javascript object, also known as a JSON object: {temp: 14.7}

Msg.payload.temp and msg.payload.hum should be numbers and you are making them objects.

Finally i have to change the ''{" with "(" and now it work.

you helped me so much this afternoon thanks a lot.

1 Like

Oh wow, that goes against years of practise in other scripting languages!
Thanks for providing the more intuitive function definition.

1 Like

In fact I just said after it as I find that the most convenient, it can go at the front if you prefer, but I find it gets in the way there and makes it more difficult to follow what is going on.

1 Like