[SQL] JDBC encoding problem
I'm having a rather strange problem that I'm hoping someone can help me
with. I'm using Struts 1.0/jsp on Debian linux under Tomcat 4.1.x and
the blackdown JVM . I'm attempting to convert my current SQL_ASCII
database to UNICODE. I'm new to this, so am most likely making a few
mistakes. Here's what I've done so far:
o Converted database encoding to be UNICODE. I'm pretty sure this part
worked okay. (did a pg_dump, then iconv -f 8859_1 -t UTF-8, then
created new db with encoding UNICODE and reloaded- no errors upon reload)
sparky:~$ psql -l
List of databases
Name| Owner | Encoding
---+--+---
unitest | kurt | UNICODE
template1 | postgres | SQL_ASCII
(2 rows)
o set client_encoding to 'UTF8';
o In my JSP files, I set the following at the top of each:
<%@ page lanuage="java" pageEncoding="UTF-8" %>
Now, to test this, I go to a japanese page, copy some text, then paste
it into a form, that gets submitted to the server and saved into the DB.
Then I try to display what I got back from the database. It comes out
garbled. HOWEVER- if I leave the 'pageEncoding' out of my display .jsp
file it still comes out garbled, UNTIL I set UTF-8 manually in my
browsers Character Encoding settings (both mozilla and IE). Then the
japanese characters render fine (just like I entered them).
Very strange. What's confusing is that when I set the pageEncoding to
'UTF-8', the characters don't render properly, and as far as I can tell,
thats the same as manually setting the browser manually. I must be
doing something wrong because I get the same results in IE and mozilla
(recent build).
What may be the problem- I don't do anything differently when getting
the data out of the database, just standard
resultset.getString("column"); Do I need to change that call, to handle
the potentially UTF-8 encoded strings? I can't find anything on that at
all with google/usenet.
Any and all help, suggestions or pointers would be greatly appreciated.
Thanks!
/kurt
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] JDBC encoding problem
It seems like I was able to get it to work with UTF-8. I found this
nifty little page: http://www.anassina.com/struts/i18n/i18n.html which
explained how to handle this within Jakarta Struts. It SEEMS like when
reading the data from the database I don't need to explictly convert it
to UTF-8, it just happens. When I converted my database, I dumped it
out, ran an ASCII to UNICODE converter, then loaded the data back in. I
was able to enter japanese characters and read them back from the DB
okay. My big question is completeness. I really have no idea if I'm
handling every case since it "just seems to work". Thanks for the reply!
/kurt
LEON wrote:
I have met the same problem when I deal with chinese.
I guess tomcat and jdbc produced this bug.
But you may solute the charset display problem.
You change the database's charset is useless when you connected DB with JDBC. You
should convert Input Text with UTF-8 before saved in database. When you fetch data
from DATABASE ,you should convert output data with some charset (F.E:GBK) before you
display the data in page.
There is some java code I used.Hope they will help you.
//Output:
OutputStream fileOut = new FileOutputStream(outputFileName);
fileOut.write(data.getBytes("GBK")); //GBK、GB2312、BIG5、UTF8
//Input:
InputStream fileIn = new FileInputStream(inputFileName);
int i = fileIn.read(buff);
String data = new String(buff,"UTF8");
Best regards.
leon
- Original Message -
From: "Kurt Overberg" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Wednesday, August 06, 2003 1:11 AM
Subject: [SQL] JDBC encoding problem
I'm having a rather strange problem that I'm hoping someone can help me
with. I'm using Struts 1.0/jsp on Debian linux under Tomcat 4.1.x and
the blackdown JVM . I'm attempting to convert my current SQL_ASCII
database to UNICODE. I'm new to this, so am most likely making a few
mistakes. Here's what I've done so far:
o Converted database encoding to be UNICODE. I'm pretty sure this part
worked okay. (did a pg_dump, then iconv -f 8859_1 -t UTF-8, then
created new db with encoding UNICODE and reloaded- no errors upon reload)
sparky:~$ psql -l
List of databases
Name| Owner | Encoding
---+--+---
unitest | kurt | UNICODE
template1 | postgres | SQL_ASCII
(2 rows)
o set client_encoding to 'UTF8';
o In my JSP files, I set the following at the top of each:
<%@ page lanuage="java" pageEncoding="UTF-8" %>
Now, to test this, I go to a japanese page, copy some text, then paste
it into a form, that gets submitted to the server and saved into the DB.
Then I try to display what I got back from the database. It comes out
garbled. HOWEVER- if I leave the 'pageEncoding' out of my display .jsp
file it still comes out garbled, UNTIL I set UTF-8 manually in my
browsers Character Encoding settings (both mozilla and IE). Then the
japanese characters render fine (just like I entered them).
Very strange. What's confusing is that when I set the pageEncoding to
'UTF-8', the characters don't render properly, and as far as I can tell,
thats the same as manually setting the browser manually. I must be
doing something wrong because I get the same results in IE and mozilla
(recent build).
What may be the problem- I don't do anything differently when getting
the data out of the database, just standard
resultset.getString("column"); Do I need to change that call, to handle
the potentially UTF-8 encoded strings? I can't find anything on that at
all with google/usenet.
Any and all help, suggestions or pointers would be greatly appreciated.
Thanks!
/kurt
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
---(end of broadcast)---TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[SQL] Query suddenly taking longer....
Hi all,
I have the following query on postgresql 7.3.2 on RedHat 7.
select *, (select count(*) from xrefmembergroup where membergroupid =
m.id) as numberingroup from membergroup m;
(basically- try to get a list of 'groups' and the number of members in
each group)
The xrefmembergroup table has about 120,000 rows, membergroup has 90.
This query has been running very quickly, but has suddenly started
taking a LONG LONG time. Nothing has else has really changed in the
system, this morning it just started taking too long (went from .5
seconds to > 5 minutes).
Now, when I do run this query my postmaster process spikes from around
10Megs (normal size) to around 250Megs and just kinda sits there until
it eventually returns 5 minutes later.
I get the feeling that the xrefmembergroup table has crossed some bounds
(disk/memory) that is causing it to be super slow, but I don't know
which one. I have b-tree indexes on all the fields in xrefmembergroup.
Here's the table definition:
Column | Type | Modifiers
---+--+
id| integer | not null default
nextval('"xrefmembergroup_id_seq"'::text)
membergroupid | integer | not null default 0
memberid | integer | not null default 0
timestamp | timestamp with time zone | default
"timestamp"('now'::text)
Indexes: xrefmembergroup_pkey primary key btree (id),
membergroupid_xrefmembergroup_key btree (membergroupid),
memberid_xrefmembergroup_key btree (memberid)
At one point, I did an EXPLAIN ANALYZE on the query and it seemed to be
using sequential scans. I can't run this query anymore because it nukes
my production server, so I'm limited in how much I can debug this right
now. I have a similar system (7.3.2 on Debian) that does not exhibit
this problem running on the same database. Don't know why its not using
the indexes. Any thoughts?
/kurt
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Query suddenly taking longer....
Tom, Thanks for the reply. I agree that the query seemed inefficient, but it ran so quickly I thought it was okay. The only difference between the two servers was that the fast one used an Index Scan while the other (the now-slow one) would use a sequential scan. The query as you re-wrote it seems to work great though. Thank you. /kurt Tom Lane wrote: Kurt Overberg <[EMAIL PROTECTED]> writes: I have the following query on postgresql 7.3.2 on RedHat 7. select *, (select count(*) from xrefmembergroup where membergroupid = m.id) as numberingroup from membergroup m; The xrefmembergroup table has about 120,000 rows, membergroup has 90. This query has been running very quickly, but has suddenly started taking a LONG LONG time. Presumably the plan changed, but without any reasonable way to tell what the old plan was, there's no way to be sure. (Possibly comparing explain plans from both servers would be useful, though.) Now, when I do run this query my postmaster process spikes from around 10Megs (normal size) to around 250Megs and just kinda sits there until it eventually returns 5 minutes later. What was the new plan, exactly? I don't see any reason for this query to chew a lot of memory. I think that the query is inherently inefficient as written, since it forces a separate scan of xrefmembergroup for every membergroup row. I don't really see how it could ever have been done in subsecond time, unless perhaps a large fraction of the xrefmembergroup entries did not match any membergroup row, which seems unlikely. I'd suggest doing something that will allow the counts to be accumulated in just one xrefmembergroup scan, with GROUP BY. A straightforward way is select m.*, numberingroup from membergroup m, (select membergroupid, count(*) as numberingroup from xrefmembergroup group by membergroupid) as c where m.id = c.membergroupid; I'm not convinced this will actually be much of a win in 7.3 unfortunately ... but it should fly in 7.4, because of the new hash aggregation code. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] JDBC encoding problem
I'm having a rather strange problem that I'm hoping someone can help me
with. I'm using Struts 1.0/jsp on Debian linux under Tomcat 4.1.x and
the blackdown JVM . I'm attempting to convert my current SQL_ASCII
database to UNICODE. I'm new to this, so am most likely making a few
mistakes. Here's what I've done so far:
o Converted database encoding to be UNICODE. I'm pretty sure this part
worked okay. (did a pg_dump, then iconv -f 8859_1 -t UTF-8, then
created new db with encoding UNICODE and reloaded- no errors upon reload)
sparky:~$ psql -l
List of databases
Name| Owner | Encoding
---+--+---
unitest | kurt | UNICODE
template1 | postgres | SQL_ASCII
(2 rows)
o set client_encoding to 'UTF8';
o In my JSP files, I set the following at the top of each:
<%@ page lanuage="java" pageEncoding="UTF-8" %>
Now, to test this, I go to a japanese page, copy some text, then paste
it into a form, that gets submitted to the server and saved into the DB.
Then I try to display what I got back from the database. It comes out
garbled. HOWEVER- if I leave the 'pageEncoding' out of my display .jsp
file it still comes out garbled, UNTIL I set UTF-8 manually in my
browsers Character Encoding settings (both mozilla and IE). Then the
japanese characters render fine (just like I entered them).
Very strange. What's confusing is that when I set the pageEncoding to
'UTF-8', the characters don't render properly, and as far as I can tell,
thats the same as manually setting the browser manually. I must be
doing something wrong because I get the same results in IE and mozilla
(recent build).
What may be the problem- I don't do anything differently when getting
the data out of the database, just standard
resultset.getString("column"); Do I need to change that call, to handle
the potentially UTF-8 encoded strings? I can't find anything on that at
all with google/usenet.
Any and all help, suggestions or pointers would be greatly appreciated.
Thanks!
/kurt
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] Selecting rows as if they were columns?
Gang,
I've got what I think should be a pretty simple problem- I just can't
figure out how I'd do it. Heck, I can't even figure out how I'd search
for an answer to this problem.
I've got 2 tables: member and member_attr
Member is just a standard entry for a member. Very simple:
table member {
idinteger,
name character varying(30)
}
Member_attr is just a table of name/value pairs, such as:
table memberextra {
memberid integer,
param1 character varying(512),
param2 character varying(512),
}
where param1 is the name, param2 is the value.
Right now, I can:
select member.id, member.name, member_attr.param1, member_attr.param2
from member, member_attr where member.id = member_attr.memberid;
...this gives me n rows per member where n = # of attributes for that
member, like this:
id | name | param1| param2
===
1| dave | home | /home/dave
1| dave | testing| 1
2| john | home | /home/john
...is there some way to make this return like this:
id | name| home | testing
==
1 | dave| /home/dave | 1
2 | john| /home/john |
...where it sort of "expands" the rows out into columns? Am I just
going about this the wrong way? Thoughts on the subject would be
greatly appreciated.
thanks!
/kurt
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
