Thread: BUG #3751: Conversion error using PreparedStatement.setObject()

BUG #3751: Conversion error using PreparedStatement.setObject()

From
"Lance Andersen"
Date:
The following bug has been logged online:

Bug reference:      3751
Logged by:          Lance Andersen
Email address:      lance.andersen@sun.com
PostgreSQL version: 8.2.x
Operating system:   N/A
Description:        Conversion error using PreparedStatement.setObject()
Details:

A PreparedStatement.setObject(1, "1", Types.Bit) will fail with the
following Exception:

org.postgresql.util.PSQLException: ERROR: column "max_val" is of type bit
but expression is of type integer
        at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorI
mpl.java:1548)
        at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.ja
va:1316)
        at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)

        at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.j
ava:452)
        at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2St
atement.java:337)
        at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2State
ment.java:283)
        at com.sun.jdbc.samples.BitTest.testSetObject48(BitTest.java:108)
        at com.sun.jdbc.samples.BitTest.runTest(BitTest.java:61)
        at com.sun.jdbc.samples.BitTest.main(BitTest.java:16)

Here is a simple repro:

package com.sun.jdbc.samples;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.math.BigDecimal;
import java.math.*;
import java.util.StringTokenizer;


public class BitTest {
    List<Drivers>  drivers;

    public static void main(String args[]){
        BitTest test = new BitTest();
        test.runTest();

    }

    public BitTest() {
        drivers = new ArrayList<Drivers>();



        // Postgresql Drivers
        drivers.add(new Drivers("org.postgresql.Driver",
"jdbc:postgresql://jsepc18.east:5432/CTS5", "cts1", "cts1" ));



    }

    public void runTest() {
        Connection conn = null;

        for(Drivers driver : drivers){
            try {
                Class.forName(driver.getDriver());
                conn = DriverManager.getConnection(driver.getUrl(),
driver.getUser(), driver.getPassword());
                DatabaseMetaData dbmeta = conn.getMetaData();
                dumpVersionInfo(dbmeta);
                testSetObject48(conn);
                conn.close();
            }catch( Exception e ) {
                e.printStackTrace();
            }

        }



    }


    public static void dumpVersionInfo(DatabaseMetaData dbmeta) {

        try {

System.out.println("\n***********************************************");

            String productName = dbmeta.getDatabaseProductName();
            String productVersion = dbmeta.getDatabaseProductVersion();
            String driverName = dbmeta.getDriverName();
            String driverVersion = dbmeta.getDriverVersion();

            System.out.println("productName: " + productName);
            System.out.println("productVersion: " + productVersion);
            System.out.println("driverName: " + driverName);
            System.out.println("Version: " + driverVersion);


System.out.println("***********************************************");

        } catch( Exception e ) {
            e.printStackTrace();
        }
    }

    private static void testSetObject48(Connection conn) {
        ResultSet rs;
        String Min_Val_Query= "SELECT MIN_VAL from Bit_Tab";
        String sMaxBooleanVal = "1";
        //sMaxBooleanVal = "true";
        Boolean bool = Boolean.valueOf("true");
        String Min_Insert= "insert into Bit_Tab values(1,0,null)";
        //System.out.println("Value to insert=" +
extractVal(Min_Insert,1));
        CallableStatement cstmt;
        try {
            Statement stmt = conn.createStatement();
            stmt.executeUpdate("delete from Bit_Tab");
            stmt.executeUpdate(Min_Insert);
            cstmt = conn.prepareCall("{call Bit_In_Min(?)}");
            cstmt.setObject(1,sMaxBooleanVal,java.sql.Types.BIT);
            //cstmt.setObject(1,bool,java.sql.Types.BIT);
            cstmt.executeUpdate();

            rs = stmt.executeQuery(Min_Val_Query);
            while(rs.next()){
                System.out.println("Expected value=" + sMaxBooleanVal + ",
Returned value as Boolean= " +rs.getBoolean(1)
                + ", as String=" + rs.getString(1));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }


    }



}

Re: BUG #3751: Conversion error using PreparedStatement.setObject()

