| Author |
Message
|
| Andrew Cambell |
 Posted: Tue Oct 09, 2001 12:51 pm Post subject: |
 |
|
Newbie
Joined: 27 Sep 2001
Posts: 5
|
I am using a MQSI Compute node to retrieve data from database and populate the o/p message. The ESQL I have been able to make work is as follows.
SET "OutputRoot"."MRM"."e_DEPTNO" = "InputBody"."e_DEPTNO";
SET "OutputRoot"."MRM"."e_DEPTNAME"[] = (SELECT ITEM T.DEPTNAME FROM Database.DEPARTMENT AS T WHERE T.DEPTNO = "InputBody"."e_DEPTNO");
SET "OutputRoot"."MRM"."e_MGRNO"[] = (SELECT ITEM T.MGRNO FROM Database.DEPARTMENT AS T WHERE T.DEPTNO = "InputBody"."e_DEPTNO");
SET "OutputRoot"."MRM"."e_ADMRDEPT"[] = (SELECT ITEM T.ADMRDEPT FROM Database.DEPARTMENT AS T WHERE T.DEPTNO = "InputBody"."e_DEPTNO");
SET "OutputRoot"."MRM"."e_LOCATION"[] = (SELECT ITEM T.LOCATION FROM Database.DEPARTMENT AS T WHERE T.DEPTNO = "InputBody"."e_DEPTNO");
SET "OutputRoot"."MRM"."e_DEPTNAME"[] = (SELECT ITEM T.DEPTNAME FROM Database.DEPARTMENT AS T WHERE T.DEPTNO = "InputBody"."e_DEPTNO");
You will notice that presently I am issuing a database call for populating each output node. So there are 5 ESQL select calls in my code. How can I make this more efficient to retrieve all the required values in one database call and populate the output message.
Thanks in Advance,
|
|
| Back to top |
|
 |
