| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| [ODBC Oracle driver]String data, right truncated |
« View previous topic :: View next topic » |
| Author |
Message
|
| dipankar |
 Posted: Wed Oct 31, 2007 7:07 am Post subject: [ODBC Oracle driver]String data, right truncated |
 |
|
Disciple
Joined: 03 Feb 2005
Posts: 171
|
Hi All,
OS: AIX, WBI MB CSD 9, MQ 5.3, Oracle 8.1.7
This is production environment
In ACPT, we have same environment except Oracle 9.2.0.
My error handler is able to insert data into error table in ACPT. But in production, same error handler is giving the following error --
| Quote: |
| [DataDirect][ODBC Oracle driver]String data, right truncated. Error in parameter 8. |
I have checked parameter 8. Nothing is wrong with the value or size of that field.
I am able to take the following file trace of exceptionlist in PROD.
| Quote: |
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = 'INSERT INTO EAI.PF_WBI_LOGGING(MASTER_TRANSACTION_ID, MAJOR_TRANSACTION_ID, MINOR_TRANSACTION_ID, INTERFACE_ID, INTERFACE_NAME, MESSAGE_FLOW_NAME, EXCEPTIONTYPE, ERRORLOCATION, ERRORLINE, ERRORTEXT, WHYFAILED, INPUT_MSG_LOCATION, SOURCE_SYSTEM, TARGET_SYSTEM, NODE_NAME, NODE_TYPE, PUT_TIME, PUT_DATE, USERID, SOURCE_QUEUE, MSG_ID, PUT_APPL_NAME) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)'
)
(0x01000000):Insert = (
(0x03000000):Type = 5
(0x03000000):Text = ''175a4758-86bc-11dc-bb38-aae671ec0000', '175a4758-86bc-11dc-bb38-aae671ec0000', '175a4758-86bc-11dc-bb38-aae671ec0000', 'CMN_SDA_DIST', 'PF_COMMON_SDA_DISTRIBUTOR', 'OTHERS.PF_COMMON_SDA_DISTRIBUTOR_MF', 'UserException', 'OTHERS.PF_COMMON_SDA_DISTRIBUTOR_MF_DetermineDestination&SetControlEnvironment.Main->OTHERS.PF_COMMON_SDA_DISTRIBUTOR_MF_DetermineDestination&SetControlEnvironment.prcMsgRouter', '7->17', 'prcMsgRouter();->THROW USER EXCEPTION VALUES( 'COMN_SDA_DIST', 'The input message does not have any destination queue as per router table for Tcode ' || chrTCode);', 'User Generated SQL 'USER' exception COMN_SDA_DIST The input message does not have any destination queue as per router table for Tcode T309', 'QueueName: PF_AQ_SDA_DISTRIBUTOR_FAILURE', 'SDA', 'SAP', 'DetermineDestination&SetControlEnvironment', 'ComputeNode', '03:22:06', '2007-10-31', 'mqm', 'PF_AQ_HH_DELIVERYQUEUE', X'414d512050465f514d5f48485353315f47095c3c2083278f', '', '
) |
I noticed broker is introduced a comma (,) after the value of last field which is the root cause of exception. I don't know how comma is coming here. In ACPT or DEV, I have never faced this problem.
Could anyone tell me where the problem lies in? Your help will be highly appreciated.
_________________
Regards |
|
| Back to top |
|
 |
