| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Problem accessing Oracle DB from message flow |
« View previous topic :: View next topic » |
| Author |
Message
|
| eai_user |
 Posted: Thu Aug 23, 2007 9:09 am Post subject: Problem accessing Oracle DB from message flow |
 |
|
Newbie
Joined: 23 Aug 2007
Posts: 3
|
I have Message broker and Oracle DB running on the same Solaris server. The Oracle DB hosts the broker database. The broker is running fine without any issues and is able to make connections to the database.
I've created a new table in the same broker database. The table has been created with a different DB user ID, and a different schema, and has granted all access to PUBLIC.
Problem:- I am trying to query the new table using the ESQL code in a message flow. I am getting the following error
<ErrorText> Node throwing exception. </ErrorText><ErrorText> Root SQL exception. </ErrorText><ErrorText> Child SQL exception. </ErrorText><ErrorText> [DataDirect][ODBC lib] Data source name not found and no default driver specified. </ErrorText><ErrorNumber>2322</ErrorNumber><ErrorDate>2007-08-20 11:45:15.884987</ErrorDate>
I am trying to do a select using the syntax SELECT RSM.* FROM Database.{broker_DSN_Name}.{table_schema}.{table_name} AS RSM WHERE RSM.ID like COALESCE(inputRef.ID,'%')
I set the broker_DSN_Name (by accessing the broker property DataSourceName), table_schema, and table_name before executing the query.
The code works when I run the message flow on my workstation (which is on Windows XP using DB2 database), but when I deploy the message flow on the message broker running on Solaris I get the error mentioned above.
I used the select query in a shared function, and also in a subflow using the Compute node (by specifying the DSN name in the property of the compute node). Both these work on Windows environment, but I get the exact same error when I deploy the flow to the broker running on Solaris.
I checked the ODBC.ini file, and it has one DSN entry with broker DB information and the DataDirect 5.0 Oracle driver information. Since the message flow uses the same user as the broker to access the broker database, I haven't used the "mqsisetdbparms" command.
It looks to be a configuration issue. I'm not sure what I am missing. can somebody please let me know if I am doing anything wrong? or Suggest a different way to do this? Thanks in advance for the help. |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Thu Aug 23, 2007 9:18 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Is the Datasource NAME the same on Solaris as it is on your machine?
I think it isn't.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| eai_user |
| Posted: Thu Aug 23, 2007 12:36 pm Post subject: |
|
|
Newbie
Joined: 23 Aug 2007
Posts: 3
|
| No, The DSN name is not the same on Solaris and my laptop. I also verifed that the right DSN name is being used on the brokers. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Aug 23, 2007 12:52 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
If the DSN name is not the same between the two brokers, then the name you use in your flow needs to change when you deploy it to the different brokers.
That error says that it is not being set correctly when it is deployed to Solaris.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| eai_user |
| Posted: Fri Aug 24, 2007 6:44 am Post subject: |
|
|
Newbie
Joined: 23 Aug 2007
Posts: 3
|
Jeff,
Before I execute the SELECT query, I am getting the DSN name the broker is using the "BrokerDataSource" and I set that to be the DSN name (since the broker DB and the the user table DB are the same).
| Code: |
SET var_brkDSNName = BrokerDataSource;
SET var_schemaname = <Set the schema name>;
SET var_tableName = <Set table Name>;
SET inputRef.ResultSet[] = SELECT RSM.* FROM Database.{var_brkDSNName}.{var_schemaname}.{var_tableName} AS RSM WHERE RSM.ID like inputRef.ID; |
I do have one question, does this code have to be executed in a compute or one of the Database nodes only? Can this whole ESQL code be included as a shared function? To begin with I started with creating the DB query as a generic function in a shared library so that all flows will have access to it. It works on Windows, so I assumed it should work on Solaris broker as well. Am I wrong in assuming that ?
Since the shared function approach wasn't working, I tried creating a shared subflow, and am still running into the same issue. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Fri Aug 24, 2007 6:51 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
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
|
|
|
|
