ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Stored Procedure result set issue in Compute node

Post new topic  Reply to topic
 Stored Procedure result set issue in Compute node « View previous topic :: View next topic » 
Author Message
afroz11031
PostPosted: Tue May 30, 2017 3:06 am    Post subject: Stored Procedure result set issue in Compute node Reply with quote

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
View user's profile Send private message
mqjeff
PostPosted: Tue May 30, 2017 4:54 am    Post subject: Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 17447

One uses JDBC and one uses ODBC.
_________________
chmod -R ugo-wx /
Back to top
View user's profile Send private message
afroz11031
PostPosted: Tue May 30, 2017 2:26 pm    Post subject: Reply with quote

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
View user's profile Send private message
fjb_saper
PostPosted: Tue May 30, 2017 8:03 pm    Post subject: Reply with quote

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20696
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
View user's profile Send private message Send e-mail
afroz11031
PostPosted: Tue May 30, 2017 8:57 pm    Post subject: Reply with quote

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
View user's profile Send private message
afroz11031
PostPosted: Wed May 31, 2017 12:43 am    Post subject: Reply with quote

Apprentice

Joined: 28 Jan 2014
Posts: 36

Dear WMB team,

Any suggestion on my query please.

Thanks
Afroz
Back to top
View user's profile Send private message
fjb_saper
PostPosted: Wed May 31, 2017 1:52 am    Post subject: Reply with quote

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20696
Location: LI,NY

Do you have an ODBC target assigned to your ESQL Compute node?
_________________
MQ & Broker admin
Back to top
View user's profile Send private message Send e-mail
afroz11031
PostPosted: Wed May 31, 2017 2:17 am    Post subject: Reply with quote

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
View user's profile Send private message
mqjeff
PostPosted: Wed May 31, 2017 4:24 am    Post subject: Reply with quote

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
View user's profile Send private message
afroz11031
PostPosted: Wed May 31, 2017 8:02 am    Post subject: Reply with quote

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
View user's profile Send private message
afroz11031
PostPosted: Wed May 31, 2017 2:13 pm    Post subject: Reply with quote

Apprentice

Joined: 28 Jan 2014
Posts: 36

Appreciate if you could please provide a solution. Thanks in advance.
Back to top
View user's profile Send private message
rekarm01
PostPosted: Wed May 31, 2017 4:55 pm    Post subject: Re: Stored Procedure result set issue in Compute node Reply with quote

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
View user's profile Send private message
nelson
PostPosted: Thu Jun 01, 2017 2:29 pm    Post subject: Reply with quote

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
View user's profile Send private message
afroz11031
PostPosted: Thu Jun 15, 2017 6:34 pm    Post subject: Re: Stored Procedure result set issue in Compute node Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Stored Procedure result set issue in Compute node
Jump to:  



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
Protected by Anti-Spam ACP
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.