Updating multiple SQL records at once

Novice to NR and SQL.

I'm doing a query where I pull columns of records (one unique) from a database.

Then I do some simple math to the non-unique records and then return all of it to their respective record in SQL based on the unique record.

When I query the database, the payload comes in as an array and objects for each set of data.

I wrote a function to do the math and return the new value which should loop for each payload. It all works but only for the first set of data from SQL. It won't loop. Not sure if it's the code or how NR is handling the payload coming out of the function.


payload[0] is the only one going through. Any help would be appreciated.

Ignore the topic on the "data after function" debug. The next node reformats the topic to update.

While I'm not prepared to type in and test the code and data that you posted only as a picture, I suspect the problem is the return statement in your do while loop.

return msg; puts msg on the output wire and exits from the function node.

Take a look at node.send()

var i = 0; 

while (i < 700) {
var qas_bc = msg.payload[i].qas_bc;
var dips = msg.payload[i].dips;
var finished = msg.payload[i].finished;

var new_dips = parseInt(dips);
var isFinished = String(finished);

if (dips > 1) {
    new_dips--;
}

else if (dips == 1) {
     new_dips--;
      isFinished = "yes";
} 

msg.qas_bc = qas_bc;
msg.dips = new_dips;
msg.finished = isFinished;

msg.payload = {
    "qas_bc": msg.qas_bc,
    "dips": msg.dips,
    "finished": msg.finished
    }

node.send(msg);
i++;
return;
}

I read and watched a few videos on multiple message outputs but they're more about arrays. I assume I have to send an individual message for each process so the SQL can properly update each record.

If I put the return; outside the last }, it still works but I get an error from the function saying it "cannot read properties of undefined (reading 'qas_bc')"

You are effectively doing this:

while (i < 700) {
   Set some variables from msg.payload[i]
   Some calculations
   msg.payload = {blah: blah}    // OVERWRITING msg.payload!
   node.send(msg)                  // Yes, this is [almost] right!
   i++
   return         // NONONO
}

You destroy your input data by overwriting msg.payload with values from payload[0].
Return exits the function node without ever trying to access payload[1] (It no longer exists anyway)
If you use node.send for every array in the array then you don't want/need return.

One way to avoid destroying the input data is to copy it into a var. Another is to build up each output in newmsg rather than msg.

What happens if msg.payload arrives with less than 700 elements?
You should test for the existence of msg.payload[i] before processing it.

It might be possible to use a split node before the function to create one message for each array element then you dont need the loop.

Hi,

I do this type of thing (MYSQL updates) and also vote for using the 'split' node followed by the core 'processing' nodes.

It automatically separates each returned row from the database and removed the loop constructs entirely.

Cheers,

Paul

Ha, figured it out. Put a split node after the SQL query, fed that into the function that just processes one object, then spit it back out. Jeez, that was way easier, lol. Thanks all.

1 Like