pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedJDBC and character sets
14+ messages / 4 participants
[nested] [flat]
* JDBC and character sets
@ 1999-06-22 10:18 David Warnock <david@sundayta.co.uk>
1999-06-22 12:07 ` Re: [INTERFACES] JDBC and character sets Tatsuo Ishii <t-ishii@sra.co.jp>
0 siblings, 1 reply; 14+ messages in thread
From: David Warnock @ 1999-06-22 10:18 UTC (permalink / raw)
To: pgsql-interfaces
Hi,
We have java applications currently using either Postgresql 6.4 or
Interbase. We use Interbase as we need unicode.
The support for unicode in Postgresql 6.5 is unclear to me. I can also
see no mention of Unicode in the jdbc driver documentation.
Can I install postgresql 6.5 so as to be able to save and restore the
unicode from java?
If so how?
Thanks
Dave
--
David Warnock
Sundayta Ltd
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [INTERFACES] JDBC and character sets
1999-06-22 10:18 JDBC and character sets David Warnock <david@sundayta.co.uk>
@ 1999-06-22 12:07 ` Tatsuo Ishii <t-ishii@sra.co.jp>
1999-06-22 13:54 ` Re: [INTERFACES] JDBC and character sets David Warnock <david@sundayta.co.uk>
0 siblings, 1 reply; 14+ messages in thread
From: Tatsuo Ishii @ 1999-06-22 12:07 UTC (permalink / raw)
To: David Warnock <david@sundayta.co.uk>; +Cc: pgsql-interfaces
>We have java applications currently using either Postgresql 6.4 or
>Interbase. We use Interbase as we need unicode.
>
>The support for unicode in Postgresql 6.5 is unclear to me. I can also
>see no mention of Unicode in the jdbc driver documentation.
>
>Can I install postgresql 6.5 so as to be able to save and restore the
>unicode from java?
>
>If so how?
I'm not sure about JDBC, but definitely PostgreSQL supports Unicode if
you enable MB option.
configure --with-mb=UNICODE
--
Tatsuo Ishii
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [INTERFACES] JDBC and character sets
1999-06-22 10:18 JDBC and character sets David Warnock <david@sundayta.co.uk>
1999-06-22 12:07 ` Re: [INTERFACES] JDBC and character sets Tatsuo Ishii <t-ishii@sra.co.jp>
@ 1999-06-22 13:54 ` David Warnock <david@sundayta.co.uk>
1999-06-22 14:13 ` Re: [INTERFACES] JDBC and character sets Tatsuo Ishii <t-ishii@sra.co.jp>
0 siblings, 1 reply; 14+ messages in thread
From: David Warnock @ 1999-06-22 13:54 UTC (permalink / raw)
To: t-ishii@sra.co.jp; +Cc: pgsql-interfaces
Tatsuo,
Many thanks for your reply.
I think I saw from the list of developers that you wrote a lot of the
multiu-byte code. Is that correct? If so my grateful thanks.
Are there any limitations or gotchas about using unicode everywhere?
Specifically
1. Column length. Is this measured in unicode characters or do I need to
increase the length of Varchars? ie is a varchar(10) certain to hold 10
unicode characters?
2. Indexing. What sort order will I get from an index or an order by for
unicode characters. Can this be customised. Generally I try to do any
really important sorting in Java where I can use the correct sort order
for the locale.
3. Upper/lowercase. I have been using separate columns for uppercase
versions of names etc again so that the case changes can be done by the
client which will know the correct rules for the locale where the data
is entered. What do upper/lower case functions in Postgresql do with
unicode?
4. Are there any limitations on what I use to write triggers? Can all
the different ways work reliably with unicode?
Many many thanks.
Dave
--
David Warnock
Sundayta Ltd
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [INTERFACES] JDBC and character sets
1999-06-22 10:18 JDBC and character sets David Warnock <david@sundayta.co.uk>
1999-06-22 12:07 ` Re: [INTERFACES] JDBC and character sets Tatsuo Ishii <t-ishii@sra.co.jp>
1999-06-22 13:54 ` Re: [INTERFACES] JDBC and character sets David Warnock <david@sundayta.co.uk>
@ 1999-06-22 14:13 ` Tatsuo Ishii <t-ishii@sra.co.jp>
1999-06-22 14:36 ` Re: [INTERFACES] JDBC and character sets David Warnock <david@sundayta.co.uk>
0 siblings, 1 reply; 14+ messages in thread
From: Tatsuo Ishii @ 1999-06-22 14:13 UTC (permalink / raw)
To: David Warnock <david@sundayta.co.uk>; +Cc: pgsql-interfaces
>I think I saw from the list of developers that you wrote a lot of the
>multiu-byte code. Is that correct? If so my grateful thanks.
Yes, I'm responsible for the code multi-byte.
>Are there any limitations or gotchas about using unicode everywhere?
>
>Specifically
>
>1. Column length. Is this measured in unicode characters or do I need to
>increase the length of Varchars? ie is a varchar(10) certain to hold 10
>unicode characters?
When you define varchar(n), n should be counted in bytes, not
characters.
We assume Unicode is input as UTF-8 encoding. In UTF-8, 10 ASCII chars
take 10 bytes. So varchar(10) will hold 10 Unicode chars if they are
all ASCII. However, if you use ISO8859 chars they will take 2 bytes
for each letter. If you use KANJI, 3 bytes for each letter. You could
use octet_length() to measure the size of a Unicode string in bytes.
>2. Indexing. What sort order will I get from an index or an order by for
>unicode characters.
It will sorted in the order of Unicode code point.
>Can this be customised.
Currently no.
>Generally I try to do any
>really important sorting in Java where I can use the correct sort order
>for the locale.
>3. Upper/lowercase. I have been using separate columns for uppercase
>versions of names etc again so that the case changes can be done by the
>client which will know the correct rules for the locale where the data
>is entered. What do upper/lower case functions in Postgresql do with
>unicode?
I think it will related to locale. I'm not sure but I've heard about
Unicode locale. If it really exists, you could do:
configure --with-mb=UNICODE --with-locale
so that upper/lower works for Unicode.
>4. Are there any limitations on what I use to write triggers? Can all
>the different ways work reliably with unicode?
I'm not sure but it should work with triggers.
--
Tatsuo Ishii
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [INTERFACES] JDBC and character sets
1999-06-22 10:18 JDBC and character sets David Warnock <david@sundayta.co.uk>
1999-06-22 12:07 ` Re: [INTERFACES] JDBC and character sets Tatsuo Ishii <t-ishii@sra.co.jp>
1999-06-22 13:54 ` Re: [INTERFACES] JDBC and character sets David Warnock <david@sundayta.co.uk>
1999-06-22 14:13 ` Re: [INTERFACES] JDBC and character sets Tatsuo Ishii <t-ishii@sra.co.jp>
@ 1999-06-22 14:36 ` David Warnock <david@sundayta.co.uk>
1999-06-22 14:47 ` Re: [INTERFACES] JDBC and character sets Tatsuo Ishii <t-ishii@sra.co.jp>
0 siblings, 1 reply; 14+ messages in thread
From: David Warnock @ 1999-06-22 14:36 UTC (permalink / raw)
To: t-ishii@sra.co.jp; +Cc: pgsql-interfaces
Tatsuo,
Many thanks for all your answers. We will be giving postgresql a full
trial soon and then hopefully the last fully closed source component of
our own software will be gone so we can look at releasing more than just
tools as open source.
Regards
Dave
--
David Warnock
Sundayta Ltd
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [INTERFACES] JDBC and character sets
1999-06-22 10:18 JDBC and character sets David Warnock <david@sundayta.co.uk>
1999-06-22 12:07 ` Re: [INTERFACES] JDBC and character sets Tatsuo Ishii <t-ishii@sra.co.jp>
1999-06-22 13:54 ` Re: [INTERFACES] JDBC and character sets David Warnock <david@sundayta.co.uk>
1999-06-22 14:13 ` Re: [INTERFACES] JDBC and character sets Tatsuo Ishii <t-ishii@sra.co.jp>
1999-06-22 14:36 ` Re: [INTERFACES] JDBC and character sets David Warnock <david@sundayta.co.uk>
@ 1999-06-22 14:47 ` Tatsuo Ishii <t-ishii@sra.co.jp>
0 siblings, 0 replies; 14+ messages in thread
From: Tatsuo Ishii @ 1999-06-22 14:47 UTC (permalink / raw)
To: David Warnock <david@sundayta.co.uk>; +Cc: pgsql-interfaces
>Many thanks for all your answers. We will be giving postgresql a full
>trial soon and then hopefully the last fully closed source component of
>our own software will be gone so we can look at releasing more than just
>tools as open source.
It's my pleasure to support you regarding Unicode. There seems very
few people who try to use MB+Unicode. Please let me know if you have
questions or problems so that I could enhance MB/Unicode support!
regards,
--
Tatsuo Ishii
^ permalink raw reply [nested|flat] 14+ messages in thread
* RE: [INTERFACES] JDBC and character sets
@ 1999-06-22 15:08 Peter Mount <petermount@it.maidstone.gov.uk>
1999-06-22 15:22 ` Re: [INTERFACES] JDBC and character sets David Warnock <david@sundayta.co.uk>
1999-06-22 15:45 ` Re: [INTERFACES] JDBC and character sets Herouth Maoz <herouth@oumail.openu.ac.il>
0 siblings, 2 replies; 14+ messages in thread
From: Peter Mount @ 1999-06-22 15:08 UTC (permalink / raw)
To: 't-ishii@sra.co.jp' <t-ishii@sra.co.jp>; David Warnock <david@sundayta.co.uk>; +Cc: pgsql-interfaces
If I understand this correctly, if I make sure the driver converts the
strings (in the correct methods) into UTF-8, then unicode support will
work?
I'm wondering, as I haven't delved into Unicode with the driver yet. If
this is the case, it will be a simple thing to implement.
Peter
--
Peter Mount
Enterprise Support
Maidstone Borough Council
Any views stated are my own, and not those of Maidstone Borough Council.
-----Original Message-----
From: Tatsuo Ishii [mailto:t-ishii@sra.co.jp]
Sent: Tuesday, June 22, 1999 3:14 PM
To: David Warnock
Cc: t-ishii@sra.co.jp; pgsql-interfaces@postgreSQL.org
Subject: Re: [INTERFACES] JDBC and character sets
>I think I saw from the list of developers that you wrote a lot of the
>multiu-byte code. Is that correct? If so my grateful thanks.
Yes, I'm responsible for the code multi-byte.
>Are there any limitations or gotchas about using unicode everywhere?
>
>Specifically
>
>1. Column length. Is this measured in unicode characters or do I need
to
>increase the length of Varchars? ie is a varchar(10) certain to hold 10
>unicode characters?
When you define varchar(n), n should be counted in bytes, not
characters.
We assume Unicode is input as UTF-8 encoding. In UTF-8, 10 ASCII chars
take 10 bytes. So varchar(10) will hold 10 Unicode chars if they are
all ASCII. However, if you use ISO8859 chars they will take 2 bytes
for each letter. If you use KANJI, 3 bytes for each letter. You could
use octet_length() to measure the size of a Unicode string in bytes.
>2. Indexing. What sort order will I get from an index or an order by
for
>unicode characters.
It will sorted in the order of Unicode code point.
>Can this be customised.
Currently no.
>Generally I try to do any
>really important sorting in Java where I can use the correct sort order
>for the locale.
>3. Upper/lowercase. I have been using separate columns for uppercase
>versions of names etc again so that the case changes can be done by the
>client which will know the correct rules for the locale where the data
>is entered. What do upper/lower case functions in Postgresql do with
>unicode?
I think it will related to locale. I'm not sure but I've heard about
Unicode locale. If it really exists, you could do:
configure --with-mb=UNICODE --with-locale
so that upper/lower works for Unicode.
>4. Are there any limitations on what I use to write triggers? Can all
>the different ways work reliably with unicode?
I'm not sure but it should work with triggers.
--
Tatsuo Ishii
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [INTERFACES] JDBC and character sets
1999-06-22 15:08 RE: [INTERFACES] JDBC and character sets Peter Mount <petermount@it.maidstone.gov.uk>
@ 1999-06-22 15:22 ` David Warnock <david@sundayta.co.uk>
1 sibling, 0 replies; 14+ messages in thread
From: David Warnock @ 1999-06-22 15:22 UTC (permalink / raw)
To: Peter Mount <petermount@it.maidstone.gov.uk>; +Cc: 't-ishii@sra.co.jp' <t-ishii@sra.co.jp>; pgsql-interfaces
Peter,
> If I understand this correctly, if I make sure the driver converts the
> strings (in the correct methods) into UTF-8, then unicode support will
> work?
>
> I'm wondering, as I haven't delved into Unicode with the driver yet. If
> this is the case, it will be a simple thing to implement.
Ooooh yes please. This would be a great thing for us. If you need any
help testing please tell us.
Regards
Dave
--
David Warnock
Sundayta Ltd
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [INTERFACES] JDBC and character sets
1999-06-22 15:08 RE: [INTERFACES] JDBC and character sets Peter Mount <petermount@it.maidstone.gov.uk>
@ 1999-06-22 15:45 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1 sibling, 0 replies; 14+ messages in thread
From: Herouth Maoz @ 1999-06-22 15:45 UTC (permalink / raw)
To: Peter Mount <petermount@it.maidstone.gov.uk>; +Cc: pgsql-interfaces
At 18:22 +0300 on 22/06/1999, David Warnock wrote:
> > If I understand this correctly, if I make sure the driver converts the
> > strings (in the correct methods) into UTF-8, then unicode support will
> > work?
> >
> > I'm wondering, as I haven't delved into Unicode with the driver yet. If
> > this is the case, it will be a simple thing to implement.
>
> Ooooh yes please. This would be a great thing for us. If you need any
> help testing please tell us.
Well, what if we haven't compiled the system for unicode? Make sure the
driver doesn't kill us in such a case. We use ISO8859-8 for our data. If
you were to convert it to utf-8, it would certainly not work as we expect.
Especially if the database is not compiled for unicode.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 14+ messages in thread
* RE: [INTERFACES] JDBC and character sets
@ 1999-06-22 15:55 Peter Mount <petermount@it.maidstone.gov.uk>
1999-06-22 16:45 ` Re: [INTERFACES] JDBC and character sets David Warnock <david@sundayta.co.uk>
1999-06-23 01:31 ` Re: [INTERFACES] JDBC and character sets Tatsuo Ishii <t-ishii@sra.co.jp>
0 siblings, 2 replies; 14+ messages in thread
From: Peter Mount @ 1999-06-22 15:55 UTC (permalink / raw)
To: 'Herouth Maoz' <herouth@oumail.openu.ac.il>; Peter Mount <petermount@it.maidstone.gov.uk>; +Cc: pgsql-interfaces
That's my main worry, and one of the reasons I haven't really looked at
it yet.
What we would need is some way for the client to see if the backend was
compiled for unicode, and enable that functionality if it is, or default
to ASCII if not.
Peter
--
Peter Mount
Enterprise Support
Maidstone Borough Council
Any views stated are my own, and not those of Maidstone Borough Council.
-----Original Message-----
From: Herouth Maoz [mailto:herouth@oumail.openu.ac.il]
Sent: Tuesday, June 22, 1999 4:45 PM
To: Peter Mount
Cc: pgsql-interfaces@postgreSQL.org
Subject: Re: [INTERFACES] JDBC and character sets
At 18:22 +0300 on 22/06/1999, David Warnock wrote:
> > If I understand this correctly, if I make sure the driver converts
the
> > strings (in the correct methods) into UTF-8, then unicode support
will
> > work?
> >
> > I'm wondering, as I haven't delved into Unicode with the driver yet.
If
> > this is the case, it will be a simple thing to implement.
>
> Ooooh yes please. This would be a great thing for us. If you need any
> help testing please tell us.
Well, what if we haven't compiled the system for unicode? Make sure the
driver doesn't kill us in such a case. We use ISO8859-8 for our data. If
you were to convert it to utf-8, it would certainly not work as we
expect.
Especially if the database is not compiled for unicode.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [INTERFACES] JDBC and character sets
1999-06-22 15:55 RE: [INTERFACES] JDBC and character sets Peter Mount <petermount@it.maidstone.gov.uk>
@ 1999-06-22 16:45 ` David Warnock <david@sundayta.co.uk>
1 sibling, 0 replies; 14+ messages in thread
From: David Warnock @ 1999-06-22 16:45 UTC (permalink / raw)
To: Peter Mount <petermount@it.maidstone.gov.uk>; +Cc: 'Herouth Maoz' <herouth@oumail.openu.ac.il>; pgsql-interfaces
Peter,
Obviously I agree 100% that supporting unicode should not break existing
applications or code.
How about either a extra parameter to the creation of a connection?
If it is easier to keep this very separate I don't mind a completely
separate jar file/driver at least to start with. After all most people
won't yet be using this feature so why bloat their driver?
Dave
--
David Warnock
Sundayta Ltd
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [INTERFACES] JDBC and character sets
1999-06-22 15:55 RE: [INTERFACES] JDBC and character sets Peter Mount <petermount@it.maidstone.gov.uk>
@ 1999-06-23 01:31 ` Tatsuo Ishii <t-ishii@sra.co.jp>
1 sibling, 0 replies; 14+ messages in thread
From: Tatsuo Ishii @ 1999-06-23 01:31 UTC (permalink / raw)
To: Peter Mount <petermount@it.maidstone.gov.uk>; +Cc: 'Herouth Maoz' <herouth@oumail.openu.ac.il>; pgsql-interfaces
> That's my main worry, and one of the reasons I haven't really looked at
> it yet.
>
> What we would need is some way for the client to see if the backend was
> compiled for unicode, and enable that functionality if it is, or default
> to ASCII if not.
You could issue "select getdatabaseencoding()" to judge for that.
1) if that fails, the backend is not compiled with MB support.
2) if it returns other than "UNICODE", the backend is compiled with MB
but the encoding for the database is not Unicode. (Note that in MB,
the encoding for a database can be decided at its creation time. So it
may not always be UNICODE even if you comiled with --with-mb=UNICODE).
Possible encoding returned by the function would be:
SQL_ASCII ASCII
EUC_JP Japanese EUC
EUC_CN Chinese EUC
EUC_KR Korean EUC
EUC_TW Taiwan EUC
UNICODE Unicode(UTF-8)
MULE_INTERNAL Mule internal
LATIN1 ISO 8859-1 English and some European languages
LATIN2 ISO 8859-2 English and some European languages
LATIN3 ISO 8859-3 English and some European languages
LATIN4 ISO 8859-4 English and some European languages
LATIN5 ISO 8859-5 English and some European languages
KOI8 KOI8-R
WIN CP1251
ALT CP866
See doc/README.mb for more details.
---
Tatsuo Ishii
^ permalink raw reply [nested|flat] 14+ messages in thread
* RE: [INTERFACES] JDBC and character sets
@ 1999-06-23 07:11 Peter Mount <petermount@it.maidstone.gov.uk>
1999-06-23 12:37 ` Re: [INTERFACES] JDBC and character sets David Warnock <david@sundayta.co.uk>
0 siblings, 1 reply; 14+ messages in thread
From: Peter Mount @ 1999-06-23 07:11 UTC (permalink / raw)
To: 'David Warnock' <david@sundayta.co.uk>; Peter Mount <petermount@it.maidstone.gov.uk>; +Cc: 'Herouth Maoz' <herouth@oumail.openu.ac.il>; pgsql-interfaces
It shouldn't bloat the driver by that much. As we already issue a few
queries on startup, I'll add a suggested test, and if that works ok, I
should have UNICODE support done by the end of the weekend.
Peter
--
Peter Mount
Enterprise Support
Maidstone Borough Council
Any views stated are my own, and not those of Maidstone Borough Council.
-----Original Message-----
From: David Warnock [mailto:david@sundayta.co.uk]
Sent: Tuesday, June 22, 1999 5:46 PM
To: Peter Mount
Cc: 'Herouth Maoz'; pgsql-interfaces@postgreSQL.org
Subject: Re: [INTERFACES] JDBC and character sets
Peter,
Obviously I agree 100% that supporting unicode should not break existing
applications or code.
How about either a extra parameter to the creation of a connection?
If it is easier to keep this very separate I don't mind a completely
separate jar file/driver at least to start with. After all most people
won't yet be using this feature so why bloat their driver?
Dave
--
David Warnock
Sundayta Ltd
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: [INTERFACES] JDBC and character sets
1999-06-23 07:11 RE: [INTERFACES] JDBC and character sets Peter Mount <petermount@it.maidstone.gov.uk>
@ 1999-06-23 12:37 ` David Warnock <david@sundayta.co.uk>
0 siblings, 0 replies; 14+ messages in thread
From: David Warnock @ 1999-06-23 12:37 UTC (permalink / raw)
To: Peter Mount <petermount@it.maidstone.gov.uk>; +Cc: pgsql-interfaces
Peter,
My oh my isn't free software awesome.
Many thanks
Dave
> It shouldn't bloat the driver by that much. As we already issue a few
> queries on startup, I'll add a suggested test, and if that works ok, I
> should have UNICODE support done by the end of the weekend.
--
David Warnock
Sundayta Ltd
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~1999-06-23 12:37 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-06-22 10:18 JDBC and character sets David Warnock <david@sundayta.co.uk>
1999-06-22 12:07 ` Tatsuo Ishii <t-ishii@sra.co.jp>
1999-06-22 13:54 ` David Warnock <david@sundayta.co.uk>
1999-06-22 14:13 ` Tatsuo Ishii <t-ishii@sra.co.jp>
1999-06-22 14:36 ` David Warnock <david@sundayta.co.uk>
1999-06-22 14:47 ` Tatsuo Ishii <t-ishii@sra.co.jp>
1999-06-22 15:08 RE: [INTERFACES] JDBC and character sets Peter Mount <petermount@it.maidstone.gov.uk>
1999-06-22 15:22 ` David Warnock <david@sundayta.co.uk>
1999-06-22 15:45 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-06-22 15:55 RE: [INTERFACES] JDBC and character sets Peter Mount <petermount@it.maidstone.gov.uk>
1999-06-22 16:45 ` David Warnock <david@sundayta.co.uk>
1999-06-23 01:31 ` Tatsuo Ishii <t-ishii@sra.co.jp>
1999-06-23 07:11 RE: [INTERFACES] JDBC and character sets Peter Mount <petermount@it.maidstone.gov.uk>
1999-06-23 12:37 ` David Warnock <david@sundayta.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox