| Author |
Message
|
| afroz11031 |
 Posted: Tue May 30, 2017 3:06 am Post subject: Stored Procedure result set issue in Compute node |
 |
|
Apprentice
Joined: 28 Jan 2014
Posts: 36
|
Hi,
I am using IIB v10 with fix pak 7,,, I am calling stored procedure from compute node which is returning multiple records. While calling SP , the value of the result set is coming as null. However same stored procedure returns the result set value while calling from Java Compute node.... I have gone thru several blogs/IBM info center but does not help me. below is the esql code which is used to call the stored procedure (hosted in client sql server) . After call i check the status of the call and do not see any error. Please assist.
CALL storedProc (Environment.Variables.ResultSet[]);
DECLARE i INTEGER CARDINALITY(Environment.Variables.ResultSet[]);
SET Environment.Variables.SQLState1 = SQLSTATE;
SET Environment.Variables.SQLCODE = SQLCODE;
SET Environment.Variables.SQLErrorText1 = SQLERRORTEXT;
SET Environment.Variables.SQLNativeError1 = SQLNATIVEERROR;
--------------------------------------------------------------
CREATE PROCEDURE storedProc() LANGUAGE DATABASE DYNAMIC RESULT SETS 1 EXTERNAL NAME "dbo._sp_MW_EBS_PO_TO_ORACLE"; |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Tue May 30, 2017 4:54 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
One uses JDBC and one uses ODBC.
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| afroz11031 |
| Posted: Tue May 30, 2017 2:26 pm Post subject: |
|
|
Apprentice
Joined: 28 Jan 2014
Posts: 36
|
| I know, but can you please provide me some solution for compute node part, do you think the way I call the stored procedure is not correct? |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Tue May 30, 2017 8:03 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| afroz11031 wrote: |
| I know, but can you please provide me some solution for compute node part, do you think the way I call the stored procedure is not correct? |
Have you tried
| Code: |
SET Environment.Variables.ResultSet.Data[]= Call storedProc().
|
How did you define the ESQL signature for your stored proc?
Have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| afroz11031 |
| Posted: Tue May 30, 2017 8:57 pm Post subject: |
|
|
Apprentice
Joined: 28 Jan 2014
Posts: 36
|
| Quote: |
Have you tried
Code:
SET Environment.Variables.ResultSet.Data[]= Call storedProc(). |
I dont think it will work as we can not assign CALL statement at the right side of assignment.
| Quote: |
| How did you define the ESQL signature for your stored proc? |
CREATE PROCEDURE storedProc() LANGUAGE DATABASE DYNAMIC RESULT SETS 1 EXTERNAL NAME "dbo._sp_MW_EBS_PO_TO_ORACLE"; |
|
| Back to top |
|
|
| afroz11031 |
| Posted: Wed May 31, 2017 12:43 am Post subject: |
|
|
Apprentice
Joined: 28 Jan 2014
Posts: 36
|
Dear WMB team,
Any suggestion on my query please.
Thanks
Afroz |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed May 31, 2017 1:52 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Do you have an ODBC target assigned to your ESQL Compute node?
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| afroz11031 |
| Posted: Wed May 31, 2017 2:17 am Post subject: |
|
|
Apprentice
Joined: 28 Jan 2014
Posts: 36
|
Thanks fjb_saper for your response,
Yes,I have assigned ODBC data source on CN.
Thanks
Afroz |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed May 31, 2017 4:24 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
What is the db side declaration of the stored procedure? Does it require an input value?
Also, as a second note... do not use CARDINALITY to loop over repeating structures in ESQL. Use a reference variable, or a for loop.
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| afroz11031 |
| Posted: Wed May 31, 2017 8:02 am Post subject: |
|
|
Apprentice
Joined: 28 Jan 2014
Posts: 36
|
| Basically it has just select query at other side and no parameter required while to call.. |
|
| Back to top |
|
|
| afroz11031 |
| Posted: Wed May 31, 2017 2:13 pm Post subject: |
|
|
Apprentice
Joined: 28 Jan 2014
Posts: 36
|
| Appreciate if you could please provide a solution. Thanks in advance. |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Wed May 31, 2017 4:55 pm Post subject: Re: Stored Procedure result set issue in Compute node |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| afroz11031 wrote: |
below is the esql code which is used to call the stored procedure (hosted in client sql server)
| Code: |
CALL storedProc (Environment.Variables.ResultSet[]);
-- ...
CREATE PROCEDURE storedProc() LANGUAGE DATABASE DYNAMIC RESULT SETS 1 EXTERNAL NAME "dbo._sp_MW_EBS_PO_TO_ORACLE"; |
|
Make sure that the SQL Server stored procedure uses the "SET NOCOUNT ON" option, to allow result sets to be returned correctly.
Check the Info Center for more details. |
|
| Back to top |
|
|
| nelson |
| Posted: Thu Jun 01, 2017 2:29 pm Post subject: |
|
|
Partisan
Joined: 02 Oct 2012
Posts: 313
|
I remember having some issue calling a SP with no parameters (I don't remember the specific details), after some try and error tests we realized that if we put at least one parameter (of course, also in the db SP) we received the correct response... So you could do some testing on that to check if is something related to the non parameter SP.
Regards. |
|
| Back to top |
|
|
| afroz11031 |
| Posted: Thu Jun 15, 2017 6:34 pm Post subject: Re: Stored Procedure result set issue in Compute node |
|
|
Apprentice
Joined: 28 Jan 2014
Posts: 36
|
Thanks rekarm01, finally it worked after setting NOCOUNT ON on the SP  ,,, it took me a long time to work on your suggestion as the Stored procedure is owned by third party.
| rekarm01 wrote: |
| afroz11031 wrote: |
below is the esql code which is used to call the stored procedure (hosted in client sql server)
| Code: |
CALL storedProc (Environment.Variables.ResultSet[]);
-- ...
CREATE PROCEDURE storedProc() LANGUAGE DATABASE DYNAMIC RESULT SETS 1 EXTERNAL NAME "dbo._sp_MW_EBS_PO_TO_ORACLE"; |
|
Make sure that the SQL Server stored procedure uses the "SET NOCOUNT ON" option, to allow result sets to be returned correctly.
Check the Info Center for more details. |
|
|
| Back to top |
|
|
|
|
