SQL Query errors terminate transaction

[Former Member]

I have a SQL query - command action block in my transaction used to insert data into a database.  I'd like to capture the output of that query into an output variable even when the query fails, but I seem to be having some trouble.  Currently, when the query fails (because the insert data is incorrect, or the database isn't available, or any other number of reasons) the transaction terminates and action blocks beyond the query do not get executed.  Is there a way to prevent the transaction from terminating and allow me to define the next-step logic when the query fails?

I'm using MII 12.2.3 Build(177)

[Former Member · Accepted solution]

Hello Alli, just place a catch after your query, this will allocate the exception to a specific branch but will resume the current flow of transaction afterward.

Cheers,

Arnaud

[Former Member · Accepted solution]

Arnaud -

To the original post I attached a screenshot of the transaction.  Right now, I have a Tracer and a Conditional action block placed after the SQL query, but neither one of them are getting executed when the SQL query fails.  Is there a specific action block I need to use?

Alli

[former_member196557 · Accepted solution]

Hi Ally!

Place a Catch Action in the branch that you want to handle the exception.  Note that if the Catch is on the left side branch, that branch will execute and then the right side branch will execute, unless there is a Terminate Transaction block in the exception branch.

Regards, Steve

[agentry_src · Accepted solution]

Hi Arnaud,

I think you on correct, but since this is new functionality with 12.2, you will need to be a bit more explicit in your directions. 

Specifically, how does she turn on the exception handling so that the Catch action block functions? ( A picture is worth a thousand words.)

Regards, Mike

[Former Member · Accepted solution]

Hi Steve!

So it works great when the query fails, but when the query passes it gets stuck in the Catch and never executes my next action.

Any ideas?

Nevermind, I figured it out...

 

Message was edited by: Alli Davidson

[Former Member]

Thanks guys!  This worked now that I understand what Arnaud meant.  

There was no configuration required with the Catch action, just simply that it exists.

[Former Member]

Hello Girls and Guys, yes sorry the answer was a bit short.

Good Idea to put a screenshot of the solution.

Just 2 things: in order for the assign output in case of success to work, you need to create another sequence (1) under the catch and move you ifQueryRanOK (with the green line content) there.

The Orange Line (2) will only be executed if there is an exception just before, while (1) will alway be executed.

The Block (3) should remain under the Catch and orange line,

Cheers,

Arnaud