Thread: Very strange 'now' behaviour in nested triggers.
In short, the idea this example is to test for is to split a
comma-separated value of some text attribute (given to the INSERT
operator) and then insert a row for each of the parts of that text
value. I've tried to do this thru a nested triggers approach.
create
table xxx ( s text, t timestamp default 'now'
);
create
function xxx () returns trigger
language plpgsql
as '
declare tail text; head integer;
begin tail:= substring(new.s, \'[^,]+$\'); head:= length(new.s)- length(tail) -1; if head > 0 then
insertinto xxx values ( substring(new.s for head) --,new.t ); end if; new.s:= trim(tail);
raise notice \'"%"\', new.s; raise notice \'"%"\', new.t; return new;
end;
';
create
trigger xxx before insert on xxx for each row execute procedure xxx ();
Then:
zzz=> INSERT INTO xxx VALUES (' a ,b, c, d , x');
NOTICE: "a"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "b"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "c"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "d"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "x"
NOTICE: "2003-07-26 19:17:26.514217"
INSERT 223886 1
zzz=> INSERT INTO xxx VALUES (' a ,b, c, d , x');
NOTICE: "a"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "b"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "c"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "d"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "x"
NOTICE: "2003-07-26 19:17:28.300914"
INSERT 223891 1
zzz=> INSERT INTO xxx VALUES (' a ,b, c, d , x');
NOTICE: "a"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "b"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "c"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "d"
NOTICE: "2003-07-26 19:17:26.514217"
NOTICE: "x"
NOTICE: "2003-07-26 19:17:30.948737"
INSERT 223896 1
zzz=> SELECT * from xxx;s | t
---+----------------------------a | 2003-07-26 19:17:26.514217b | 2003-07-26 19:17:26.514217c | 2003-07-26
19:17:26.514217d| 2003-07-26 19:17:26.514217x | 2003-07-26 19:17:26.514217a | 2003-07-26 19:17:26.514217b | 2003-07-26
19:17:26.514217c| 2003-07-26 19:17:26.514217d | 2003-07-26 19:17:26.514217x | 2003-07-26 19:17:28.300914a | 2003-07-26
19:17:26.514217b| 2003-07-26 19:17:26.514217c | 2003-07-26 19:17:26.514217d | 2003-07-26 19:17:26.514217x | 2003-07-26
19:17:30.948737
(15 rows)
So, all the timestamps except those for the last 'x' field are the
same! These "the same" timestamps are really the timestamp of the
first top-level INSERT. And the timestamps for the last field of the
comma-separated string are the correct things. This last field is
cultivated by the top-level trigger's call.
If to set new.t for nested triggers explicitly (commented in the
trigger code above), then all will be ok. But this is not a cure, of
course.
So, what does it mean? Is this a bug (PostgreSQL 7.3.2)? Or do I
misunderstand something?
Thanks in advance.
On Saturday 26 July 2003 14:39, Denis Zaitsev wrote:
> In short, the idea this example is to test for is to split a
> comma-separated value of some text attribute (given to the INSERT
> operator) and then insert a row for each of the parts of that text
> value. I've tried to do this thru a nested triggers approach.
I'm not sure I'd use this approach for very long strings, but we can sort out
your timestamp problem.
> create
> table xxx (
> s text,
> t timestamp
> default 'now' ^^^
Note the quoted 'now'.
[snip recursive before trigger - final element gets inserted by the actual SQL
below - abcd get inserted by the trigger]
> zzz=> INSERT INTO xxx VALUES (' a ,b, c, d , x');
> NOTICE: "a"
> NOTICE: "2003-07-26 19:17:26.514217"
> NOTICE: "b"
> NOTICE: "2003-07-26 19:17:26.514217"
> NOTICE: "c"
> NOTICE: "2003-07-26 19:17:26.514217"
> NOTICE: "d"
> NOTICE: "2003-07-26 19:17:26.514217"
> NOTICE: "x"
> NOTICE: "2003-07-26 19:17:30.948737"
> INSERT 223896 1
> So, all the timestamps except those for the last 'x' field are the
> same! These "the same" timestamps are really the timestamp of the
> first top-level INSERT. And the timestamps for the last field of the
> comma-separated string are the correct things. This last field is
> cultivated by the top-level trigger's call.
>
> If to set new.t for nested triggers explicitly (commented in the
> trigger code above), then all will be ok. But this is not a cure, of
> course.
>
> So, what does it mean? Is this a bug (PostgreSQL 7.3.2)? Or do I
> misunderstand something?
Not exactly a bug. The crucial thing is that 'now' gets evaluated when the
query is parsed and the plan built. For the main INSERT that's at the start
of the transaction (which is what you want).
For the trigger function, what happens is the plan for that insert gets
compiled the first time the function is called and 'now' gets frozen.
Solution: make the default now() or CURRENT_TIMESTAMP and all will be as you
expect.
PS - I think this is mentioned in the manuals somewhere, but it's not
surprising you missed it. Interesting example.
-- Richard Huxton Archonet Ltd
Denis Zaitsev <zzz@anda.ru> writes:
> create table xxx (
> s text,
> t timestamp
> default 'now'
> );
That's a dangerous way to define the default --- 'now' is taken as a
literal of type timestamp, which means it will be reduced to a timestamp
constant as soon as a statement that requires the default is planned.
You lose in plpgsql because of plan caching, but you'd also lose if you
tried to PREPARE the insert command. Example:
regression=# insert into xxx values('a');
INSERT 154541 1
regression=# insert into xxx values('b');
INSERT 154542 1
regression=# prepare s(text) as insert into xxx values($1);
PREPARE
regression=# execute s('q1');
EXECUTE
regression=# execute s('q2');
EXECUTE
regression=# select * from xxx;s | t
----+----------------------------a | 2003-07-26 10:18:51.364913b | 2003-07-26 10:18:53.519648q1 | 2003-07-26
10:19:21.795415q2| 2003-07-26 10:19:21.795415
(4 rows)
The default would work the way you want with almost any other way of
doing it. For instance
default now()default current_timestampdefault localtimestampdefault 'now'::text
Given that you want timestamp without time zone, I'd probably use
"default localtimestamp".
regards, tom lane
On Sat, Jul 26, 2003 at 03:14:16PM +0100, Richard Huxton wrote: > On Saturday 26 July 2003 14:39, Denis Zaitsev wrote: > > In short, the idea this example is to test for is to split a > > comma-separated value of some text attribute (given to the INSERT > > operator) and then insert a row for each of the parts of that text > > value. I've tried to do this thru a nested triggers approach. > > I'm not sure I'd use this approach for very long strings Of course not a very deep recursion, the strings are expected to consist of less than 10 pieces. > Not exactly a bug. The crucial thing is that 'now' gets evaluated when the > query is parsed and the plan built. For the main INSERT that's at the start > of the transaction (which is what you want). > > For the trigger function, what happens is the plan for that insert gets > compiled the first time the function is called and 'now' gets frozen. Ok, thanks a much. I've realized... > Solution: make the default now() or CURRENT_TIMESTAMP and all will be as you > expect. > > PS - I think this is mentioned in the manuals somewhere, but it's not > surprising you missed it. Interesting example. As I remember, namely 'now' is mentioned in the manuals, as the best approach to keep the same value thru the whole transaction. That is why I used it here. For now I've tested that now() does the thing. Why? I remember that now() is changing thru the transaction, just showing the current time...
On Sat, Jul 26, 2003 at 10:31:44AM -0400, Tom Lane wrote: > Denis Zaitsev <zzz@anda.ru> writes: > > create table xxx ( > > s text, > > t timestamp > > default 'now' > > ); > > That's a dangerous way to define the default --- 'now' is taken as a > literal of type timestamp, which means it will be reduced to a timestamp > constant as soon as a statement that requires the default is planned. > You lose in plpgsql because of plan caching, but you'd also lose if you > tried to PREPARE the insert command. Example: Aaa... So, the INSERT inside a trigger will use the 'now' for the time this trigger is compiled (i.e. called first time)? Do I understand right? And the only outer trigger uses the right 'now' as its value goes from the top-level INSERT... Thank you very much. By the way, do you think this method with nested triggers has some 'moral weakness' vs. just cycling left-to-right on the comma-separated string in the 'do instead' rule for some view of xxx?
> On Sat, Jul 26, 2003 at 03:14:16PM +0100, Richard Huxton wrote: >> On Saturday 26 July 2003 14:39, Denis Zaitsev wrote: > >> Solution: make the default now() or CURRENT_TIMESTAMP and all will be as >> you >> expect. >> >> PS - I think this is mentioned in the manuals somewhere, but it's not >> surprising you missed it. Interesting example. > > As I remember, namely 'now' is mentioned in the manuals, as the best > approach to keep the same value thru the whole transaction. That is > why I used it here. For now I've tested that now() does the thing. > Why? I remember that now() is changing thru the transaction, just > showing the current time... No- 'now',now() and CURRENT_TIMESTAMP all stay fixed during a transaction. The one that changes is timeofday() I think. See the "Functions and Operators" section for details. - Richard
On Sun, Jul 27, 2003 at 08:47:16AM +0100, Richard Huxton wrote:
>
> No- 'now',now() and CURRENT_TIMESTAMP all stay fixed during a transaction.
> The one that changes is timeofday() I think. See the "Functions and
> Operators" section for details.
Yes, indeed... Documentation describes this. And I haven't found
anything about 'now' that I used to say... I don't know why (my
memory leak?) :)
Thanks.
BTW, this text is at the bottom of the "Date/Time Functions and
Operators" section (functions-datetime.html):
SELECT CURRENT_TIMESTAMP;
SELECT now();
SELECT TIMESTAMP 'now';
Note: You do not want to use the third form when specifying a DEFAULT clause while creating a table. The
systemwill convert now to a timestamp as soon as the constant is parsed, so that when the default value is needed,
thetime of the table creation would be used! ...
It's nearly what you have written about. But I want to note <the time
of the table creation> phrase. Should it be fixed there?
Denis Zaitsev <zzz@anda.ru> writes: > On Sat, Jul 26, 2003 at 10:31:44AM -0400, Tom Lane wrote: >> That's a dangerous way to define the default --- 'now' is taken as a >> literal of type timestamp, which means it will be reduced to a timestamp >> constant as soon as a statement that requires the default is planned. > Aaa... So, the INSERT inside a trigger will use the 'now' for the > time this trigger is compiled (i.e. called first time)? Do I > understand right? And the only outer trigger uses the right 'now' as > its value goes from the top-level INSERT... Right. I put up a proposal in pgsql-hackers to change this behavior: http://archives.postgresql.org/pgsql-hackers/2003-07/msg00818.php If we made that change then the "wrong" way of defining the default would fail in an obvious fashion --- the 'now' would get reduced to a particular time immediately at CREATE TABLE. Doubtless this would annoy some people, but the "right" way of defining the default isn't really any harder, and it would save folks from getting burnt in corner cases, like you were. Any comments? regards, tom lane
On Sun, Jul 27, 2003 at 11:49:10AM -0400, Tom Lane wrote: > > I put up a proposal in pgsql-hackers to change this behavior: > http://archives.postgresql.org/pgsql-hackers/2003-07/msg00818.php > If we made that change then the "wrong" way of defining the default > would fail in an obvious fashion --- the 'now' would get reduced to a > particular time immediately at CREATE TABLE. Doubtless this would annoy > some people, but the "right" way of defining the default isn't really > any harder, and it would save folks from getting burnt in corner cases, > like you were. > > Any comments? I think that is a preferred behavior. -Roberto -- +----| Roberto Mello - http://www.brasileiro.net/ |------+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + øøøøøøøøøøøøøøøøøøøøøøøø-----°*'. (Explosive Tagline)
Dmitry Tkach <dmitry@openratings.com> writes:
> Why not get rid of 'now' alltogether? Are there any cases when it is
> actually useful as opposed to now()?
Data entry. You don't necessarily have the option to invoke a function,
as opposed to just sending a string for the datetime input parser.
(It would be rather difficult for an application to allow this one case
without permitting SQL-injection attacks, I'd think.)
regards, tom lane
Tom Lane wrote: > >I put up a proposal in pgsql-hackers to change this behavior: >http://archives.postgresql.org/pgsql-hackers/2003-07/msg00818.php >If we made that change then the "wrong" way of defining the default >would fail in an obvious fashion --- the 'now' would get reduced to a >particular time immediately at CREATE TABLE. Doubtless this would annoy >some people, but the "right" way of defining the default isn't really >any harder, and it would save folks from getting burnt in corner cases, >like you were. > >Any comments? > > > Why not get rid of 'now' alltogether? Are there any cases when it is actually useful as opposed to now()? Dima
Tom Lane wrote: >Dmitry Tkach <dmitry@openratings.com> writes: > > >>Does it mean that the *application* (not the database) user would then >>have to know the exact specific way to represent the current time in his >>data entry form? >>Such an application looks like (how do I say it politely?) not a very >>user-friendly one to me :-) >> >> > >So? "now()" is certainly not more user-friendly than "now". > Nope... it isn't. My point was that, if the app wanted to be user friendly, it would not attempt to take the input directly from user and stuff it into the sql - it would probably have some checkbox or drop-down list in the GUI form, that would indicate that the user wants the current time stamp, and use the the proper internal represntation in the generated sql... In that case having to execute a function (now()) would not make it vulnerable to a sql injection... > My point >is that wherever you are making the decision that you want to input >current time, there may be layers between you and the database that will >only want to pass data-value strings and not function invocations. > > > Yeah... I've actually found one after I sent that last message :-) - it does seem to come handy in COPY TABLE - although, in that case, I'd say it would be much more useful to make COPY TABLE understand the defined defaults on the table, just the way INSERT does ... Dima
Dmitry Tkach <dmitry@openratings.com> writes:
> Does it mean that the *application* (not the database) user would then
> have to know the exact specific way to represent the current time in his
> data entry form?
> Such an application looks like (how do I say it politely?) not a very
> user-friendly one to me :-)
So? "now()" is certainly not more user-friendly than "now". My point
is that wherever you are making the decision that you want to input
current time, there may be layers between you and the database that will
only want to pass data-value strings and not function invocations.
regards, tom lane
> > >Data entry. You don't necessarily have the option to invoke a function, >as opposed to just sending a string for the datetime input parser. >(It would be rather difficult for an application to allow this one case >without permitting SQL-injection attacks, I'd think.) > > > Does it mean that the *application* (not the database) user would then have to know the exact specific way to represent the current time in his data entry form? Such an application looks like (how do I say it politely?) not a very user-friendly one to me :-) Dima
On Sun, Jul 27, 2003 at 11:49:10AM -0400, Tom Lane wrote: > I put up a proposal in pgsql-hackers to change this behavior: > http://archives.postgresql.org/pgsql-hackers/2003-07/msg00818.php > If we made that change then the "wrong" way of defining the default > would fail in an obvious fashion --- the 'now' would get reduced to a > particular time immediately at CREATE TABLE. Doubtless this would annoy > some people, but the "right" way of defining the default isn't really > any harder, and it would save folks from getting burnt in corner cases, > like you were. > > Any comments? The proposed behavious promises to be "strict, simple and well-defined". The current one definitely tends to have "dirty and non-obvious side effects here and there". I think the new behaviour should conquer. BTW, the documentation describes this proposed behaviour (but why?), not the current one...
Could we cleanly convert 'now' to now()? I assume not because we pass
the string to the date code, and have no way to pass back a function
(now()).
Having now and now() behave differently certainly is strange.
CURRENT_TIMESTAMP works fine, but it does because it isn't in quotes.
Strangely, CURRENT_TIMESTAMP converts to 'now', not now(). Is that a
problem?
| CURRENT_TIMESTAMP { /* * Translate as
"'now'::text::timestamptz". * See comments for CURRENT_DATE. */
A_Const*s = makeNode(A_Const); TypeName *d;
s->val.type = T_String; s->val.val.str = "now"; s->typename =
SystemTypeName("text");
d = SystemTypeName("timestamptz"); /* SQL99 mandates a default precision of 6 for
timestamp. * Also, that is about as precise as we will get since * we are using a
microsecondtime interface. * - thomas 2001-12-07 */ d->typmod =
6;
$$ = (Node *)makeTypeCast((Node *)s, d); }
---------------------------------------------------------------------------
Tom Lane wrote:
> Denis Zaitsev <zzz@anda.ru> writes:
> > On Sat, Jul 26, 2003 at 10:31:44AM -0400, Tom Lane wrote:
> >> That's a dangerous way to define the default --- 'now' is taken as a
> >> literal of type timestamp, which means it will be reduced to a timestamp
> >> constant as soon as a statement that requires the default is planned.
>
> > Aaa... So, the INSERT inside a trigger will use the 'now' for the
> > time this trigger is compiled (i.e. called first time)? Do I
> > understand right? And the only outer trigger uses the right 'now' as
> > its value goes from the top-level INSERT...
>
> Right.
>
> I put up a proposal in pgsql-hackers to change this behavior:
> http://archives.postgresql.org/pgsql-hackers/2003-07/msg00818.php
> If we made that change then the "wrong" way of defining the default
> would fail in an obvious fashion --- the 'now' would get reduced to a
> particular time immediately at CREATE TABLE. Doubtless this would annoy
> some people, but the "right" way of defining the default isn't really
> any harder, and it would save folks from getting burnt in corner cases,
> like you were.
>
> Any comments?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Strangely, CURRENT_TIMESTAMP converts to 'now', not now(). Is that a
> problem?
No, it is not, because the text-to-timestamptz conversion is marked
volatile and won't be const-folded. I've always thought it was a very
ugly implementation though, mainly because it doesn't reverse-list
nicely in rule dumps. Sometime we should try to fix it so that it
reverse-lists as "current_timestamp(n)", and likewise for the similar
special cases in gram.y.
regards, tom lane
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
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
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
beforesaved in database. When you fetch data from DATABASE ,you should convert output data with some charset (F.E:GBK)
beforeyou 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" <kurt@hotdogrecords.com>
To: <pgsql-sql@postgresql.org>; <pgsql-jdbc@postgresql.org>
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
>
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-8before 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" <kurt@hotdogrecords.com> > To: <pgsql-sql@postgresql.org>; <pgsql-jdbc@postgresql.org> > 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, pleasesend an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get throughto the mailing list cleanly > >