From
Kris Jurka
Date:
On Thu, 15 Nov 2007, Lance Andersen wrote:

>
> The following bug has been logged online:
>
> Bug reference:      3751
> PostgreSQL version: 8.2.x
> Description:        Conversion error using PreparedStatement.setObject()
> Details:
>
> A PreparedStatement.setObject(1, "1", Types.Bit) will fail with the
> following Exception:

This is not a great test case.

1) It doesn't contain the definition of the Drivers class so it doesn't
compile.  The whole drivers class and dumping metadata is needless
complication for a simple test case.

2) It doesn't contain the definition of the bit_tab table, so it doesn't
run.

3) The error is actually coming from "stmt.executeUpdate(Min_Insert)", not
a PreparedStatement.

So where does that leave us?

1) The raw insert fails.  INSERT INTO bit_tab (1,0,null) fails because 1
gets typed as an integer and there are no implicit or assignment casts
from integer to bit.  You would need to say, '1' so it comes in untyped
and converted to bit, or be explicit about the type with a cast, saying
1::bit or CAST(1 AS bit).

2) There might be a problem with bit conversion in prepared statements,
but we didn't get that far.

Other notes:

In PG the bit type is really for multiple bits, not a single
bit.  Consider SELECT 77::bit(8) results in "01001101".  It's more
likely that you want to use boolean as the type instead although it
doesn't have any casts that will help you out in this situation either.

Kris Jurka

Re: BUG #3751: Conversion error using PreparedStatement.setObject()

From
Kris Jurka
Date:
The test runs for me when I change all of the underlying types from bit
to boolean:

create table Bit_Tab (MAX_VAL boolean, MIN_VAL boolean, NULL_VAL boolean
NULL) ;

CREATE OR REPLACE FUNCTION Bit_In_Min (MIN_PARAM boolean) returns void as
'begin update Bit_Tab set MIN_VAL=MIN_PARAM; end;' language 'plpgsql' ;

Kris Jurka

Lance J. Andersen wrote:
> Sorry Bad, Cut and paste.  This test is a strip down of much larger
> test.  The reason the metadata is there as this gets run from a
> framework which exercises JDBC drivers from all of the major vendors
> which is also the reason for the Drivers class.
>
>
> As far as the INSERT,  i did not look at the postgresql docs in enough
> detail  probably given that it works against all of the other vendors
> who support BIT data types, so my mistake.
>
> Here is the the entire scenario:
>
> The table is created as
>
> create table Bit_Tab (MAX_VAL bit(1), MIN_VAL bit(1), NULL_VAL bit(1)
> NULL) ;
>
> and the stored procedure via
>
>
> CREATE OR REPLACE FUNCTION Bit_In_Min (MIN_PARAM bit(1)) returns void as
> 'begin update Bit_Tab set MIN_VAL=MIN_PARAM; end;' language 'plpgsql' ;
>
>
>
>
> even if i change the insert as you suggest, to
>
> insert into Bit_Tab values('1', '0', null )
>
> it still fails
>
> org.postgresql.util.PSQLException: ERROR: column "min_val" is of type
> bit but expression is of type boolean
>        at
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
>
>        at
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
>
>        at
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
>
>        at
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
>
>        at
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:351)
>
>        at
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:302)
>
>        at com.sun.jdbc.samples.BitTest.testSetObject48(BitTest.java:93)
>        at com.sun.jdbc.samples.BitTest.runTest(BitTest.java:41)
>        at com.sun.jdbc.samples.BitTest.main(BitTest.java:16)
> BUILD SUCCESSFUL (total time: 2 seconds)
>
>
> The failure now is on cstmt.executeUpdate() which i would infer either
> the driver is not doing the proper conversion or the function is having
> issues.
>
> The test is validating that a String can be sent as a BIT and returned
> as a Boolean per the JDBC specifcation.
>
> -lance
>
>
>
>
>
> Kris Jurka wrote:
>>
>>
>> On Thu, 15 Nov 2007, Lance Andersen wrote:
>>
>>>
>>> The following bug has been logged online:
>>>
>>> Bug reference:      3751
>>> PostgreSQL version: 8.2.x
>>> Description:        Conversion error using PreparedStatement.setObject()
>>> Details:
>>>
>>> A PreparedStatement.setObject(1, "1", Types.Bit) will fail with the
>>> following Exception:
>>
>> This is not a great test case.
>>
>> 1) It doesn't contain the definition of the Drivers class so it
>> doesn't compile.  The whole drivers class and dumping metadata is
>> needless complication for a simple test case.
>>
>> 2) It doesn't contain the definition of the bit_tab table, so it
>> doesn't run.
>>
>> 3) The error is actually coming from "stmt.executeUpdate(Min_Insert)",
>> not a PreparedStatement.
>>
>> So where does that leave us?
>>
>> 1) The raw insert fails.  INSERT INTO bit_tab (1,0,null) fails because
>> 1 gets typed as an integer and there are no implicit or assignment
>> casts from integer to bit.  You would need to say, '1' so it comes in
>> untyped and converted to bit, or be explicit about the type with a
>> cast, saying
>> 1::bit or CAST(1 AS bit).
>>
>> 2) There might be a problem with bit conversion in prepared
>> statements, but we didn't get that far.
>>
>> Other notes:
>>
>> In PG the bit type is really for multiple bits, not a single bit.
>> Consider SELECT 77::bit(8) results in "01001101".  It's more likely
>> that you want to use boolean as the type instead although it doesn't
>> have any casts that will help you out in this situation either.
>>
>> Kris Jurka

