Thread: How to insert Chinese (big5) through JDBC?

How to insert Chinese (big5) through JDBC?

From
Eric Tan
Date:
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

Re: How to insert Chinese (big5) through JDBC?

From
Kris Jurka
Date:

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


Re: How to insert Chinese (big5) through JDBC?

From
Eric Tan
Date:
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 <books@ejurka.com> 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

Re: How to insert Chinese (big5) through JDBC?

From
Eric Tan
Date:
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 <neltan2002@yahoo.com.hk> 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 <books@ejurka.com> 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

Re: How to insert Chinese (big5) through JDBC?

From
Kris Jurka
Date:

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



Re: How to insert Chinese (big5) through JDBC?

From
Eric Tan
Date:
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 <books@ejurka.com> 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

Re: How to insert Chinese (big5) through JDBC?

From
Kris Jurka
Date:

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



Re: How to insert Chinese (big5) through JDBC?

From
Eric Tan
Date:
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 <books@ejurka.com> 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 te rminate 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

Re: How to insert Chinese (big5) through JDBC?

From
Kris Jurka
Date:

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



Re: How to insert Chinese (big5) through JDBC?

From
Eric Tan
Date:
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 <books@ejurka.com> 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