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 » PASSING

Post new topic  Reply to topic
 PASSING « View previous topic :: View next topic » 
Author Message
kotagiriaashish
PostPosted: Sat Jun 06, 2015 10:56 pm    Post subject: PASSING Reply with quote

Disciple

Joined: 06 Aug 2011
Posts: 165

Hello I have to pass a row variable to oracle stored procedure signature of the procedure is

Code:

create or replace PROCEDURE procCursorExample(cursorParam IN  SYS_REFCURSOR  , userNameParam OUT VARCHAR2);


I tried to pass a reference to ROW datatype like


Code:
DECLARE REFERENCEVAR REFERENCE TO INPUTREF.Somethings.something;
      
      CALL row_test(REFERENCEVAR,Environment.variables.data.name.hello);



Code:

create procedure row_test (IN row_reference REFERENCE, OUT content CHARACTER)
LANGUAGE DATABASE EXTERNAL NAME ".somepackagec";





failed with exception " A non scalar parameter passed to Stored Procedure"

can anyone please help me with this? I tried some variations but unable to get it to work.
Back to top
View user's profile Send private message
fjb_saper
PostPosted: Sat Jun 06, 2015 11:16 pm    Post subject: Reply with quote

Grand High Poobah

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

It says somewhere in the manual that you're not authorized to pass a row type into a stored proc. So define it as a branch / leaf in the tree immediately below the row...
_________________
MQ & Broker admin
Back to top
View user's profile Send private message Send e-mail
kotagiriaashish
PostPosted: Sun Jun 07, 2015 5:08 am    Post subject: Reply with quote

Disciple

Joined: 06 Aug 2011
Posts: 165

Thanks fjb_saper
I was finally able to do it with small modification to the procedure declaration on the ESQL side...

Code:
 
create procedure DEMO_PROCEDURE (OUT content CHARACTER)
LANGUAGE DATABASE DYNAMIC RESULT SETS 1  EXTERNAL NAME ".procCursorExample";



Code:

   CALL DEMO_PROCEDURE(Environment.variables.datamaplk.row[],Character_holder) IN Database.{PROCEDURE_SCHEMA};   
Back to top
View user's profile Send private message
HemavathyRG
PostPosted: Wed Apr 20, 2016 11:58 pm    Post subject: need to pass array to the stored procedure Reply with quote

Newbie

Joined: 20 Apr 2016
Posts: 8

hi kotagiriaashish,
I fallowed what ever u suggested but I ma getting the below exception.
Can you please help me out.

