Stop Flow on Database Insert error

General
1

I have the following flow (which is working fine BTW) but I have a requirement that if the insert fails the update should not take place.

image
image943×514 63.8 KB

As you see (green line) the flow is such that the insert if followed by an update.
What is the best way to stop the flow if there is an error in the insert?

I tried adding a function after the insert node but that did not work. I also used a catch set to detect an error on "SHRDGUI Insert" node but did not work for me.
May be I'm going into this in the wrong way.
Any ideas?

Thanks guys!

2

Does the red DB node output on both ports when there is an error? Or just the bottom one?

3

The error is the bottom. The top show the content that is passed to the insert.

4

show us what comes out of both top and bottom nodes when an insert error occurs.

use debug nodes set to "show complete msg"

5

Here are the two scenarios:

With no error:

image
image795×139 13.5 KB

and with error:

image
image856×534 45.6 KB

When there is an error debug 155 shows not output.

6

BTW, one of the things I tried was adding a function to the error output and in the function I use a throw but did not stop the process.

7

I cannot see the output of debug 155.

please cause 1 msg to cause 1 insert error and show BOTH debug nodes output expanded so I can decern what is happening.

In short, I want to see if 1 msg (that causes an insert error) outputs BOTH ports of the DB node AND see if there is anything in them that would allow you to check (and halt) the flow of the msg.

No, that is not how Node-RED works. Messages are passed down the wires. Either disconnect the wire (design time / no good here) or halt the msg flow (using something like a function node to return null or switch node that evaluates a non true condition)

8

The thing is that when there is an error the other exit point does not show any thing only the error is shown.

9

That is why my first question was

So, if the msg leaves the bottom port ONLY, the top port never releases a message so will never travel down the wires (that you highlighted in green) - there is no message to stop (it was never emitted)

Am I misunderstanding or missing something?

10

Yes. I think that's the case.
I expect that the error output takes precedence in case of an error. Thats the reason I tried this:

image

but even when I return null it does nothing. I'm thinking it is because the function is not part of the flow.

11

Then, thinking about it. One would think that if there is no output nothing should travel down the wire to trigger the next action.

So, with that thought I disconnected the output of the insert node and ... it continues to process the update.

I then realized that the data for the update joins from the counter branch.

image

At first I thought it was JUST a counter but it will push data through.

Is is possible to use a catch node and then read it from a function? If that is possible then a function between the Graduates counter and the Graduation updates function should do the trick.
When there are no errors pass the data through but on error return null. What do you think?

Actually I could even use the Graduation Updates to read for the error and handle it right there, if there is a way to read the catch node for errors.

12

I don't use Oracle, nor do I know what your INSERT and UPDATE queries do.
But I'm sure that Oracle provides triggers.

Can you define a post-insert trigger to do the UPDATE processing entirely within the database?

13

Actually I just found a solution inside NR. Using a global variable "withErrors" and the function Graduation Updates. If the insert node throws an error, then it sets the global. I then read the global variable (if it is set) in the function and return none if it is, otherwise I carry on as I should. It worked a treat!
Thanks for your reply.