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

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