ODBC connector, multiple msg(SQL) from For cycle

Jobs
1

Hello evrey one,

have an issue with the Helix ODBC node. I'm passing three messages from function node with three different queries (tested and correct), but it returns three identical messages related to the results of the last received query. Could it be a cache issue?

thanks

2

Welcome to the forum @Riccaleu

The most likely cause of this is that you are re-using the same msg object in your function. In javascript objects are stored by reference, so if you do, for example,

msg.payload = "something"
node.send(msg)
msg.payload = "something else"
node.send(msg)

then the second msg.payload = statement will overwrite the payload of the message you have just sent, which the ODBC node will not yet have actioned.
One solution is to clone the existing message each time

msg1 = RED.util.cloneMessage(msg)
msg1.payload = "something"
node.send(msg1)
msg2 = RED.util.cloneMessage(msg)
msg2.payload = "something else"
node.send(msg2)

You won't need to clone for the third one.

If that isn't it then post the code of the function. Copy/paste please, not screenshot.

See also Cloning messages in a flow : Node-RED which goes into cloning and pass by reference in more detail.

1 Like
3

Thanks for the quick reply

i have a message for each i element could be thousand

here the code

var query = ""
for (var i = 0; i < length; i++) {

     query = 
     `SELECT 
        
     var msg = {}
     msg.query = query
 
     msg.dsn = global.get("dsn");
     node.send(msg);


}
4

Switch to using let or not use a key identifier like msg along with var

var will apply to outside the scope - and if you use msg with var - you are affecting the msg that is still travelling during the cycle

for (let i = 0; i < length; i++) {
     
      let query =  `SELECT...'
      let msg = {}
      msg.query = query

      msg.dsn = global.get("dsn");
      node.send(msg);
}

Examples

var msg = {"Hello": "World"}

for(var i = 0; i<10;i++){
    var msg = {"What": "The!"}
    node.send(msg)
}
// msg is now surprisingly {"What": "The!"}

var msg = {"Hello": "World"}

for(let = 0; i<10;i++){
    let msg = {"What": "The!"}
    node.send(msg)
}
// msg is still {"Hello": "World"}
5

Which is one of the reasons why var is now deprecated. Use let or const instead.

1 Like
6

Thanks for the suggestion,
i applied let for each variable but unfortunately nothing change

7

can you share whole function block?

1 Like
8

yes , for privacy i need to mask some stuff`

context.data = context.data || {};

switch (msg.topic) {
     case "string":
          context.data.string = msg.payload.items;
          msg = null;
          break; 
}

let IdWL = context.data.string

let dateStart = new Date();
dateStart.setMinutes(0);
dateStart.setSeconds(0);//arrotondo alll'ora intera corrente

let dateEnd = new Date();


dateEnd.setDate(dateEnd.getDate() - 5)
dateEnd.setHours(0);
dateEnd.setMinutes(0);
dateEnd.setSeconds(0);



let dataStartFormat = dateStart.toISOString().replace('T', ' ').substring(0, 19);
let dataEndFormat = dateEnd.toISOString().replace('T', ' ').substring(0,19);


for (let i = 0; i < IdWL.length; i++) {

     let msg ={}
     let query = 
     `SELECT 
          ID,RECORDTIME, VALUE
     FROM 
          HIS15MIN
     WHERE 
         
     ORDER BY
          "RecordTime" ASC`
     
    
     msg.query = query
     msg.toipic = IdWL[i].pointId
     msg.dsn = global.get("dsn");
     node.send(msg);


}

Thanks a lot

9

What happens when you rate limit the output of the function node using a delay node set to rate limit? giving enough time to run each query.

1 Like
10

The spelling there looks suspect.

As well as rate limiting as suggested, feed the output of the function node into a debug node set to Output Complete Message and check that they look correct.

11

Thanks It works!

but i don't think is the best way to solve the problem, at least not the most efficient one, i'd like to menage it trougth waiting the message to complete its path or something like this

12

sorry,
just an error of writing. results in debug are ok!

13

knowing nothing about the node forward makes it hard to help further.

You can use rate limit as a queue. To make it more efficient once your sql node responds you can feed msg.flush set to 1 back to the delay node to release next. Read help text for delay node, both Colin and me have posted example flows for this in the past.
here is one

1 Like
14

Just to add some input, which I think is related??

a Semaphore node that was discussed above, had some issues, so I addressed them with my take on a Semaphore Node (with a lot of extras)

It may come in handy to address the discussed

1 Like