Read from mysql db

@Tefita - in the join node, set the "Send the message -> After a number of message parts" to 2. As it is, you are only sending one value thru and it is in msg.payload. when you put in the 2 you should see th key:value pairs showing up. not msg.payload.temp_amp

Thank you for answering my questions, but I still have problems, do not know if I should change something in the node template?

image3
image2

Colin, place the node debug on all the nodes, and send no more information than the capture.

Please attach an export of your flow

Thanks for your help, here is the flow

[{"id":"9f5049ab.a43a98","type":"mqtt in","z":"284cd45c.6a8e2c","name":"","topic":"prueba1/temp_amb","qos":"0","broker":"478ceb99.2c8d94","x":328,"y":306.9895658493042,"wires":[["4a052a03.7d37c4"]]},{"id":"80161886.658e18","type":"mqtt in","z":"284cd45c.6a8e2c","name":"","topic":"prueba1/temp_corp","qos":"0","broker":"478ceb99.2c8d94","x":317,"y":420.98956871032715,"wires":[["4a052a03.7d37c4"]]},{"id":"d08e3627.a84018","type":"debug","z":"284cd45c.6a8e2c","name":"debug_mysql","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":1081.0001831054688,"y":250.9896068572998,"wires":[]},{"id":"4a052a03.7d37c4","type":"join","z":"284cd45c.6a8e2c","name":"Datos","mode":"custom","build":"object","property":"topic","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":true,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":514.0000953674316,"y":366.9896306991577,"wires":[["80775f4c.4f899","6b20ebde.333334"]]},{"id":"855df027.87953","type":"mysql","z":"284cd45c.6a8e2c","mydb":"8df20f66.9d9a3","name":"","x":877.1285095214844,"y":245,"wires":[["d08e3627.a84018"]]},{"id":"6b20ebde.333334","type":"template","z":"284cd45c.6a8e2c","name":"Datos_BD","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO dispositivo2 (temp_amb, temp_corp)\nVALUES ({{payload.temp_amb}}, {{payload.temp_corp}});\n","output":"str","x":678.1666870117188,"y":277.3229808807373,"wires":[["855df027.87953","836a9322.9998f"]]},{"id":"80775f4c.4f899","type":"debug","z":"284cd45c.6a8e2c","name":"debug_nodo_join","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":694.1666717529297,"y":433.9895601272583,"wires":[]},{"id":"836a9322.9998f","type":"debug","z":"284cd45c.6a8e2c","name":"debug_nodo_template","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":905.1666793823242,"y":344.9895782470703,"wires":[]},{"id":"478ceb99.2c8d94","type":"mqtt-broker","z":"","name":"","broker":"localhost","port":"1883","clientid":"NodeRedSQLClient","usetls":false,"compatmode":true,"keepalive":"15","cleansession":true,"willTopic":"","willQos":"0","willPayload":"","birthTopic":"","birthQos":"0","birthPayload":""},{"id":"8df20f66.9d9a3","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"dispositivo2","tz":""}]

please read this post and then edit your post above How to share code or flow json

Oh thanks the correction, I hope the flow is fine

In your template node you have

INSERT INTO dispositivo2 (temp_amb, temp_corp)
VALUES ({{payload.temp_amb}}, {{payload.temp_corp}});

