Re: JPOX Types.CHAR error

> A quick check reveals nothing obviously wrong with how
> DatabaseMetaData.getTypeInfo returns data for Types.CHAR.

Well that depends on your point of view ;-)
PSQL 8.1-405 JDBC driver now returns SQL type "char" as Types.OTHER (instead
of Types.CHAR), and returns "bpchar" as Types.CHAR. Since PSQL has always
supported "char" as Types.CHAR (since it kinda seems reasonable), JPOX uses
that as the default ... and it is no longer there (yet is in PSQL 8.0). I can
set up JPOX to fake this type for users so they see no change but it would
make more sense (to me) to have the JDBC driver return consistent with
previous incarnations. Maybe there was a good reason why "char" is no longer
Types.CHAR ?

--
Andy

On Tue, 16 May 2006, Andy Jefferson wrote:

>> A quick check reveals nothing obviously wrong with how
>> DatabaseMetaData.getTypeInfo returns data for Types.CHAR.
>
> Well that depends on your point of view ;-)
> PSQL 8.1-405 JDBC driver now returns SQL type "char" as Types.OTHER (instead
> of Types.CHAR), and returns "bpchar" as Types.CHAR. Since PSQL has always
> supported "char" as Types.CHAR (since it kinda seems reasonable), JPOX uses
> that as the default ... and it is no longer there (yet is in PSQL 8.0). I can
> set up JPOX to fake this type for users so they see no change but it would
> make more sense (to me) to have the JDBC driver return consistent with
> previous incarnations. Maybe there was a good reason why "char" is no longer
> Types.CHAR ?
>

I'm not sure there's a good reason that "char" isn't returned as
Types.CHAR, but you definitely want to be using bpchar as the default
mapping for Types.CHAR. The type "char" is a single byte datatype
that is really only used for internal system catalogs and is not to be
confused with char(N). One of the bugs you're probably seeing is
that the postgresql jdbc driver does not return the results of
DatabaseMetaData.getTypeInfo sorted according to the javadoc spec of
closest match, but instead returns data in random order.

jurka=# create table chartest (a "char", b char, c char(5));
CREATE TABLE
jurka=# \d chartest
Table "public.chartest"
Column | Type | Modifiers
--------+--------------+-----------
a | "char" |
b | character(1) |
c | character(5) |
jurka=# select a.attname,t.typname FROM pg_attribute a, pg_type t where
a.atttypid = t.oid and a.attrelid = 'chartest'::regclass::oid and a.attnum
> 0;
attname | typname
---------+---------
a | char
c | bpchar
b | bpchar
(3 rows)

Kris Jurka

> I'm not sure there's a good reason that "char" isn't returned as
> Types.CHAR, but you definitely want to be using bpchar as the default
> mapping for Types.CHAR.

Thanks for the reply Kris. Only one question. Has "bpchar" existed in
Postgresql for some time (e.g versions 7.0 onwards) or is it recent ? We
(JPOX) need to support many versions of Postgresql and I don't want to change
historical support here.

--
Andy

On Tuesday 16 May 2006 10:57, Andy Jefferson wrote:
> > I'm not sure there's a good reason that "char" isn't returned as
> > Types.CHAR, but you definitely want to be using bpchar as the default
> > mapping for Types.CHAR.
>
> Thanks for the reply Kris. Only one question. Has "bpchar" existed in
> Postgresql for some time (e.g versions 7.0 onwards) or is it recent ? We
> (JPOX) need to support many versions of Postgresql and I don't want to
> change historical support here.

Also, I cannot do bpchar(100) whereas I can do char(100) ... the precision
returned by getTypeInfo is limited to 9 for bpchar whereas is 65000 for char.

--
Andy

> Also, I cannot do bpchar(100) whereas I can do char(100) ... the precision
> returned by getTypeInfo is limited to 9 for bpchar whereas is 65000 for
> char.

Ignore that. All Postgresql types returned by getTypeInfo() return the
precision as 9 ... whatever that is supposed to mean. Any chance that
Postgresql JDBC will one day return correct info for the precision ? It would
make life a hell of a lot easier for systems that use the results of its JDBC
method calls.

--
Andy

On Tue, 16 May 2006, Andy Jefferson wrote:

>> I'm not sure there's a good reason that "char" isn't returned as
>> Types.CHAR, but you definitely want to be using bpchar as the default
>> mapping for Types.CHAR.
>
> Thanks for the reply Kris. Only one question. Has "bpchar" existed in
> Postgresql for some time (e.g versions 7.0 onwards) or is it recent ? We
> (JPOX) need to support many versions of Postgresql and I don't want to change
> historical support here.
>

bpchar is available in all versions from 7.0 on.

Kris Jurka

On Tue, 16 May 2006, Andy Jefferson wrote:

>> Also, I cannot do bpchar(100) whereas I can do char(100) ... the precision
>> returned by getTypeInfo is limited to 9 for bpchar whereas is 65000 for
>> char.
>
> Ignore that. All Postgresql types returned by getTypeInfo() return the
> precision as 9 ... whatever that is supposed to mean. Any chance that
> Postgresql JDBC will one day return correct info for the precision ? It
> would make life a hell of a lot easier for systems that use the results
> of its JDBC method calls.
>

Improvements have already been made in this are for the 8.2 driver (which
can be used against older database versions). It currently reports
10485760 as the precision for bpchar.

The complication on your part is that bpchar is kind of an internal type
name and you've already noticed that you can't say CREATE TABLE t (a
bpchar(5)). The grammar only supports the precision attribute on the
external spelling of the type name, char or character. I'm not sure how
the pg jdbc driver can help you know this.

Kris Jurka