Need Help to Insert, Update and Delete Data from Node-RED Dashboard Inputs to the Database

Dashboard
1

Hi,

I'm creating a web application using Node-RED. I'm having complications in inserting, updating and deleting data from the Node-Red dashboard inputs (drop down, switch, slider, numeric, text input, date picker) to the database. User registration, login and display information are the main functions of the web application.

I have created a flow to test whether the data able to be added to the database table.

There's always a "TypeError: Cannot read property 'name' of undefined" coming up when I trying to insert the data.

Maybe I'm doing this wrongly.

I really need help on this. Thanks for helping.

2

It is impossible to help when you have told us so little information. You have not even told us what sort of database it is.
Start by using debug nodes at appropriate points in the flow to check that the messages you expect are there. If it is the database insert that is failing then particularly look at the messages going into the database node. If you still can't find the problem then post a small flow that shows the problem. You can select the relevant nodes and use the export facility to copy those the paste buffer, then paste it here. Put three backtick characters on a line before and another after the flow here, so that it is formatted correctly.

3

Hi Colin,

I'm using MySQL database. The following is the flow that I had created to test whether the values passed to the database. But, it failed with "TypeError: Cannot read property 'name' of undefined" coming up when I trying to insert the data.

[{"id":"5e58d423.cd4cfc","type":"ui_text_input","z":"bc8d59e2.327ba8","name":"name","label":"Name","group":"61ac4a1.b073eb4","order":1,"width":0,"height":0,"passthru":false,"mode":"text","delay":"300","topic":"[{\"type\":\"string\",\"name\":\"name\"}]","x":110,"y":80,"wires":[["763ef92c.20ef58"]]},{"id":"b732f414.b9d8e8","type":"ui_text_input","z":"bc8d59e2.327ba8","name":"code","label":"Code","group":"61ac4a1.b073eb4","order":3,"width":0,"height":0,"passthru":false,"mode":"number","delay":"300","topic":"[{\"type\":\"integer\",\"name\":\"code\"}]","x":110,"y":200,"wires":[["763ef92c.20ef58"]]},{"id":"7360668a.315308","type":"function","z":"bc8d59e2.327ba8","name":"insert-query","func":"msg.topic=\"INSERT INTO test (name,lastname,code) VALUES (?,?,?)\";\nmsg.payload=[msg.payload.name,msg.payload.lastname,msg.payload.code];\nreturn msg;","outputs":1,"noerr":0,"x":510,"y":140,"wires":[["db6640.732dc9c"]]},{"id":"db6640.732dc9c","type":"mysql","z":"bc8d59e2.327ba8","mydb":"272ea16.4569d5e","name":"database","x":700,"y":140,"wires":[[]]},{"id":"763ef92c.20ef58","type":"ui_button","z":"bc8d59e2.327ba8","name":"save-button","group":"61ac4a1.b073eb4","order":4,"width":0,"height":0,"passthru":false,"label":"Save","color":"","bgcolor":"","icon":"","payload":"{}","payloadType":"global","topic":"","x":310,"y":140,"wires":[["7360668a.315308"]]},{"id":"58932c23.0f4344","type":"ui_dropdown","z":"bc8d59e2.327ba8","name":"lastname","label":"","place":"Select Last Name","group":"61ac4a1.b073eb4","order":2,"width":0,"height":0,"passthru":false,"options":[{"label":"Rishan","value":1,"type":"num"},{"label":"Login","value":2,"type":"num"}],"payload":"","topic":"[{\"type\":\"integer\",\"name\":\"lastname\"}]","x":120,"y":140,"wires":[["763ef92c.20ef58"]]},{"id":"61ac4a1.b073eb4","type":"ui_group","z":"","name":"Insert Data Test","tab":"423fdbe9.e7bc44","order":1,"disp":true,"width":"6","collapse":false},{"id":"272ea16.4569d5e","type":"MySQLdatabase","z":"","host":"localhost","port":"3306","db":"ram_test","tz":""},{"id":"423fdbe9.e7bc44","type":"ui_tab","z":"","name":"Input Test","icon":"dashboard","order":3}]

Hope to hear from you soon.

4

Did you do what I suggested and put debug nodes in appropriate places to check that the messages are as you expect?

If you hover over the error message you will see that the error is coming from your function node, so nothing to do with the database insert. So I would start by putting a debug on the output of your Save Button node to check you are sending the function node what you expect.

5

Hi Colin,

I did as what you said and I got msg.payload as undefined on the output of the Save Button node.

image
image.png1179×298 20 KB

6

Well that is why it is failing then. What have you configured the button to output?

7

In fact I don't see how the flow you posted can ever work. I think you probably want a ui form node to replace the four ui nodes you have.

8

The following is the configuration of the button:

image

