MQSeries.net :: View topic - Adding the schema name in the SP's

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 » Adding the schema name in the SP's

Adding the schema name in the SP's

« View previous topic :: View next topic »

Author

Message

rajmq

Posted: Mon Jun 23, 2008 8:06 am Post subject: Adding the schema name in the SP's

Partisan

Joined: 29 Sep 2002
Posts: 331
Location: USA

I am calling the store procedure from esql.
I have used the below esql in the flow and working without any issue.

CALL sp_load_data(txtid,serid );

CREATE PROCEDURE sp_load_data(IN txtid CHARACTER,IN serid CHARACTER) EXTERNALNAME "TESTDB.pkg.test.sp_load_data";

But I need to pass the db/schema names from flow using promote property(bar-file configuration).

i have tried like below but it is not worked.

CREATE PROCEDURE sp_load_data (IN txtid CHARACTER, IN serid CHARACTER) EXTERNALNAME "database.schema1.pkg.test.sp_load_data";

database.schema1 - how to pass the Dynamic values ?

TIA

_________________
IBM Certified System Administrator - WebSphere MQ V6.0
IBM Certified System Administrator - WebSphere Business Integration Message Broker V6.0

vsr

Posted: Mon Jun 23, 2008 9:24 am Post subject:

Centurion

Joined: 04 Apr 2006
Posts: 104

I may be wrong but did you try "{database}.{schema1}.pkg.test.sp_load_data" where database and schema1 holds the userdefined values ?

broker_new

Posted: Tue Jun 24, 2008 3:40 am Post subject:

Yatiri

Joined: 30 Nov 2006
Posts: 614
Location: Washington DC

Try using the User Defined Properties and declare the variables as EXTERNAL in ESQL to get those values.

rajmq

Posted: Tue Jun 24, 2008 8:03 am Post subject:

Partisan

Joined: 29 Sep 2002
Posts: 331
Location: USA

Thanks for all you suggestions.

{Database}.{schema1}.pkg.test.sp_load_data ....NOT Working

I have defined the schema varialble in the user defined property and while calling the esql "database.schema1.pkg.test.sp_load_data".

Actually the database name is getting populated correctly but not schema1 name !!
_________________
IBM Certified System Administrator - WebSphere MQ V6.0
IBM Certified System Administrator - WebSphere Business Integration Message Broker V6.0

vsr

Posted: Tue Jun 24, 2008 10:04 am Post subject:

Centurion

Joined: 04 Apr 2006
Posts: 104

If it works for 'database' then it should work for 'schema1' too. Are you sure that the schema name you are passing(in the bar file) and the value you are expecting are same ? May be a typo in the code?

I am curious at the procedure though. The routine name has Database.Schema.PackageName. I guess it's Oracle database. I don't remember passing DatabaseName for the EXTERNAL routine name, I am not sure though.

Display posts from previous:

Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Adding the schema name in the SP's

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