Re: BUG #3751: Conversion error using PreparedStatement.setObject()

From
Kris Jurka
Date:
Lance J. Andersen wrote:
> Thank you for your time, but i think there is still a driver issue here:
>
>
> If i use the same  types as i sent in the email and execute
>
> select * from  bit_in_min(1::bit)
>
> I have no problems and the table is correctly updated.
>
> This would lead me to believe that the driver has a problem with
> correctly mapping the setObect() of the String to a BIT which is
> required conversion by the JDBC spec.
>

According to our reading of the JDBC spec java.sql.Types.BIT and BOOLEAN
are equivalent.  So it doesn't make sense to map BIT to one server type
and BOOLEAN to another.  When thinking about Types.BIT it's easy to see
a server type named "bit" and assume it's a match, but for the semantics
of Types.BIT, we think boolean is a better match.

http://archives.postgresql.org/pgsql-jdbc/2004-04/msg00107.php

Just because "bit" can be made to work doesn't make it the best option.
  Particularly our concern arises from what to do when returning meta
data.  Consider a table that has columns "a bit(1), b bit(2)".  While a
does have boolean semantics because it's limited to a length of 1, b is
not.  Returning b as Types.BIT would be a mistake.  Driving this
decision solely on the length attribute is not good because the server
will not give you the length information in certain circumstances and
all you'll have is the raw "bit" type name.  This is why we've chose to
use boolean as the server type for Types.BIT + BOOLEAN.

Kris Jurka

Re: BUG #3751: Conversion error using PreparedStatement.setObject()

From
"Lance J. Andersen"
Date:
Sorry Bad, Cut and paste.  This test is a strip down of much larger
test.  The reason the metadata is there as this gets run from a
framework which exercises JDBC drivers from all of the major vendors
which is also the reason for the Drivers class.


As far as the INSERT,  i did not look at the postgresql docs in enough
detail  probably given that it works against all of the other vendors
who support BIT data types, so my mistake.

Here is the the entire scenario:

The table is created as

create table Bit_Tab (MAX_VAL bit(1), MIN_VAL bit(1), NULL_VAL bit(1) NULL) ;

and the stored procedure via


CREATE OR REPLACE FUNCTION Bit_In_Min (MIN_PARAM bit(1)) returns void as 'begin update Bit_Tab set MIN_VAL=MIN_PARAM;
end;'language 'plpgsql' ; 




even if i change the insert as you suggest, to

insert into Bit_Tab values('1', '0', null )

it still fails

