| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| ESQL and detecting SQL stmt errors |
« View previous topic :: View next topic » |
| Author |
Message
|
| Gideon |
 Posted: Thu Nov 03, 2011 12:55 pm Post subject: ESQL and detecting SQL stmt errors |
 |
|
Chevalier
Joined: 18 Aug 2009
Posts: 403
|
I am intentionally trying to insert on top of a previously entered
row to see how to handle exceptions
I sent the following command in ESQL
| Code: |
SET cmd = 'INSERT into db2inst1.catalog (pid,price) values (1,1)';
SET OutputLocalEnvironment.Variables.Ret = PASSTHRU(cmd); |
This works the first time, but the second time through the code
throughs an exception, and goes out the Failure terminal, where I
can see it in the Trace node
When I UN-CHECK the "Throuw exception on Database error", the flow
no longer throws an error, the output routes through the "Out" terminal
The first time through (on an empty table, successfull insert, the
trace shows:
| Code: |
--- Local Env:( ['MQROOT' : 0x1f5d26c0]
(0x01000000:Name):Variables =
)^M
--- Env:^M
--- Excpt:^M
-------------------------- |
On the second time through (bad insert cmd trying to overwrite a record),
it agains goes through the 'out' terminal, and the trace node shows:
| Code: |
--- Local Env:^M
--- Env:^M
--- Excpt:^M
-------------------------- |
In the first case I see a local env, but not the "Ret" I was trying to
write to
In the second case I do not see a local env of any sort
SInce both cases do not show a RET, I can not test ret inside of ESQL to
determine if the INSERT completed successfully
How can I do this ? |
|
| Back to top |
|
 |
| harish_td |
| Posted: Thu Nov 03, 2011 3:27 pm Post subject: |
|
|
Master
Joined: 13 Feb 2006
Posts: 236
|
|
| Back to top |
|
|
| inMo |
| Posted: Mon Nov 07, 2011 11:30 am Post subject: |
|
|
Master
Joined: 27 Jun 2009
Posts: 216
Location: NY
|
| The concept is to try the action , then check the return codes. I imagine the return code(s) are discussed in the previous post's links, so I won't discuss them here. Can you define the use case that is driving you to uncheck the "Throw exception on Database error" option ? (There are valid reasons, hence the option, just curious what yours is?) |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Mon Nov 07, 2011 12:08 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
One case I have encounteres a few times is where you don't care if an insert causes a primary key violation (eg, a duplicate row).
As suggested, you can disable the automatic throwing of an exception and generate your own if needed. This gives you the chance to add some extra details to the exception.
For example, adding the SQL Command that caused the exception.
OR
Adding some data to help with finding where the exception took place.
I actually prefer this to letting the exception to be thrown automatically.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| inMo |
| Posted: Mon Nov 07, 2011 12:54 pm Post subject: |
|
|
Master
Joined: 27 Jun 2009
Posts: 216
Location: NY
|
| Yes. Value added processing on error condition can prove quite helpful. The ideas expressed in the previous post can add meaningful value, especially when tied to a comprehensive exception handling strategy. My general concern was the idea that turning off the throw errors option could have been stated as the "I'll take full responsibility for handling my errors" option. Unfortuenately, not everyone that agrees to this condition actually does. |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
