FAQ

How to insert Chinese (big5) through JDBC?

Eric Tan
Jul 30, 2003 at 7:15 am
Hi all,
I encounter problem when inserting Chinese.
These are my setting:
- postgresql 7.3 (database encoding: "SQL_ASCII")
- JDBC: pg73jdbc2.jar
- Tomcat 4.1.24
- JSP/Servlet/Java Bean
insert into table1 values('¤@¤G¤T') // chinese cannot display here...
Insert Chinese words are ok. But when I select the record, there were wrong sth. like '? ?³æ '
The sql statement is in big5 format because I write it in a log file.
I've tried using charset like this. Not knowing why. All fail...
jdbc:postgresql://202.71.201.252:5432/CA_care?charSet=ISO-8859-1
jdbc:postgresql://202.71.201.252:5432/CA_care?charSet=SQL_ASCII
jdbc:postgresql://202.71.201.252:5432/CA_care?charSet=UTF-8
jdbc:postgresql://202.71.201.252:5432/CA_care?charSet=Big5

Any idea.
Best Regards,
Eric Tan

最新鈴聲推介:十面埋伏,多謝失戀,心淡...
http://ringtone.yahoo.com.hk
reply

Search Discussions

9 responses

  • Kris Jurka at Jul 30, 2003 at 11:41 am

    On Wed, 30 Jul 2003, [big5] Eric Tan wrote:

    Hi all,
    I encounter problem when inserting Chinese.
    These are my setting:
    - postgresql 7.3 (database encoding: "SQL_ASCII")
    - JDBC: pg73jdbc2.jar
    - Tomcat 4.1.24
    - JSP/Servlet/Java Bean
    insert into table1 values('¤@¤G¤T') // chinese cannot display here...
    Insert Chinese words are ok. But when I select the record, there were wrong sth. like '? ?³æ '
    The sql statement is in big5 format because I write it in a log file.
    I've tried using charset like this. Not knowing why. All fail...
    jdbc:postgresql://202.71.201.252:5432/CA_care?charSet=ISO-8859-1
    jdbc:postgresql://202.71.201.252:5432/CA_care?charSet=SQL_ASCII
    jdbc:postgresql://202.71.201.252:5432/CA_care?charSet=UTF-8
    jdbc:postgresql://202.71.201.252:5432/CA_care?charSet=Big5

    Any idea.
    Best Regards,
    Eric Tan
    You cannot store Big5 data in a SQL_ASCII encoded database. Consider
    using UNICODE. Also the ?charset=xxx argument in the database URL is no
    longer valid in the Postgresql JDBC drivers starting with 7.3.

    Kris Jurka
  • Eric Tan at Jul 31, 2003 at 1:05 am
    Hi Kris Jurka,

    When initializing the data, I can move the big5 data from MSSQL7 and insert into the postgresql database through VB. Why?

    Another case: I write the insert statement to the debug file. I can read the Chinese there. If I copy and paste in "putty", I can also insert those Chinese correctly.

    Thus, I guess the problem is inserting through JDBC.

    Best Regards,
    Eric Tan

    Kris Jurka wrote:

    On Wed, 30 Jul 2003, [big5] Eric Tan wrote:

    Hi all,
    I encounter problem when inserting Chinese.
    These are my setting:
    - postgresql 7.3 (database encoding: "SQL_ASCII")
    - JDBC: pg73jdbc2.jar
    - Tomcat 4.1.24
    - JSP/Servlet/Java Bean
    insert into table1 values('¤@¤G¤T') // chinese cannot display here...
    Insert Chinese words are ok. But when I select the record, there were wrong sth. like '? ?³æ '
    The sql statement is in big5 format because I write it in a log file.
    I've tried using charset like this. Not knowing why. All fail...
    jdbc:postgresql://202.71.201.252:5432/CA_care?charSet=ISO-8859-1
    jdbc:postgresql://202.71.201.252:5432/CA_care?charSet=SQL_ASCII
    jdbc:postgresql://202.71.201.252:5432/CA_care?charSet=UTF-8
    jdbc:postgresql://202.71.201.252:5432/CA_care?charSet=Big5

    Any idea.
    Best Regards,
    Eric Tan
    You cannot store Big5 data in a SQL_ASCII encoded database. Consider
    using UNICODE. Also the ?charset=xxx argument in the database URL is no
    longer valid in the Postgresql JDBC drivers starting with 7.3.

    Kris Jurka


    最新鈴聲推介:十面埋伏,多謝失戀,心淡...
    http://ringtone.yahoo.com.hk
  • Eric Tan at Jul 31, 2003 at 7:26 am
    Hi,
    I can now insert Chinese!!
    I'm now using EUC_TW as the database encoding.
    However, the default client encoding is now EUC_TW. (that's why I cannot insert Chinese). I have to exe "set client_encoding to 'Big5';" when inserting and selecting.

    Can I set Big5 as the default client_encoding ?

    Best Regards,
    Eric Tan

    Eric Tan wrote:
    Hi Kris Jurka,

    When initializing the data, I can move the big5 data from MSSQL7 and insert into the postgresql database through VB. Why?

    Another case: I write the insert statement to the debug file. I can read the Chinese there. If I copy and paste in "putty", I can also insert those Chinese correctly.

    Thus, I guess the problem is inserting through JDBC.

    Best Regards,
    Eric Tan

    Kris Jurka wrote:

    On Wed, 30 Jul 2003, [big5] Eric Tan wrote:

    Hi all,
    I encounter problem when inserting Chinese.
    These are my setting:
    - postgresql 7.3 (database encoding: "SQL_ASCII")
    - JDBC: pg73jdbc2.jar
    - Tomcat 4.1.24
    - JSP/Servlet/Java Bean
    insert into table1 values('¤@¤G¤T') // chinese cannot display here...
    Insert Chinese words are ok. But when I select the record, there were wrong sth. like '? ?³æ '
    The sql statement is in big5 format because I write it in a log file.
    I've tried using charset like this. Not knowing why. All fail...
    jdbc:postgresql://202.71.201.252:5432/CA_care?charSet=ISO-8859-1
    jdbc:postgresql://202.71.201.252:5432/CA_care?charSet=SQL_ASCII
    jdbc:postgresql://202.71.201.252:5432/CA_care?charSet=UTF-8
    jdbc:postgresql://202.71.201.252:5432/CA_care?charSet=Big5

    Any idea.
    Best Regards,
    Eric Tan
    You cannot store Big5 data in a SQL_ASCII encoded database. Consider
    using UNICODE. Also the ?charset=xxx argument in the database URL is no
    longer valid in the Postgresql JDBC drivers starting with 7.3.

    Kris Jurka


    最新鈴聲推介:十面埋伏,多謝失戀,心淡...
    http://ringtone.yahoo.com.hk



    最新鈴聲推介:十面埋伏,多謝失戀,心淡...
    http://ringtone.yahoo.com.hk
  • Kris Jurka at Jul 31, 2003 at 8:11 am

    On Thu, 31 Jul 2003, [big5] Eric Tan wrote:

    Hi,
    I can now insert Chinese!!
    I'm now using EUC_TW as the database encoding.
    However, the default client encoding is now EUC_TW. (that's why I
    cannot insert Chinese). I have to exe "set client_encoding to 'Big5';"
    when inserting and selecting.

    Can I set Big5 as the default client_encoding ?
    You can make it the default for the database or for the user you are
    connecting as. Try running

    ALTER DATABASE <databasename> SET client_enconding TO big5;

    or

    ALTER USER <username> SET client_encoding TO big5;

    Kris Jurka
  • Eric Tan at Jul 31, 2003 at 9:53 am
    The command only work in command prompt - putty":
    ALTER DATABASE "CA_care" SET client_enconding TO 'big5';

    But it fail when inserting from jsp & VB.
    I have to run this code in all program which may access database.
    pgsql.execute("set client_encoding to 'Big5';");

    Any other solution?

    Kris Jurka wrote:

    On Thu, 31 Jul 2003, [big5] Eric Tan wrote:

    Hi,
    I can now insert Chinese!!
    I'm now using EUC_TW as the database encoding.
    However, the default client encoding is now EUC_TW. (that's why I
    cannot insert Chinese). I have to exe "set client_encoding to 'Big5';"
    when inserting and selecting.

    Can I set Big5 as the default client_encoding ?
    You can make it the default for the database or for the user you are
    connecting as. Try running

    ALTER DATABASE SET client_enconding TO big5;

    or

    ALTER USER SET client_encoding TO big5;

    Kris Jurka



    ---------------------------(end of broadcast)---------------------------
    TIP 9: the planner will ignore your desire to choose an index scan if your
    joining column's datatypes do not match

    最新鈴聲推介:十面埋伏,多謝失戀,心淡...
    http://ringtone.yahoo.com.hk
  • Kris Jurka at Jul 31, 2003 at 10:39 am

    On Thu, 31 Jul 2003, [big5] Eric Tan wrote:

    The command only work in command prompt - putty":
    ALTER DATABASE "CA_care" SET client_enconding TO 'big5';

    But it fail when inserting from jsp & VB.
    I have to run this code in all program which may access database.
    pgsql.execute("set client_encoding to 'Big5';");

    Any other solution?
    The idea is that you run the ALTER DATABASE command once and then every
    time you connect it will already be set, so you do not need to make that
    call in your client. Notice the second time I connect the client_encoding
    is already set.

    jurka@doug:~$ psql tantest
    Welcome to psql 7.3.3, the PostgreSQL interactive terminal.

    Type: \copyright for distribution terms
    \h for help with SQL commands
    \? for help on internal slash commands
    \g or terminate with semicolon to execute query
    \q to quit

    tantest=# show client_encoding;
    client_encoding
    -----------------
    SQL_ASCII
    (1 row)

    tantest=# alter database tantest set client_encoding to big5;
    ALTER DATABASE
    tantest=# \q
    jurka@doug:~$ psql tantest
    Welcome to psql 7.3.3, the PostgreSQL interactive terminal.

    Type: \copyright for distribution terms
    \h for help with SQL commands
    \? for help on internal slash commands
    \g or terminate with semicolon to execute query
    \q to quit

    tantest=# show client_encoding;
    client_encoding
    -----------------
    big5
    (1 row)



    To check if you have correctly run the ALTER DATABASE statement, check

    SELECT datconfig FROM pg_database WHERE datname='<dbname>';

    You should see
    tantest=# select datconfig FROM pg_database where datname='tantest';
    datconfig
    ------------------------
    {client_encoding=big5}
    (1 row)


    Kris Jurka
  • Eric Tan at Aug 1, 2003 at 1:20 am
    I guess it was. I doesn't.

    CA_care=# SELECT datconfig FROM pg_database WHERE datname='CA_care';
    datconfig
    ------------------------
    {client_encoding=Big5}

    CA_care=# \encoding
    EUC_TW

    When I select fields with Chinese content in jsp, it cannot display correctly, unless I add this:
    pgsql.execute("set client_encoding to 'Big5';");
    and get it
    pgsql.getChinese2("receivedfrom")

    ** "pgsql" is the java bean handling resultset

    Best Regards,
    Eric Tan

    Kris Jurka wrote:

    On Thu, 31 Jul 2003, [big5] Eric Tan wrote:

    The command only work in command prompt - putty":
    ALTER DATABASE "CA_care" SET client_enconding TO 'big5';

    But it fail when inserting from jsp & VB.
    I have to run this code in all program which may access database.
    pgsql.execute("set client_encoding to 'Big5';");

    Any other solution?
    The idea is that you run the ALTER DATABASE command once and then every
    time you connect it will already be set, so you do not need to make that
    call in your client. Notice the second time I connect the client_encoding
    is already set.

    jurka@doug:~$ psql tantest
    Welcome to psql 7.3.3, the PostgreSQL interactive terminal.

    Type: \copyright for distribution terms
    \h for help with SQL commands
    \? for help on internal slash commands
    \g or terminate with semicolon to execute query
    \q to quit

    tantest=# show client_encoding;
    client_encoding
    -----------------
    SQL_ASCII
    (1 row)

    tantest=# alter database tantest set client_encoding to big5;
    ALTER DATABASE
    tantest=# \q
    jurka@doug:~$ psql tantest
    Welcome to psql 7.3.3, the PostgreSQL interactive terminal.

    Type: \copyright for distribution terms
    \h for help with SQL commands
    \? for help on internal slash commands
    \g or terminate with semicolon to execute query
    \q to quit

    tantest=# show client_encoding;
    client_encoding
    -----------------
    big5
    (1 row)



    To check if you have correctly run the ALTER DATABASE statement, check

    SELECT datconfig FROM pg_database WHERE datname='';

    You should see
    tantest=# select datconfig FROM pg_database where datname='tantest';
    datconfig
    ------------------------
    {client_encoding=big5}
    (1 row)


    Kris Jurka



    最新鈴聲推介:十面埋伏,多謝失戀,心淡...
    http://ringtone.yahoo.com.hk
  • Kris Jurka at Aug 2, 2003 at 10:38 am

    On Fri, 1 Aug 2003, [big5] Eric Tan wrote:

    I guess it was. I doesn't.

    CA_care=# SELECT datconfig FROM pg_database WHERE datname='CA_care';
    datconfig
    ------------------------
    {client_encoding=Big5}

    CA_care=# \encoding
    EUC_TW
    When I select fields with Chinese content in jsp, it cannot display
    correctly, unless I add this: pgsql.execute("set client_encoding to
    'Big5';"); and get it pgsql.getChinese2("receivedfrom")
    Yes, I apologize for having steered you wrong. After some further
    investigation, the approach I mentioned will work for other clients, but
    not for JDBC. The postgresql JDBC driver does a SET client_encoding =
    'UNICODE' when it first starts up which overwrites any default value
    assigned to a user or database. The paradigm it seems to be working with
    is that anything Java based should be working with Unicode data only, and
    it will let the backend do the encoding conversion between Unicode and the
    server encoding. This means you need to alter your code to convert from
    Big5 to Unicode to work with this system although I'm not sure what the
    best way to do this is.

    You mentioned that this was a jsp application, what is the default
    character encoding for the application server? Does adjusting that help?

    Kris Jurka
  • Eric Tan at Aug 4, 2003 at 12:59 am
    Thank you very much. Kris Jurka.
    I'm now understand !

    I will change the connection bean. Add the line required there.

    You mentioned client_encoding works for some clients. What are they?

    Or can I change JDBC driver ? Where is the "set client_encoding" in JDBC ?

    Best Regards,
    neltan

    Kris Jurka wrote:

    On Fri, 1 Aug 2003, [big5] Eric Tan wrote:

    I guess it was. I doesn't.

    CA_care=# SELECT datconfig FROM pg_database WHERE datname='CA_care';
    datconfig
    ------------------------
    {client_encoding=Big5}

    CA_care=# \encoding
    EUC_TW
    When I select fields with Chinese content in jsp, it cannot display
    correctly, unless I add this: pgsql.execute("set client_encoding to
    'Big5';"); and get it pgsql.getChinese2("receivedfrom")
    Yes, I apologize for having steered you wrong. After some further
    investigation, the approach I mentioned will work for other clients, but
    not for JDBC. The postgresql JDBC driver does a SET client_encoding =
    'UNICODE' when it first starts up which overwrites any default value
    assigned to a user or database. The paradigm it seems to be working with
    is that anything Java based should be working with Unicode data only, and
    it will let the backend do the encoding conversion between Unicode and the
    server encoding. This means you need to alter your code to convert from
    Big5 to Unicode to work with this system although I'm not sure what the
    best way to do this is.

    You mentioned that this was a jsp application, what is the default
    character encoding for the application server? Does adjusting that help?

    Kris Jurka



    ---------------------------(end of broadcast)---------------------------
    TIP 9: the planner will ignore your desire to choose an index scan if your
    joining column's datatypes do not match

    最新鈴聲推介:十面埋伏,多謝失戀,心淡...
    http://ringtone.yahoo.com.hk

Related Discussions

Discussion Navigation
viewthread | post

2 users in discussion

Eric Tan: 6 posts Kris Jurka: 4 posts