| Author |
Message
|
| Inforz |
 Posted: Thu Jun 30, 2011 3:10 am Post subject: Accessing DB tables from MB v7 |
 |
|
Centurion
Joined: 15 Apr 2011
Posts: 139
Location: Chennai, India
|
I am trying to connect to Oracle DB in linux from MB(windows) using an ODBC entry and Oracle client(tnsnames.ora entry). But am not able to connect to a particular table and fetch its row.
This is the esql stmt am using in compute node. Please let me know if any changes to be done.
SET Environment.Variables.ociv = THE(SELECT A.OTBND_CARR_IMPL_VER FROM Database.verlook As A where A.INBND_CARR_IMPL_VER = Inbound_CIV);
while debugging, after this stmt, the environment variable is not present nor any error has been thrown. |
|
| Back to top |
|
 |
| Inforz |
| Posted: Thu Jun 30, 2011 3:12 am Post subject: |
|
|
Centurion
Joined: 15 Apr 2011
Posts: 139
Location: Chennai, India
|
| I say am able to connect from the "Test connection success" result after clicking "Test Connection" button in administration tools->DataSources |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Jun 30, 2011 3:25 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Toolkit has absolutely nothing to do with runtime database configuration.
If you want to test runtime database configuration, you can use mqsicvp - in v7 at least.
Don't use debugger, take a user trace. |
|
| Back to top |
|
|
| cvag |
| Posted: Fri Jul 01, 2011 12:08 am Post subject: |
|
|
Centurion
Joined: 17 Mar 2008
Posts: 127
|
Hi Inforz,
| Quote: |
| SET Environment.Variables.ociv = THE(SELECT A.OTBND_CARR_IMPL_VER FROM Database.verlook As A where A.INBND_CARR_IMPL_VER = Inbound_CIV); |
Please try with this once
SET Environment.Variables.ociv = THE(SELECT ITEM A.OTBND_CARR_IMPL_VER FROM Database.verlook As A where A.INBND_CARR_IMPL_VER = Inbound_CIV);
Think ITEM keyword is necessary in that syntax |
|
| Back to top |
|
|
| Inforz |
| Posted: Fri Jul 01, 2011 12:56 am Post subject: |
|
|
Centurion
Joined: 15 Apr 2011
Posts: 139
Location: Chennai, India
|
thanks for the comments from mqjeff and cvag. I just tried to reduce the sql query as follows.
SET Environment.Variables.ociv = THE(SELECT ITEM A.OTBND_CARR_IMPL_VER FROM Database.verlook As A);
and got a cast exception saying Invalid Characters. And in the exception tree at the last I found
Text = 3[a box symbol]0[a box symbol]1[a box symbol]
but when I issued the same select query from sqlplus, i got the value to be 301, I dont know why a box symbol is appended between each number and that is throwing the cast exception.
Anybody have encountered such error??
Thanks, |
|
| Back to top |
|
|
| Inforz |
| Posted: Fri Jul 01, 2011 1:12 am Post subject: |
|
|
Centurion
Joined: 15 Apr 2011
Posts: 139
Location: Chennai, India
|
Hi mqjeff,
when I issued mqsicvp against the brokername and DSN, i got the following msg.
BIP8273I: The following datatypes and functions are not natively supported by datasource 'ORACLEDSN' using this ODBC driver: Unsupported datatypes: 'DATE, TIME, CLOB, BLOB'
Unsupported functions: 'ACOS, ASIN, ATAN, ATAN2, COT, DEGREES, RADIANS, RAND, ROUND, POSITION, EXTRACT, CAST, COALESCE, NULLIF'
Examine the specific datatypes and functions not supported natively by this datasource using this ODBC driver.
When using these datatypes and functions within ESQL, the associated data processing is done within WebSphere Message Broker rather than being processed by the database provider.
But when compared the error I have just previously quoted, I dont think this can be a cause. do you?
Thanks, |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Fri Jul 01, 2011 4:13 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Can you display your ODBC.ini ?
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| Inforz |
| Posted: Wed Jul 06, 2011 6:12 am Post subject: |
|
|
Centurion
Joined: 15 Apr 2011
Posts: 139
Location: Chennai, India
|
Complete contents of c:\windows\odbc.ini
[ODBC 32 bit Data Sources]
ORACLEDSN=Oracle in OraClient10g_home1 (32 bit)
ODSN=WebSphere Message Broker DataDirect Technologies 6.0 32-BIT Oracle Wire Protocol (32 bit)
[ORACLEDSN]
Driver32=C:\oracle\product\10.2.0\client_1\BIN\SQORA32.DLL
[ODSN]
Driver32=C:\Program Files\IBM\MQSI\7.0\bin\UKORA24.DLL |
|
| Back to top |
|
|
| Inforz |
| Posted: Wed Jul 06, 2011 6:19 am Post subject: |
|
|
Centurion
Joined: 15 Apr 2011
Posts: 139
Location: Chennai, India
|
FYI,
I use ORACLEDSN in compute node's DSN property. |
|
| Back to top |
|
|
| smdavies99 |
| Posted: Wed Jul 06, 2011 9:01 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
| Inforz wrote: |
BIP8273I: The following datatypes and functions are not natively supported by datasource 'ORACLEDSN' using this ODBC driver: Unsupported datatypes: 'DATE, TIME, CLOB, BLOB'
Unsupported functions: 'ACOS, ASIN, ATAN, ATAN2, COT, DEGREES, RADIANS, RAND, ROUND, POSITION, EXTRACT, CAST, COALESCE, NULLIF'
Examine the specific datatypes and functions not supported natively by this datasource using this ODBC driver.
|
The message is which from:-
E Error
S Status
W Warning
I Informational
??????
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Jul 06, 2011 9:41 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| Inforz wrote: |
| but when I issued the same select query from sqlplus, i got the value to be 301, I dont know why a box symbol is appended between each number and that is throwing the cast exception. |
The box character is a placeholder for the other half of a double byte character. Whatever's running sqlplus is correctly interpreting this, your broker configuration is not.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Inforz |
| Posted: Wed Jul 06, 2011 11:14 pm Post subject: |
|
|
Centurion
Joined: 15 Apr 2011
Posts: 139
Location: Chennai, India
|
| Thanks Vitor, any possible solutions for this? |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Thu Jul 07, 2011 12:15 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Jul 07, 2011 4:59 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| Inforz wrote: |
| Thanks Vitor, any possible solutions for this? |
Correct the configuration? 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
|
|
