Read from mysql db

Dashboard
1

I am tried to read data from MySQL db from node red flow. I have created the flow below but it doesn't show me any an error or anything at all.any help regard this please?

flow
flow.png728Ă—206 7.78 KB

This my flow shown below

[{"id":"a39c7184.d9df6","type":"mysql","z":"627ecc4d.710c94","mydb":"494c5b78.7c6a84","name":"hello","x":409,"y":158,"wires":[["946d1bad.7af978"]]},{"id":"6c996cfc.b19b04","type":"inject","z":"627ecc4d.710c94","name":"","topic":"","payload":"","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":123,"y":160,"wires":[["1d492521.7c287b"]]},{"id":"e75c49b9.08d068","type":"debug","z":"627ecc4d.710c94","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":630,"y":246,"wires":[]},{"id":"946d1bad.7af978","type":"function","z":"627ecc4d.710c94","name":"output","func":"if (msg.payload >0){\nreturn msg.payload;\n}","outputs":1,"noerr":0,"x":553,"y":160,"wires":[["e75c49b9.08d068"]]},{"id":"1d492521.7c287b","type":"function","z":"627ecc4d.710c94","name":"select","func":"\nmsg.topic = \"SELECT * FROM location \";\n","outputs":1,"noerr":0,"x":263,"y":159,"wires":[["a39c7184.d9df6"]]},{"id":"494c5b78.7c6a84","type":"MySQLdatabase","z":"","host":"sl-eu-lon-2-portal.11.dblayer.com","port":"27707 ","db":"compose","tz":""}]
2

You flow is not importable. You need to edit your previous flow and put a line with three backtick characters before it and another after it.

3

Also I suspect that you may not have tried putting a debug node after the first function block to make sure the message it sends is what you expect. Have you done that?

4

sorry ,but do you mean to add ``` the start and the end of the flow

[{"id":"a39c7184.d9df6","type":"mysql","z":"627ecc4d.710c94","mydb":"494c5b78.7c6a84","name":"hello","x":464,"y":92,"wires":[["946d1bad.7af978","e75c49b9.08d068"]]},{"id":"6c996cfc.b19b04","type":"inject","z":"627ecc4d.710c94","name":"","topic":"","payload":"","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":123,"y":160,"wires":[["1d492521.7c287b"]]},{"id":"e75c49b9.08d068","type":"debug","z":"627ecc4d.710c94","name":"","active":true,"tosidebar":true,"console":true,"tostatus":true,"complete":"payload","x":640,"y":246,"wires":[]},{"id":"946d1bad.7af978","type":"function","z":"627ecc4d.710c94","name":"output","func":"if (msg.payload >0){\nreturn msg.payload;\n}","outputs":1,"noerr":0,"x":553,"y":160,"wires":[["e75c49b9.08d068"]]},{"id":"1d492521.7c287b","type":"function","z":"627ecc4d.710c94","name":"select","func":"\nmsg.topic = 'SELECT mac FROM location';\n// msg.topic = msg.payload;\nreturn msg.topic;\n","outputs":1,"noerr":0,"x":263,"y":159,"wires":[["a39c7184.d9df6","e75c49b9.08d068"]]},{"id":"494c5b78.7c6a84","type":"MySQLdatabase","z":"","host":"sl-eu-lon-2-portal.11.dblayer.com","port":"27707 ","db":"compose","tz":""}]
5

I have putting now a debug node after the first function block and, it shows an error as shown below

"Function tried to send a message of type string"

6

As I said you should put the three backticks on separate lines before and after the flow, so the forum knows it is code. I am not sure exactly what you did but anyway I managed to import the flow.

The message says you are return a string. You are returning msg.topic which is a string, so the message is quite correct. You must always return a complete message, so your last line should be
return msg;
See https://nodered.org/docs/writing-functions for more information about writing functions.

1 Like
7

Thanks ,it solve it.

8

Hello, please I need your help, I am subscribed to different topics and the values ​​of these topics I wish to store them in a Mysql database, but when I perform the data insertion I have these results. That is, the same value of a single topic is repeated in the columns, and I want each value of each topic to be inserted in the corresponding column, please how could this be solved

Captura3

9

Well from what you show, it is working fine. you have told it to insert five '16's.

so the question is how are you building your insert statement and what is the data you feed it (you can find this by using a debug node attached to the node feeding what ever node you are using to build the sql statement.

10

Thanks for answering me Zenofmud, and the node function this using the following:

var pld = "INSERT INTO dispositivo2 "
pld = pld + "(temp_amb, temp_corp )"
pld = pld + "VALUES ('"+msg.payload+"','"+msg.payload+"');"
msg.topic = pld
return msg;

The data I get from 2 different topics

11

I also use this flow

Captura5
Captura5.PNG793Ă—230 18.6 KB

12

The fact that you have two mqtt in nodes wired to the same function node will not automagically combine two separate msg objects into 1 -- you will need a join node for that. You will probably want it to wait for both topics to arrive, and combine them into a single key/value msg, using the topic as the key name.

Following the join node, I like to use a template node with the sql query string, substituting the two incoming value with some "mustache" syntax, similar to this:

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

... makes it much easier to build the string, and the template node will allow you to put the output string directly into the msg.topic property, where the sql node looks for it.

BTW, it is recommended that MQTT topics should not start or end with a slash character...

14

Thank you for answering my question Shrickus, I have made all the mentioned changes; but now I have this error; I'm new to node, could you please help me with this?

Captura10
Captura10.PNG1137Ă—194 23.7 KB

Captura7
Captura8

15

You have the template node setting msg.payload instead of msg.topic

16

Thank you for your response Colin, but you made the change and now I get this message, in reality there are no changes that can be made to avoid these errors.

Captura1
Captura3

17

It is pointless showing debug output unless you make it clear which node is giving which debug output. All you showed was a join node and debug outputs.

18

It is true, Colin, but I still can not insert the data in the database, I have placed a node debug to the output and I still get this message:

"msg.topic : the query is not defined as a string"

19

Where is that message coming from? If it is coming out of the sql node, then we need to see what is going into it.

20

Colin, that's how I have my flow; I have made the aforementioned changes but I can not insert the data

image
image.png1175Ă—212 18.9 KB

21

As I said, we need to see what is going into each node. Put a debug at each point and post them here. Give the debug nodes names then it will be clear from the output which is which. Post another picture of the flow obviously so we can see which is which.