| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Problems reading VARCHAR2(4000) field from Oracle |
« View previous topic :: View next topic » |
| Author |
Message
|
| martinrydman |
 Posted: Mon Mar 01, 2004 1:40 am Post subject: Problems reading VARCHAR2(4000) field from Oracle |
 |
|
Centurion
Joined: 30 Jan 2004
Posts: 139
Location: Gothenburg, Sweden
|
Hi!
Anyone familiar with accessing Oracle from WBIMB, your help would be greatly appreciated. The problem is descibed below.
TIA!
/Martin
Versions:
---------
Oracle: 9.2.0.4.0
ODBC Driver: 9.02.00.54 (Oracle in Client)
WBIMB: 5.0.2 (CSD02)
Plattform: WIN2000 SP4
Problem description:
--------------------
We're reading from an Oracle table defined as:
| Code: |
create table CONCAT_MESSAGES
(
MESSAGE_TYPE_ID VARCHAR2(20) not null,
UNIQUE_GROUP_NUMBER NUMBER(32) not null,
RECORD_NUMBER NUMBER(4) not null,
TOTAL_RECORDS_IN_GROUP NUMBER(4) not null,
HEADER VARCHAR2(200) not null,
MSG VARCHAR2(4000) not null,
TSTAMP DATE not null,
constraint CONCAT_MESSAGES_PK primary key (UNIQUE_GROUP_NUMBER, RECORD_NUMBER) |
We're experiencing intermittent problems with the MSG field. We've created a testflow that only tries to access this table. The only thing executed in this flow is the following ESQL:
| Code: |
| SET Environment.Message.Record[] = (SELECT DB.MSG FROM Database.CONCAT_MESSAGES AS DB); |
This statement will succeed sporadically, as a subsequent trace of Environment confirms. Most of the time, however, it will terminate with the ExceptionList shown at the end of this text.
To make sure that there's nothing wrong with the connection or table as such, the following statement has also been run in the same flow (accessing HEADER instead of MSG):
| Code: |
| SET Environment.Message.Record[] = (SELECT DB.HEADER FROM Database.CONCAT_MESSAGES AS DB); |
This request will allways succeed.
| Quote: |
ExceptionList:
--------------
(
(0x01000000):RecoverableException = (
(0x03000000):File = 'F:\build\S000_P\src\DataFlowEngine\ImbComputeNode.cpp'
(0x03000000):Line = 471
(0x03000000):Function = 'ImbComputeNode::evaluate'
(0x03000000):Type = 'ComIbmComputeNode'
(0x03000000):Name = 'MRTest#FCMComposite_1_2'
(0x03000000):Label = 'MRTest.Compute'
(0x03000000):Text = 'Caught exception and rethrowing'
(0x03000000):Catalog = 'BIPv500'
(0x03000000):Severity = 3
(0x03000000):Number = 2230
(0x01000000):RecoverableException = (
(0x03000000):File = 'F:\build\S000_P\src\DataFlowEngine\ImbRdl\ImbRdlStatementGroup.cpp'
(0x03000000):Line = 158
(0x03000000):Function = 'SqlStatementGroup::execute'
(0x03000000):Type = 'ComIbmComputeNode'
(0x03000000):Name = 'MRTest#FCMComposite_1_2'
(0x03000000):Label = 'MRTest.Compute'
(0x03000000):Text = 'Error detected, rethrowing'
(0x03000000):Catalog = 'BIPv500'
(0x03000000):Severity = 3
(0x03000000):Number = 2488
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '.MRTest_Compute.Main'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '5.9'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = 'SET Environment.Message.Record[ ] = (SELECT COLUMN(0) AS :MSG FROM DATABASE(SELECT DB.MSG FROM CONCAT_MESSAGES DB));'
)
(0x01000000):RecoverableException = (
(0x03000000):File = 'F:\build\S000_P\src\DataFlowEngine\ImbRdl\ImbRdlExternalDb.cpp'
(0x03000000):Line = 276
(0x03000000):Function = 'SqlExternalDbStmt::executeStmt'
(0x03000000):Type = 'ComIbmComputeNode'
(0x03000000):Name = 'MRTest#FCMComposite_1_2'
(0x03000000):Label = 'MRTest.Compute'
(0x03000000):Text = 'The following error occurred during execution of an SQL statement'
(0x03000000):Catalog = 'BIPv500'
(0x03000000):Severity = 3
(0x03000000):Number = 2519
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = ''
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = '1.1'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = 'DPWEBSPP'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = 'SELECT DB.MSG FROM CONCAT_MESSAGES DB'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = ''
)
(0x01000000):DatabaseException = (
(0x03000000):File = 'F:\build\S000_P\src\DataFlowEngine\ImbOdbc.cpp'
(0x03000000):Line = 2335
(0x03000000):Function = 'ImbOdbcDynamicResultSet::initialise'
(0x03000000):Type = ''
(0x03000000):Name = ''
(0x03000000):Label = ''
(0x03000000):Text = 'Unsupported SQL type'
(0x03000000):Catalog = 'BIPv500'
(0x03000000):Severity = 3
(0x03000000):Number = 2324
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = 'MSG'
)
(0x01000000):Insert = (
(0x03000000):Type = 2
(0x03000000):Text = '-9'
)
)
)
)
)
) |
|
|
| Back to top |
|
 |
