Iterating an array of elements and query execution on Sql Server database

General
1

Hi,

I have a 'json' object with an array that contains some objects as its element. I would like to iterate over the array, do some calculations with the values of each array element and then create a new object to represent the result. I would like to insert each newly created result object into a table in Sql Server database. The insert query could also be followed by several different sql statements down the flow.

Here is what i have tried so far:

  1. Loop through the array elements
  2. For each element i.e object, calculated new results from the values in the object properties.
  3. Defined a new result object to store the calculation results.
  4. Before next iteration starts, i have used "node.send(msg)" to pass it to "node-red-contrib-mssql-plus" node and execute an insert query.

The process repeats for each element in the array. I am sending messages asynchronously by using "node.send()" function. Is this the right way and only way to do it? With this approach, i could not find a way to get a response back from the database. In other words, i do not know if the insert statement executed successfully or not.

Is there a possibility to know if the previous query executed successfully before processing other elements of an array and executing the same query within the loop? I am looking for a solution to make the complete process synchronous. I hope, i have explained my question in a simplest form and i am sorry about my below average English.

Thanks a lot in advance.

Best regards,

2

I assume you mean a javascript object, since json is always a string.

Probably not the best way. Instead, start by feeding it into a Split node, that will send one message for each element in the array so you can then manipulate them one at a time.

3

Hi,
Thank you for your suggestion.

I have understood that it is better to use split node to access and manipulate the elements of an array.
But does it still answers the question that i have posted? Is there a way in the split node to access and manipulate next element only after the data insertion of previous element into a table is accomplished?

In other words, how do i make the implementation synchronous?

Best regards,

4

Use a delay node set to rate limit, set the limit high ( 1 message every minute or higher if needed). then when the sql node finishes send a message into the rate limit node, of {"flush":1}. This will flush the next item from the delay node. Read the delay node help text for more options. There are some contrib queuing nodes to.

5

That is not a question specific to any particular node.
Is it necessary to sequence them in that way? Does the data inserted for the first message affect the data inserted for the second or later? What will go wrong if you just let the sql node handle them at full speed?

6

Hi,
The sequencing is important in my case because i would like to make sure that every element in the array is manipulated and stored successfully into the database. If an exception occurs for an iteration while executing sql statements into the database, no matter for what ever reason, i could skip this particular iteration and carry on with other remaining elements.

The size of the array which i am considering could be above 10,000 or even more in some cases. If i let the sql node handle them at full speed as you have said then, won't the database throw 'Connection time out' error? That is what i have experienced so far and this error could be prevented by using some delay nodes or by setting a timer for few seconds. The sql server database is also being used constantly and simultaneously by other applications in our company.

The process, starting from accessing the array elements, manipulating the elements and executing the sql statements should be repeated every X minutes. And i would like to repeat the process only after all elements in the array are processed.

Best regards,

7

OK, here is a simple flow which allows a sequence of nodes to be protected so that only one message is allowed in at a time. It uses a Delay node in Rate Limit mode to queue them, but releases them, using the Flush mechanism, as soon as the previous one is complete. Set the timeout in the delay node to a value greater than the maximum time you expect it ever to take. If for some reason the flow locks up (a message fails to indicate completion) then the next message will be released after that time.
Make sure that you trap any errors and feed back to the Flush node when you have handled the error. Also make sure only one message is fed back for each one in, even in the case of errors.

image
image1068×291 33.5 KB 

[{"id":"b6630ded2db7d680","type":"inject","z":"bdd7be38.d3b55","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":140,"y":840,"wires":[["ed63ee4225312b40"]]},{"id":"ed63ee4225312b40","type":"delay","z":"bdd7be38.d3b55","name":"Queue","pauseType":"rate","timeout":"5","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"minute","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"allowrate":false,"outputs":1,"x":310,"y":840,"wires":[["d4d479e614e82a49","7eb760e019b512dc"]]},{"id":"a82c03c3d34f683c","type":"delay","z":"bdd7be38.d3b55","name":"Some more stuff to do","pauseType":"delay","timeout":"5","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"allowrate":false,"outputs":1,"x":800,"y":840,"wires":[["7c6253e5d34769ac","b23cea1074943d4d"]]},{"id":"2128a855234c1016","type":"link in","z":"bdd7be38.d3b55","name":"link in 1","links":["7c6253e5d34769ac"],"x":95,"y":920,"wires":[["3a9faf0a95b4a9bb"]]},{"id":"7c6253e5d34769ac","type":"link out","z":"bdd7be38.d3b55","name":"link out 1","mode":"link","links":["2128a855234c1016"],"x":665,"y":920,"wires":[]},{"id":"b23cea1074943d4d","type":"debug","z":"bdd7be38.d3b55","name":"OUT","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":670,"y":760,"wires":[]},{"id":"d4d479e614e82a49","type":"debug","z":"bdd7be38.d3b55","name":"IN","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":470,"y":760,"wires":[]},{"id":"3a9faf0a95b4a9bb","type":"function","z":"bdd7be38.d3b55","name":"Flush","func":"return {flush: 1}","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":190,"y":920,"wires":[["ed63ee4225312b40"]]},{"id":"7eb760e019b512dc","type":"function","z":"bdd7be38.d3b55","name":"Some functions to be performed","func":"\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":550,"y":840,"wires":[["a82c03c3d34f683c"]]},{"id":"e35f37deeae94860","type":"comment","z":"bdd7be38.d3b55","name":"Set the queue timeout to larger than you ever expect the process to take","info":"","x":270,"y":720,"wires":[]}]

[Edit] I see this is the same suggestion that @E1cid made.

8

Thank you very much for the suggestions. This is very helpful and it seems this is the right way to implement. As you have mentioned, this is also similar to the solution which @E1cid is also recommending.

Thank you very much guys.

Best regards,

9

Hi,

I have tried to implement using the Delay node as you have mentioned. Every element of the queue gets flushed out twice, once after using {"flush":1}; the number of times as the number of elements in the queue and secondly, the items from the queue is also removed in an interval defined in the rate attribute in the delay node.
Am i doing something wrong?

10

Show us how you have configured the delay node

11

Hi,
sorry for the late reply. I just figured out that, the link in and link out node between flush function node and queue node is showing this strange problem. I removed the link nodes and then simply joined the two nodes with a straight connection and then it is working as expected.

Thank you once again for your concern. I will post again if i get other issues.

Best regards

12

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.