I am working with a couple of Arduino nano boards and node red installed on a windows server. I have node red running as expected, installed the SQL express edition on it and have successfully managed to figure out how to write commands and make entries into the table in my DB.
NOTE: I am using the MSSQL node.
I am however struggling with updating the entry in the table.
I would like to update the timestamp of that entry based on the MQTT message that is received from the client on my nanos.
This is a sample of what i have used for inserting into the table.
d = new Date(), dformat = [d.getMonth()+1, d.getDate(), d.getFullYear()].join('/') + ' ' + [d.getHours(), d.getMinutes(), d.getSeconds()].join(':');
pld = "INSERT INTO [CoIPSERVER].[dbo].[MQTTData] "
pld = pld + "(Location, Topic, Payload, Timestamp) "
pld = pld + "VALUES ('" + msg.Location + "', '" + msg.topic + "', '" + msg.payload + "', '" + dformat + "')";
msg.topic = ''
msg.payload = pld
return msg;
Now when the Arduino board goes offline and comes back online, i would like to have a check done in node red to see if this client already exists in the table. If it is, then it should just update the timestamp. If not, then insert a new record.
Appreciate any help on how this is to be done.
TIA