org.postgresql.util.PSQLException: ERROR: column "min_val" is of type
bit but expression is of type boolean
        at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
        at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
        at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
        at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
        at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:351)
        at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:302)
        at com.sun.jdbc.samples.BitTest.testSetObject48(BitTest.java:93)
        at com.sun.jdbc.samples.BitTest.runTest(BitTest.java:41)
        at com.sun.jdbc.samples.BitTest.main(BitTest.java:16)
BUILD SUCCESSFUL (total time: 2 seconds)


The failure now is on cstmt.executeUpdate() which i would infer either
the driver is not doing the proper conversion or the function is having
issues.

The test is validating that a String can be sent as a BIT and returned
as a Boolean per the JDBC specifcation.

-lance





Kris Jurka wrote:
>
>
> On Thu, 15 Nov 2007, Lance Andersen wrote:
>
>>
>> The following bug has been logged online:
>>
>> Bug reference:      3751
>> PostgreSQL version: 8.2.x
>> Description:        Conversion error using PreparedStatement.setObject()
>> Details:
>>
>> A PreparedStatement.setObject(1, "1", Types.Bit) will fail with the
>> following Exception:
>
> This is not a great test case.
>
> 1) It doesn't contain the definition of the Drivers class so it
> doesn't compile.  The whole drivers class and dumping metadata is
> needless complication for a simple test case.
>
> 2) It doesn't contain the definition of the bit_tab table, so it
> doesn't run.
>
> 3) The error is actually coming from "stmt.executeUpdate(Min_Insert)",
> not a PreparedStatement.
>
> So where does that leave us?
>
> 1) The raw insert fails.  INSERT INTO bit_tab (1,0,null) fails because
> 1 gets typed as an integer and there are no implicit or assignment
> casts from integer to bit.  You would need to say, '1' so it comes in
> untyped and converted to bit, or be explicit about the type with a
> cast, saying
> 1::bit or CAST(1 AS bit).
>
> 2) There might be a problem with bit conversion in prepared
> statements, but we didn't get that far.
>
> Other notes:
>
> In PG the bit type is really for multiple bits, not a single bit.
> Consider SELECT 77::bit(8) results in "01001101".  It's more likely
> that you want to use boolean as the type instead although it doesn't
> have any casts that will help you out in this situation either.
>
> Kris Jurka

Attachment

Re: BUG #3751: Conversion error using PreparedStatement.setObject()

From
"Lance J. Andersen"
Date:
Thank you for your time, but i think there is still a driver issue here:


If i use the same  types as i sent in the email and execute

select * from  bit_in_min(1::bit)

I have no problems and the table is correctly updated.

This would lead me to believe that the driver has a problem with
correctly mapping the setObect() of the String to a BIT which is
required conversion by the JDBC spec.


Regards
Lance

