How to save a value from a node to database

[kroonp]

Hello all,

I have a flow and in this flow a can save numbers in a sql database. I want te replace the numbers in the flow to dynamic numbers from sensor. I think a need tot do this with an array.

I've tried different ways but it doesn't work. Is there anyone who wants to indicate how I can achieve this, or perhaps has an example?

Thanks in advance,
Peter

[{"id":"8b4e6a14e81f8a68","type":"tab","label":"invoer in database","disabled":false,"info":"","env":[]},{"id":"39a046a3a92a5c7f","type":"inject","z":"8b4e6a14e81f8a68","name":"Go","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":"","topic":"","payload":"400","payloadType":"num","x":150,"y":125,"wires":[["6c2cdc1a2e01b7f3"]]},{"id":"6c2cdc1a2e01b7f3","type":"function","z":"8b4e6a14e81f8a68","name":"INSERT gas","func":"msg.topic = \"INSERT INTO meterstanden (gas) VALUES (300)\";\nmsg.payload = [300];\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":345,"y":100,"wires":[["3c11cc576ec8a049"]]},{"id":"1e7bc0f02695eb01","type":"debug","z":"8b4e6a14e81f8a68","name":"","active":true,"console":"false","complete":"false","x":830,"y":150,"wires":[]},{"id":"3c11cc576ec8a049","type":"mysql","z":"8b4e6a14e81f8a68","mydb":"a351da32cbcc0b60","name":"","x":630,"y":150,"wires":[["1e7bc0f02695eb01"]]},{"id":"57068c50e1424f0c","type":"inject","z":"8b4e6a14e81f8a68","name":"Go","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":"","topic":"","payload":"400","payloadType":"num","x":150,"y":175,"wires":[["7c03990938557fdd","766622559d9a88ab"]]},{"id":"7c03990938557fdd","type":"function","z":"8b4e6a14e81f8a68","name":"INSERT gas","func":"msg.topic = \"INSERT INTO meterstanden (gas) VALUES (555)\";\nmsg.payload = [350];\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":345,"y":150,"wires":[["3c11cc576ec8a049"]]},{"id":"766622559d9a88ab","type":"function","z":"8b4e6a14e81f8a68","name":"SELECT Bind","func":"msg.topic = \"SELECT * FROM meterstanden \";\nmsg.payload = [msg.payload];\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":345,"y":200,"wires":[["3c11cc576ec8a049"]]},{"id":"dcc3256d0ce552f5","type":"inject","z":"8b4e6a14e81f8a68","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":160,"y":225,"wires":[["766622559d9a88ab"]]},{"id":"e8e5b1e52ef8e6a6","type":"inject","z":"8b4e6a14e81f8a68","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"name\":\"John\",\"age\":30}","payloadType":"json","x":400,"y":250,"wires":[["3c11cc576ec8a049"]]},{"id":"99301d0f9b754bc5","type":"debug","z":"8b4e6a14e81f8a68","name":"","active":true,"console":"false","complete":"false","x":745,"y":475,"wires":[]},{"id":"f6bc45f84d3ec7b5","type":"mysql","z":"8b4e6a14e81f8a68","mydb":"a351da32cbcc0b60","name":"","x":555,"y":475,"wires":[["99301d0f9b754bc5"]]},{"id":"9b2fa9e4f573e509","type":"function","z":"8b4e6a14e81f8a68","name":"function 16","func":"msg.topic = \"INSERT INTO meterstanden (gas,dal) VALUES (300,200)\";\nmsg.payload = [300,500];\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":355,"y":475,"wires":[["f6bc45f84d3ec7b5"]]},{"id":"cb79abc2378959cf","type":"inject","z":"8b4e6a14e81f8a68","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":185,"y":475,"wires":[["9b2fa9e4f573e509"]]},{"id":"a351da32cbcc0b60","type":"MySQLdatabase","name":"Homeassistant ","host":"core-mariadb","port":"3306","db":"homeassistant","tz":"","charset":"UTF8"}]

[E1cid]

If you are using node-red-node-mysql (node) - Node-RED
then try

msg.topic = "INSERT INTO meterstanden (gas) VALUES (:gas)";
msg.payload = {gas:  msg.payload};
return msg;

[edit] fixed error as msg.payload is a number, it has to be assign as an object.

[marcus-j-davies]

Hi @kroonp

Your almost there:
instead of hard coding the values, replace them with a question mark.

The question marks will be used by the MySQL node - and will represent the ordinance, that is set in your payload array .

msg.topic = "INSERT INTO person(first_name,last_name) VALUES (?,?)";
msg.payload = ['Marcus','Davies'];
return msg;

If you are inserting these values based on an event, just create the array at that time