so when and where are msg.payload.temp_amp and msg.payload.temp_corp being created? (HINT: You might want to change the debug node's so they display the complete msg object)

I made the given suggestion and the changes were made, but then I must change the template node to another node, to get the insertion in the database

Thanks for the help


f2

You have the Join node set to Combine each msg.topic. It should be combine each msg.payload using topic as the key. Change that then look at the output of the join node again particularly what is in the payload to see what you have to put in the template.

At the exit of the join node I get the following message, where it is a "payload",
C2

I have not really realized where my fault is, in the template flow I have configured it in this way:

INSERT INTO dispositivo2 (temp_amb, temp_corp)
VALUES ({{payload.temp_amb}}, {{payload.temp_corp}});

You have not corrected the join node as I said. You need to change it to combine each msg.psyload.

is true, I did not understand correctly what I should do, but now I have this in debug:
p1
p2

I still can not understand how that payload: object, insert it in a database

You can access the two variables using msg.payload["prueba1/temp_amb"] and similarly for the other one. You have to use the [...] because of the /. If you wrote msg.payload.prueba1/temp_amb it would think you meant msg.payload.prueba1 divided by temp_amb.

If you hover with the mouse over the value in the debug pane then is shows some little square buttons. One of those will say Path Copied if you click it which means it will have copied to the paste buffer the path in the message to that value, which you can then paste in somewhere. That saves having to work out how what it is. Try it and you will see what I mean.

[Edit] I have made a correction above, forgot the quotes in the square brackets.

Thanks Colin, I am not very clear about, I must change this / by "[...]"

There is only so much I can do without actually developing your whole app for you. You want to get those values into your template, I have told you how to access them.

Thanks Colin, but I still have a problem in the node template

What problem?

The name of the topic on which I am writing has been changed to avoid inconveniences, but I can not insert in the database yet I share my flow, so please check it

[{"id":"627efcf9.07be44","type":"mqtt in","z":"22868e62.834752","name":"","topic":"tempamb","qos":"0","broker":"478ceb99.2c8d94","x":275.6666564941406,"y":254,"wires":[["f842bdee.6c952"]]},{"id":"3b627e1b.a530c2","type":"mqtt in","z":"22868e62.834752","name":"","topic":"tempcorp","qos":"0","broker":"478ceb99.2c8d94","x":264.6666564941406,"y":368.00000286102295,"wires":[["f842bdee.6c952"]]},{"id":"386f9141.5be72e","type":"debug","z":"22868e62.834752","name":"debug_mysql","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":1058.6668395996094,"y":198.0000410079956,"wires":[]},{"id":"f842bdee.6c952","type":"join","z":"22868e62.834752","name":"Datos","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":491.66675186157227,"y":314.0000648498535,"wires":[["780b5174.00ce6","c4a3b81.1b4af48"]]},{"id":"be0eaff0.93142","type":"mysql","z":"22868e62.834752","mydb":"8df20f66.9d9a3","name":"","x":854.795166015625,"y":192.0104341506958,"wires":[["386f9141.5be72e"]]},{"id":"780b5174.00ce6","type":"debug","z":"22868e62.834752","name":"debug_nodo_join","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":671.8333282470703,"y":380.9999942779541,"wires":[]},{"id":"c29ee9d3.41ec18","type":"debug","z":"22868e62.834752","name":"debug_nodo_template","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":882.8333358764648,"y":292.0000123977661,"wires":[]},{"id":"c4a3b81.1b4af48","type":"template","z":"22868e62.834752","name":"Datos_BD","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"INSERT INTO dispositivo2 (temp_amb, temp_corp)\nVALUES ({{payload.tempamb}}, {{payload.tempcorp}});","output":"str","x":651.8333282470703,"y":235.3333797454834,"wires":[["be0eaff0.93142","c29ee9d3.41ec18"]]},{"id":"478ceb99.2c8d94","type":"mqtt-broker","z":"","name":"","broker":"localhost","port":"1883","clientid":"NodeRedSQLClient","usetls":false,"compatmode":true,"keepalive":"15","cleansession":true,"willTopic":"","willQos":"0","willPayload":"","birthTopic":"","birthQos":"0","birthPayload":""},{"id":"8df20f66.9d9a3","type":"MySQLdatabase","z":"","host":"127.0.0.1","port":"3306","db":"dispositivo2","tz":""}]

Thank you so much for everything.

There was no need to change the topics, I told you how to access it the way it was, however you can change them if you want.
Are you getting the right query out of the template node now? Until you get the right query out of the template then it will not insert the data into the database.