| Author |
Message
|
| ethirajesh |
 Posted: Thu Dec 02, 2010 3:42 am Post subject: Invoking stored procedure in MB |
 |
|
Apprentice
Joined: 04 Oct 2010
Posts: 46
|
I am very new to MB. I need to invoke a DB2 stored procedure which runs on a different server from MB. The SP will return
4 result sets(each result may have upto 150 rows). I need to have this result sets stored in message flow to create output XML by referring this result sets.
How Can I code ESQL to achieve this, pls see my understanding below :
Declare the SP :
CREATE PROCEDURE SIHSP125(IN STR_NMBR INT, IN SGN_ID VARCHAR, IN STATUS CHAR, OUT <to be determined>)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 4
EXTERNAL NAME "MySchema.SIHSP125"
Invoke the SP :
CALL SIHSP125(STR_NMBR, SGN_ID, STATUS, ResultSet1[], ResultSet2[], ResultSet3[], ResultSet4[])
What is the preferred way to save the result sets, pls share the snippet of the code if possible.
Thanks a lot. |
|
| Back to top |
|
 |
| pcelari |
| Posted: Thu Dec 02, 2010 7:18 am Post subject: |
|
|
Chevalier
Joined: 31 Mar 2006
Posts: 411
Location: New York
|
You should use the Environment tree to store the resultset, see below:
CALL SIHSP125(STR_NMBR, SGN_ID, STATUS, WhatEverItIs, Environment.ResultSet[])
You shouldn't need to care about the number of resultsets at calling.
Please note the resultset is not the OUT parameter. it is an extra one. You may wonder why the number of parameters exceeds that in the definition. But this is the way it works.
Hope this helps.
_________________
pcelari
-----------------------------------------
- a master of always being a newbie |
|
| Back to top |
|
|
| ethirajesh |
| Posted: Thu Dec 02, 2010 8:27 pm Post subject: Invoking stored procedure in MB |
|
|
Apprentice
Joined: 04 Oct 2010
Posts: 46
|
Okies. Thanks for your reply.
One doubt, as I have to access all these four result sets to build an output XML how can I do that. Since the statement has only one "Environment.ResultSets[]" how can I access a field in particular result set, say second result set, out of these four. Like this I need to refer all the fields in these result sets in order to create the XML. |
|
| Back to top |
|
|
| harish_td |
| Posted: Thu Dec 02, 2010 9:41 pm Post subject: |
|
|
Master
Joined: 13 Feb 2006
Posts: 236
|
|
| Back to top |
|
|
| ethirajesh |
| Posted: Mon Dec 06, 2010 1:42 am Post subject: |
|
|
Apprentice
Joined: 04 Oct 2010
Posts: 46
|
okay, thanks for your reply.
I got a doubt, How can I access a particular field/column from a specifi result sets as there are 4 result sets.
Once I get this understanding I will be able to do good hands on ESQL, please share your thoughts..
Thanks
Rajesh Ethiraj |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Mon Dec 06, 2010 3:29 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Add a Trace Node to your flow after the DB request.
Set the output to show where you put the results. EG ${Environment.Data}
This should be very illuminating.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| ethirajesh |
| Posted: Fri Dec 10, 2010 3:43 am Post subject: |
|
|
Apprentice
Joined: 04 Oct 2010
Posts: 46
|
Hi I tried putting the Trace Node and selected the destination property as File. The file has below error message. I am not sure what this error message says. Could you please guide me :
( ['MQROOT' : 0xa50f950]
(0x01000000:Name):Variable =
)
The message flow produces output message with data lenght as 0. The Input XML is as below :
<?xml version="1.0" encoding="utf-8"?>
<SIGNS>
<SIGN>129808841</SIGN>
</SIGNS>
Below is the code which will invoke the SP SIHSP121 (returns 4 result sets) to get details about Sign number 129808841 :
===============================================
CREATE COMPUTE MODULE TestFlow_Compute
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
DECLARE SIGN_ID INT;
DECLARE STATUS CHAR 'A';
DECLARE USER_ID CHAR 'ABC';
DECLARE STORE_NUMBER INT 3;
DECLARE RETURN_CODE INT;
DECLARE RETURN_MSG CHAR;
SET SIGN_ID = InputRoot.XMLNSC.SIGNS.SIGN;
CALL SIHSP121(USER_ID, SIGN_ID, STORE_NUMBER, STATUS, RETURN_CODE, RETURN_MSG,
Environment.Variable.ResultSet1[],
Environment.Variable.ResultSet2[],
Environment.Variable.ResultSet3[],
Environment.Variable.ResultSet4[]);
END;
END MODULE;
CREATE PROCEDURE SIHSP121(IN USER_ID CHAR, IN SIGN_ID INT, IN STORE_NUMBER INT, IN STATUS CHAR,
OUT RETURN_CODE INT,
OUT RETURN_MSG CHAR)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 4
EXTERNAL NAME "DB2TDEV.SIHSP121";
CREATE COMPUTE MODULE CreateXML
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
DECLARE SignNumber INT;
SET SignNumber=(SELECT P.SGN_FRMT_SHRT_N FROM Environment.Variable.ResultSet1[]AS P);
SET OutputRoot.XMLNSC.SIGNS.SIGN=SignNumber;
END;
END MODULE;
===============================================
NOTE : I am just starting with construction, this one m doing for learning pupose, request your help. Thanks a lot |
|
| Back to top |
|
|
| harish_td |
| Posted: Fri Dec 10, 2010 11:06 pm Post subject: |
|
|
Master
Joined: 13 Feb 2006
Posts: 236
|
Take a user trace and post the relevant error message that you might be getting.
Can you execute this stored procedure from a SQL editor (Toad/DB2 Control Center) and see whether you are getting any data in return for your input? |
|
| Back to top |
|
|
| ethirajesh |
| Posted: Mon Dec 13, 2010 4:26 am Post subject: Resolved |
|
|
Apprentice
Joined: 04 Oct 2010
Posts: 46
|
Hi Friends,
Finally I written a right code to retrive the result set into environment Tree.
@harish_td : The user trace log did not produce any output earlier. AFter writing the proper ESQl code now it is having the result sets.
Thanks all for your guidiance
Thanks
Rajesh Ethiraj |
|
| Back to top |
|
|
|
|