| Gaya3 |
| Posted: Wed Oct 31, 2007 7:14 am Post subject: |
|
|
Jedi
Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US
|
|
| Back to top |
|
|
| dipankar |
| Posted: Wed Oct 31, 2007 9:23 am Post subject: |
|
|
Disciple
Joined: 03 Feb 2005
Posts: 171
|
Hi Gaya,
I know the meaning of right truncated and I also wrote the following
| Quote: |
| I have checked parameter 8. Nothing is wrong with the value or size of that field. |
FYI, I have checked every field size & its value.
The most important thing is I am getting error only in PROD. With the same input message, I have tested in other environment, everything is working as expected.
The problem is with the last comma. But I don't know how it comes.
_________________
Regards |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Wed Oct 31, 2007 9:51 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
I think you are debugging the wrong problem.
Look at the trace of the SQL expression on the Oracle side.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Wed Oct 31, 2007 10:46 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi dipankar,
Where are you getting the value from, for the field 'PUT_APPL_NAME' in the SQL ?
Are you using an EVAL on this Insert query by any chance ?
Run a user trace to check the value for the PUT_APPL_NAME value, and also paste your snippet of code if possible.
Regards. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Oct 31, 2007 1:30 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Could it be that the number of fields to insert does not match the number of "?" in the statement?? 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| shalabh1976 |
| Posted: Wed Oct 31, 2007 5:35 pm Post subject: |
|
|
Partisan
Joined: 18 Jul 2002
Posts: 381
Location: Gurgaon, India
|
Dipankar,
In the link given by Gayathri I found that one of the possible reasons for the error can be:
| Quote: |
4. Oracle 8 Client connecting to an Oracle 9 Server
|
Could this be a reason for the problem?
_________________
Shalabh
IBM Cert. WMB V6.0
IBM Cert. MQ V5.3 App. Prog.
IBM Cert. DB2 9 DB Associate |
|
| Back to top |
|
|
| chrisc |
| Posted: Wed Oct 31, 2007 8:01 pm Post subject: |
|
|
Voyager
Joined: 19 Mar 2006
Posts: 77
|
This might be a bit left-field, but we have only just encountered a similar situation here...
When we tried to SELECT data from a DB2 database (just a plain old select) we got an error SQLSTATE=01004 which is basically the same thing (data right truncation). The fields in question were VARCHAR fields, one of 256 characters and two of 1024 characters.
If I cast the fields in question in the SELECT to VARCHAR(255) - and no longer - the select works fine, but anything longer and it breaks.
After reading some obscure docs we are coming to the conclusion that it may actually be a codepage issue, or possibly a bug, related to the fact that we have Unicode database tables in an EBCDIC database (DB2 v8 in compatibility mode). There may be an issue related to the fact the database cannot fit 256 Unicode characters into the same number of bytes as 256 EBCDIC characters, hence the truncation error.
It may be worth checking your Oracle database to see if the codepages are all set up correctly, as you may be looking at a similar problem...?
(Like I said, left-field suggestion but it might be worth a look.) |
|
| Back to top |
|
|
| AkankshA |
| Posted: Wed Oct 31, 2007 8:24 pm Post subject: |
|
|
Grand Master
Joined: 12 Jan 2006
Posts: 1494
Location: Singapore
|
i have faced this problem when i tried to connect oracle 9i client with oracle 8i Db.... and i suspect the compatibility otherwise too...
may be you would want to check once with the same version of C&S
_________________
Cheers |
|
| Back to top |
|
|
| dipankar |
| Posted: Thu Nov 01, 2007 1:49 am Post subject: |
|
|
Disciple
Joined: 03 Feb 2005
Posts: 171
|
Hi All,
Thank you all very much for your responses.
| Quote: |
Where are you getting the value from, for the field 'PUT_APPL_NAME' in the SQL ?
Are you using an EVAL on this Insert query by any chance ?
Run a user trace to check the value for the PUT_APPL_NAME value, and also paste your snippet of code if possible. |
Elvis -- I am getting value of 'PUT_APPL_NAME' from MQMD.PutApplName field. I am not using EVAL anywhere. User trace on PROD is not possible for me. The value of PUT_APPL_NAME is blank in MQMD header.
The code is simple INSERT statement -
| Code: |
| INSERT INTO Database.EAI.PF_WBI_LOGGING (MASTER_TRANSACTION_ID,MAJOR_TRANSACTION_ID, MINOR_TRANSACTION_ID, INTERFACE_ID, INTERFACE_NAME, MESSAGE_FLOW_NAME, EXCEPTIONTYPE, ERRORLOCATION, ERRORLINE, ERRORTEXT, WHYFAILED, INPUT_MSG_LOCATION, SOURCE_SYSTEM, TARGET_SYSTEM, NODE_NAME, NODE_TYPE, PUT_TIME, PUT_DATE, USERID, SOURCE_QUEUE, MSG_ID, PUT_APPL_NAME) VALUES (chrMasterTransactionId, chrMajorTransactionId, chrMinorTransactionId, chrInterfaceId, chrInterfaceName, chrMsgFlowName, chrExceptType, chrErrLocation, chrLine, chrText, chrWhyFailed, chrWhereInputMsg, chrSourceSystem, chrTargetSystem, chrNodeName, chrNodeType, chrPutTime, chrPutDate, chrUserID, chrSourceQueue, blbMsgID, chrPutApplName); |
| Quote: |
| Could it be that the number of fields to insert does not match the number of "?" in the statement?? |
No
I don't know which Oracle Client is being used in PROD. I will update you once I get this information from client.
Jeff -- I will ask client to take SQL trace in Oracle if possible. Thanks.
_________________
Regards |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Nov 01, 2007 2:29 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Don't discard Chrisc's post about CCSID. He might be on to something there....
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| dipankar |
| Posted: Thu Nov 01, 2007 6:03 am Post subject: |
|
|
Disciple
Joined: 03 Feb 2005
Posts: 171
|
Hi All,
I was just informed by client that Oracle client 8.1.7 & Oracle server 9.2.0 is being used on PROD.
I believe this is the main cause of failure. I am now going to ask client for using Oracle client 9.2.0.
_________________
Regards |
|
| 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
|
|
|
|