Id Number:BIP2322E:Child SQL exception:IM002:0:[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.

Datasource name is mentioned in the ESQL node properties.
Back to top
View user's profile Send private message
smdavies99
PostPosted: Thu Apr 21, 2016 1:51 am    Post subject: Re: need to pass array to the stored procedure Reply with quote

Jedi Council

Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.

HemavathyRG wrote:


Datasource name is mentioned in the ESQL node properties.


does broker know about the datasource?
_________________
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
View user's profile Send private message
Vitor
PostPosted: Thu Apr 21, 2016 4:31 am    Post subject: Re: need to pass array to the stored procedure Reply with quote

Grand High Poobah

Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA

smdavies99 wrote:
HemavathyRG wrote:


Datasource name is mentioned in the ESQL node properties.


does broker know about the datasource?


By which I think my worthy associate is asking if the datasource is defined to the broker runtime as well as mentioned in the properties.
_________________
Honesty is the best policy.
Insanity is the best defence.
Back to top
View user's profile Send private message
HemavathyRG
PostPosted: Fri Apr 22, 2016 5:28 am    Post subject: Re: need to pass array to the stored procedure Reply with quote

Newbie

Joined: 20 Apr 2016
Posts: 8

smdavies99 wrote:
HemavathyRG wrote:


Datasource name is mentioned in the ESQL node properties.


does broker know about the datasource?


Yes.
its configured at broker level as well.

In the call statement if I remove in DATABSE, IT WILL SAY THAT a "NON SCALAR PARAMETER I SAPSSED TO THE STORE DPROCEDURE "

My SP definition is given below.
CREATE PROCEDURE IWP_MODIFYDELEGATION(IN P_REQUEST_ID CHARACTER,OUT RET_MSG_OUT CHARACTER,OUT SQLCODE_OUT CHARACTER)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "OPS.IWP_MODIFYDELEGATION";

DECLARE DbSchema char 'OPS';
CALL IWP_AL_MODIFYDELEGATION(modifyRequestType,requestId,retMsgOut,sqlcodeOut) IN Database.{DbSchema}.IWP_MODIFYDELEGATION;
Back to top
View user's profile Send private message
maurito
PostPosted: Sun Apr 24, 2016 10:55 pm    Post subject: Re: need to pass array to the stored procedure Reply with quote

Partisan

Joined: 17 Apr 2014
Posts: 358

HemavathyRG wrote:
smdavies99 wrote:
HemavathyRG wrote:


Datasource name is mentioned in the ESQL node properties.


does broker know about the datasource?


Yes.
its configured at broker level as well.

In the call statement if I remove in DATABSE, IT WILL SAY THAT a "NON SCALAR PARAMETER I SAPSSED TO THE STORE DPROCEDURE "

My SP definition is given below.
CREATE PROCEDURE IWP_MODIFYDELEGATION(IN P_REQUEST_ID CHARACTER,OUT RET_MSG_OUT CHARACTER,OUT SQLCODE_OUT CHARACTER)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "OPS.IWP_MODIFYDELEGATION";

DECLARE DbSchema char 'OPS';
CALL IWP_AL_MODIFYDELEGATION(modifyRequestType,requestId,retMsgOut,sqlcodeOut) IN Database.{DbSchema}.IWP_MODIFYDELEGATION;

Your call to the procedure does not match the signature.
The last parameter in the call should be a row to receive the result set.

So you shoul have something like
Code:
CALL IWP_AL_MODIFYDELEGATION(request_id_IN,ret_msg_OUT,sqlcode_Out,Environment.results[]) IN Database.{DbSchema}.IWP_MODIFYDELEGATION;
Back to top
View user's profile Send private message
HemavathyRG
PostPosted: Mon Apr 25, 2016 4:13 am    Post subject: Re: need to pass array to the stored procedure Reply with quote

Newbie

Joined: 20 Apr 2016
Posts: 8

maurito wrote:
HemavathyRG wrote:
smdavies99 wrote:
HemavathyRG wrote:


Datasource name is mentioned in the ESQL node properties.


does broker know about the datasource?


Yes.
its configured at broker level as well.

In the call statement if I remove in DATABSE, IT WILL SAY THAT a "NON SCALAR PARAMETER I SAPSSED TO THE STORE DPROCEDURE "

My SP definition is given below.
CREATE PROCEDURE IWP_MODIFYDELEGATION(IN P_REQUEST_ID CHARACTER,OUT RET_MSG_OUT CHARACTER,OUT SQLCODE_OUT CHARACTER)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "OPS.IWP_MODIFYDELEGATION";

DECLARE DbSchema char 'OPS';
CALL IWP_AL_MODIFYDELEGATION(modifyRequestType,requestId,retMsgOut,sqlcodeOut) IN Database.{DbSchema}.IWP_MODIFYDELEGATION;

Your call to the procedure does not match the signature.
The last parameter in the call should be a row to receive the result set.

So you shoul have something like
Code:
CALL IWP_AL_MODIFYDELEGATION(request_id_IN,ret_msg_OUT,sqlcode_Out,Environment.results[]) IN Database.{DbSchema}.IWP_MODIFYDELEGATION;



Still I am getting the same exception. datasource name not found.

here Is my request

<modifyDelegationArray>
<transactionId>abc</transactionId>
<loginId>XX5778</loginId>
<status>Testing</status>
<startDate>2016-03-24</startDate>
<endDate>2016-03-02</endDate>
</modifyDelegationArray>
<requestId>197</requestId>
</iwp:contact admin>

modifyDelegationArray --> can be multiple
Back to top
View user's profile Send private message
mqjeff
PostPosted: Mon Apr 25, 2016 4:14 am    Post subject: Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 17447

What does 'datasource not found' mean?
_________________
chmod -R ugo-wx /
Back to top
View user's profile Send private message
maurito
PostPosted: Mon Apr 25, 2016 4:34 am    Post subject: Re: need to pass array to the stored procedure Reply with quote

Partisan

Joined: 17 Apr 2014
Posts: 358

HemavathyRG wrote:
Still I am getting the same exception. datasource name not found.

http://www.ibm.com/support/knowledgecenter/SSMKHH_9.0.0/com.ibm.etools.mft.doc/ak04920_.htm?lang=en
Review the syntax of your CALL statement.
Back to top
View user's profile Send private message
visasimbu
PostPosted: Fri Apr 29, 2016 2:49 am    Post subject: Reply with quote

Disciple

Joined: 06 Nov 2009
Posts: 171

Did enabled SQLDescribeParms in the DSN ?
Back to top
View user's profile Send private message Send e-mail
maurito
PostPosted: Fri Apr 29, 2016 3:13 am    Post subject: Reply with quote

Partisan

Joined: 17 Apr 2014
Posts: 358

visasimbu wrote:
Did enabled SQLDescribeParms in the DSN ?

If the datasource is NOT FOUND, it does not matter whether you enable any of the options or not, it still will not find it.
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 » PASSING
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.