MQSeries.net :: View topic

MQSeries.net

Tech Exchange

Education

Certifications

Library

Info Center

SupportPacs

FAQÂ Â

Usergroups

RSS Feed - WebSphere MQ Support

RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » PASSING

PASSING

« View previous topic :: View next topic »

Author

Message

kotagiriaashish

Posted: Sat Jun 06, 2015 10:56 pm Post subject: PASSING

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.

fjb_saper

Posted: Sat Jun 06, 2015 11:16 pm Post subject:

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20768
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

kotagiriaashish

Posted: Sun Jun 07, 2015 5:08 am Post subject:

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};

HemavathyRG

Posted: Wed Apr 20, 2016 11:58 pm Post subject: need to pass array to the stored procedure

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.

smdavies99

Posted: Thu Apr 21, 2016 1:51 am Post subject: Re: need to pass array to the stored procedure

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.

Vitor

Posted: Thu Apr 21, 2016 4:31 am Post subject: Re: need to pass array to the stored procedure

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.

HemavathyRG

Posted: Fri Apr 22, 2016 5:28 am Post subject: Re: need to pass array to the stored procedure

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;

maurito

Posted: Sun Apr 24, 2016 10:55 pm Post subject: Re: need to pass array to the stored procedure

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?

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;

HemavathyRG

Posted: Mon Apr 25, 2016 4:13 am Post subject: Re: need to pass array to the stored procedure

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?

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

mqjeff

Posted: Mon Apr 25, 2016 4:14 am Post subject:

Grand Master

Joined: 25 Jun 2008
Posts: 17447

What does 'datasource not found' mean?
_________________
chmod -R ugo-wx /

maurito

Posted: Mon Apr 25, 2016 4:34 am Post subject: Re: need to pass array to the stored procedure

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.

visasimbu

Posted: Fri Apr 29, 2016 2:49 am Post subject:

Disciple

Joined: 06 Nov 2009
Posts: 171

Did enabled SQLDescribeParms in the DSN ?

maurito

Posted: Fri Apr 29, 2016 3:13 am Post subject:

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.

Display posts from previous:

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