| Author |
Message
|
| kalpakci |
 Posted: Mon Mar 26, 2012 12:10 am Post subject: (Screen Shot) oracle queries returns nothing (db2:ok) |
 |
|
Newbie
Joined: 19 Mar 2012
Posts: 3
|
I create a simple message flow for testing
My simple ESQL Code:
I check MQ Message and it returns nothing
(1:RED: DB2 DB works perfectly.)
(2:BLUE: Oracle DB v10r2 returns nothing.)
I tested flow+ESQL with a DB2 table and i can see table content with xml tags in MQ Explorer.
How can i fix it?
Note: I created database definitions for Oracle&db2 and referenced them in my message flow project.
Last edited by kalpakci on Tue Sep 04, 2012 2:39 am; edited 3 times in total |
|
| Back to top |
|
 |
| Esa |
| Posted: Mon Mar 26, 2012 12:29 am Post subject: |
|
|
Grand Master
Joined: 22 May 2008
Posts: 1387
Location: Finland
|
This should work:
| Code: |
| SELECT T FROM Database.TABLE AS T |
|
|
| Back to top |
|
|
| fjb_saper |
| Posted: Mon Mar 26, 2012 8:04 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
I hope you referenced Oracle DB correctly.
A select on an oracle DB with the node referencing DB2 will not yield any proper result... 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| mqsiuser |
| Posted: Mon Mar 26, 2012 8:38 pm Post subject: Re: (Screen Shot) oracle queries returns nothing (db2:ok) |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
| kalpakci wrote: |
| Note: I created database definitions for Oracle&db2 and referenced them in my message flow project. |
I also did that in the beginning and then skipped this idea. Just use the environment (probably pass over a reference to it).
I highly assume you are on Windows (Toolkit + Broker Runtime). You have to setup an ODBC-Datasource there, afaicr one for each database you use... that will be one like "DB2" and one you name it "ORCL". You should test if they are working properly independently of Broker (there is a "test connection" button) !
Then you can use these Datasources within Broker... as the Datasource-Name (within your ESQL-Compute-Node).
Also use DB-Tools like SQL-Developer to establish a connection to the Oracle DB and browser its content.
_________________
Just use REFERENCEs |
|
| Back to top |
|
|
| kalpakci |
| Posted: Wed Sep 05, 2012 1:49 am Post subject: Re: (Screen Shot) oracle queries returns nothing (db2:ok) |
|
|
Newbie
Joined: 19 Mar 2012
Posts: 3
|
| mqsiuser wrote: |
| kalpakci wrote: |
| Note: I created database definitions for Oracle&db2 and referenced them in my message flow project. |
I also did that in the beginning and then skipped this idea. Just use the environment (probably pass over a reference to it).
I highly assume you are on Windows (Toolkit + Broker Runtime). You have to setup an ODBC-Datasource there, afaicr one for each database you use... that will be one like "DB2" and one you name it "ORCL". You should test if they are working properly independently of Broker (there is a "test connection" button) !
Then you can use these Datasources within Broker... as the Datasource-Name (within your ESQL-Compute-Node).
Also use DB-Tools like SQL-Developer to establish a connection to the Oracle DB and browser its content. |
You are right, I'm on Windows (Toolkit + Broker Runtime).
I created a ODBC connection and ODBC Test connection was successful.
i set up a user ID and password (with mqsisetdbparms command)for this DNS but i'm not sure it's necessary.
I've changed the data source name (as Oracle ODBC Data Source Name)
I'm getting this error:
Note:If I create database definitions for Oracle(with JDBC) i won't get any error here.
Missing something here? A JDBC connection is easy for me but i still don't know how to test ODBC Database connections in MB. |
|
| Back to top |
|
|
| mqsiuser |
| Posted: Wed Sep 05, 2012 2:25 am Post subject: Re: (Screen Shot) oracle queries returns nothing (db2:ok) |
|
|
Yatiri
Joined: 15 Apr 2008
Posts: 637
Location: Germany
|
That is just a warning: ignore it
The warning basically says "I cannot resolve the thing"... but if you are untyped (which is ok in some/many cases), then that's how it is.
Somewhere in eclipse you can set/unset "treat warnings as error": Unset this.
Can you (still) deploy ?! |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Sep 05, 2012 3:43 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
That is the wrong ODBC driver for Oracle.
It is the cause of your issue.
The original ESQL Select statement you had was correct.
You MUST use the DataDirect ODBC Driver for Oracle that comes with Broker.
You CAN NOT use any other Oracle ODBC Driver.
If you were at Broker v7 or later, you could use mqsicvp to test that the datasource was working. You CAN'T do this any other way in v6.1.
You should make sure that your team has a plan to upgrade to v7 or v8 in the next few years. It's IMPORTANT to stay at a supported level. |
|
| Back to top |
|
|
|
|
