CODEWITHSUNDEEP
javascriptmysqlsqlnode-red
Mike L
Mike L

Reputation: 41

For Loop to insert multiple rows in SQL

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.

Upvotes: 0

Views: 2261

Answers (2)

codtex
codtex

Reputation: 6548

@Chris Travers solution is working great if you want to have an INSERT statement for each value in msg.payload. With some modifications like this: 

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

You can have a single INSERT statement like this:

INSERT INTO opt_alarms VALUES
(now(),'RECEIVING', 'C-2B'),(now(),'RECEIVING', 'C-3A'),(now(),'RECEIVING', 'C-3B'),(now(),'RECEIVING', 'C-3C'),(now(),'RECEIVING', 'C-3E'),(now(),'RECEIVING', 'C-3F'),(now(),'RECEIVING', 'C-4A'),(now(),'RECEIVING', 'C-4B'),(now(),'RECEIVING', 'C-4D'),(now(),'RECEIVING', 'C-4E'),(now(),'RECEIVING', 'C-4F'),(now(),'RECEIVING', 'C-5A'),(now(),'RECEIVING', 'C-5B'),(now(),'RECEIVING', 'C-5C'),(now(),'RECEIVING', 'C-5D'),(now(),'RECEIVING', 'C-5E')

Upvotes: 1

Chris Travers
Chris Travers

Reputation: 26454

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.

Upvotes: 2

Related Questions