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

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

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

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

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

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