msg.topic = "INSERT INTO meterstanden(gas) VALUES (?)";
msg.payload = [msg.payload.someEvent.value];
return msg;

EDIT
@E1cid answer is prefered as it's clearer where the values are taken from.

[kroonp]

I made an input_number.gas en give it number:777
in the table a field: gas - int - 11

msg.topic = "INSERT INTO meterstanden(gas) VALUES (?)";
msg.payload = [msg.payload.input_number.gas.value];
return msg;

TypeError: Cannot read properties of undefined (reading 'gas')

What do I wrong?

[marcus-j-davies]

msg.payload = [msg.payload.input_number.gas.value];

Should probably be

msg.payload = [msg.payload.input_number.gas];

value was just my example
Just pass it the path to the value.

msg.payload.Something

[kroonp]

Thanks Marcus.
I have now omitted 'value'. Unfortunately I still get an error message.

[{"id":"02ee788a8ce3d967","type":"debug","z":"8b4e6a14e81f8a68","name":"","active":true,"console":"false","complete":"false","x":730,"y":700,"wires":[]},{"id":"5f319d3bd6902a02","type":"mysql","z":"8b4e6a14e81f8a68","mydb":"a351da32cbcc0b60","name":"","x":540,"y":700,"wires":[["02ee788a8ce3d967"]]},{"id":"904badc6413a0560","type":"function","z":"8b4e6a14e81f8a68","name":"function 18","func":"msg.topic = \"INSERT INTO meterstanden(gas) VALUES (?)\";\nmsg.payload = [msg.payload.input_number.gas];\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":340,"y":700,"wires":[["5f319d3bd6902a02"]]},{"id":"542fa217a468162d","type":"inject","z":"8b4e6a14e81f8a68","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":160,"y":700,"wires":[["904badc6413a0560","2ab13501b2f0b12e"]]},{"id":"2ab13501b2f0b12e","type":"api-current-state","z":"8b4e6a14e81f8a68","name":"input_number.gas","server":"3c6b2d2d.b63e72","version":3,"outputs":1,"halt_if":"","halt_if_type":"num","halt_if_compare":"is","entity_id":"input_number.gas","state_type":"num","blockInputOverrides":false,"outputProperties":[{"property":"payload","propertyType":"msg","value":"","valueType":"entityState"},{"property":"data","propertyType":"msg","value":"","valueType":"entity"}],"for":"0","forType":"num","forUnits":"minutes","override_topic":false,"state_location":"payload","override_payload":"msg","entity_location":"data","override_data":"msg","x":360,"y":750,"wires":[["f8d9b6ff411aae65"]]},{"id":"f8d9b6ff411aae65","type":"debug","z":"8b4e6a14e81f8a68","name":"debug 395","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":730,"y":750,"wires":[]},{"id":"a351da32cbcc0b60","type":"MySQLdatabase","name":"Homeassistant ","host":"core-mariadb","port":"3306","db":"homeassistant","tz":"","charset":"UTF8"},{"id":"3c6b2d2d.b63e72","type":"server","name":"Home Assistant","version":5,"addon":true,"rejectUnauthorizedCerts":true,"ha_boolean":"y|yes|true|on|home|open","connectionDelay":true,"cacheJson":true,"heartbeat":false,"heartbeatInterval":30,"areaSelector":"friendlyName","deviceSelector":"friendlyName","entitySelector":"friendlyName","statusSeparator":"at: ","statusYear":"hidden","statusMonth":"short","statusDay":"numeric","statusHourCycle":"h23","statusTimeFormat":"h:m","enableGlobalContextStore":true}]

01/04/2023, 17:29:37node: function 18
function : (error)
"TypeError: Cannot read properties of undefined (reading 'gas')"
01/04/2023, 17:29:37node: debug 395
msg.payload : number
777

[marcus-j-davies]

msg.payload.input_number.gas does not exist in your message

According to your payload it will be. msg.payload

msg.payload.input_number.gas will be a message of

{
  payload: {
    input_number: {
      gas: 100
    }
  }
}

but your sending (Im using an example timestamp)

{
  payload:1680364764
}

Therefore, it will be (based on your inject node)

msg.topic = "INSERT INTO meterstanden(gas) VALUES (?)";
msg.payload = [msg.payload];
return msg;

[kroonp]

Dear people, thanks for the help. Unfortunately, it didn't work out. I have tried all possibilities to get a working flow. I give up!

[Colin]

@kroonp

I have tried all possibilities to get a working flow.

I beg to disagree. You have not tried the one that works.

If you showed us the message you are feeding to the db node, that doesn't work, someone could probably help.

[kroonp]

I didn't want to bother you any further.

