This discussion is archived
8 Replies Latest reply: Aug 23, 2012 5:18 PM by 744361 RSS

Inserting broken pipe character in DB from Java creating problem

744361 Newbie
Currently Being Moderated
Hello experts,

We want to insert a broker pipe character in oracle 10g from java.
But it get changed to pipe character.

We are having japanese client for which we want to maintain this kind of character.

If someone know the solution, please update.
  • 1. Re: Inserting broken pipe character in DB from Java creating problem
    orafad Oracle ACE
    Currently Being Moderated
    Define 'broken pipe' character.

    Explain "get changed".

    What version (10g is a label, no version) of Database server?

    Database character sets? (from nls_database_parameters view)

    How are you inserting the character from java?
  • 2. Re: Inserting broken pipe character in DB from Java creating problem
    744361 Newbie
    Currently Being Moderated
    Thank you for quick reply orafad.


    We are inserting the broken pipe (¦) character in a table-column of type varchar2 in oracle (10g version:10.1.0.5.0 ) using

    java.sql.Statement in java (Java version : 1.5.0.6 and JDBC Driver: ojdbc14.2.jar). But when it's inserted in database it gets changed into normal pipe character (|).


    Following are the NLS settings:

    PARAMETER     :     VALUE
    NLS_LANGUAGE     :     JAPANESE
    NLS_TERRITORY     :     JAPAN
    NLS_CURRENCY     :     Y
    NLS_ISO_CURRENCY     :     JAPAN
    NLS_NUMERIC_CHARACTERS     :     .,
    NLS_CHARACTERSET     :     JA16SJIS
    NLS_CALENDAR     :     GREGORIAN
    NLS_DATE_FORMAT     :     DD-MON-RRRR
    NLS_DATE_LANGUAGE     :     AMERICAN
    NLS_SORT     :     BINARY
    NLS_TIME_FORMAT     :     HH24:MI:SSXFF
    NLS_TIMESTAMP_FORMAT     :     RR-MM-DD HH24:MI:SSXFF
    NLS_TIME_TZ_FORMAT     :     HH24:MI:SSXFF TZR
    NLS_TIMESTAMP_TZ_FORMAT     :     RR-MM-DD HH24:MI:SSXFF TZR
    NLS_DUAL_CURRENCY     :     \
    NLS_COMP     :     BINARY
    NLS_LENGTH_SEMANTICS     :     BYTE
    NLS_NCHAR_CONV_EXCP     :     FALSE
    NLS_NCHAR_CHARACTERSET     :     AL16UTF16
    NLS_RDBMS_VERSION     :     10.1.0.5.0
  • 3. Re: Inserting broken pipe character in DB from Java creating problem
    Sergiusz Wolicki (Oracle) Expert
    Currently Being Moderated
    Please, post the code fragment you use to insert this character. By "broken pipe" character, I guess you mean "U+FFE4 Full Width Broken Bar", which has code 0xFA55 in JA16SJIS.
    A probable cause of the issue is that you use a text literal but you do not specify the proper encoding when compiling the Java source. Instead of using the character directly, try its Unicode escape "\uffe4".


    -- Sergiusz
  • 4. Re: Inserting broken pipe character in DB from Java creating problem
    744361 Newbie
    Currently Being Moderated
    Hi,

    I understand your explanation.

    It is not possible to put actual code, that's complex and coupled with other things.
    I like to share the scenario where it failing,

    1. Broken Pipe character(double byte (\u00fa\u0055)) is put along with other japanese long text in a *.txt file.
    2. A java file reads the above *.txt file and inserts the file text in a table column in oracle.

    The .txt file is saved in Shift_JIS encoding.
    In java file, the same *.txt file is read using 'CP943C' charset and written into database.

    So as per your suggestion I have to compare literal first and than if I found broken bar in file, I need to change it with specified unicode.
    Is that so?

    --Saurabh.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
  • 5. Re: Inserting broken pipe character in DB from Java creating problem
    Sergiusz Wolicki (Oracle) Expert
    Currently Being Moderated
    As far as I can tell, if you read the Shift_JIS file using the CP943C encoding, then the full width broken bar character will become the normal (half) width broken bar character U+00A6. But as this character is not represented in JA16SJIS, it gets converted to the replacement character U+007C Vertical Line aka "pipe character". To read the Shift_JIS file preserving the full width broken bar U+FFE4, use the MS932 encoding instead of CP943C.


    -- Sergiusz
  • 6. Re: Inserting broken pipe character in DB from Java creating problem
    744361 Newbie
    Currently Being Moderated
    Earlier we have used the same encoding MS932 for reading and writing files & writing data into database.
    But in that case full width tilde character (~) is changed into question mark ? character, and to solve this we changed encoding from MS932 to CP943C.

    Now, if we will revert it back, we will reach into previous position.
    Anyway, if there is any common encoding which can support number of characters together that only can help us in Japanese.

    Thanks for valuable comments.
  • 7. Re: Inserting broken pipe character in DB from Java creating problem
    Sergiusz Wolicki (Oracle) Expert
    Currently Being Moderated
    For the correct support of the tilde character 0x8160 (U+FF5E), use JA16SJISTILDE for the database character set. It differs from JA16SJIS exactly by this single character. So, use MS932 and JA16SJISTILDE.

    You can also go the modern way and use AL32UTF8 for the database character set. Then, you can have a single database for all languages. not just for Japanese. This is the recommended character set for all new deployments, even if it uses more space for Japanese than the dedicated JA16SJIS(TILDE).

    -- Sergiusz
  • 8. Re: Inserting broken pipe character in DB from Java creating problem
    744361 Newbie
    Currently Being Moderated
    Thank you for your help.

    Your suggestions will be helpful for future implementation.
    As currently to change character set in production is very risky.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points