Been working on this for several days and have exhausted my abilities and various suggestions via the web and these forums...so thank you in advance for any assistance!
Basis:
Have 3 MQTT inputs/message payloads
In node red, converting payload to number(s)
Since all messages had same designation of msg.payload, using a change node/move rule to make unique transformations of 3 msg.payload (s) to (msg.Temp, msg.Humidity, msg.Pressure)
Have PHP MySQL running on Pi, Node red connection tested successfully with simple insert statements where the insert statement contains static values, verifying successful DB insert
Issue/Error
The "INSERT INTO" function is:
var newMsg = {
topic : "INSERT INTO exterior_climate (Temp, Pressure, Humidity, Light, Voltage) VALUES ('"+msg.Temp+"','"+msg.Pressure+"','"+msg.Humidity+"',30,20)"
};
return newMsg
However no records are being inserted into the DB, shown below is the output from the debug window:
All inserts into the DB fail. I am confused that the debug is outputting 3 messages staggered that in sum do show the proper msg.XXX in their correct location (BTW: by design the last 2 values are static for the moment), but that the insert does not appear to be fully populated with the 3 values, then transferred to the insert.
Just to add further clarification, in case you didn't realise, the problem is that your function assumes that each message contains all three values whereas, as you can see, each message has only one of them defined. The solution is to use a Join node as suggested. You won't need to move the values into individual properties, the Join node will do that for you.
Thank you both! I had tried the Join earlier, but missed the "Send the message after xxx message parts" so obviously the join was not properly configured. With this properly configured I am able to get the proper values into the insert statement as shown:
However, the insert statement is not actually inserting any records into the DB, everything looks OK to me. Can anyone see an issue with my Insert statement?
var newMsg = {
topic : "INSERT INTO exterior_climate (Temp, Pressure,Humidity, Light, Voltage) VALUES ("+msg.payload.Outside_Temp+","+msg.payload.Outside_Pressure+","+msg.payload.Outside_Humidity+",30,20)"
}
return newMsg;
As previously stated, I was able to do inserts with hand entered numbers prior.
Don't show us what is in the function, what matters is what is going into the sql node. Use the little Copy Value button next to the value in the debug window.
You didn't answer the question about what is coming out of the sql node.
Also are you using node-red-node-mysql (look in Manage Palette to check). Have you installed any other sql nodes?
Here is what is coming out of the debug node that is wired after the insert statement:
[null,null,null,null,null]
Yes I am using the Node Red MY SQL Node, properly configured and connected (as shown by the green dot). During previous troubleshooting using static values in the insert command, I was able to successfully insert records into the DB.
All, again...many thanks. I continued to fiddle with the insert statement and even though I am uncertain which of the many things I tried did work. The data is now inserting to SQL. I hope to get more proficient with NR so that at some time I may be of assistance to others. Kind Regards
Moving to the next step of selecting data from the MYSQL DB and pushing X records out to a chart. I have learned a lot and copied a lot of examples with marginal success, but have exhausted my abilities...so once again asking for assistance.
My goal is to take the outputs of the select statement which contain multiple fields/data points which use this within a template node:
SELECT Timestamp,Temp,Pressure,Humidity,Light FROM exterior_climate ORDER BY TIMESTAMP DESC LIMIT 20
With of course 20 objects from the select statement.
From there I have tried dozens of different functions, formats, and set nodes to prepare this message for output to a chart but with no real success. My goal is (perhaps lofty) to dynamically set the data series from the SQL table names as moving ahead I may add fields to the database and would like Node Red to accommodate these new fields without re-coding. To-date I have only been able to get the following results:
Obviously only currently getting the first series of Temp data, and unable to parse the field name out of the first object. Thank you in advance for any guidance!
Thank you so very much Steve! I will take your suggestion and move to a new thread. The solution you directed me to provides valuable progress, and I will continue to work towards dynamic series identification (if indeed that is at all possible).
