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 » Dynamically change the Data Source using UDP in ESQL?

Post new topic  Reply to topic
 Dynamically change the Data Source using UDP in ESQL? « View previous topic :: View next topic » 
Author Message
prasadzkv
PostPosted: Thu Apr 06, 2017 8:35 am    Post subject: Dynamically change the Data Source using UDP in ESQL? Reply with quote

Novice

Joined: 13 Jul 2016
Posts: 21
Location: Chennai

Team,

In Oracle DB have to update two tables in two different DSN (i.e, two different ODBC Connections.)

We have to access the DSN name through user defined Property (UDP).

For that We written the following code but we are getting the error. Could you please help.

Declared the following variables in schema level.

DECLARE ONEDB_ODBC EXTERNAL CHARACTER; (getting the 1st dsn from UDP)
DECLARE TWODB_ODBC EXTERNAL CHARACTER; (getting the 2nd dsn from UDP)


UPDATE Database.{ONEDB_ODBC}.TABLE_ONE AS a
SET STATUS_CD = ERROR_CD
WHERE a.COLUMN_NUMBER = Number;


UPDATE Database.{TWODB_ODBC}.TABLE_TWO AS b
SET STATUS_CD = ERROR_CD
WHERE b.COL_TEST_NUMBER = Number;

Thanks in advance.
_________________
-venkat
Back to top
View user's profile Send private message
Vitor
PostPosted: Thu Apr 06, 2017 8:41 am    Post subject: Reply with quote

Grand High Poobah

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

That's not the ODBC connection name, that's the schema name.

I think it's an odd Oracle topology where you couldn't use a single ODBC connection to the Oracle instance to update two different tables, no matter which schema(s) the tables happened to be in.
_________________
Honesty is the best policy.
Insanity is the best defence.
Back to top
View user's profile Send private message
mqjeff
PostPosted: Thu Apr 06, 2017 9:14 am    Post subject: Reply with quote

Grand Master

Joined: 25 Jun 2008
Posts: 17447

Vitor wrote:
That's not the ODBC connection name, that's the schema name.


What he means is that you have put the name of the ODBC Connection definition where you need to put the name of the database schema that holds the table you are trying to update.

If you want to specify a DSN, you must also specify the database schema. So you must have something like
Code:
UPDATE Database.{ONEDB_ODBC}.Schema.TABLE_ONE


You can specify the schema without specifying the DSN, but not the other way around... You might be able to use the default schema on the DSN by setting the schema name to "". But that's a random guess by a random stranger on the internet.

Vitor wrote:
I think it's an odd Oracle topology where you couldn't use a single ODBC connection to the Oracle instance to update two different tables, no matter which schema(s) the tables happened to be in.


I'd hope that it's different information being updated in different tables in different Oracle schemas on different Oracle DB servers...
_________________
chmod -R ugo-wx /
Back to top
View user's profile Send private message
Vitor
PostPosted: Thu Apr 06, 2017 9:34 am    Post subject: Reply with quote

Grand High Poobah

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

mqjeff wrote:
Vitor wrote:
That's not the ODBC connection name, that's the schema name.


What he means is that you have put the name of the ODBC Connection definition where you need to put the name of the database schema that holds the table you are trying to update.


Thank you. I'm experiencing communication difficulties at the moment. Principally trying to communicate that a group of fairly senior people are clueless, mentally deficient, chimps with no grasp of technology or their own strategy without using most of the words in that sentence. I fear legal action by chimps who resent the comparison.

mqjeff wrote:
I'd hope that it's different information being updated in different tables in different Oracle schemas on different Oracle DB servers...


Good point. Blinded by our use of something called "RAC" which seems to automagically move Oracle data round the servers. There's no indication the OP enjoys such advantages.
_________________
Honesty is the best policy.
Insanity is the best defence.
Back to top
View user's profile Send private message
prasadzkv
PostPosted: Sun Apr 09, 2017 11:26 pm    Post subject: Dynamically change the Data Source using UDP in ESQL? Reply with quote

Novice

Joined: 13 Jul 2016
Posts: 21
Location: Chennai

Thanks jeff,

its working fine.
_________________
-venkat
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 » Dynamically change the Data Source using UDP in ESQL?
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.