Kris Jurka wrote:
> The test runs for me when I change all of the underlying types from
> bit to boolean:
>
> create table Bit_Tab (MAX_VAL boolean, MIN_VAL boolean, NULL_VAL
> boolean NULL) ;
>
> CREATE OR REPLACE FUNCTION Bit_In_Min (MIN_PARAM boolean) returns void as
> 'begin update Bit_Tab set MIN_VAL=MIN_PARAM; end;' language 'plpgsql' ;
>
> Kris Jurka
>
> Lance J. Andersen wrote:
>> Sorry Bad, Cut and paste.  This test is a strip down of much larger
>> test.  The reason the metadata is there as this gets run from a
>> framework which exercises JDBC drivers from all of the major vendors
>> which is also the reason for the Drivers class.
>>
>>
>> As far as the INSERT,  i did not look at the postgresql docs in
>> enough detail  probably given that it works against all of the other
>> vendors who support BIT data types, so my mistake.
>>
>> Here is the the entire scenario:
>>
>> The table is created as
>>
>> create table Bit_Tab (MAX_VAL bit(1), MIN_VAL bit(1), NULL_VAL bit(1)
>> NULL) ;
>>
>> and the stored procedure via
>>
>>
>> CREATE OR REPLACE FUNCTION Bit_In_Min (MIN_PARAM bit(1)) returns void
>> as 'begin update Bit_Tab set MIN_VAL=MIN_PARAM; end;' language
>> 'plpgsql' ;
>>
>>
>>
>>
>> even if i change the insert as you suggest, to
>>
>> insert into Bit_Tab values('1', '0', null )
>>
>> it still fails
>>
>> org.postgresql.util.PSQLException: ERROR: column "min_val" is of type
>> bit but expression is of type boolean
>>        at
>> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
>>
>>        at
>> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
>>
>>        at
>> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
>>
>>        at
>> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
>>
>>        at
>> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:351)
>>
>>        at
>> org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:302)
>>
>>        at com.sun.jdbc.samples.BitTest.testSetObject48(BitTest.java:93)
>>        at com.sun.jdbc.samples.BitTest.runTest(BitTest.java:41)
>>        at com.sun.jdbc.samples.BitTest.main(BitTest.java:16)
>> BUILD SUCCESSFUL (total time: 2 seconds)
>>
>>
>> The failure now is on cstmt.executeUpdate() which i would infer
>> either the driver is not doing the proper conversion or the function
>> is having issues.
>>
>> The test is validating that a String can be sent as a BIT and
>> returned as a Boolean per the JDBC specifcation.
>>
>> -lance
>>
>>
>>
>>
>>
>> Kris Jurka wrote:
>>>
>>>
>>> On Thu, 15 Nov 2007, Lance Andersen wrote:
>>>
>>>>
>>>> The following bug has been logged online:
>>>>
>>>> Bug reference:      3751
>>>> PostgreSQL version: 8.2.x
>>>> Description:        Conversion error using
>>>> PreparedStatement.setObject()
>>>> Details:
>>>>
>>>> A PreparedStatement.setObject(1, "1", Types.Bit) will fail with the
>>>> following Exception:
>>>
>>> This is not a great test case.
>>>
>>> 1) It doesn't contain the definition of the Drivers class so it
>>> doesn't compile.  The whole drivers class and dumping metadata is
>>> needless complication for a simple test case.
>>>
>>> 2) It doesn't contain the definition of the bit_tab table, so it
>>> doesn't run.
>>>
>>> 3) The error is actually coming from
>>> "stmt.executeUpdate(Min_Insert)", not a PreparedStatement.
>>>
>>> So where does that leave us?
>>>
>>> 1) The raw insert fails.  INSERT INTO bit_tab (1,0,null) fails
>>> because 1 gets typed as an integer and there are no implicit or
>>> assignment casts from integer to bit.  You would need to say, '1' so
>>> it comes in untyped and converted to bit, or be explicit about the
>>> type with a cast, saying
>>> 1::bit or CAST(1 AS bit).
>>>
>>> 2) There might be a problem with bit conversion in prepared
>>> statements, but we didn't get that far.
>>>
>>> Other notes:
>>>
>>> In PG the bit type is really for multiple bits, not a single bit.
>>> Consider SELECT 77::bit(8) results in "01001101".  It's more likely
>>> that you want to use boolean as the type instead although it doesn't
>>> have any casts that will help you out in this situation either.
>>>
>>> Kris Jurka
>

Re: BUG #3751: Conversion error using PreparedStatement.setObject()

From
"Lance J. Andersen"
Date:
Kris,

Thanks for the follow up.

Kris Jurka wrote:
> Lance J. Andersen wrote:
>> Thank you for your time, but i think there is still a driver issue here:
>>
>>
>> If i use the same  types as i sent in the email and execute
>>
>> select * from  bit_in_min(1::bit)
>>
>> I have no problems and the table is correctly updated.
>>
>> This would lead me to believe that the driver has a problem with
>> correctly mapping the setObect() of the String to a BIT which is
>> required conversion by the JDBC spec.
>>
>
> According to our reading of the JDBC spec java.sql.Types.BIT and
> BOOLEAN are equivalent.  So it doesn't make sense to map BIT to one
> server type and BOOLEAN to another.  When thinking about Types.BIT
> it's easy to see a server type named "bit" and assume it's a match,
> but for the semantics of Types.BIT, we think boolean is a better match.
The table in this case that needs to be looked at is B-5, not B-4 which
i believe you are referring to.  The expectation of
setObject(int/String, Object, int)  is that you are sending the value to
the backend as the SQL Type specified for the 3rd parameter, in this
case a BIT.

