Insert Into MySQL table

[JayDLL]

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

[ScheepersJohan]

What is the msg look like from the serial node?

[Colin]

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.

[zenofmud]

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.

[jbudd]

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.