Обсуждение: Wrong SqlType for boolean columns

Hello everyone,

i found out that my postgres driver maps a boolean column to the sqlType -7, wich is java.sql.Types.BIT and not
java.sql.Types.Boolean(Boolean is 16). This is a very strange behavior.  

I think this is a bug within the jdbc-driver.

link to JavaDoc:
http://java.sun.com/javase/6/docs/api/constant-values.html#java.sql.Types.BOOLEAN

the code i used to test this:

//returns a connection to the database
Connection con = ConnectionManager.getConnection();
PreparedStatement prep = con.prepareStatement("select isboolean from test");
ResultSet rs = prep.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
System.out.println(rsmd.getColumnType(1));
System.out.println(rsmd.getColumnTypeName(1));
rs.close();
prep.close();
con.close();

definition of table:
CREATE TABLE test
(
  id serial NOT NULL,
  "name" character varying NOT NULL DEFAULT ''::character varying,
  "number" integer DEFAULT 1,
  date date DEFAULT now(),
  isboolean boolean DEFAULT true,
  CONSTRAINT test_pkey PRIMARY KEY (id)
)

the output of this program is:
-7
bool

but it should be:
16
bool

I tested this with:
postgresql-8.3-605.jdbc3.jar
postgresql-8.4-701.jdbc3.jar
postgresql-8.4-701.jdbc4.jar

Server is PostgreSQL 8.4.4 on x86_64-pc-linux-gnu by gcc4-4.real (Ubuntu)...

Im using java-sdk 6.

greetings
Michael
--
Neu: GMX De-Mail - Einfach wie E-Mail, sicher wie ein Brief!
Jetzt De-Mail-Adresse reservieren: http://portal.gmx.net/de/go/demail

fortunasliebling@gmx.de wrote:

> i found out that my postgres driver maps a boolean column to the sqlType -7, wich is java.sql.Types.BIT and not
java.sql.Types.Boolean(Boolean is 16). This is a very strange behavior.  
>
> I think this is a bug within the jdbc-driver.

It is not a bug. BOOLEAN does not exist in earlier JDBC versions, and
drivers can map boolean types to either BIT or BOOLEAN. We pick BIT so
we can build drivers for earlier JDBC versions. (BIT is something of a
confusing type, as the mappings defined by JDBC for the JDBC BIT type
only really make sense for a single boolean value, not a SQL BIT(n) type)

See the datatype mapping tables in Appendix B of the JDBC spec.

I agree that it is confusing, but that is just a reflection of the JDBC
spec being confusing..

-O

Oliver Jowett, 28.07.2010 10:59:
>> i found out that my postgres driver maps a boolean column to the
>> sqlType -7, wich is java.sql.Types.BIT and not
>> java.sql.Types.Boolean (Boolean is 16). This is a very strange
>> behavior.
>>
>
> It is not a bug. BOOLEAN does not exist in earlier JDBC versions,
> and drivers can map boolean types to either BIT or BOOLEAN. We pick
> BIT so we can build drivers for earlier JDBC versions. (BIT is
> something of a confusing type, as the mappings defined by JDBC for
> the JDBC BIT type only really make sense for a single boolean value,
> not a SQL BIT(n) type)


Wouldn't it make sense to map this to BOOLEAN if the driver is built for JDBC4 and to BIT if is built for JDBC3?

Regards
Thomas

Thomas Kellerer wrote:

> Wouldn't it make sense to map this to BOOLEAN if the driver is built for
> JDBC4 and to BIT if is built for JDBC3?

IIRC, it gets a bit awkward because the mappings aren't in
JDBC-version-specific code.

Applications should really be prepared to deal with BIT, anyway..

-O

On Wed, 28 Jul 2010, Thomas Kellerer wrote:

> Oliver Jowett, 28.07.2010 10:59:
>> It is not a bug. BOOLEAN does not exist in earlier JDBC versions,
>> and drivers can map boolean types to either BIT or BOOLEAN. We pick
>> BIT so we can build drivers for earlier JDBC versions. (BIT is
>> something of a confusing type, as the mappings defined by JDBC for
>> the JDBC BIT type only really make sense for a single boolean value,
>> not a SQL BIT(n) type)
>
> Wouldn't it make sense to map this to BOOLEAN if the driver is built for
> JDBC4 and to BIT if is built for JDBC3?
>

Actually BIT is JDBC2 and BOOLEAN is JDBC3.  Since we no longer build
JDBC2 releases for the latest drivers, we could make this change, I'm just
not sure I see the point.  I guess we'd get fewer of these complaints.

Kris Jurka