I would expect that the driver can do the conversion in this case
because i have the table created with a BIT(1) column and the backend
supports both BIT and BOOLEAN.

13.2.2.2 of the JDBC 3.0 and 13.2.2.3 of  the JDBC 4.0 spec tries to
make this clear that an explicit conversion is required.

For the JDBC 4.1 spec, i plan to add additional clarifications, but i
would expect the setObject() call in the test to succeed based on the
current spec.


>
> http://archives.postgresql.org/pgsql-jdbc/2004-04/msg00107.php
>
> Just because "bit" can be made to work doesn't make it the best
> option.  Particularly our concern arises from what to do when
> returning meta data.
Can you describe this in more detail as I have not noticed a problem via
ResultSetMetaData or DatabaseMetaData.getColumns()  as the columns
indicate they are a BIT with the correct precision.
> Consider a table that has columns "a bit(1), b bit(2)".  While a does
> have boolean semantics because it's limited to a length of 1, b is
> not.  Returning b as Types.BIT would be a mistake.  Driving this
> decision solely on the length attribute is not good because the server
> will not give you the length information in certain circumstances and
> all you'll have is the raw "bit" type name.  This is why we've chose
> to use boolean as the server type for Types.BIT + BOOLEAN.
Given you support both types, I would expect that if a user wanted to
coerce to a given data type that they would use the  3 parameter version
of setObject() and the rules defined in 13.2.2.3  and B-5 would be followed

Regards
Lance
>
> Kris Jurka

Re: BUG #3751: Conversion error using PreparedStatement.setObject()

From
Kris Jurka
Date:
On Fri, 16 Nov 2007, Lance J. Andersen wrote:

> The table in this case that needs to be looked at is B-5, not B-4 which i
> believe you are referring to.  The expectation of setObject(int/String,
> Object, int)  is that you are sending the value to the backend as the SQL
> Type specified for the 3rd parameter, in this case a BIT.
>
> I would expect that the driver can do the conversion in this case because i
> have the table created with a BIT(1) column and the backend supports both BIT
> and BOOLEAN.
>
> 13.2.2.2 of the JDBC 3.0 and 13.2.2.3 of  the JDBC 4.0 spec tries to make
> this clear that an explicit conversion is required.
>
> For the JDBC 4.1 spec, i plan to add additional clarifications, but i would
> expect the setObject() call in the test to succeed based on the current spec.
>

No part of the spec says anything about the server or SQL Type that a Java
type or JDBC type maps to, so your whole argument is predicated on the
fact that java.sql.Types.BIT must match up with the server side bit type,
but my contention is that it doesn't.  The current 2003 sql spec does not
have bit types at all, but the previous versions did.  The 1999 spec has a
"bit" type, but it is a bit string which is what the postgresql server
side type implements, not a single bit.  The best mapping for a bit string
to a Java type is java.util.BitSet, not java.lang.Boolean.

>>
>> Just because "bit" can be made to work doesn't make it the best option.
>> Particularly our concern arises from what to do when returning meta data.
>>
> Can you describe this in more detail as I have not noticed a problem via
> ResultSetMetaData or DatabaseMetaData.getColumns()  as the columns indicate
> they are a BIT with the correct precision.

SELECT '1'::bit || '1'::bit;

SELECT '1'::bit(1) UNION ALL SELECT '11'::bit(2);

CREATE TABLE bittable (a bit(1), b bit(2));
SELECT COALESCE(a, b) FROM bittable;

CREATE TABLE bittable2 (a "bit");
SELECT a FROM bittable2;

None of these ResultSets will contain length information sufficient to let
you know if you have bit(1) or bit(N) data.

Kris Jurka