I'm now ready to store the values ​​in the database table. However, I now get 5 new records with every run when the intention is to store the 5 values ​​in 1 record. The stored values ​​are correct and will be placed in the correct field of the database. Furthermore, my approach to the flow is probably much too cumbersome and it should be simpler.

My knowledge is still insufficient to get it done in the right way.

Is send along the flow.

[{"id":"403b34f4df79d237","type":"function","z":"a4d2bf7d268cee7f","name":"function 29","func":"var arrNum = [];\ncontext.node1 = context.node1 || 0;\ncontext.node2 = context.node2 || 0;\ncontext.node3 = context.node3 || 0;\ncontext.node4 = context.node4 || 0;\ncontext.node5 = context.node5 || 0;\n\n\nif (msg.topic == 'gas') {\n    context.node1 = msg.payload;\n    arrNum[0] = msg.payload;\n}\nelse if (msg.topic == 'dal_cons') {\n    context.node2 = msg.payload;\n    arrNum[1] = msg.payload;\n}\nelse if (msg.topic == 'dal_prod') {\n    context.node3 = msg.payload;\n    arrNum[2] = msg.payload;\n}\nelse if (msg.topic == 'normaal_cons') {\n    context.node4 = msg.payload;\n    arrNum[3] = msg.payload;\n}\nelse if(msg.topic == 'normaal_prod') {\n    context.node5 = msg.payload;\n    arrNum[4] = msg.payload;\n}\n\nmsg.payload = [arrNum[0], arrNum[1], arrNum[2], arrNum[3], arrNum[4] ];\nmsg.topic = \"INSERT INTO meterstanden (`gas`,`dal_cons`, `dal_prod`,`normaal_cons`, `normaal_prod`) VALUES (?,?,?,?,?);\"\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":620,"y":725,"wires":[["d4a4b586d527319c"]]},{"id":"5bf82d602a3c678e","type":"inject","z":"a4d2bf7d268cee7f","name":"","props":[{"p":"payload"},{"p":"topic","v":"33","vt":"num"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":135,"y":725,"wires":[["a747ac4a85b7ae7e","ce20a7827b1dde06","c38ed5c5513151d6","0b5e447ef0455e4e","ccfa296b4b817d86"]]},{"id":"a747ac4a85b7ae7e","type":"api-current-state","z":"a4d2bf7d268cee7f","name":"gas","server":"3c6b2d2d.b63e72","version":3,"outputs":1,"halt_if":"","halt_if_type":"num","halt_if_compare":"is","entity_id":"input_number.gas","state_type":"num","blockInputOverrides":false,"outputProperties":[{"property":"payload","propertyType":"msg","value":"","valueType":"entityState"},{"property":"data","propertyType":"msg","value":"","valueType":"entity"},{"property":"topic","propertyType":"msg","value":"gas","valueType":"str"}],"for":"0","forType":"num","forUnits":"minutes","override_topic":false,"state_location":"payload","override_payload":"msg","entity_location":"data","override_data":"msg","x":325,"y":625,"wires":[["403b34f4df79d237"]]},{"id":"ce20a7827b1dde06","type":"api-current-state","z":"a4d2bf7d268cee7f","name":"dal_cons","server":"3c6b2d2d.b63e72","version":3,"outputs":1,"halt_if":"","halt_if_type":"num","halt_if_compare":"is","entity_id":"sensor.energy_consumed_tariff_1","state_type":"num","blockInputOverrides":false,"outputProperties":[{"property":"payload","propertyType":"msg","value":"","valueType":"entityState"},{"property":"data","propertyType":"msg","value":"","valueType":"entity"},{"property":"topic","propertyType":"msg","value":"dal_cons","valueType":"str"}],"for":"0","forType":"num","forUnits":"minutes","override_topic":false,"state_location":"payload","override_payload":"msg","entity_location":"data","override_data":"msg","x":335,"y":675,"wires":[["403b34f4df79d237"]]},{"id":"c38ed5c5513151d6","type":"api-current-state","z":"a4d2bf7d268cee7f","name":"dal_prod","server":"3c6b2d2d.b63e72","version":3,"outputs":1,"halt_if":"","halt_if_type":"num","halt_if_compare":"is","entity_id":"sensor.energy_produced_tariff_1","state_type":"num","blockInputOverrides":false,"outputProperties":[{"property":"payload","propertyType":"msg","value":"","valueType":"entityState"},{"property":"data","propertyType":"msg","value":"","valueType":"entity"},{"property":"topic","propertyType":"msg","value":"dal_prod","valueType":"str"}],"for":"0","forType":"num","forUnits":"minutes","override_topic":false,"state_location":"payload","override_payload":"msg","entity_location":"data","override_data":"msg","x":335,"y":725,"wires":[["403b34f4df79d237"]]},{"id":"d4a4b586d527319c","type":"mysql","z":"a4d2bf7d268cee7f","mydb":"a351da32cbcc0b60","name":"","x":830,"y":725,"wires":[["8b52072e32428d4c"]]},{"id":"0b5e447ef0455e4e","type":"api-current-state","z":"a4d2bf7d268cee7f","name":"normaal_cons","server":"3c6b2d2d.b63e72","version":3,"outputs":1,"halt_if":"","halt_if_type":"num","halt_if_compare":"is","entity_id":"sensor.energy_consumed_tariff_2","state_type":"num","blockInputOverrides":false,"outputProperties":[{"property":"payload","propertyType":"msg","value":"","valueType":"entityState"},{"property":"data","propertyType":"msg","value":"","valueType":"entity"},{"property":"topic","propertyType":"msg","value":"normaal_cons","valueType":"str"}],"for":"0","forType":"num","forUnits":"minutes","override_topic":false,"state_location":"payload","override_payload":"msg","entity_location":"data","override_data":"msg","x":355,"y":775,"wires":[["403b34f4df79d237"]]},{"id":"ccfa296b4b817d86","type":"api-current-state","z":"a4d2bf7d268cee7f","name":"normaal_prod","server":"3c6b2d2d.b63e72","version":3,"outputs":1,"halt_if":"","halt_if_type":"num","halt_if_compare":"is","entity_id":"sensor.energy_produced_tariff_2","state_type":"num","blockInputOverrides":false,"outputProperties":[{"property":"payload","propertyType":"msg","value":"","valueType":"entityState"},{"property":"data","propertyType":"msg","value":"","valueType":"entity"},{"property":"topic","propertyType":"msg","value":"normaal_prod","valueType":"str"}],"for":"0","forType":"num","forUnits":"minutes","override_topic":false,"state_location":"payload","override_payload":"msg","entity_location":"data","override_data":"msg","x":355,"y":825,"wires":[["403b34f4df79d237"]]},{"id":"8b52072e32428d4c","type":"debug","z":"a4d2bf7d268cee7f","name":"debug 400","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":780,"y":800,"wires":[]},{"id":"3c6b2d2d.b63e72","type":"server","name":"Home Assistant","version":5,"addon":true,"rejectUnauthorizedCerts":true,"ha_boolean":"y|yes|true|on|home|open","connectionDelay":true,"cacheJson":true,"heartbeat":false,"heartbeatInterval":30,"areaSelector":"friendlyName","deviceSelector":"friendlyName","entitySelector":"friendlyName","statusSeparator":"at: ","statusYear":"hidden","statusMonth":"short","statusDay":"numeric","statusHourCycle":"h23","statusTimeFormat":"h:m","enableGlobalContextStore":true},{"id":"a351da32cbcc0b60","type":"MySQLdatabase","name":"Homeassistant ","host":"core-mariadb","port":"3306","db":"homeassistant","tz":"","charset":"UTF8"}]

[marcus-j-davies]

I now get 5 new records with every run when the intention is to store the 5 values ​​in 1 record

Only today a similar "problem" occurred.

Your 5 states, DO NOT represent 1 message - they represent 5 messages = 5 executions of your function 29 and MySQL node.

Messages in Node RED, are separate entities - and EACH message will travel down the flow - executing the nodes they travel through.

The Fix:

use a JOIN node to combine these 5 messages, allowing them to be delivered as 1 = 1 execution of the function 29 and MySQL node.

Set the JOIN node to deliver the combined messages after the 5th message.

The thing to remember: Each message is a seperate run or instance of data tarvelling through your flow, you can of course join these messages together as I have done above.

EDIT:
You will need to rewrite your function node, to be able to extract the relevant parts out of the combined payload.

You can set the JOIN node to return an array of messages

payload:[
   {topic'gas',payload: 45},
   {topic'dal_cons',payload: 35},
]

or to combiner the properties of each message, but I don't think that will work work with you set up
(not many of use Home Assistant)

[kroonp]

The result of the join node is now:
{"gas":777,"dal_cons":9220.795,"dal_prod":1394.833,"normaal_cons":7173.891,"normaal_prod":3225.873}

So that works, but how do I get them into the database now.

[marcus-j-davies]

Use the approach by @E1cid

pass the resulting payload created by the JOIN node into your function node with the following.

msg.topic = "INSERT INTO meterstanden (gas,dal_cons,dal_prod,normaal_cons,normaal_prod) VALUES(:gas,:dal_cons,:normaal_cons,:normaal_prod)";
return msg;

Explanation:
:gas means use the property in your payload named gas

Note: I have guess your column name here.

[kroonp]

Marcus,
Thanx for you help. All is working.
Top.
Peter