How to read oracle database contain Chinese Character ?

[Former Member]

Hi,

I have scenario that i have to query from oracle database using jdbc adapter. The problem is there are some chinese character in site the table.

How can i query those Chinese Character because i can't find any codepage setting in sender / receiver adapter. Please advise.

Thank You and Best Regards

Fernand

[Former Member]

Hi Stefan

Thank you for your opinion,

But then i can't say that is client problem.

I have successfully implemented this code below to convert back to the right chinese character.

// Create the encoder and decoder for the character encoding

String testString = "Juice, Orange (Mr Juicy) a÷﹐EaG￥A￥y￥I?i￥A 2.35L";

Charset charset = Charset.forName("big5");

CharsetDecoder decoder = charset.newDecoder();

Charset charset1 = Charset.forName("windows-1252");

CharsetEncoder encoder = charset1.newEncoder();

a = testString;

String b = new String();

try {

ByteBuffer bbuf = encoder.encode(CharBuffer.wrap(a));

CharBuffer cbuf = decoder.decode(bbuf);

a = cbuf.toString();

} catch (CharacterCodingException cce) {

String errorMessage = "Exception during character encoding/decoding: " + cce.getMessage();

}

return a;

All the chinese character have been encode correctly. Please give me feedback or comment on the code.

Best Regards

Fernand

[Former Member]

Sorry the expected result is :

Juice, Orange (Mr Juicy) 金裝果汁先生橙汁 2.35L

Thank You and Best Regards

Fernand

[Former Member]

Hi Stefan,

After i have comfirmed again with the user actually, the database is not UTF-8

NLS_CHARACTERSET = WE8MSWIN1252

NLS_NCHAR_CHARACTERSET = AL16UTF16

i have no idea how to convert, could you advise any sample UDF to convert ?

Thank You and Best Regards

Fernand

[stefan_grube]

WIN1252 is western Europe and does not contain any chinese characters.

I think you have chinese characters in a database, that does not support this. Therefore all entries in the database are considered as 1 byte characters.

In my opinion this is a data base issue and you should not try to solve the database issue with PI. Fix the issue in the database itself.

If you want to store chinese characters in that database, you have to set it to unicode (UTF-8, UTF-16)

Regards

Stefan

[Former Member]

Hi Stefan,

This is the xml message from integration server :

How can i get the same result for JDBC adapter ? Please advise.

Thank you and Best Regards

Fernand

[stefan_grube]

It seems to be GB18030

http://en.wikipedia.org/wiki/GB_18030

BIG5 is not used in China, only in Taiwan.

You could use a UDF for tranforming the characters to UTF-8, but I think you sould ask Oracle for help in this scenario. The JDBC drives always should provide UTF-8, as this is Java standard.

Regards

Stefan

[stefan_grube]

Hi Fernand,

One thing came into my mind: Wha should there be GB18030 characters in a UTF-8 database?

I think the issue is that you have wrong string entries in the database. Could you browse the database entries and confirm, that the entries are correct?

Regards

Stefan

[Former Member]

Hi XI expert,

Please advise, if you have got similar scenario. i am still cannot read correctly those all the chinese character from oracle database.

Thank you and Best Regards

Fernand

[stefan_grube]

As your databse seems to be unicode, could you explain, how the chinese characters are presented?

What is encoding of XMl inside XI, how do the characters look like?

What about file?

[Former Member]

Hi Stefan,

Thank you for your information, but then i couldn't found any related information how to add additional parameter for unicode in oracle url "jdbc:oracle:thin:@201.100.0.33:1521:PI70".

i have tried developm simple jsp page to display the same data from the same table in oracle the jsp program working correctly. i can see the correct chinese character in web browser, but why XI jdbc adapter cannot do ? because both using the same oracle driver, url ?

Do you have any idea ?

Hi Ying-Jie Chen,

This is my oracle server database setting :

NLS_CHARACTERSET = AL32UTF8

NLS_NCHAR_CHARACTERSET = AL16UTF16

Please advise base on your experience how to handle this situation so i can query the correct chinese character ? i have tried successfully reading chinese character from text file (set code page = Big5 in sender file adapter) . but still failed for JDBC adapter.

Thank You and Best Regards

Fernand

[Former Member]

Hi Fernand,

What is the database characterset used in your backend Oracle database ?

You may ask your DBA to check the following two DB parameters to figure out :

NLS_CHARACTERSET

NLS_NCHAR_CHARACTERSET

Oracle SQLPlus is a C-based tool dependent on OS-level CLI library, where you

can control how characterset conversion occures between Oracle client and server by

setting OS environment NLS_LANG to an appropriate value.

XI JDBC adapter used type-4 Oracle JDBC driver which is pure Java-based and pure unicode

bypassing CLI library and effects imposed by NLS_LANG.

Therefore the end results from both tools may differ.

One case when the results will differ is as following :

DB characterset used is "WE8DEC", ie, ISO-8859-1. Now both trad. Chinese and simplified Chinese

text can still be inserted into tables in a byte-by-byte fashion.

Queries containing Chinese in SQLPlus may still be displayed correctly if the NLS_LANG env variable

is set appropriately and the OS supports.

However in this same case, the queries run with JDBC driver will not yield correct Chinese text

since WE8DEC --> Unicode conversion will corrupt either trad. or simplified Chinese encoding.

FYI,

Ying-Jie Chen

[stefan_grube]

From SAP note 831162:

"2. Unicode Handling

Q: I am inserting Unicode data into a database table or selecting Unicode data from a table. However, the data inserted into or retrieved from the table appears garbled. Why doesn't the JDBC Adapter handle Unicode correctly?

A: While the JDBC Adapter is Unicode-aware, many JDBC drivers and/or database management systems aren't by default and need a codepage or Unicode-awareness to be configured explicitly. For the respective JDBC drivers, this codepage setting is often configured via the driver URL. For details, refer to the documentation of your JDBC driver or database management system."

Does this help?

Stefan

[Former Member]

Hi Prateek,

I have tried the scenario, but the sender jdbc adapter couln't read correctly the chinese character. is there any others solution ? When i was query the table directly through sql plus all those chinese character can display correctly but not from xi jdbc adapter.

Thank you and Best Regards

Fernand

[prateek]

Have you tried running the sceanario directly? XI JDBC Adapter understands Unicode. So there is no additional need to include some codepage detail. There are certain JDBC drivers for which codepage settings may be required. In that case, the setting must be added as part of driver URL.

Regards,

Prateek