| mgk |
| Posted: Tue Mar 02, 2004 10:25 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi,
What this error is telling you is that the value returned by Oracle to an SQLDescribeParam call for MSG returned -9 which is not a supported ODBC datatype. I do not have my manuals with the at the moment, but I think -9 is a wide character format of somekind. Are you sure that the definition of the table you provided is correct? Check that the MSG column is not a wide characte column. If you are sure it is not wide chars then I suggest you raise a PMR.
Cheers,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| martinrydman |
| Posted: Wed Mar 03, 2004 3:20 am Post subject: |
|
|
Centurion
Joined: 30 Jan 2004
Posts: 139
Location: Gothenburg, Sweden
|
Hi,
and thanks! I've got this answer from another newsgroup, and it seems it did the trick:
You appear to be using the Oracle-supplied ODBC driver.
You must use the driver supplied with WBIMB which is called something like
'MQSeries DataDirect 4.10...'. This is the only ODBC driver supported for
use
with WBIMB - using any other will have unpredictable results.
You must also configure each ODBC datasource definition as described in the
WBIMB documentation - both on the ODBC GUI panels and the Windows registry.
John Overstall
IBM Websphere MQ Development.
/Martin |
|
| Back to top |
|
|
| Thripura Sundari |
| Posted: Tue Jul 26, 2005 5:14 am Post subject: Compute Node for Database Connectivity - peculiar behavior |
|
|
Acolyte
Joined: 13 Jun 2005
Posts: 57
Location: Bangalore, India
|
Hi all,
I am using SQL server 2000 and I created a database connection (in the data perspective) giving the sql user authentication, and specifying
| Quote: |
| DataDirect Connect JDBC Driver |
and
as the class path. The class was
| Quote: |
| com.microsoft.jdbc.sqlserver.SQLServerDriver |
.
I used a compute node to select some data from a table, the ESQL for which is as follows:
| Code: |
CREATE COMPUTE MODULE DBFlow_Compute
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
SET OutputRoot.MQMD.StrucId=MQMD_STRUC_ID;
SET OutputRoot.MQMD.Version=MQMD_CURRENT_VERSION;
SET OutputRoot.XML.Test.Result[]= (SELECT T.CruiseLineId, T.CruiseLineName, T. CruiseLineDescription FROM Database.dbo.CruiseLine AS T);
END;
END MODULE; |
The input is any xml which has nothing to do with the compute node except that it shouldnt throw an XML parser exception.
Well, the comedy (at least because I dont know the reason..) is that, I get the same exception text for the above SQL code as the one I got for a different compute node I had earlier used, in which the esql was,
| Code: |
SET OutputRoot.XML.Test.Result[]= (SELECT T.CityCode, T.CountryCode, T.Statename, T.StateCode FROM Database.dbo.City AS T);
|
I had deployed the bar file every time and infact emptied it and added the contents. I put a trace node, but of no use, it is not crossing the compute node. So I am not able to trace the exception from the compute node.
I would like to know if it requires reconnecting or recreating the connection to the database everytime we deploy.
I would like to know the reason for the exception, and why this peculiar behavior. I am surprised.
Thanks.
Regards,
Thripura Sundari |
|
| 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
|
|
|
|
