| Author |
Message
|
| Roothless |
 Posted: Fri Dec 18, 2009 3:36 am Post subject: CodedCharSet issue in Broker 6.1.0.3 |
 |
|
Apprentice
Joined: 11 Dec 2006
Posts: 26
|
Hi All,
when we retrieve data from Oracle 9i.2 , we get data plus unicode square character between each character.
E.x c(unicode square)u(unicode square)s(unicode square)t(unicode square)o(unicode square)m(unicode square)e(unicode square)r(unicode square)
In compute node WMB 6.1.0.3 i use passthru statement to execute the query. I tried direct query without PASSTHRU it didnt work with oracle DB.
I tried CodedCharSet and Encoding value not working.
Please help me.
Thanks
_________________
MQ Toddler |
|
| Back to top |
|
 |
| kimbert |
| Posted: Fri Dec 18, 2009 3:42 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5543
Location: Southampton
|
|
| Back to top |
|
|
| Roothless |
| Posted: Fri Dec 18, 2009 4:50 am Post subject: |
|
|
Apprentice
Joined: 11 Dec 2006
Posts: 26
|
hi, thanks for the link
i am fetching the data records from Oracle DB.
the problem is , each character is followed by empty space like<Data>A B C D</Data>, but the actual data should be<Data>ABCD</Data>.
i tried constructing message with CodedCharSetid and Encoding values , but no use.
down the flow i use route to label for routing,but it fails as i have space character between actual character.
ORacle db uses ccsid AL16UTF16
Is there any i can convert the received Oracle Data to normal Data using ESQL.
thanks
_________________
MQ Toddler |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Fri Dec 18, 2009 2:39 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20768
Location: LI,NY
|
| Roothless wrote: |
hi, thanks for the link
i am fetching the data records from Oracle DB.
the problem is , each character is followed by empty space like<Data>A B C D</Data>, but the actual data should be<Data>ABCD</Data>.
i tried constructing message with CodedCharSetid and Encoding values , but no use.
down the flow i use route to label for routing,but it fails as i have space character between actual character.
ORacle db uses ccsid AL16UTF16
Is there any i can convert the received Oracle Data to normal Data using ESQL.
thanks |
Should be relatively easy. Show us how you retrieve the data from Oracle... and assign it to the label... 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| Roothless |
| Posted: Sat Dec 19, 2009 3:50 am Post subject: |
|
|
Apprentice
Joined: 11 Dec 2006
Posts: 26
|
SET OutputRoot.XMLNS.Data.Events[] = PASSTHRU('SELECT TO_CHAR(EVENT_ID) EVENT_ID, CONNECTOR_ID, OBJECT_KEY, OBJECT_NAME,OBJECT_VERB, EVENT_PRIORITY,EVENT_TIME, EVENT_STATUS
FROM XWORLDS_EVENTS WHERE EVENT_STATUS=1');
or
SET Environment.Variables.Data.Events[] =
PASSTHRU('SELECT TO_CHAR(EVENT_ID) EVENT_ID, CONNECTOR_ID, OBJECT_KEY, OBJECT_NAME,OBJECT_VERB, EVENT_PRIORITY,EVENT_TIME, EVENT_STATUS
FROM XWORLDS_EVENTS WHERE EVENT_STATUS=1');
I tried withour passthru statement , but it created someother issue.
Thanks
_________________
MQ Toddler |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Sat Dec 19, 2009 5:49 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20768
Location: LI,NY
|
So, now we see how you retrieve the data from Oracle. How do you assign it to the label?
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| Roothless |
| Posted: Sat Dec 19, 2009 9:00 am Post subject: |
|
|
Apprentice
Joined: 11 Dec 2006
Posts: 26
|
Hi,
using PROPAGATE statement , i will get list of messages and send it queue.
Then i read from the queue each message and set
SET OutputLocalEnvironment.Destination.RouterList.DestinationData[1].labelName
=InputRoot.XMLNS.Data.Event.EVENT_ID;
As the message containes oracle ccsid , EVENT_ID , i.e 5678889 instead it contains 5 6 7 8 8 8 9(space between characters).
Thanks
_________________
MQ Toddler |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Sat Dec 19, 2009 2:25 pm Post subject: Re: CodedCharSet issue in Broker 6.1.0.3 |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| Roothless wrote: |
| Oracle db uses ccsid AL16UTF16 |
For Oracle, that would be called an 'NLS_CHARACTERSET', not a 'ccsid'.
The broker ODBC drivers do not support AL16UTF16; consult the documentation for how to set up the Oracle database and ODBC driver for Unicode support.
Failing that, there might be a few workarounds:
| Roothless wrote: |
| Code: |
| SET OutputRoot.XMLNS.Data.Events[] = PASSTHRU('SELECT TO_CHAR(EVENT_ID) EVENT_ID, ... |
|
The PASSTHRU statement is converting Oracle EVENT_ID to character. What is the original datatype? Perhaps it could be passed directly, and then the broker can convert it instead.
Or, as a last resort, it might be possible to uncorrupt the string on the broker side, with something like:
| Code: |
DECLARE b BLOB;
SET b = CAST(InputRoot.XMLNS.Data.Event.EVENT_ID AS BLOB CCSID 819);
SET OutputLocalEnvironment.Destination.RouterList.DestinationData[1].labelName
= CAST(b AS CHARACTER CCSID 1200); -- 1200 for UTF-16BE, or 1202 for UTF16-LE |
|
|
| Back to top |
|
|
| Roothless |
| Posted: Mon Dec 21, 2009 1:07 am Post subject: |
|
|
Apprentice
Joined: 11 Dec 2006
Posts: 26
|
Issue resolved. Thanks for all.
The problem was with Driver i used. Here goes the fullstory. My OS is windows 2008 64 bits. But WMB doesn't have MQ Datadirect ODBC driver for 64 bits for windows, only 32-bits. So i used Oracle driver available with Oracle 10g. That created whole lot of mess.
Use only MQSeries Datadirect drivers. If u r using windows 32-bits no problem at all. For 64 bits run C:\WINDOWS\SysWOW64\odbcad32.exe. Thats it.
_________________
MQ Toddler |
|
| Back to top |
|
|
|
|
