Thread: JDBC and Latin1 Database problem
Hi all,
I'm using postgresql 7.4 and pg74.215.jdbc3.jar as jdbc driver. I got
this error deploying my application on Linux/Tomcat5 This error is not
throwned in my development environment Win2000/Tomcat4.1.
Any Idea?
To inform the jdbc driver that it must converts caracters I put the
following URL in my server.xml (notice the encoding=latin1). Without this
variable caracter � takes 2 spaces in the latin db. So ������ will takes 12
spaces. This make my varchar(10) to small.
jdbc:postgresql://127.0.0.1:5432/webCatalog?encoding=latin1
Is that the way to do it .. because I search a lot an only found a mail
talking about this flag...
Thanks for your help!!
/David
Error executing: INSERT INTO AK (AKNUM,AKDESC_PRI,
AKDESC_SEC,AKFRAIS,AKTAUX,AKTYPE) VALUES
('CHEQ','CH?QUE','CHEQUE',22,null,1)
org.postgresql.util.PSQLException: ERROR: could not convert UTF-8 character
0x00ef to ISO8859-1
ERROR [main] (Dao.java:267) 2005-01-04 20:40:10,044 : Error processing the
sql file: resources/auxilary/payment/insert_test.sql
org.postgresql.util.PSQLException: ERROR: could not convert UTF-8 character
0x00ef to ISO8859-1
at com.ibatis.db.util.ScriptRunner.runScript(ScriptRunner.java:211)
at com.davecorp.webos.dao.Dao.executeSqlFile(Dao.java:264)
at com.davecorp.webos.dao.Dao.notificationEvent(Dao.java:234)
David Gagnon wrote:
> To inform the jdbc driver that it must converts caracters I put the
> following URL in my server.xml (notice the encoding=latin1). Without
> this variable caracter é takes 2 spaces in the latin db. So éééééé will
> takes 12 spaces. This make my varchar(10) to small.
> jdbc:postgresql://127.0.0.1:5432/webCatalog?encoding=latin1
The 'encoding' parameter is only used for connections to pre-7.3
servers; in other cases it is ignored.
What is the database encoding? ("SHOW server_encoding")
> Error executing: INSERT INTO AK (AKNUM,AKDESC_PRI,
> AKDESC_SEC,AKFRAIS,AKTAUX,AKTYPE) VALUES
> ('CHEQ','CH?QUE','CHEQUE',22,null,1)
> org.postgresql.util.PSQLException: ERROR: could not convert UTF-8
> character 0x00ef to ISO8859-1
Are you constructing this entire query string yourself, or are you using
a PreparedStatement with parameters?
Can you show us some sample code that demonstrates the problem?
-O
>From: Oliver Jowett <oliver@opencloud.com>
>To: David Gagnon <dgagnon74@hotmail.com>
>CC: pgsql-jdbc@postgresql.org
>Subject: Re: [JDBC] JDBC and Latin1 Database problem
>Date: Wed, 05 Jan 2005 15:55:21 +1300
>
>David Gagnon wrote:
>
>>To inform the jdbc driver that it must converts caracters I put the
>>following URL in my server.xml (notice the encoding=latin1). Without this
>>variable caracter � takes 2 spaces in the latin db. So ������ will takes
>>12 spaces. This make my varchar(10) to small.
>
>>jdbc:postgresql://127.0.0.1:5432/webCatalog?encoding=latin1
>
>The 'encoding' parameter is only used for connections to pre-7.3 servers;
>in other cases it is ignored.
Really?! I must say that this REALLY solved my encoding problem. And I'm
using 7.4 ?! Without this flag I always get the error describe before.
Do I have to specify something to inform the JDBC driver that it connect to
a Latin1 database? I do get the error I described :-(
>What is the database encoding? ("SHOW server_encoding")
dgagnon@segfault dgagnon $ psql webCatalog
Welcome to psql 7.4.6, 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
webCatalog=# SHOW server_encoding
webCatalog-# ;
server_encoding
-----------------
LATIN1
(1 row)
webCatalog=#
>
>> Error executing: INSERT INTO AK (AKNUM,AKDESC_PRI,
>>AKDESC_SEC,AKFRAIS,AKTAUX,AKTYPE) VALUES
>>('CHEQ','CH?QUE','CHEQUE',22,null,1)
>>org.postgresql.util.PSQLException: ERROR: could not convert UTF-8
>>character 0x00ef to ISO8859-1
>
>Are you constructing this entire query string yourself, or are you using a
>PreparedStatement with parameters?
This statement is read from an sql file. So no prepare statement here is
the code that cause the problem:
Statement statement = conn.createStatement();
println(command);
if (log.isDebugEnabled()) {
log.debug(command);
}
boolean hasResults = false;
if (stopOnError) {
hasResults = statement.execute(command.toString());
} else {
try {
statement.execute(command.toString());
} catch (SQLException e) {
e.fillInStackTrace();
printlnError("Error executing: " + command);
printlnError(e);
}
}
But this code works for 2 years without problem ... before I changed my
database encoding to Latin1 of course ...
The SQL statemetn in the corresponding file:
INSERT INTO AT (ATNUM, ATDESC_PRI, ATDESC_SEC, ATTAUX, ATNIV, ATCALC,
ATGLNUMV, ATGLNUMA) VALUES ('tvh','Taxe de vente harmonis�ePri','Taxe de
vente harmonis�e',15,0,False,'10-2090','10-2090');
Thanks for you help. I mess with database encoding for a while now ..
Yesterday I thought I had finally fixed everything (with the encoding flag)
... It's not that simple :-(
Thanks
/David
>
>Can you show us some sample code that demonstrates the problem?
>
>-O
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
David Gagnon wrote: >> The 'encoding' parameter is only used for connections to pre-7.3 >> servers; in other cases it is ignored. > > > Really?! I must say that this REALLY solved my encoding problem. And I'm > using 7.4 ?! Without this flag I always get the error describe before. Hmm -- perhaps the stable driver does things differently. I don't have that code readily to hand. Can you try the development driver (without any encoding parameter) and see if you get different behaviour? > Do I have to specify something to inform the JDBC driver that it connect > to a Latin1 database? You shouldn't need to. > This statement is read from an sql file. So no prepare statement here > is the code that cause the problem: > hasResults = statement.execute(command.toString()); Can you dump out the individual (unicode) character values of 'command' just to check you're really executing the query you think you are? -O
"David Gagnon" <dgagnon74 'at' hotmail.com> writes:
> ERROR [main] (Dao.java:267) 2005-01-04 20:40:10,044 : Error
> processing the sql file: resources/auxilary/payment/insert_test.sql
> org.postgresql.util.PSQLException: ERROR: could not convert UTF-8
> character 0x00ef to ISO8859-1
I think there is a problem in the data you're trying to send to
the database. Don't forget that Java strings have no "charset"
(actually they are internally using unicode) so there is some
care when reading data from files into java strings, in order to
be sure that the correct charset (the one in which the data in
files is encoded) was specified.
The character sequence for sure doesn't look like a valid UTF8
one, as the problem can be duplicated with iconv:
[gc@meuh /tmp] perl -e 'print pack("H*", "00EF")' > 0x00ef
[gc@meuh /tmp] hexdump -C 0x00ef
00000000 00 ef |..|
00000002
[gc@meuh /tmp] iconv -f utf8 -t latin1 0x00ef
iconv: incomplete character or shift sequence at end of buffer
--
Guillaume Cottenceau