How to Check whether There is Existing Data before Allowing Users to Insert the Data to the Database


#21

Sorry @zenofmud , for not sharing the flow, I've actually tried to modify the flow but I could help.

[{"id":"663256c1.ef9618","type":"ui_text_input","z":"a0a2199f.f5e638","name":"name","label":"Name","tooltip":"","group":"356fac5d.d980c4","order":2,"width":0,"height":0,"passthru":false,"mode":"text","delay":"300","topic":"name","x":570,"y":280,"wires":[["9c430827.1649e8"]]},{"id":"8e6c4c9d.3b383","type":"ui_text_input","z":"a0a2199f.f5e638","name":"apellido","label":"apellido","tooltip":"","group":"356fac5d.d980c4","order":3,"width":0,"height":0,"passthru":false,"mode":"text","delay":"300","topic":"apellido","x":580,"y":340,"wires":[["9c430827.1649e8"]]},{"id":"12a585f6.e531ba","type":"ui_button","z":"a0a2199f.f5e638","name":"save-button","group":"356fac5d.d980c4","order":7,"width":0,"height":0,"passthru":true,"label":"Guardar datos","tooltip":"","color":"","bgcolor":"","icon":"assignment_turned_in","payload":"{}","payloadType":"global","topic":"save-button","x":570,"y":560,"wires":[["9c430827.1649e8","228f7175.69169e"]]},{"id":"9dbf8c03.4e568","type":"debug","z":"a0a2199f.f5e638","name":"node_switch1","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":1140,"y":340,"wires":[]},{"id":"9c430827.1649e8","type":"join","z":"a0a2199f.f5e638","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":true,"timeout":"","count":"5","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":750,"y":340,"wires":[["e4b9d7ca.a5ec88","60ccb3fe.4bcd8c"]]},{"id":"e4b9d7ca.a5ec88","type":"switch","z":"a0a2199f.f5e638","name":"switch_select","property":"topic","propertyType":"msg","rules":[{"t":"eq","v":"save-button","vt":"str"}],"checkall":"true","repair":false,"outputs":1,"x":910,"y":360,"wires":[["9dbf8c03.4e568","d084b396.0651d"]]},{"id":"7aa36605.3867e8","type":"debug","z":"a0a2199f.f5e638","name":"node_bd_pac","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1380,"y":320,"wires":[]},{"id":"60ccb3fe.4bcd8c","type":"debug","z":"a0a2199f.f5e638","name":"node_join","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1020,"y":280,"wires":[]},{"id":"d084b396.0651d","type":"function","z":"a0a2199f.f5e638","name":"select-query","func":"var select = {topic: \"SELECT COUNT(*) AS count FROM pacientes WHERE nombre='\" + msg.payload.name + \"'\"};\nreturn select;","outputs":1,"noerr":0,"x":950,"y":420,"wires":[["8a6f7674.c3ec98","8dd0ca48.b80278","bfb6b546.6fabd8"]]},{"id":"8a6f7674.c3ec98","type":"debug","z":"a0a2199f.f5e638","name":"node_select","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1030,"y":500,"wires":[]},{"id":"1fa2ae8b.72e9c1","type":"ui_text_input","z":"a0a2199f.f5e638","name":"edad","label":"edad","tooltip":"","group":"356fac5d.d980c4","order":5,"width":0,"height":0,"passthru":false,"mode":"number","delay":"300","topic":"edad","x":570,"y":440,"wires":[["9c430827.1649e8"]]},{"id":"951ae802.19d948","type":"ui_text_input","z":"a0a2199f.f5e638","name":"sexo","label":"sexo","tooltip":"","group":"356fac5d.d980c4","order":6,"width":0,"height":0,"passthru":false,"mode":"text","delay":"300","topic":"sexo","x":570,"y":500,"wires":[["9c430827.1649e8"]]},{"id":"ade31e4f.08585","type":"ui_text_input","z":"a0a2199f.f5e638","name":"cedula","label":"cedula","tooltip":"","group":"356fac5d.d980c4","order":4,"width":0,"height":0,"passthru":true,"mode":"number","delay":300,"topic":"cedula","x":570,"y":400,"wires":[["9c430827.1649e8","e0ca343.98933c8"]]},{"id":"13b7e857.884ca8","type":"change","z":"a0a2199f.f5e638","name":"set","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":230,"y":400,"wires":[["8b7d066a.c7e1a8"]]},{"id":"8b7d066a.c7e1a8","type":"ui_button","z":"a0a2199f.f5e638","name":"clear-button","group":"356fac5d.d980c4","order":8,"width":0,"height":0,"passthru":false,"label":"Borrar campos","tooltip":"","color":"","bgcolor":"","icon":"delete","payload":"[]","payloadType":"json","topic":"","x":370,"y":400,"wires":[["663256c1.ef9618","8e6c4c9d.3b383","ade31e4f.08585","951ae802.19d948","1fa2ae8b.72e9c1","12a585f6.e531ba"]]},{"id":"228f7175.69169e","type":"debug","z":"a0a2199f.f5e638","name":"node-button","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":790,"y":560,"wires":[]},{"id":"8dd0ca48.b80278","type":"mysql","z":"a0a2199f.f5e638","mydb":"698ecd0.1670e34","name":"BD_DATOS","x":1150,"y":400,"wires":[["7aa36605.3867e8"]]},{"id":"e0ca343.98933c8","type":"debug","z":"a0a2199f.f5e638","name":"cedula","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":800,"y":500,"wires":[]},{"id":"2480d039.f669e","type":"function","z":"a0a2199f.f5e638","name":"insert-query","func":"insert = {topic: \"INSERT INTO pacientes (nombre,apellido,cedula,edad,sexo) VALUES ('\" + msg.payload.name + \"','\" + msg.payload.apellido + \"','\" + msg.payload.cedula + \"','\" + msg.payload.edad + \"','\" + msg.payload.sexo + \"')\"};\nactionString = \"Dato ingresado satisfactoriamente!\";\nreturn [insert,actionString];\n\n\n//insert = {topic: \"INSERT INTO pacientes (nombre,apellido,cedula,edad,sexo) VALUES ('\" + msg.payload.name + \"','\" + msg.payload.apellido + \"','\" + msg.payload.cedula + \"','\" + msg.payload.edad + \"','\" + msg.payload.sexo + \"')\"};\n//return insert;  \n","outputs":1,"noerr":0,"x":1410,"y":480,"wires":[["d63e1165.fd7fe","628ee543.03c4ac"]]},{"id":"bfb6b546.6fabd8","type":"switch","z":"a0a2199f.f5e638","name":"switch_insert","property":"payload[0].count","propertyType":"msg","rules":[{"t":"eq","v":"0","vt":"msg"},{"t":"eq","v":"1","vt":"msg"}],"checkall":"true","repair":false,"outputs":2,"x":1190,"y":460,"wires":[["975efbe.6731308","2480d039.f669e"],["db614880.d35f48","7150fef5.16df6"]]},{"id":"db614880.d35f48","type":"function","z":"a0a2199f.f5e638","name":"failure-msg","func":"actionString = \"Ya se ingresado ese nombre!\";\nmsg.payload = actionString;\nreturn msg;","outputs":1,"noerr":0,"x":1410,"y":540,"wires":[["add1cfb6.0b88e"]]},{"id":"d63e1165.fd7fe","type":"mysql","z":"a0a2199f.f5e638","mydb":"698ecd0.1670e34","name":"BD_DATOS","x":1590,"y":480,"wires":[["a6c52cd3.8dfe1"]]},{"id":"1583bc32.a442d4","type":"function","z":"a0a2199f.f5e638","name":"select-query","func":"insert = {topic: \"INSERT INTO pacientes (nombre,apellido,cedula,edad,sexo) VALUES ('\" + msg.payload.name + \"','\" + msg.payload.apellido + \"','\" + msg.payload.cedula + \"','\" + msg.payload.edad + \"','\" + msg.payload.sexo + \"')\"};\nreturn insert; ","outputs":1,"noerr":0,"x":1030,"y":620,"wires":[[]]},{"id":"628ee543.03c4ac","type":"debug","z":"a0a2199f.f5e638","name":"node_insert","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":1600,"y":420,"wires":[]},{"id":"add1cfb6.0b88e","type":"debug","z":"a0a2199f.f5e638","name":"node_fallo","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":1650,"y":580,"wires":[]},{"id":"a6c52cd3.8dfe1","type":"debug","z":"a0a2199f.f5e638","name":"node_bdpacientes","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1800,"y":520,"wires":[]},{"id":"7150fef5.16df6","type":"debug","z":"a0a2199f.f5e638","name":"node_siwtch3","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1410,"y":620,"wires":[]},{"id":"975efbe.6731308","type":"debug","z":"a0a2199f.f5e638","name":"node_siwtch2","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1380,"y":400,"wires":[]},{"id":"a942a4fe.805278","type":"ui_toast","z":"a0a2199f.f5e638","position":"dialog","displayTime":"3","highlight":"","outputs":1,"ok":"OK","cancel":"","topic":"Ingreso correcto del paciente","name":"Ingreso correcto del paciente","x":1640,"y":340,"wires":[[]]},{"id":"87508a4f.47d1e8","type":"ui_toast","z":"a0a2199f.f5e638","position":"dialog","displayTime":"3","highlight":"","outputs":1,"ok":"OK","cancel":"","topic":"fallo","name":"fallo","x":1630,"y":640,"wires":[[]]},{"id":"356fac5d.d980c4","type":"ui_group","z":"","name":"Pacientes","tab":"16e86f12.023081","order":2,"disp":true,"width":"6","collapse":true},{"id":"698ecd0.1670e34","type":"MySQLdatabase","z":"","host":"","port":"","db":"","tz":""},{"id":"16e86f12.023081","type":"ui_tab","z":"","name":"dispositivo","icon":"healing","order":2}]

#22

1)I have the error in node function "insert-query".
2) In this insert the data of the table (name, surname, age, identity card, sex)

insert = {topic: "INSERT INTO pacientes (nombre,apellido,cedula,edad,sexo) VALUES ('" + msg.payload.name + "','" + msg.payload.apellido + "','" + msg.payload.cedula + "','" + msg.payload.edad + "','" + msg.payload.sexo + "')"};
actionString = "Dato ingresado satisfactoriamente!";
return [insert,actionString];

#23

So add a debug after that and what is the message you are sending?


#24

I always have this message when I enter a "name" already stored in the table or a new "name".
This I get at the exit of the node function "insert-query".
INSERT1


#25

So if it isn't working you need to break the problem down to fid the bit that is broken.
Check that the message going into the function you posted

insert = {topic: "INSERT INTO pacientes (nombre,apellido,cedula,edad,sexo) VALUES ('" + msg.payload.name + "','" + msg.payload.apellido + "','" + msg.payload.cedula + "','" + msg.payload.edad + "','" + msg.payload.sexo + "')"};
actionString = "Dato ingresado satisfactoriamente!";
return [insert,actionString];

and the messages that leave the function are what you think they are. You do this by adding debug nodes. If it can't read property 'name' check that where you are trying to use name it exists.


#26

As @ukmoose said, you need to work backwards. If your 'insert-query' is getting an error, you need to look at what is going into that node. Looking at your flow the input comes from the switch node and the msg that enters the switch node comes from the 'select-query' function.

So what is coming out of that node and is it the data you are trying to access in the 'insert-query' node?


#27

I appreciate your help @ukmoose , @zenofmud

I get the following:

  1. Debug: node name:
    name

  2. Debug output: node join:
    node_join

  3. Debug: switch_select
    node_switch1

  4. The error is presented in the node function "select-query", because I have a "msg.payload: undefined".
    node_select

  5. Debug: node_switch 2; this is the output of the switch_insert
    salida_s2


#28

So your select-query function has an error saying that msg.payload is not defined. Take a step back and figure out where the data send to that function is coming from and what is in the msg being sent.

It is your responsibility to send data to that function node in a format that the function node will use.


#29

The data comes from the "switch_select" node, and I get the following:
sw1_i3

I have verified the path of the name message and it is this:
payload.name


#30
insert = {topic: "INSERT INTO pacientes (nombre,apellido,cedula,edad,sexo) VALUES ('" + msg.payload.name + "','" + msg.payload.apellido + "','" + msg.payload.cedula + "','" + msg.payload.edad + "','" + msg.payload.sexo + "')"};
actionString = "Dato ingresado satisfactoriamente!";
return [insert,actionString];

In this code you are returning the insert message on the first output of the Function node and you are returning actionString on the second output of the Function node.

But actionString is a string - not a message object. You must return messages from the Function, not plain strings.

var insert = {topic: "INSERT INTO pacientes (nombre,apellido,cedula,edad,sexo) VALUES ('" + msg.payload.name + "','" + msg.payload.apellido + "','" + msg.payload.cedula + "','" + msg.payload.edad + "','" + msg.payload.sexo + "')"};
var actionString = {payload:"Dato ingresado satisfactoriamente!"};
return [insert,actionString];

#31

@knolleary Nick, that is the second error
@Tefita In the 'select query' function you are returning an object with only a msg.topic and no msg.payload. That is why you are getting the msg.payload: undefined


#32

And that probably isn't an error, because the sql node uses msg.topic to define the query and doesn't use msg.payload.


#33

At least cleaning that up will be a good learning exercise for @Tefita as she trys to learn how to use NR.


#34

I have done the mentioned thing but now I have another error:

var select = {payload: "SELECT COUNT(*) AS count FROM pacientes WHERE nombre='" + msg.payload.name + "'"};
return select;

SELECT1


#35

Then should consider what was said by @knolleary : sql node uses msg.topic


#36

So by this last post of yours, can we assume you have it working?


#37

No, I need your help please, I continue with the problem, I do not know how to solve it.
This error in is node function "select-query"
select2


#38

As I said before look at the msg entering the function node. In the function node you are trying to get information out of the payload but there is no payload. You have to fix that!