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.
Colin, thanks for responding, but at the exit of the node template I get the following:
tempcorp : msg : Object
object
topic: "tempcorp"
payload: string
INSERT INTO dispositivo2 (temp_amb, temp_corp)
VALUES (68, 40)
qos: 0
retain: false
_msgid: "34aacb31.bf14e4"
The inconvenience occurs that is shown at the exit all that what I have programmed in the node template: that I have it configured in this way:
INSERT INTO dispositivo2 (temp_amb, temp_corp)
VALUES ({{payload.tempamb}}, {{payload.tempcorp}})
That looks ok, but if you look at the spec of the db node you will see that the query has to be in the topic not the payload. You have the template node configured to put the result in msg.payload, it should be msg.topic. I am fairly sure that you were told this much earlier, but perhaps that was a different thread.
It is true Colin thank you very much! It worked!, But this only works with whole numbers, sometimes my topic sends a string message, and when this happens this string is not inserted into the database
object
topic: "INSERT INTO dispositivo2 (temp_amb, temp_corp)āµVALUES (67, Hola)"
payload: object
tempamb: "67"
tempcorp: "Hola"
qos: 0
retain: false
_msgid: "e44ae4ac.761cd8"
This I get at the debug output of the mysql node
"Error: ER_BAD_FIELD_ERROR: Unknown column 'Hola' in 'field list'"
If you want to put strings in then you need quotes round the value in the query
INSERT INTO dispositivo2 (temp_amb, temp_corp)
VALUES ('{{payload.tempamb}}', '{{payload.tempcorp}}')
That is nothing to do with node red of course, that is an sqlite issue,
but what type have you made the column in the database when you created it?
Also, you don't really want that new line in the middle of the topic, it may not cause a problem but is not a good idea. In the template make it one line instead of two, so take out the new line before VALUES.
Thank you very much Colin, it worked correctly with the mentioned change, in the database I have the columns of type VARCHAR
That is ok then, I was concerned you might have made them of number type in which case you could not write a string.
In the creation of the database I considered that I had data of type Varchar, thanks for all the help in fact my knowledge is basic in node-red.
I have worked with the database and the node red locally, but now I want to make the change and work with a database locally, but with node red in a virtual machine; I have problems in the connection of the database; could someone help me please.
Regards
so if there are problems Iām guessing there is N error message?
What does the error message say?
Given that the original issue was marked solved itās probably better to start a new issue including the information I asked for.
Thank you very much for answering, I will start a new problem.
Regards
