| Author |
Message
|
| 9999999 |
 Posted: Mon Jan 13, 2014 6:28 pm Post subject: Calling Stored Procedure to Get Data from ESQL |
 |
|
Newbie
Joined: 13 Jan 2014
Posts: 3
|
Hi All,
I am trying to call a stored procedure from ESQL to retrieve data from database.
My Stored Procedure
CREATE PROCEDURE SelectBillPayees ()
DYNAMIC RESULT SETS 1
P1: BEGIN
-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN for
SELECT * FROM DB2ADMIN.BILLPAYEES;
-- Cursor left open for client application
OPEN cursor1;
END P1
My ESQL Code
CALL p_selectBillPayees(Environment.ResultSet[]) ;
CREATE PROCEDURE p_selectBillPayees()
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "DB2ADMIN.SELECTBILLPAYEES";
"DB2ADMIN" is the Schema Name.
When I am trying to run this code from my msgflow(with debugger), it just gets hanged once it hits the call statement.
Can someone pls let me know what wrong I am doing here. Any help is greatly appreciated.
Thanks. |
|
| Back to top |
|
 |
| Esa |
| Posted: Mon Jan 13, 2014 11:11 pm Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
Your stored procedure just opens a cursor? No wonder that it hangs.
I think it should return a result set instead. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Mon Jan 13, 2014 11:38 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Please use [ C O D E ] tags around your bits of code. It makes it a lot easier for others to read. For example
| Code: |
declare cFred CHARACTER;
|
A tip for developing stored procedures is to test them from one of the many tools available for the DB concerned. Once you get them working under the same user as the that is used for running the broker you have a far better chance of getting them to work properly from ESQL.
_________________
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 |
|
|
| dogorsy |
| Posted: Tue Jan 14, 2014 8:42 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| Esa wrote: |
Your stored procedure just opens a cursor? No wonder that it hangs.
I think it should return a result set instead. |
??? can you please elaborate on that ? |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Jan 14, 2014 10:29 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| dogorsy wrote: |
| Esa wrote: |
Your stored procedure just opens a cursor? No wonder that it hangs.
I think it should return a result set instead. |
??? can you please elaborate on that ? |
Your procedure opens a cursor. What do you expect to be using that cursor? How does your WMB code then access it and obtain data from it?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mgk |
| Posted: Tue Jan 14, 2014 10:46 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
| Quote: |
| Your procedure opens a cursor. What do you expect to be using that cursor? How does your WMB code then access it and obtain data from it? |
You guys need to bark less and read more, and when you're reading, take a look at some Stored Procedure examples  . To return a result set from a stored procedure, you exit the procedure with one or more cursors left open. Then the magic of ODBC makes these available to the caller (Broker in this case) as result sets. As an example see here:
http://yiyujia.blogspot.co.uk/2011/11/simple-tutorial-about-create-sql-stored.html
To actually resolve this problem can you check that the procedure can be called from the DB2 command line. Then if it can, try getting user trace without the debugger attached and see what it returns or if it still hangs...
Kind regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions.
Last edited by mgk on Tue Jan 14, 2014 10:50 am; edited 1 time in total |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Jan 14, 2014 10:50 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| mgk wrote: |
| You guys need to bark less and read more, and when you're reading, take a look at some Stored Procedure examples . |
I have at no point in my career stooped so low as to read the documentation. I shall continue to just assume things and bark loudly.... 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Esa |
| Posted: Tue Jan 14, 2014 12:16 pm Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
| Vitor wrote: |
| mgk wrote: |
| You guys need to bark less and read more, and when you're reading, take a look at some Stored Procedure examples . |
I have at no point in my career stooped so low as to read the documentation. I shall continue to just assume things and bark loudly.... |
 Assuming things and barking is fun! |
|
| Back to top |
|
|
| 9999999 |
| Posted: Tue Jan 14, 2014 12:55 pm Post subject: |
|
|
Newbie
Joined: 13 Jan 2014
Posts: 3
|
Thanks All for the replies,
I wrote a standalone jave program and was able to call the stored procedure and get the data, but when I am calling the same from ESQL, it is not working. Can someone pls help?
Thanks. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Tue Jan 14, 2014 1:08 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Well done for getting the procedure working.
Have you reviewed the other posts in this forum that relate to this subject?
That would be my next step.
_________________
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 |
|
|
| dogorsy |
| Posted: Tue Jan 14, 2014 10:06 pm Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| Esa wrote: |
| Vitor wrote: |
| mgk wrote: |
| You guys need to bark less and read more, and when you're reading, take a look at some Stored Procedure examples . |
I have at no point in my career stooped so low as to read the documentation. I shall continue to just assume things and bark loudly.... |
Assuming things and barking is fun! |
Maybe so, but it does not help to solve the problem, on the contrary, it confuses the OP, and encouraging people NOT to read the documentation is not the correct thing to do.
@9999999 You mentioned that it hangs running with debugger, have you tried turning the debugger off ? |
|
| Back to top |
|
|
| Esa |
| Posted: Wed Jan 15, 2014 2:16 am Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
| dogorsy wrote: |
| Esa wrote: |
| Vitor wrote: |
| mgk wrote: |
| You guys need to bark less and read more, and when you're reading, take a look at some Stored Procedure examples . |
I have at no point in my career stooped so low as to read the documentation. I shall continue to just assume things and bark loudly.... |
Assuming things and barking is fun! |
Maybe so, but it does not help to solve the problem, on the contrary, it confuses the OP, and encouraging people NOT to read the documentation is not the correct thing to do.
|
It depends, it's very much a cultural thing.
But, in general, posting a wrong answer, boasting about not reading the documentation and then looking silly can be a demonstration targeted to encourage people NOT to NOT read the documentation.
To be serious, in my experience posting a silly reply has more often than not helped the OP. It brings the topic back on top of the forum and usually triggers a discussion that may contain helpful information. Like this time, I suppose. I will certainly keep on sending silly posts. But only when the problem is interesting enough to deserve more attention.
Last edited by Esa on Wed Jan 15, 2014 2:24 am; edited 1 time in total |
|
| Back to top |
|
|
| dogorsy |
| Posted: Wed Jan 15, 2014 2:24 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| @Esa maybe you should be saying "misleading and inaccurate posts" rather than "silly". |
|
| Back to top |
|
|
|
|
