It hardly matters if you are only inserting a couple of fields, but assuming the table has more fields...
You could do this (which I have not tested)
msg.topic = "INSERT INTO climatesensor"
msg.topic += " (sensorid, timestamp, temperature, pressure, humidity, airquality)"
msg.topic += " VALUES (?, CURRENT_TIMESTAMP(), ?, ?, ?, ?)"
msg.payload = [42, 97.4, 981, 75, 106]
return msg;
And 6 months later you discover that your BME680 can also report sea level pressure so you decide to include that.
Now your code has to change
msg.topic = "INSERT INTO climatesensor"
msg.topic += " (sensorid, timestamp, temperature, sealevelpressure, pressure, humidity, airquality)"
msg.topic += " VALUES (?, CURRENT_TIMESTAMP(), ?, ?, ?, ?, ?)"
msg.payload = [42, 97.4, 981, 1012.1, 75, 106]
Or is it
msg.topic = "INSERT INTO climatesensor"
msg.topic += " (sensorid, timestamp, temperature, sealevelpressure, pressure, humidity, airquality)"
msg.topic += " VALUES (?, CURRENT_TIMESTAMP(), ?, ?, ?, ?, ?)"
msg.payload = [42, 97.4, 1012.1, 981, 75, 106]
Using named parameters you add in the new fieldname and payload key, no need to ensure they are in the same position in msg.payload (object properties don't really have a position)
msg.topic = "INSERT INTO climatesensor"
msg.topic += " (sensorid, timestamp, temperature, sealevelpressure, pressure, humidity, airquality)"
msg.topic += " VALUES (:sensorid, CURRENT_TIMESTAMP(), :temperature, :sealevelpressure, :pressure, :humidity, :airquality)"
msg.payload = {
"sensorid": 42,
"temperature": 97.4,
"sealevelpressure": 1012.1,
"pressure": 981,
"humidity": 75,
"airquality": 106
}
Note that a BME680 sends it's data already in json form, so you may not actually need to rebuild msg.payload at all.
Here is an example from my flows:
if (msg.payload.Temperature > -20 && msg.payload.Temperature < 80 ) { // sanity check
msg.topic = "insert into sensordata "
+ "(location, timestamp, sensortype, temperature, humidity, dewpoint, pressure, seapressure, gas) "
// the payload properties are exactly as sent by the sensor
+ "VALUES (:location, :timestamp, :sensortype, :Temperature, :Humidity, :DewPoint, :Pressure, :SeaPressure, :Gas)"
return msg
}
Indeed the quotes are not needed here. But if I were assembling msg.payload in a template node they would be needed, so I generally include them.