MQSeries.net :: View topic - database call from a MAP

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 » database call from a MAP

database call from a MAP

« View previous topic :: View next topic »

Author

Message

phanish

Posted: Sun Feb 15, 2015 12:06 pm Post subject: database call from a MAP

Apprentice

Joined: 04 May 2013
Posts: 30

Hi

in IIB9 Map I am trying to make a simple SQL query to fetch a single column value from the database table.I am using custom esql for this purpose and invoking a function defined in the esql. below is the query written in the esql function

Code:

SET SQLResult = THE(Select A.INVIEW from Database.DSN1.DBName.COUNTRY_DETAILS AS A where A.COUNTRY = CountryKey)

At runtime it is throwing an exception below.

Quote:

An attempt was made to access a database without specifing a datasource

but I am already passing DSN name.Could you please direct me here. I tried using "select from database" option also but I am seeing driver issues.

fjb_saper

Posted: Sun Feb 15, 2015 1:57 pm Post subject:

Grand High Poobah

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

Well it is complaining about not having a DSN on the compute node. Obviously you are calling the ESQL from a mapping node. Did you check the properties of the mapping node? Is there a place there for a DSN?

_________________
MQ & Broker admin

Simbu

Posted: Mon Feb 16, 2015 11:00 pm Post subject:

Master

Joined: 17 Jun 2011
Posts: 289
Location: Tamil Nadu, India

Hi, IBM KC says

Quote:

The ESQL must not include SQL calls to a data source

when using Custom ESQL.

Please read "Requirements for ESQL modules called from a graphical data map"

phanish

Posted: Wed Feb 18, 2015 1:04 am Post subject:

Apprentice

Joined: 04 May 2013
Posts: 30

Hi,

I tried accessing database from compute node also without specifying DSN in the node properties even then it's throwing error rather doing it in the esql.when I specify DSN in the compute node it works.

Code:

Text:CHARACTER:[unixODBC][IBM][ODBC Oracle Wire Protocol driver][Oracle]ORA-12203: unable to connect to destination

below is the excerpt

Code:

DECLARE DataSource1 NAME 'DEV_DSN';
SET OutputLocalEnvironment.Variables.CountryCode[] = PASSTHRU('select A.INVIEW from SCHEMA1.COUNTRY_DETAILS AS A where A.COUNTRY= ?' TO Database.DataSource1 VALUES(SQLCC));

Does it have anything to do with the Oracle driver?

Simbu

Posted: Wed Feb 18, 2015 1:43 am Post subject:

Master

Joined: 17 Jun 2011
Posts: 289
Location: Tamil Nadu, India

phanish wrote:

when I specify DSN in the compute node it works.

It means it works as expected. Compute node Data Source property is optional but in your case it is must because you are using Database reference in PASSTHRU. Please read the description of Data Source.

phanish

Posted: Sun Feb 22, 2015 10:08 pm Post subject:

Apprentice

Joined: 04 May 2013
Posts: 30

Hi,

I am able to connect to Database and run a select query from the MAP now,for that I had to configure JDBCProvider and explicitly create a .dbm configuring with the SID,host,port,UserID and pwd.This would be tedious when migrating this QA and higher domains. Is there any way at all to do this using a DSN in the MAP. Could you please throw some light on this.

fjb_saper

Posted: Mon Feb 23, 2015 5:10 am Post subject:

Grand High Poobah

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

phanish wrote:

No you did it the right way. The only thing that changes for the other environments is the configurable service. No need to recreate the .dbm which is only for use with the toolkit.

_________________
MQ & Broker admin

phanish

Posted: Mon Feb 23, 2015 7:21 am Post subject:

Apprentice

Joined: 04 May 2013
Posts: 30

Thanks saper:D

can it be done through a DSN from a MAP,like we do it an Esql from a compute node?? I know there is no place to specify DSN name in the mapping node but still I am wondering this is possible.The process entailment is I cannot use an esql unless necessary.

fjb_saper

Posted: Mon Feb 23, 2015 7:27 am Post subject:

Grand High Poobah

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

phanish wrote:

Different animal. Requires a jdbc connection, hence the jdbc configurable service...

You'll notice certain DB nodes require an odbc connection (DSN) and others require a JDBC connection. Check out which requires which...
The mapping node AFAIK requires a jdbc connection.

Have fun

_________________
MQ & Broker admin

shashivarungupta

Posted: Mon Feb 23, 2015 4:53 pm Post subject:

Grand Master

Joined: 24 Feb 2009
Posts: 1343
Location: Floating in space on a round rock.

fjb_saper wrote:

phanish wrote:

@fjb_saper
And I agree with you..

Map looks for data design project and the configurable service to access the database tables., i.e. jdbc connection.

Whereas ComputeNode/Database node will require odbc system dsn name to connect to remote databases., ie.. odbc connection.

It also depends on your system's architecture, what does it say? what to use it?
Is Java a preference over ESQL, then use Java Compute Node with jdbc connection (refer ibm infocenter for more details)?

_________________
*Life will beat you down, you need to decide to fight back or leave it.

Display posts from previous:

Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » database call from a MAP

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