| Author |
Message
|
| pcelari |
 Posted: Fri Mar 16, 2007 7:13 am Post subject: any example where ESQL call stored procedure ? |
 |
|
Chevalier
Joined: 31 Mar 2006
Posts: 411
Location: New York
|
Hi,
anyone know an example in calling stored procedure from ESQL, not the online document which is too brief and somehow confusing.
Is there such an example in the Sample Gallery?
I have a hard time trying to understand what it means by a "SP returning two resultsets". does it mean two row of records?
if a SP is defined with two in, and two out, adding Environment.ResultSet[] to the parameter list as suggested in the document example results in an exception being thrown over incompatible number of parameters.
thanks for any information on this.
_________________
pcelari
-----------------------------------------
- a master of always being a newbie |
|
| Back to top |
|
 |
| mvarghese |
| Posted: Sat Mar 17, 2007 4:52 am Post subject: |
|
|
Centurion
Joined: 27 Sep 2006
Posts: 141
|
I didn't seen any examples in MB sample gallery..may be am wrong.
But its easy if u can use java node and call stored procedures ...this things u can get from any java programming eg guides.
_________________
Jain Varghese |
|
| Back to top |
|
|
| mgk |
| Posted: Sat Mar 17, 2007 9:08 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi, If you post the definition of the procedure you are trying to call (and the DB version where it is running) I will try and show you the ESQL you need to call it.
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 |
|
|
| pcelari |
| Posted: Mon Mar 19, 2007 7:13 am Post subject: |
|
|
Chevalier
Joined: 31 Mar 2006
Posts: 411
Location: New York
|
| mgk wrote: |
| Hi, If you post the definition of the procedure you are trying to call (and the DB version where it is running) I will try and show you the ESQL you need to call it. |
thanks a lot for offering to help. Here's the SP definition:
CREATE PROCEDURE DB2SP_GETCUSTNAME
(
IN c_id CHAR(9),
OUT a_ReturnCode INTEGER,
OUT a_ReturnMessage VARCHAR(254)
)
RESULT SET 1
LANGUAGE SQL
MODIFIES SQL DATA
COLLID MCCK04
WLM ENVIRONMENT WLMDBT2
ASUTIME NO LIMIT
RUN OPTIONS 'NOTEST(ALL,*,,VADTCPIP&192.168.39.12:*)'
I tested calling it from DB2 CLI, successfully by
db2=> call DB2SP_GETCUSTNAME (10030-134, ?, ?)
A_RETURNCODE: 0
A_RETURNMESSAGE:
CUST_ID CUST_NAME
10030-134 Brian C Clipper
"DB2SP_GETCUSTNAME" RETURN_STATUS: 0
In my compute node ESQL, I define the procedure:
create procedure CallMySP
(in ID char,
out returnCode integer,
out ResultMsg char) language DATABASE DYNAMIC RESULTSETS 1
external name "DB2SP_GETCUSTNAME";
here's the code I use to call the procedure, based on the example in the documentation
...
declare ret_code integer;
declare msg char;
call CallMySP(InputBody.ID, ret_code, msg, Environment.ResultSet[]);
But I got an exception from my debugger output: "The procedure '&1' with '&2' parameters could not be matched with a corresponding Database stored procedure."
What am I missing?
_________________
pcelari
-----------------------------------------
- a master of always being a newbie |
|
| Back to top |
|
|
| Edde |
| Posted: Mon Mar 19, 2007 10:52 am Post subject: |
|
|
Acolyte
Joined: 01 Oct 2006
Posts: 67
Location: Moscow, Russia
|
Did you create stored procedure under the same user that broker uses to connect to database?
Try to fill scheme name in your ESQL definition. |
|
| Back to top |
|
|
| pcelari |
| Posted: Mon Mar 19, 2007 12:18 pm Post subject: |
|
|
Chevalier
Joined: 31 Mar 2006
Posts: 411
Location: New York
|
| Edde wrote: |
Did you create stored procedure under the same user that broker uses to connect to database?
Try to fill scheme name in your ESQL definition. |
the SP is located on a remote mainframe DB2 region. the SP does return a returnCode and a returnMsg, if I don't put Environment.ResultSet[] in the parameter list. So the SP itself works.
My problem is how to retrieve the resultset.
According to wbimb document, I added the extra Environment.ResultSet[]. it is this one that resulted in exception being thrown.
Any idea about this?
_________________
pcelari
-----------------------------------------
- a master of always being a newbie |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Mon Mar 19, 2007 1:12 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
|
| Back to top |
|
|
| pcelari |
| Posted: Tue Mar 20, 2007 1:24 pm Post subject: |
|
|
Chevalier
Joined: 31 Mar 2006
Posts: 411
Location: New York
|
it doesn't work as described in the document, which is highly confusing anyway.
Again, my definition in ESQL:
create procedure CallMySP (in ID char, out returnCode integer,
out ResultMsg char)
language DATABASE DYNAMIC RESULTSETS 1
external name "DB2SP_GETCUSTNAME";
i.e. with parameter layout: (in, out, out)
If I call it the following way, I would be calling it with an addition parameter: (in, out, out, out)
call CallMySP(InputBody.ID, ret_code, msg, Environment.ResultSet[]);
No wonder I got the exception. Can anyone please clarify this?
thanks a lot.
_________________
pcelari
-----------------------------------------
- a master of always being a newbie |
|
| Back to top |
|
|
| mgk |
| Posted: Tue Mar 20, 2007 2:39 pm Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi,
I have looked at the ESQL you posted, and it looks correct. Therefore, there must be something else wrong that will require more detailed analysis that can be provided here. Please raise a PMR to get this looked at.
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 |
|
|
| mqseries0209 |
| Posted: Wed Mar 21, 2007 7:24 am Post subject: Hi |
|
|
Voyager
Joined: 30 Mar 2006
Posts: 90
|
No Wonder you are having the exception because of Number of parameters passed to SP are more than what SP is defined for.
One solution is to modify the SP, so that the ResultSet is returned explicitly in a OUT parameter.
One more thing is check if you are getting the same exception with a java program calling the SP (This helps a lot for debudding purposes as you will be using the same drivers to access the DB as the Broker) |
|
| Back to top |
|
|
| mgk |
| Posted: Wed Mar 21, 2007 7:38 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
No "mqseries0209", this is not correct. A procedure declared as:
create procedure CallMySP
(in ID char,
out returnCode integer,
out ResultMsg char) language DATABASE DYNAMIC RESULTSETS 1 ....
Has 3 scalar parameters and one list parameter (the resultset)
Therefore, when it is called is MUST be called with 3 scalar parameters and one list parameter (as the last parameter) like this:
call CallMySP( param1, param2, param3, Environment.myResultSet[]);
Which is exactly what "pcelari" is doing. As I said, the signatures appear to match correctly, so there must be another reason for the problem, hence my suggestion that a PMR be raised to get to the bottom of this.
_________________
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 |
|
|
| pcelari |
| Posted: Thu Mar 22, 2007 7:08 am Post subject: |
|
|
Chevalier
Joined: 31 Mar 2006
Posts: 411
Location: New York
|
thanks for the response. I'll do more trace on the msgflow, and report back my further finding with mqsi support.
I did the same SP call with Java, it works fine and I could retrieve all the records contained in the resultset.
_________________
pcelari
-----------------------------------------
- a master of always being a newbie |
|
| Back to top |
|
|
| mqseries0209 |
| Posted: Thu Mar 22, 2007 7:48 am Post subject: |
|
|
Voyager
Joined: 30 Mar 2006
Posts: 90
|
Thanks, MGK for clearing the wrong info I posted,  I apologise for any mis-leading info but hey who cares, thats how you learn 
 |
|
| Back to top |
|
|
| pcelari |
| Posted: Fri Mar 23, 2007 3:49 pm Post subject: |
|
|
Chevalier
Joined: 31 Mar 2006
Posts: 411
Location: New York
|
| mgk wrote: |
... Which is exactly what "pcelari" is doing. As I said, the signatures appear to match correctly, so there must be another reason for the problem, hence my suggestion that a PMR be raised to get to the bottom of this. |
thanks a lot MGK for confirming my code's correctness. I could trace the problem back to a mismatch of inout with out. Now I got my result set.
Again, many thanks for all the helps from everyone.
_________________
pcelari
-----------------------------------------
- a master of always being a newbie |
|
| Back to top |
|
|
|
|
