| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Calling procedure with REFERENCE ELEMENT |
« View previous topic :: View next topic » |
| Author |
Message
|
| WBIBB |
 Posted: Mon Nov 15, 2004 10:35 am Post subject: Calling procedure with REFERENCE ELEMENT |
 |
|
Novice
Joined: 24 Jul 2003
Posts: 19
|
Hi,
I am calling a procedure with 3 IN and 1 OUT(REFERENCE) parameter.The procedure in turn calls the stored procedure which returns the result set.The resultset is used to build a temporary XML tree within the procedure. (WMQI2.1)
How do I continue processing the returned XML tree since I have to find out the cardinality.The following is the code I am trying to run and haven't been successful.
| Code: |
Declare NumID INT;
Declare WMQI_Idnum CHAR;
Declare WMQI_Idlen INT;
Declare WMQI_Version CHAR;
Declare WMQI_Date CHAR;
Declare WMQIoutref REFERENCE TO OutputRoot;
CREATE LASTCHILD OF OutputRoot DOMAIN 'XML';
CALL WMQI_TRANSLATION (WMQI_Idnum,WMQI_Idlen,WMQI_Version,WMQI_Date, WMQIoutref);
CREATE LASTCHILD OF OutputRoot DOMAIN 'XML';
SET Environment.Variables.outref = WMQIoutref;
SET NumID = CARDINALITY(WMQIoutref.ABC[]);
SET WMQIoutref.NumID = NumID;
CREATE PROCEDURE WMQI_TRANSLATION
(
IN pIdnum CHAR,
IN pIdLen INTEGER,
IN pVersion CHAR,
IN pDate CHAR,
OUT poutref REFERENCE
)
BEGIN
Declare Numrows INT;
Declare CNT INT;
Declare StartLen INT;
SET StartLen = 4;
WHILE (CNT <= Numrows) DO
--Build XML tree returned from result set of Stored Procedure
SET poutref.ABC[CNT]= SUBSTRING(CAST(RSPOUT AS CHAR) FROM StartLen FOR 8);
SET CNT = CNT + 1;
SET StartLen = StartLen + 12;
END WHILE;
SET StartLen = 0;
END;
CREATE PROCEDURE SP0001(INOUT RQSTIN CHAR)EXTERNAL NAME "PLN.SP0001";
|
I would appreciate your response and thanks in advance.
Regards
Mike |
|
| Back to top |
|
 |
