| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Broker, Oracle DB and UFT-8 |
« View previous topic :: View next topic » |
| Author |
Message
|
| mqmqmq |
 Posted: Tue Dec 30, 2008 10:50 pm Post subject: Broker, Oracle DB and UFT-8 |
 |
|
Novice
Joined: 02 Jul 2008
Posts: 22
|
Hi,
I have WebSphere MQ (version 6.0.2.5) running on Solaris 10. Broker (version 6.1.0.2) runs also on Solaris 10. Queue managers runs also on Solaris 10 and has CCSID as 1208 (utf8).
I have a Windows XP laptop where Message Broker Toolkit is installed (version 6.1.0). There is also rhfutilc -utility (version 6.0.0) and Oracle SQL Developer on that laptop.
Broker flow receives only xml files (via queue). Files should have been written in UTF-8 (is there a way to check it?). The following row is in every xml file:
| Code: |
| <?xml version="1.0" encoding="UTF-8" standalone="yes"?> |
Flow's job is to insert the data from those xml files (with little modification if needed) into Oracle DB. At this time the test xml files are put to queue by using rhfutilc or by bat -script which uses mqftsndc.exe.
Flow works ok, since the data is inserted into DB ok.
When I use SQL Developer to check the data, the special characters are not showing correctly. There are only squeres instead of special characters. I have tried to change the encoding in SQL Developer, but still no special characters visible. If I do the INSERT statement in SQL Developer and right after the SELECT statement, then the special characters are visible ok.
I have also used SQL Plus which runs on Solaris 10. When I do the SELECT statement the special characters are not shown at all. For example I receive "Mndag" instead of "MÃ¥ndag". When I do INSERT statement from SQL Plus and right after the I use SQL Developer again to check the data, then special characters are squeres (data which was inserted via SQL Plus).
I have also used the following SQL in Broker flow to insert the data in DB:
| Code: |
| INSERT INTO TABLE1(ID, DATA1) VALUES(1, 'MÃ¥ndag') |
Code works ok (I use PASSTHRU -method), but again when using SQL Developer to check the data, there are no special characters visible (or they are visible, but they are squeres:-().
So the questions:
Does the problems I have come only from the tools (SQL Developer etc.) I use? How on earth should I configure them?
Should I do some CAST -operations in Compute Node for the data before inserting the data in DB?
Should I do some bigger conversion for the whole data before inserting the data in DB? Ie. read as a blob, set OutputRoot.Propertis.CodedCharSetId etc, use ResetContentDescriptor and then insert the data in DB in Compute Node.
I heard that the default charset for Solaris is Posix C (basic 7bit ASCII), but it supports also a lot more. Does this cause trouble? Should the default charset point to utf8?
Should some environment variables in Solaris to be set to point to utf-8 charset? If so, which env var should I check?
Is there something that should be checked from Oracle DB side?
Is there a way to cast the data from DB column to certain CCSID? I tried the following, but without success:
| Code: |
| SELECT CAST(C1 AS CHAR(10) CCSID 1208) AS COL1 FROM TABLE1 |
Happy New Year anyway!!
BR,
--
Tim |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Wed Dec 31, 2008 3:02 am Post subject: Re: Broker, Oracle DB and UFT-8 |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| mqmqmq wrote: |
Hi,
I have WebSphere MQ (version 6.0.2.5) running on Solaris 10. Broker (version 6.1.0.2) runs also on Solaris 10. Queue managers runs also on Solaris 10 and has CCSID as 1208 (utf8).
I have a Windows XP laptop where Message Broker Toolkit is installed (version 6.1.0). There is also rhfutilc -utility (version 6.0.0) and Oracle SQL Developer on that laptop.
Broker flow receives only xml files (via queue). Files should have been written in UTF-8 (is there a way to check it?). The following row is in every xml file:
| Code: |
| <?xml version="1.0" encoding="UTF-8" standalone="yes"?> |
Flow's job is to insert the data from those xml files (with little modification if needed) into Oracle DB. At this time the test xml files are put to queue by using rhfutilc or by bat -script which uses mqftsndc.exe.
Flow works ok, since the data is inserted into DB ok. |
How do you know? Did you check the hex values to make sure for special characters?
| mqmqmq wrote: |
When I use SQL Developer to check the data, the special characters are not showing correctly. There are only squeres instead of special characters. I have tried to change the encoding in SQL Developer, but still no special characters visible. If I do the INSERT statement in SQL Developer and right after the SELECT statement, then the special characters are visible ok.
I have also used SQL Plus which runs on Solaris 10. When I do the SELECT statement the special characters are not shown at all. For example I receive "Mndag" instead of "MÃ¥ndag". When I do INSERT statement from SQL Plus and right after the I use SQL Developer again to check the data, then special characters are squeres (data which was inserted via SQL Plus).
I have also used the following SQL in Broker flow to insert the data in DB:
| Code: |
| INSERT INTO TABLE1(ID, DATA1) VALUES(1, 'MÃ¥ndag') |
Code works ok (I use PASSTHRU -method), but again when using SQL Developer to check the data, there are no special characters visible (or they are visible, but they are squeres:-().
So the questions:
Does the problems I have come only from the tools (SQL Developer etc.) I use? How on earth should I configure them? |
You need to configure the environment in which you run the tools to be UTF-8 compatible. Check for lang or langu as environment variable (check upper case as well). The OS must have a UTF-8 charset installed.
The shell the tool executes in must have the UTF-8 charset ACTIVE
| mqmqmq wrote: |
Should I do some CAST -operations in Compute Node for the data before inserting the data in DB?
Should I do some bigger conversion for the whole data before inserting the data in DB? Ie. read as a blob, set OutputRoot.Propertis.CodedCharSetId etc, use ResetContentDescriptor and then insert the data in DB in Compute Node.
I heard that the default charset for Solaris is Posix C (basic 7bit ASCII), but it supports also a lot more. Does this cause trouble? Should the default charset point to utf8? |
Yes see my comment above. Before changing anything to the data you insert check how it goes with the environment variable. Should this not suffice be aware that the internal representation for the broker is Unicode (ccsid=1200) and that you are looking for UTF-8 or ccsid 1208.
| mqmqmq wrote: |
| Should some environment variables in Solaris to be set to point to utf-8 charset? If so, which env var should I check? |
At OS level UTF-8 charset needs to be installed.
At shell level it needs to be active. Talk to your Unix specialist on how to do that. You might want to add the charset to mqsiprofile script or to the start script for the broker environment (think system startup).
| mqmqmq wrote: |
| Is there something that should be checked from Oracle DB side? |
Make sure the CCSID for the Oracle DB is set to UTF-8 .
| mqmqmq wrote: |
Is there a way to cast the data from DB column to certain CCSID? I tried the following, but without success:
| Code: |
| SELECT CAST(C1 AS CHAR(10) CCSID 1208) AS COL1 FROM TABLE1 |
Happy New Year anyway!!
BR,
--
Tim |
No for the Select you should be fine. You might want select as BLOB and then cast the result as CHAR from BLOB CCSID 1208.
Hope it answers most of your questions. Happy New year 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| mqmqmq |
| Posted: Wed Dec 31, 2008 3:13 am Post subject: |
|
|
Novice
Joined: 02 Jul 2008
Posts: 22
|
Hi,
Thanks fjb_saper for your answers! I will read them carefully with a cold drink tonight 
BR,
--
Tim |
|
| Back to top |
|
|
| mqmqmq |
| Posted: Wed Dec 31, 2008 3:22 am Post subject: |
|
|
Novice
Joined: 02 Jul 2008
Posts: 22
|
Hi again,
Forgot from previous post 
As fjb_saper wrote about the environment vars...
Would environment variables MQSI_LOCAL_CCSID and/or NLS_LANG do any good for this issue? I will check those out anyway when I'm back in business again.
Thanks!
BR,
--
Tim |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Dec 31, 2008 3:27 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| mqmqmq wrote: |
Hi again,
Forgot from previous post
As fjb_saper wrote about the environment vars...
Would environment variables MQSI_LOCAL_CCSID and/or NLS_LANG do any good for this issue? I will check those out anyway when I'm back in business again.
Thanks!
BR,
--
Tim |
For MQSI_LOCAL_CCSID you should probably check with IBM.
NLS_LANG I believe is a subset of LANG. So if you set LANG correctly NLS_LANG will be set correctly.
Typically LANG will govern a bunch of env charset related variables.
Setting LANG will then set all those in the background.
_________________
MQ & Broker admin |
|
| 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
|
|
|
|