| EddieA |
| Posted: Tue Oct 09, 2001 5:06 pm Post subject: |
|
|
Jedi
Joined: 28 Jun 2001
Posts: 2453
Location: Los Angeles
|
Andrew,
How about:
SET OutputRoot.XML.Body.DBStuff[] = (SELECT
T.DEPTNAME
T.MGRNO
T.ADMRDEPT
T.LOCATION
T.DEPTNAME
FROM Database.DEPARTMENT AS T WHERE T.DEPTNO = InputBody.e_DEPTNO);
SET OutputRoot.MRM.e_DEPTNAME = OutputRoot.XML.Body.DBStuff.DEPTNAME;
SET OutputRoot.MRM.e_MGRNO = OutputRoot.XML.Body.DBStuff.MGRNO;
SET OutputRoot.MRM.e_ADMRDEPT = OutputRoot.XML.Body.DBStuff.ADRMDEPT;
SET OutputRoot.MRM.e_LOCATION = OutputRoot.XML.Body.DBStuff.LOCATION;
SET OutputRoot.MRM.e_DEPTNAME = OutputRoot.XML.Body.DBStuff.DEPTNAME;
SET OutputRoot.XML = NULL;
I hope I got that syntax right <GRIN>.
Now, if the MRM name was the same as the DB name, you *should* be able to do just:
SET OutputRoot.MRM[] = (SELECT
T.DEPTNAME
T.MGRNO
T.ADMRDEPT
T.LOCATION
T.DEPTNAME
FROM Database.DEPARTMENT AS T WHERE T.DEPTNO = InputBody.DEPTNO);
*EXCEPT*, that (on 2.0 at least) the above would correctly populate DEPTNAME, MGRNO, ADMRDEPT, LOCATION, and DEPTNAME but also *DELETE* any other MRM fields that had been filled in before the command was used. Haven't tried it on 2.0.2, yet.
Cheers.
_________________
Eddie Atherton
IBM Certified Solution Developer - WebSphere Message Broker V6.1
IBM Certified Solution Developer - WebSphere Message Broker V7.0 |
|
| Back to top |
|
|
| Tibor |
| Posted: Wed Oct 10, 2001 10:42 pm Post subject: |
|
|
Grand Master
Joined: 20 May 2001
Posts: 1033
Location: Hungary
|
Eddie wrote:
>Now, if the MRM name was the same as the DB name, you *should* be able to do just...
IMO, much simpler using SELECT field AS name form:
select T.DEPTNAME as e_DEPTNAME ...
Tibor |
|
| Back to top |
|
|
| Andrew Cambell |
| Posted: Thu Oct 11, 2001 11:55 am Post subject: |
|
|
Newbie
Joined: 27 Sep 2001
Posts: 5
|
Thanks for all the help guys. I tried Eddie's approach and had problems with MQ Series. All kinds of vague errors like Database locked,ODBC Error...started cropping up. I finally changed my message type from MRM to XML and it works. Following is my final ESQL in case you are intrested.
SET OutputRoot = InputRoot;
SET OutputRoot.XML.Message.RESULT[] = (SELECT T.DEPTNO, T.DEPTNAME, T.MGRNO, T.ADMRDEPT, T.LOCATION
FROM Database.DEPARTMENT AS T WHERE T.DEPTNO = InputRoot.XML.Message.DEPTNO);
SET OutputRoot.XML.Message.DEPTNAME[] = OutputRoot.XML.Message.RESULT.DEPTNAME[];
SET OutputRoot.XML.Message.MGRNO[] = OutputRoot.XML.Message.RESULT.MGRNO[];
SET OutputRoot.XML.Message.ADMRDEPT[] = OutputRoot.XML.Message.RESULT.ADMRDEPT[];
SET OutputRoot.XML.Message.LOCATION[] = OutputRoot.XML.Message.RESULT.LOCATION[];
SET OutputRoot.XML.Message.RESULT[] = NULL;
I still have one problem.
SET OutputRoot.XML.Message.RESUTL[] = NULL;
does not do anything, So I am getting the data twice in the ouput. I have ways to work around this.
Thanks Again,
Andrew
|
|
| Back to top |
|
|
| Tibor |
| Posted: Thu Oct 11, 2001 1:25 pm Post subject: |
|
|
Grand Master
Joined: 20 May 2001
Posts: 1033
Location: Hungary
|
| Quote: |
On 2001-10-11 12:55, Andrew Cambell wrote:
SET OutputRoot.XML.Message.RESUTL[] = NULL;
does not do anything, So I am getting the data twice in the ouput. I have ways to work around this.
|
Andy,
(Output)Root is a tree and you can delete *any* point! Try this:
SET OutputRoot.XML.Message = NULL;
Tibor
|
|
| Back to top |
|
|
| Outdesign |
| Posted: Fri Oct 12, 2001 2:42 am Post subject: |
|
|
Apprentice
Joined: 16 Sep 2001
Posts: 38
Location: Hampshire, UK
|
Strongly suspect what Tibor meant to say here was ...
SET OutputRoot.XML.Message.RESULT = NULL;
and not
SET OutputRoot.XML.Message = NULL;
as this would effectively remove the entire output message
[ This Message was edited by: Outdesign on 2001-10-12 03:43 ] |
|
| Back to top |
|
|
| Andrew Cambell |
| Posted: Fri Oct 12, 2001 4:36 am Post subject: |
|
|
Newbie
Joined: 27 Sep 2001
Posts: 5
|
Agreee with you 'Outdesign'.
SET OutputRoot.XML.Message = NULL; will delete the entire output.
As I mentioned
SET OutputRoot.XML.Message.RESULT = NULL;
does not do anything.
Any thoughts!! |
|
| Back to top |
|
|
| Tibor |
| Posted: Fri Oct 12, 2001 6:07 am Post subject: |
|
|
Grand Master
Joined: 20 May 2001
Posts: 1033
Location: Hungary
|
Sorry for misinformation, I didn't study your code rather. I used to put temporary datas an other area than output values, e.g.
set OutputRoot.XML.tmp.result[] = (select...);
Then copying values and:
set OutputRoot.XML.tmp = NULL;
Tibor |
|
| Back to top |
|
|
|
|
