Node red and relational database (mysql)?

General
1

Greetings to all.
I perform the registration of some data (name, surname, etc) and store them in a mysql table called patients, but this table is related to another named data and have the attribute idpacientes in the two tables, as observe in the figure, when I enter a patient this generates an incremental number that represents the patient and this should be stored in the data table, but that does not happen and I get the following error can someone help me please.

bd
bd.PNG1129×627 17.8 KB 

[{"id":"a69c2bb7.39ace8","type":"mqtt in","z":"21583b71.76bf34","name":"","topic":"tamb","qos":"0","broker":"55d76ff9.f1284","x":290,"y":240,"wires":[["6a6182dd.e17e4c","fe35e5e9.9864e8"]]},{"id":"9d8afa34.ffc778","type":"mqtt in","z":"21583b71.76bf34","name":"","topic":"tcorp","qos":"0","broker":"55d76ff9.f1284","x":292.0000228881836,"y":337.0000305175781,"wires":[["6a6182dd.e17e4c","a71d37f7.f92ef8"]]},{"id":"994e9545.176438","type":"debug","z":"21583b71.76bf34","name":"debug_mysql","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":1187.0001792907715,"y":83.99998092651367,"wires":[]},{"id":"6a6182dd.e17e4c","type":"join","z":"21583b71.76bf34","name":"Datos","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"11","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":529.0001182556152,"y":89.00004959106445,"wires":[["ff42dfa.a693a2","67e2113e.cf3d3"]]},{"id":"ff42dfa.a693a2","type":"debug","z":"21583b71.76bf34","name":"debug_nodo_join","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":711.1666603088379,"y":145.99999046325684,"wires":[]},{"id":"f70dcf25.3997c","type":"debug","z":"21583b71.76bf34","name":"debug_nodo_template","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":962.166675567627,"y":146.0000286102295,"wires":[]},{"id":"67e2113e.cf3d3","type":"template","z":"21583b71.76bf34","name":"Datos_BD","field":"topic","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO datos (Tamb, Tcorp, rcard, Dcaid, Acorp, Aritm, Ccaid, Ccorp, Critm, Pamb,Pcorp,idpacientes)\nVALUES ('{{payload.tamb}}', '{{payload.tcorp}}','{{payload.rcard}}','{{payload.dcaid}}','{{payload.acorp}}','{{payload.aritm}}','{{payload.ccaid}}','{{payload.ccorp}}','{{payload.critm}}','{{payload.pamb}}','{{payload.pcorp}}',{{payload.idpacientes}})","output":"str","x":732.166748046875,"y":91.33341979980469,"wires":[["f70dcf25.3997c","b44dffc8.ae0ae"]]},{"id":"aab7d475.05cc28","type":"mqtt in","z":"21583b71.76bf34","name":"","topic":"rcard","qos":"0","broker":"55d76ff9.f1284","x":293.1666679382324,"y":434.3333740234375,"wires":[["6a6182dd.e17e4c","be649bce.da6638"]]},{"id":"e10ee658.d57c38","type":"mqtt in","z":"21583b71.76bf34","name":"","topic":"dcaid","qos":"0","broker":"55d76ff9.f1284","x":293.1666603088379,"y":533.3333740234375,"wires":[["6a6182dd.e17e4c","fc9fa8f0.e88958"]]},{"id":"1a7bfea4.6b56d1","type":"mqtt in","z":"21583b71.76bf34","name":"","topic":"acorp","qos":"0","broker":"55d76ff9.f1284","x":290,"y":600,"wires":[["6a6182dd.e17e4c","d8f9a520.7eb2e8"]]},{"id":"2da167ae.7a6468","type":"mqtt in","z":"21583b71.76bf34","name":"","topic":"aritm","qos":"2","broker":"55d76ff9.f1284","x":290,"y":680,"wires":[["6a6182dd.e17e4c","496d5cda.cd18b4"]]},{"id":"f9637f9d.7a976","type":"comment","z":"21583b71.76bf34","name":"Ingreso BD","info":"","x":157.50000381469727,"y":73.99998664855957,"wires":[]},{"id":"226ab9ee.2f2c46","type":"mqtt in","z":"21583b71.76bf34","name":"","topic":"ccaid","qos":"2","broker":"55d76ff9.f1284","x":290,"y":760,"wires":[["6a6182dd.e17e4c","25e48769.ba8808"]]},{"id":"4d83f52d.c739ac","type":"mqtt in","z":"21583b71.76bf34","name":"","topic":"ccorp","qos":"2","broker":"55d76ff9.f1284","x":290,"y":820,"wires":[["6a6182dd.e17e4c","a8067789.772e68"]]},{"id":"f4359937.c58e88","type":"mqtt in","z":"21583b71.76bf34","name":"","topic":"critm","qos":"2","broker":"55d76ff9.f1284","x":290,"y":880,"wires":[["6a6182dd.e17e4c","a2f8da5e.91dfe8"]]},{"id":"a3318035.6c301","type":"mqtt in","z":"21583b71.76bf34","name":"","topic":"pamb","qos":"2","broker":"55d76ff9.f1284","x":290,"y":940,"wires":[["6a6182dd.e17e4c","49e9a3c.c9d685c"]]},{"id":"60dc7a70.4fb504","type":"mqtt in","z":"21583b71.76bf34","name":"","topic":"pcorp","qos":"2","broker":"55d76ff9.f1284","x":290,"y":1020,"wires":[["6a6182dd.e17e4c","88567265.d0076"]]},{"id":"30a081e7.69941e","type":"comment","z":"21583b71.76bf34","name":"Página Web","info":"","x":140.99999618530273,"y":205.0781135559082,"wires":[]},{"id":"b44dffc8.ae0ae","type":"mysql","z":"21583b71.76bf34","mydb":"b0768069.53b72","name":"","x":1029.128589630127,"y":88.01042175292969,"wires":[["994e9545.176438"]]},{"id":"fc9fa8f0.e88958","type":"ui_text_input","z":"21583b71.76bf34","name":"","label":"Detector de caidas","tooltip":"","group":"56f79abd.6320c4","order":1,"width":0,"height":0,"passthru":true,"mode":"text","delay":300,"topic":"","x":731.0000152587891,"y":526.078125,"wires":[[]]},{"id":"d8f9a520.7eb2e8","type":"ui_text_input","z":"21583b71.76bf34","name":"","label":"Alarma temperatura","tooltip":"","group":"56f79abd.6320c4","order":2,"width":0,"height":0,"passthru":true,"mode":"text","delay":300,"topic":"","x":730.9999961853027,"y":605.0781345367432,"wires":[[]]},{"id":"496d5cda.cd18b4","type":"ui_text_input","z":"21583b71.76bf34","name":"","label":"Alarma ritmo","tooltip":"","group":"56f79abd.6320c4","order":3,"width":0,"height":0,"passthru":true,"mode":"text","delay":300,"topic":"","x":722.0000114440918,"y":686.078164100647,"wires":[[]]},{"id":"25e48769.ba8808","type":"ui_text","z":"21583b71.76bf34","group":"b57a22fd.812da","order":2,"width":0,"height":0,"name":"","label":"Caídas","format":"{{msg.payload}}","layout":"row-spread","x":709.143238067627,"y":764.0390510559082,"wires":[]},{"id":"a8067789.772e68","type":"ui_text","z":"21583b71.76bf34","group":"b57a22fd.812da","order":3,"width":0,"height":0,"name":"","label":"Alarma temp corp","format":"{{msg.payload}}","layout":"row-spread","x":738.9765510559082,"y":821.3724250793457,"wires":[]},{"id":"a2f8da5e.91dfe8","type":"ui_text","z":"21583b71.76bf34","group":"b57a22fd.812da","order":4,"width":0,"height":0,"name":"","label":"Alarmas ritmo","format":"{{msg.payload}}","layout":"row-spread","x":722.9765510559082,"y":883.3724250793457,"wires":[]},{"id":"fe35e5e9.9864e8","type":"ui_gauge","z":"21583b71.76bf34","name":"","group":"293ba6c0.530d0a","order":0,"width":0,"height":0,"gtype":"gage","title":"Temperatura Ambiente","label":"°C","format":"{{value}}","min":"0","max":"100","colors":["#1a719b","#29bcbc","#3e9ec4"],"seg1":"","seg2":"","x":720,"y":220,"wires":[]},{"id":"a71d37f7.f92ef8","type":"ui_gauge","z":"21583b71.76bf34","name":"","group":"95bc1cba.c56de","order":0,"width":0,"height":0,"gtype":"gage","title":"Temperatura corporal","label":"°C","format":"{{value}}","min":"0","max":"100","colors":["#00b500","#e6e600","#ca3838"],"seg1":"","seg2":"","x":726.0000133514404,"y":320.07815504074097,"wires":[]},{"id":"be649bce.da6638","type":"ui_gauge","z":"21583b71.76bf34","name":"","group":"9d168e43.85bdb","order":0,"width":0,"height":0,"gtype":"gage","title":"Ritmo cardiaco","label":"LPM","format":"{{value}}","min":0,"max":"100","colors":["#00b500","#e6e600","#ca3838"],"seg1":"","seg2":"","x":717.9999961853027,"y":422.0781555175781,"wires":[]},{"id":"49e9a3c.c9d685c","type":"ui_gauge","z":"21583b71.76bf34","name":"","group":"b7aab8a8.973748","order":0,"width":0,"height":0,"gtype":"compass","title":"Prom tamb","label":"units","format":"{{value}}°C","min":0,"max":"100","colors":["#00b500","#ff80ff","#ca3838"],"seg1":"","seg2":"","x":712.143238067627,"y":951.0390510559082,"wires":[]},{"id":"88567265.d0076","type":"ui_gauge","z":"21583b71.76bf34","name":"","group":"b7aab8a8.973748","order":1,"width":0,"height":0,"gtype":"compass","title":"Prom tcorp","label":"units","format":"{{value}}°C","min":0,"max":"100","colors":["#400080","#e6e600","#ca3838"],"seg1":"","seg2":"","x":710.143238067627,"y":1018.0390510559082,"wires":[]},{"id":"82bc321d.667bd","type":"ui_text_input","z":"21583b71.76bf34","name":"name","label":"Name","tooltip":"","group":"356fac5d.d980c4","order":1,"width":0,"height":0,"passthru":false,"mode":"text","delay":"300","topic":"name","x":550,"y":1120,"wires":[["c983ed94.1e645"]]},{"id":"4eb83a89.564704","type":"ui_text_input","z":"21583b71.76bf34","name":"apellido","label":"apellido","tooltip":"","group":"356fac5d.d980c4","order":3,"width":0,"height":0,"passthru":false,"mode":"text","delay":"300","topic":"apellido","x":540,"y":1180,"wires":[["c983ed94.1e645"]]},{"id":"4ca7eb01.fa6314","type":"ui_button","z":"21583b71.76bf34","name":"save-button","group":"356fac5d.d980c4","order":4,"width":0,"height":0,"passthru":false,"label":"Save","tooltip":"","color":"","bgcolor":"","icon":"","payload":"{}","payloadType":"global","topic":"save-button","x":550,"y":1440,"wires":[["c983ed94.1e645"]]},{"id":"4cd7f29e.436c1c","type":"debug","z":"21583b71.76bf34","name":"node_switch","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":1090,"y":1180,"wires":[]},{"id":"c983ed94.1e645","type":"join","z":"21583b71.76bf34","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":true,"timeout":"","count":"1","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":710,"y":1180,"wires":[["46189387.53838c","13d804ed.86d35b"]]},{"id":"46189387.53838c","type":"switch","z":"21583b71.76bf34","name":"","property":"topic","propertyType":"msg","rules":[{"t":"eq","v":"save-button","vt":"str"}],"checkall":"true","repair":false,"outputs":1,"x":850,"y":1200,"wires":[["4cd7f29e.436c1c","d1182b85.deb568"]]},{"id":"6582a3d4.cdb18c","type":"debug","z":"21583b71.76bf34","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":1330,"y":1260,"wires":[]},{"id":"17c048.1babbfb9","type":"mysql","z":"21583b71.76bf34","mydb":"b0768069.53b72","name":"","x":1050,"y":1220,"wires":[["6582a3d4.cdb18c","24c5862b.e89c2a"]]},{"id":"13d804ed.86d35b","type":"debug","z":"21583b71.76bf34","name":"node_join","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":980,"y":1120,"wires":[]},{"id":"d1182b85.deb568","type":"function","z":"21583b71.76bf34","name":"select-query","func":"insert = {topic: \"INSERT INTO pacientes (nombre,apellido,edad,sexo) VALUES ('\" + msg.payload.name + \"','\" + msg.payload.apellido + \"','\" + msg.payload.edad + \"','\" + msg.payload.sexo + \"')\"};\nreturn insert; ","outputs":1,"noerr":0,"x":910,"y":1260,"wires":[["17c048.1babbfb9","72ae7761.69c438"]]},{"id":"72ae7761.69c438","type":"debug","z":"21583b71.76bf34","name":"node_function","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1100,"y":1280,"wires":[]},{"id":"d759d4ea.0859b8","type":"ui_button","z":"21583b71.76bf34","name":"clear button","group":"356fac5d.d980c4","order":4,"width":0,"height":0,"passthru":false,"label":"clear","tooltip":"","color":"","bgcolor":"","icon":"","payload":"{}","payloadType":"global","topic":"","x":110,"y":1200,"wires":[["e61afa87.f3fc88"]]},{"id":"e61afa87.f3fc88","type":"change","z":"21583b71.76bf34","name":"clear-text-inputs","rules":[{"t":"set","p":"clear","pt":"msg","to":"true","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":320,"y":1200,"wires":[["82bc321d.667bd","4eb83a89.564704","86b4e489.513d98","a5908810.1de6d8"]]},{"id":"24c5862b.e89c2a","type":"ui_toast","z":"21583b71.76bf34","position":"dialog","displayTime":"3","highlight":"","outputs":1,"ok":"OK","cancel":"","topic":"Ingreso correcto del paciente","name":"Ingreso correcto del paciente","x":1360,"y":1200,"wires":[[]]},{"id":"86b4e489.513d98","type":"ui_text_input","z":"21583b71.76bf34","name":"edad","label":"edad","tooltip":"","group":"356fac5d.d980c4","order":3,"width":0,"height":0,"passthru":false,"mode":"number","delay":"300","topic":"edad","x":530,"y":1240,"wires":[["c983ed94.1e645"]]},{"id":"a5908810.1de6d8","type":"ui_text_input","z":"21583b71.76bf34","name":"sexo","label":"sexo","tooltip":"","group":"356fac5d.d980c4","order":3,"width":0,"height":0,"passthru":false,"mode":"text","delay":"300","topic":"sexo","x":530,"y":1300,"wires":[["c983ed94.1e645"]]},{"id":"55d76ff9.f1284","type":"mqtt-broker","z":"","name":"","broker":"localhost","port":"1883","clientid":"","usetls":false,"verifyservercert":true,"compatmode":true,"keepalive":"15","cleansession":true,"birthTopic":"","birthQos":"0","birthRetain":null,"birthPayload":"","closeTopic":"","closePayload":"","willTopic":"","willQos":"0","willRetain":null,"willPayload":""},{"id":"b0768069.53b72","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"DISPOSITIVO","tz":""},{"id":"56f79abd.6320c4","type":"ui_group","z":"","name":"Alarmas","tab":"16e86f12.023081","order":5,"disp":true,"width":"6","collapse":false},{"id":"b57a22fd.812da","type":"ui_group","z":"","name":"Contadores","tab":"16e86f12.023081","order":6,"disp":true,"width":"6","collapse":false},{"id":"293ba6c0.530d0a","type":"ui_group","z":"","name":"Tempamb","tab":"16e86f12.023081","order":2,"disp":true,"width":"6","collapse":false},{"id":"95bc1cba.c56de","type":"ui_group","z":"","name":"Tempcorp","tab":"16e86f12.023081","order":3,"disp":true,"width":"6","collapse":false},{"id":"9d168e43.85bdb","type":"ui_group","z":"","name":"Ritmocard","tab":"16e86f12.023081","order":4,"disp":true,"width":"6","collapse":false},{"id":"b7aab8a8.973748","type":"ui_group","z":"","name":"Promedio","tab":"16e86f12.023081","order":7,"disp":true,"width":"6","collapse":false},{"id":"356fac5d.d980c4","type":"ui_group","z":"","name":"Pacientes","tab":"16e86f12.023081","order":1,"disp":true,"width":"6","collapse":false},{"id":"16e86f12.023081","type":"ui_tab","z":"","name":"dispositivo","icon":"dashboard","order":2}]

"Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 2"

I do not know if it is possible to do what I want

2

Put a debug node showing the message going into the SQL node and see what it says that generates the error. If you can't see what is wrong with your query then post it by here

3

You have two tables. In the table pacientes the column idpacientes is an auto_incremented value. There for everytime you add a row, the idpacientes gets incremented.

In the second table datos you also have a column idpacientes.

In the flow you have a template node Datos_BD where you build an insert for the datos table and you use payload.idpacientes in it.

Where is payload.idpacientes coming from? (and in the table definition, it should NOT be allowed to be null)

4

Thanks for answering. This I have in the output of the mysql node "BD_datos" that I have the error:

"Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 2"

If I have this problem I have never defined payload.idpacientes because that parameter is belonging to the Patients table in mysql; I do not know if I should add any additional node to obtain this parameter, I have inconveniently I have not worked with related bases. I only added it in the template node, where the other data that comes from node mqtt is read

flujo1
flujo1.PNG1254×159 12.1 KB

I add the flow image too

FLUJO
FLUJO.PNG966×733 75 KB

5

Are you rolling your own EMR?! If so, I'm intrigued.

6

The error message you are getting points at a problem with the syntax of the sql query you are passing to the database node.

This is why Colin has suggested you use a Debug node to see the exact query you are passing the database node so you can spot what that syntax error is.

7

Electronic Medical Record ??, it could be said that something relatively similar only made the storage of the monitored data of each patient

8

I added the debug node and I have this error
node_mysql

9

That is the output of the database node.

We are suggesting you use a Debug node to see what message you are passing to the Database node.

You cannot fix the sql query if you don't look at it.

10

It's true, this I get at the exit of the template node, but that in the problem never registers the idpaciente attribute because I have not declared it and I do not know if you can help me do it, I have problems because they are related tables in mysq

node_template

11

Reading back I see that @zenofmud already replied on this.

I dont' know what your flow is meant to be doing so I cannot tell you where you should be getting idpaciente from - but it's clear in that Debug output it doesn't have a value, so the SQL statement is invalid.

12

If that's the problem, I do not get that parameter.
In the template node I add the data that is sent from several mqtt node.
But the idpatient parameter is a parameter that must be registered in a "patient" table and how they are related to the "data" table; then it is the same, this idpacientes is a autoincremental number

13

Do you know what query you want to send, or is your problem actually that you are not familiar with relational databases so you don't know exactly what to do? If so this is not really the right forum, as the problem is an SQL issue not a node-RED issue. There may be someone who can help of course.

14

You have a table of patient data - their name, gender etc. And each has a unique identifier.

You then have a table of records for individual patients. Those records use the patient identifier to show which patient the record is for.

When you get a piece of data from the MQTT node, how do you know what patient it is for?

15

In the tables that is what I have, but in truth that is the problem: when I get the data of mttq how this one knows for what patient it is !!! Any solution to this please? Because I assumed that if I add that idpaciente parameter it would be enough but it is not like that.

16

I'm not sure how we can help. We don't know anything about the system you are building. Who is publishing patient data to an MQTT broker?

17

Thanks Colin but to the answer of Knolleary gave me an idea that something is missing so that the data of mqtt know that patient is.

18

Everything is independent:

  1. I enter the patient's data manually and these are stored in a table.
    NODE_PACIENTES
  2. mqtt data is sent independently.
  3. Assume that with idpaciente was sufficient to be related between tables.
    datos
19

But who is sending it and how does anyone know which patient it relates to?

20

I send an external device that is publishing several topics, then in the mqtt node I subscribe to these topics.

And only the relationship between the "patients" table and the "data" table is made; to refer patients