| Author |
Message
|
| Esa |
 Posted: Thu Jan 19, 2012 1:35 am Post subject: ESQL throw with dynamic number of inserts |
 |
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
Now here's a little puzzle for all of you who don't have anything else to do:
How can you throw an exception in ESQL code and produce an unpredictable number of inserts in the ExceptionList
Hint: the parameters of VALUES clause in THROW statement become the inserts in the ExceptionList
Restrictions:
- you are not allowed to use EVAL function
- you are not allowed to build 10 procedures with different number of parameters in the VALUE clause and call them based on the number of inserts |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Thu Jan 19, 2012 8:30 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20767
Location: LI,NY
|
What happens when you pass the Values clause a character variable that is a comma separated string 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Jan 19, 2012 8:44 am Post subject: Re: ESQL throw with dynamic number of inserts |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| Esa wrote: |
| - you are not allowed to build 10 procedures with different number of parameters in the VALUE clause and call them based on the number of inserts |
You wouldn't need 10 procedures. The supplied messages only allow 8 inserts and there's nothing here about building a new message catalog.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Esa |
| Posted: Thu Jan 19, 2012 9:56 am Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
| fjb_saper wrote: |
| What happens when you pass the Values clause a character variable that is a comma separated string |
The same thing that happens with this:
SET OutputRoot.XMLNSC.Message.Field = 'Wonder, if, i, get, 5, Fields';
| Vitor wrote: |
| You wouldn't need 10 procedures. The supplied messages only allow 8 inserts |
Good point. Let's say up to 10 then.
| Vitor wrote: |
| there's nothing here about building a new message catalog |
C'mon, you cannot add inserts to an ExceptionList with a message catalog! Shame on you! |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Jan 19, 2012 11:21 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20767
Location: LI,NY
|
| Esa wrote: |
| fjb_saper wrote: |
| What happens when you pass the Values clause a character variable that is a comma separated string |
The same thing that happens with this:
SET OutputRoot.XMLNSC.Message.Field = 'Wonder, if, i, get, 5, Fields';
|
So you're saying it is different from
| Code: |
THROW USER EXCEPTION MESSAGE 3001 VALUES ('Wonder', 'if', 'i', 'get', '5', 'Fields');
Of course building it you would have to escape some ':
mymsg = '''Wonder'', ''if'', ''i'', ''get'', ''5'', ''fields''';
|
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Jan 19, 2012 11:30 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
just for giggles, I tried
| Code: |
Set Environment.Variables.RecoverableException.RecoverableException.Name='Dave';
Set Environment.Variables.RecoverableException.RecoverableException.Value='is not here';
THROW USER EXCEPTION CATALOG 'BIPv700' MESSAGE 2951 VALUES(Environment.Variables.RecoverableException) ; |
And it certainly did throw an exception.
But the exception didn't necessarily indicate that Dave's not here. |
|
| Back to top |
|
|
| Esa |
| Posted: Thu Jan 19, 2012 1:32 pm Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
| fjb_saper wrote: |
| Code: |
THROW USER EXCEPTION MESSAGE 3001 VALUES ('Wonder', 'if', 'i', 'get', '5', 'Fields');
Of course building it you would have to escape some ':
mymsg = '''Wonder'', ''if'', ''i'', ''get'', ''5'', ''fields''';
|
|
Did you test it? I'll have to test it before I can make a judgement. But I think it won't work in the required way...
| mqjeff wrote: |
| And it certainly did throw an exception. |
Certainly. This one throws an exception, too:
| Code: |
THROW USER EXCEPTION VALUES(LIST{'one', 'two', 'three'});
|
but the exception is about trying to assign a list into a scalar variable... |
|
| Back to top |
|
|
| cociu_2012 |
| Posted: Thu Jan 19, 2012 11:42 pm Post subject: |
|
|
Acolyte
Joined: 06 Jan 2012
Posts: 72
|
I haven't found other solution but EVAL.
I had the same problem with IN clause for SELECT function, and didn't find a good answer. So I'm curios how did you do it.
Passing strings, lists, does not work, it's throwing a single insert.
I first assumed that:
| Code: |
DECLARE undefined CHARACTER '''First insert: '',1, ''Second insert: '', 2';
THROW USER EXCEPTION CATALOG 'BIPv700' MESSAGE
2951 VALUES({undefined}); |
Will work, but that's not a good syntax for VALUE clause. I'm out of options.
Last edited by cociu_2012 on Fri Jan 20, 2012 1:53 am; edited 1 time in total |
|
| Back to top |
|
|
| Esa |
| Posted: Fri Jan 20, 2012 12:20 am Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
| cociu_2012 wrote: |
I haven't found other solution but EVAL.
I had the same problem with IN clause for SELECT statement, and didn't find a good answer
|
The IN clause might accept a LIST like this:
| Code: |
| LIST{'one', 'two', 'three'} |
| cociu_2012 wrote: |
| Code: |
DECLARE undefined CHARACTER '''First insert: '',1, ''Second insert: '', 2';
THROW USER EXCEPTION CATALOG 'BIPv700' MESSAGE
2951 VALUES({undefined}); |
Will work, but that's not a good syntax for VALUE clause. I'm out of options. |
Thank you very much, cociu_2012, for saving me the trouble of testing it myself. That rules out fjb_saper's suggestion. |
|
| Back to top |
|
|
| cociu_2012 |
| Posted: Fri Jan 20, 2012 12:32 am Post subject: |
|
|
Acolyte
Joined: 06 Jan 2012
Posts: 72
|
| Esa wrote: |
Thank you very much, cociu_2012, for sparing me the trouble of testing it myself. That rules out fjb_saper's suggestion. |
I believe that fjb_saper's suggestion was slightly different.
He tried the classy way, just passing the string to the VALUE clause. Something like this:
| Code: |
mymsg = '''Wonder'', ''if'', ''i'', ''get'', ''5'', ''fields''';
THROW USER EXCEPTION MESSAGE 3001 VALUES (mymsg);
|
That's a valid syntax but it will throw just one insert with the string you've built.
I went much further : adding {}, and that's wrong.
About LIST, it works when you know exactly how many parameters to pass. But if is dynamic, run-time evaluated? Same example you've posted. |
|
| Back to top |
|
|
| Esa |
| Posted: Fri Jan 20, 2012 12:50 am Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
| cociu_2012 wrote: |
| About LIST, it works when you know exactly how many parameters to pass. But if is dynamic, run-time evaluated? Same example you've posted. |
Well, you just create a field in LocalEnvironment, add your values to it as children and then apply a select. That will give you the list.
Feel free to test, but that's not an answer to my question, however! |
|
| Back to top |
|
|
| cociu_2012 |
| Posted: Fri Jan 20, 2012 12:57 am Post subject: |
|
|
Acolyte
Joined: 06 Jan 2012
Posts: 72
|
| Esa wrote: |
Well, you just create a field in LocalEnvironment, add your values to it as children and then apply a select. That will give you the list.
Feel free to test, but that's not an answer to my question, however! |
Been there did that - not working. It compares just with first value. IN clause doesn't loop through a list.
I think that when you'll post the solution, it will also apply for me. |
|
| Back to top |
|
|
| Esa |
| Posted: Fri Jan 20, 2012 1:17 am Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
| cociu_2012 wrote: |
| Esa wrote: |
Well, you just create a field in LocalEnvironment, add your values to it as children and then apply a select. That will give you the list.
Feel free to test, but that's not an answer to my question, however! |
Been there did that - not working. It compares just with first value. IN clause doesn't loop through a list.
I think that when you'll post the solution, it will also apply for me. |
The InfoCenter does not say anything about IN clause, it's standard SQL. From ESQL point of view it's just a part of a WHERE clause.
So you tried something like this:
| Code: |
| SELECT ... WHERE var IN SELECT ITEM field FROM ref.Fields |
Btw, SELECT is a function, not a statement  |
|
| Back to top |
|
|
| cociu_2012 |
| Posted: Fri Jan 20, 2012 1:49 am Post subject: |
|
|
Acolyte
Joined: 06 Jan 2012
Posts: 72
|
| Esa wrote: |
So you tried something like this:
| Code: |
| SELECT ... WHERE var IN SELECT ITEM field FROM ref.Fields |
|
I thought about SELECT IN SELECT but never tried.
EVAL worked out, so I got over it.
| Esa wrote: |
Btw, SELECT is a function, not a statement |
Sorry, I will edit that. |
|
| Back to top |
|
|
| mgk |
| Posted: Fri Jan 20, 2012 2:15 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1647
|
| Quote: |
| IN clause doesn't loop through a list |
It does now as it was updated in a V7 fixpac (1 or 2). The docs now explain this and show how it works...
Kind Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
|
|