I didn't use form ui because there is no dropdown element in the form ui.

How we can do this in proper way? I'm new to Node-Red.

I'm having complications especially in passing the values.

9

I could see the config of the button node from the flow you posted. The button node is not capable of combining other messages, it just sends its message when you click it.

I see you are right about the dropdown in the form button. In that case you can feed the three data nodes into a Join node set to manual mode and to combine the payloads into key/value pairs (make sure the three data nodes send out different topics) and make the button generate msg.complete and feed that into the join node too. Then the join node will send a combined message when you press the button. Put a debug on the output of the join to see what you get.

10

How to make the button generate msg.complete? The data nodes sending different topics? By the way, is the data nodes topic is correct?

The following is the flow. I think I did as what you said.

image

The following is the button configuration:

image

11

In the data nodes you just want to set the topics to name, lastname and code.
In fact I don't think you can set msg.complete in the button, so feed it into a Change node configured to Set msg.complete, it doesn't matter what you set it to as long as it is not null.

12

I set the topics to name, lastname and code. I feed the button into a Change node configured to Set msg.complete.

The following is the error that I'm seeing for the moment:

image
image.png1185×299 23.1 KB

13

The message suggests you have not set a topic in one of the ui nodes. You may need to set one in the button, not sure.

14

Nothing comes up once I set a topic to the button. I had shared the JSON of the flow below.

[{"id":"5e58d423.cd4cfc","type":"ui_text_input","z":"bc8d59e2.327ba8","name":"name","label":"Name","group":"61ac4a1.b073eb4","order":1,"width":0,"height":0,"passthru":false,"mode":"text","delay":"300","topic":"name","x":110,"y":80,"wires":[["a9e8ce7a.cb765"]]},{"id":"b732f414.b9d8e8","type":"ui_text_input","z":"bc8d59e2.327ba8","name":"code","label":"Code","group":"61ac4a1.b073eb4","order":3,"width":0,"height":0,"passthru":false,"mode":"number","delay":"300","topic":"code","x":110,"y":160,"wires":[["a9e8ce7a.cb765"]]},{"id":"763ef92c.20ef58","type":"ui_button","z":"bc8d59e2.327ba8","name":"save-button","group":"61ac4a1.b073eb4","order":4,"width":0,"height":0,"passthru":false,"label":"Save","color":"","bgcolor":"","icon":"","payload":"{}","payloadType":"global","topic":"save-button","x":130,"y":200,"wires":[["a9e8ce7a.cb765","71dc71ba.9be99"]]},{"id":"58932c23.0f4344","type":"ui_dropdown","z":"bc8d59e2.327ba8","name":"lastname","label":"","place":"Select Last Name","group":"61ac4a1.b073eb4","order":2,"width":0,"height":0,"passthru":false,"options":[{"label":"Rishan","value":1,"type":"num"},{"label":"Login","value":2,"type":"num"}],"payload":"","topic":"lastname","x":120,"y":120,"wires":[["a9e8ce7a.cb765"]]},{"id":"5fff9e99.0e91","type":"debug","z":"bc8d59e2.327ba8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":470,"y":80,"wires":[]},{"id":"a9e8ce7a.cb765","type":"join","z":"bc8d59e2.327ba8","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":310,"y":80,"wires":[["5fff9e99.0e91"]]},{"id":"71dc71ba.9be99","type":"change","z":"bc8d59e2.327ba8","name":"set to msg.complete","rules":[{"t":"set","p":"payload","pt":"msg","to":"complete","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":360,"y":200,"wires":[["a9e8ce7a.cb765"]]},{"id":"61ac4a1.b073eb4","type":"ui_group","z":"","name":"Insert Data Test","tab":"423fdbe9.e7bc44","order":1,"disp":true,"width":"6","collapse":false},{"id":"423fdbe9.e7bc44","type":"ui_tab","z":"","name":"Input Test","icon":"dashboard","order":3}]

15

In the change node you need to set msg.complete to something, not set msg.payload to msg.complete.

16

To something? I can't get you.

17

To anything, it doesn't matter what it is set to. Set it the string complete for example. The Join node just looks for msg.complete to be present, it doesn't test the value.

18

I think you might find that there is still an issue after making the change I suggested. It might seem to work perfectly at first but then not quite do what you want. Come back if it is an issue for you.

19

Hi Colin,

I think it's working as what I expected. However, I would like to know whether we are able to save the values to the database with the insert-query function that I had created? The following is the screenshot:

image
image.png1186×378 26.2 KB

The following is the insert-query function that I had created:

msg.topic="INSERT INTO test (name,lastname,code) VALUES (?,?,?)";
msg.payload=[msg.payload.name,msg.payload.lastname,msg.payload.code];
return msg;

Hope to hear from you soon.

20

Does it work?
If not what happens?