For Loop to insert multiple rows in SQL

Question

I have the following incoming payload:

msg.payload : array [16]
[ "C-2B", "C-3A", "C-3B", "C-3C", "C-3E", "C-3F", "C-4A", "C-4B", "C-4D", "C-4E", "C-4F", "C-5A", "C-5B", "C-5C", "C-5D", "C-5E" ]

The above payload is being inputted into the following function node (which will then be forwarded to the appropriate opt_alarms table:

for ( i = 0; i < msg.payload.length; i++){
    insert = "INSERT INTO opt_alarms VALUES (now(),'RECEIVING', '"+ String(msg.payload[i]) +"')";
    return {topic:insert, payload:insert}
}

I'm trying to parse the incoming payload and have it list each value, from the incoming array, on a separate row within that opt_alarms TABLE. Any suggestions would be appreciated.

Answer 1

If you are trying to use return inside a loop, I would recommend using map instead of a for loop. So instead of a for loop:

var statements = msg.payload.map(function(x){
    return "INSERT INTO opt_alarms VALUES (now(),'RECEIVING', '" + string(x) + "'";
});

This is not quite equivalent but since you are creating a two element object with the same sql in both, it at least transforms the array of values into an array of SQL statements.

This is not SQL injection safe so you want to look carefully to see if parameterized queries are supported and if not make sure you sanitize your input to MySQL specifications.