Insert Into MySQL table

Hello everyone,

Sorry to have to ask, but I am new to node-red and I am pulling my hair out with this one! I know it's not hard to do this, still, I just can't get it to work. I have a really simple flow, the image is below.

Basically I have a Rockwell PLC that is connected to node-red, the PLC data is coming into node-red correctly and it shows up exactly as it should in the log. I also have a valid connection to MySQL, so I believe the problem is with my insert into statement.

Sorry the image below is small, but I can only post two because I'm a newbee! If you click on it and click on it again it'll zoom up where there is much more information that could help.

If anyone can help me I'd gladly appreciate it, I have an important project to finish under a short deadline.

I'd even consider paid services for anyone who can solve this issue for me, sure, I know that it doesn't work that way on these forums, but this is really important for a water district.

And when I know how to do this I'll respond in turn for other users.

Kind regards,
Jay

What is the msg look like from the serial node?

You have shown an image containing
msg.topic = "...."
Where have you put that? It looks as if it should be in a function node, but there isn't a function node in your flow.

First thing you should do is test a 'select' statement against the database to make sure your mysql node settings are correct.

Add an insert node and set msg.topic to
select count(*) from your_table_name
where 'your_table_name' is the name of the table in the database.

This way you can see if your node settings are correct.

Your SQL statement should be passed to the mysql node in msg.topic.

Using the examples at https://flows.nodered.org/node/node-red-node-mysql you could use one of these formats:

1 The entire query in msg.topic and assuming msg.payload contains just the plc_value

msg.topic = "insert into plc_data (plc_tag, plc_value, plc_timestamp) values ('POTW_ph', msg.payload, '2011-01-01 12:00:00')"

2 Data in msg.payload as an array

msg.payload = ["POTW_ph", 101, "2011-01-01 12:00:00"]
msg.topic = "insert into plc_data (plc_tag, plc_value, plc_timestamp) values (?, ?, ?);"

3 Data in msg.payload as a JSON object

msg.payload = {"plc_tag": "POTW_ph", "plc_value": 101, "timestamp": "2011-01-01 12:00:00"}
msg.topic = "insert into plc_data (plc_tag, plc_value, plc_timestamp) values (:plc_tag, :plc_value, :plc_timestamp);"

I've always used the first version but now I've read the page I linked above, I could have saved myself a lot of reformatting by using the third. :woozy_face:

1 Like