| Nizam |
| Posted: Mon Nov 15, 2004 11:11 am Post subject: |
|
|
Disciple
Joined: 10 Feb 2004
Posts: 160
|
Try declaring your OUT(REFERENCE) as INOUT
INOUT poutref REFERENCE; |
|
| Back to top |
|
|
| brenner |
| Posted: Tue Nov 16, 2004 12:18 am Post subject: |
|
|
Newbie
Joined: 22 Oct 2004
Posts: 7
Location: IBM Hursley
|
Try calling your stored procedure using passthru, that will build your
tree automatically, for example:
SET poutref.ABC[]= PASSTHRU('{CALL PLN.SP0001 (?, ?, ?)}', parm1,parm2,parm3); |
|
| Back to top |
|
|
| leongor |
| Posted: Tue Nov 16, 2004 5:04 am Post subject: |
|
|
Master
Joined: 13 May 2002
Posts: 264
Location: Israel
|
BTW, OutputRoot reference should be IN also, because you are not going to change a reference, but the data it points.
_________________
Regards.
Leonid.
IBM Certified MQSeries Specialist. |
|
| Back to top |
|
|
| WBIBB |
| Posted: Wed Nov 17, 2004 6:27 am Post subject: |
|
|
Novice
Joined: 24 Jul 2003
Posts: 19
|
Hi All,
Thanks for your reply and was able to make little progress..
I was able to see the values getting set in trace by changing it to INOUT parameter.However I am getting error because of "XML Writing Errors have occurred ".I am suspecting that new elements built are not properly formed after this statement in below code
CREATE LASTCHILD OF OutputRoot DOMAIN 'XML';
leongor
IN is character type and OUT is XML tree of REFERENCE type
brenner:
The stored procedure returns fixed length string and have to to be parsed.
| Code: |
Declare NumID INT;
Declare WMQI_Idnum CHAR;
Declare WMQI_Idlen INT;
Declare WMQI_Version CHAR;
Declare WMQI_Date CHAR;
Declare poutref REFERENCE TO OutputRoot;
CALL WMQI_TRANSLATION (WMQI_Idnum,WMQI_Idlen,WMQI_Version,WMQI_Date, poutref);
IF poutref.XML.ID[1] IS NOT NULL THEN
--Start Current process with translated ID or ID's
SET NumID = CARDINALITY(poutref.XML.ID[]);
SET poutref.XML.NumID = NumID;
SET poutref.XML.Code = 'Start the Current process with translated ID';
ELSE
IF (poutref.XML.ReturnCode = 90 AND poutref.XML.Numofrows =0) THEN
--Continue with current process with input submitted
SET poutref.XML.Code = 'Start the Current process with submitted ID';
ELSE
--Build the error and send it to Enrollment
SET poutref.XML.Code = 'End the process building the ERROR MSG with submitted ID';
END IF;
END IF;
CREATE PROCEDURE WMQI_TRANSLATION
(
IN pIdnum CHAR,
IN pIdLen INTEGER,
IN pVersion CHAR,
IN pDate CHAR,
INOUT poutref REFERENCE
)
BEGIN
Declare Numrows INT;
Declare CNT INT;
Declare StartLen INT;
Declare ErrorCode CHAR;
Declare ErrorMsg CHAR;
DeClare INACTIND CHAR;
DeClare IDTYPE CHAR;
Declare RQSTIN CHAR;
Declare RSPOUT CHAR;
Declare CDlen INTEGER;
Declare Reclen INTEGER;
SET Startpos = 4;
SET CDlen = 8;
SET reclen = 30;
SET INACTIND = 'Y';
SET IDTYPE = 'XYZ';
SET RQSTIN = pIdnum||IDTYPE||INACTIND;
CALL SP0001(RQSTIN);
--Stored procedure returns a fixed length string of result set 2000bytes
--Have to parse thru the array at specific positions as in below loop to get the output
CREATE LASTCHILD OF OutputRoot DOMAIN 'XML';
SET RSPOUT = CAST(RQSTIN AS CHAR);
SET TotalLen = LENGTH(RSPOUT);
SET ReturnCode = SUBSTRING(RSPOUT FROM 25 FOR 2);
SET Numrows = CAST(SUBSTRING(RSPOUT FROM 33 FOR 8) AS INT);
SET poutref.XML.ReturnCode = ReturnCode;
SET poutref.XML.Numofrows = Numrows;
SET poutref.XML.Length = CAST(TotalLen AS CHAR);
SET poutref.XML.RSPOUT = CAST(RSPOUT AS CHAR);
IF (ReturnCode = '00') OR (ReturnCode = '90') THEN
SET CNT = 1;
WHILE (CNT <= Numrows) DO
--Build XML tree based on result set from Stored Procedure
SET poutref.XML.ABC[CNT] = SUBSTRING(CAST(RSPOUT AS CHAR) FROM Startpos FOR 8);
SET Startpos = StartposLen + 8;
SET poutref.XML.CDE[CNT] = SUBSTRING(CAST(RSPOUT AS CHAR) FROM StartLen FOR 8);
SET CNT = CNT + 1;
SET StartLen = StartLen + 30;
END WHILE;
ELSE
SET ErrorCode = '805' ;
SET ErrorMsg = 'Invalid input';
IF (ReturnCode = '99') THEN
SET poutref.XML.Errorcode = ErrorCode ;
SET poutref.XML.ErrorMsg = ErrorMsg ;
END IF;
END IF;
SET StartLen = 0;
END;
CREATE PROCEDURE SP0001(INOUT RQSTIN CHAR)EXTERNAL NAME "PLN.SP0001";
|
Let me know if I am doing something wrong??.Thanks in advance..
Regards
WBIBB |
|
| Back to top |
|
|
| mgk |
| Posted: Wed Nov 17, 2004 8:53 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi,
Your code has (at least) four root elements under the XML parser, which is not valid XML:
| Quote: |
SET poutref.XML.ABC = ...
SET poutref.XML.CDE = ...
SET poutref.XML.Errorcode = ...
SET poutref.XML.ErrorMsg = ... |
You need a single top level root element under root to contain the XML message such as:
| Code: |
SET poutref.XML.Top.ABC = ...
SET poutref.XML.Top.CDE = ...
SET poutref.XML.Top.Errorcode = ...
SET poutref.XML.Top.ErrorMsg = ... |
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 |
|
|
|
|
|
| |
|
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
|
|
|
|
