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 Index » WebSphere Message Broker (ACE) Support » Without using ITEM in the SELECT statement, how can I get

Post new topic  Reply to topic
 Without using ITEM in the SELECT statement, how can I get « View previous topic :: View next topic » 
Author Message
narendra
PostPosted: Wed Jun 19, 2002 7:42 am    Post subject: Without using ITEM in the SELECT statement, how can I get Reply with quote

Apprentice

Joined: 04 Jun 2002
Posts: 26

Hi,

I would like some assistance.

I successfully use the PROPAGATE function to create multiple messages from the result
sets returned by select statements. However I run into some problem using the
ITEM key word when one of the col is NULL in the select statement.


THIS IS THE CONTENT OF THE COMPUTE NODE

******************* BEGIN COMPUTE *********************************

DECLARE I INTEGER;
SET I = 1;
WHILE I < CARDINALITY(InputRoot.*[]) DO
SET OutputRoot.*[I] = InputRoot.*[I];
SET I=I+1;
END WHILE;
-- Enter SQL below this line. SQL above this line might be regenerated, causing any modifications to be lost.

-- This compute node will propagate each row from the staff table into one separate message (per row)

DECLARE i INTEGER;
Set i =1;

SET InputDestinationList.XML.TempData."ID"[]= ( SELECT ITEM A.ID
from Database.STAFF AS A WHERE A.ID < 30 );

SET InputDestinationList.XML.TempData."NAME"[]= ( SELECT ITEM A.NAME
from Database.STAFF AS A WHERE A.ID < 30 );

SET InputDestinationList.XML.TempData."JOB"[]= ( SELECT ITEM A.JOB
from Database.STAFF AS A WHERE A.ID < 30 );

WHILE i <= CARDINALITY(InputDestinationList.XML.TempData."ID"[])
DO
SET OutputRoot = InputRoot;
SET OutputRoot.XML = NULL;
SET OutputRoot.XML.MSG."id" = InputDestinationList.XML.TempData."ID"[i];
SET OutputRoot.XML.MSG."name" = InputDestinationList.XML.TempData."NAME"[i];
SET OutputRoot.XML.MSG."job" = InputDestinationList.XML.TempData."JOB"[i];
PROPAGATE;
SET i = i+1;
END WHILE;
RETURN FALSE;



**************** END COMPUTE ********************

THIS IS THE OUTPUT DATA

****************begin output1**************************
<MSG>
<id>10</id>
<name>Sanders</name>
<job>Mgr </job>
</MSG>

****************end output1*********************************


please note using ITEM in the SELECT statement will format it nicely. However if NAME or JOB column in staff table contains NULL, SELECT ITEM will fail

without using ITEM in the above SELECT , the output looks like


**************begin output2**************************
<MSG>
<id>
<ID>10</ID>
</id>
<name>
<NAME>Sanders</NAME>
</name>
<job>
<JOB>Mgr </JOB>
</job>
</MSG>

***************end of output2***********************************

IF NAME contains NULL, output2 will look like this using ITEM keyword in the select statement
<MSG>
<id>
<ID>10</ID>
</id>
<name>
<NAME></NAME>
</name>
<job>
<JOB>Mgr </JOB>
</job>
</MSG>






****************** begin of mqsi broker trace ***************

2002-06-19 13:35:27.281000 2544 UserTrace BIP2567I: Node 'EORDER_2.Compute1': Assigning NULL to 'InputDestinationList.XML.TempData.A.NAME[1]', thus deleting it.
2002-06-19 13:35:27.281000 2544 UserTrace BIP2231E: Error detected whilst processing a message 'EORDER_2.Compute1'.
The message broker detected an error whilst processing a message in node 'EORDER_2.Compute1'. The message has been augmented with an exception list and has been propagated to the node's failure terminal for further processing.
See the following messages for details of the error.
2002-06-19 13:35:27.281000 2544 RecoverableException BIP2333E: Error attaching field '' as a sibling of field 'A.NAME' which has no parent.
The message broker attempted to attach the field '' as a sibling of the field 'A.NAME' but the latter field does not have a parent field.
This error can only arise as a result of an error in a node or parser implementation library. Contact the author or supplier of the loadable implementation library and obtain a correct version.



*******************end of mqsi brokertrace




my question is:
Without using ITEM in the SELECT statement, how can I get output1 as the desired output ?

Back to top
View user's profile Send private message
kirani
PostPosted: Wed Jun 19, 2002 8:49 am    Post subject: Reply with quote

Jedi Knight

Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA

You can not use InputDestinationList to store temp values returned from database. Input trees are read only and cannot be modified using ESQL. You need to change your code to use OutputDestinationList instead.

Try changing your code to following and see if you get desired output or not.

Code:

DECLARE I INTEGER;
SET I = 1;
WHILE I < CARDINALITY(InputRoot.*[]) DO
SET OutputRoot.*[I] = InputRoot.*[I];
SET I=I+1;
END WHILE;
-- Enter SQL below this line. SQL above this line might be regenerated, causing any modifications to be lost.

-- This compute node will propagate each row from the staff table into one separate message (per row)

DECLARE i INTEGER;
Set i =1;
DECLARE TCNT INTEGER;

SET OutputDestinationList.Variables.TempData[]= ( SELECT A.ID, A.NAME, A.JOB
from Database.STAFF AS A WHERE A.ID < 30 );

SET TCNT = CARDINALITY(OutputDestinationList.Variables.TempData[]);
WHILE i <=  TCNT
DO
SET OutputRoot = InputRoot;
SET OutputRoot.XML = NULL;
SET OutputRoot.XML.MSG."id" = OutputDestinationList.Variables.TempData[i]."ID";
SET OutputRoot.XML.MSG."name" = OutputDestinationList.Variables.TempData[i]."NAME";
SET OutputRoot.XML.MSG."job" = OutputDestinationList.Variables.TempData[i]."JOB";
PROPAGATE;
SET i = i+1;
END WHILE;
RETURN FALSE;

_________________
Kiran


IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries

Back to top
View user's profile Send private message Visit poster's website
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Without using ITEM in the SELECT statement, how can I get
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.