ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum IndexWebSphere Message Broker SupportESQL Question

Post new topicReply to topic
ESQL Question View previous topic :: View next topic
Author Message
Andrew Cambell
PostPosted: Tue Oct 09, 2001 12:51 pm Post subject: Reply with quote

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
View user's profile Send private message
EddieA
PostPosted: Tue Oct 09, 2001 5:06 pm Post subject: Reply with quote

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
View user's profile Send private message
Tibor
PostPosted: Wed Oct 10, 2001 10:42 pm Post subject: Reply with quote

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
View user's profile Send private message
Andrew Cambell
PostPosted: Thu Oct 11, 2001 11:55 am Post subject: Reply with quote

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
View user's profile Send private message
Tibor
PostPosted: Thu Oct 11, 2001 1:25 pm Post subject: Reply with quote

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
View user's profile Send private message
Outdesign
PostPosted: Fri Oct 12, 2001 2:42 am Post subject: Reply with quote

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
View user's profile Send private message Visit poster's website
Andrew Cambell
PostPosted: Fri Oct 12, 2001 4:36 am Post subject: Reply with quote

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
View user's profile Send private message
Tibor
PostPosted: Fri Oct 12, 2001 6:07 am Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:
Post new topicReply to topic Page 1 of 1

MQSeries.net Forum IndexWebSphere Message Broker SupportESQL Question
Jump to:



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
Protected by Anti-Spam ACP


Theme by Dustin Baccetti
Powered by phpBB 2001, 2002 phpBB Group

Copyright MQSeries.net. All rights reserved.