Re: Bug: Driver(8.2dev-500.jdbc3) does not handle boolean->BIT

j.random.programmer wrote:
> Ok. This while BIT thing is very non-intuitive from my
> perspective.

It sure is. You're trying to deal with a type that's not directly 
supported by the JDBC spec, using only spec-provided mechanisms. Having 
a JDBC type called "BIT" which actually maps to a single boolean type is 
very confusing. If you assume that JDBC's BIT has *nothing* to do with 
the server type called "bit", and that it's just a coincidence that they 
have the same name, then things should be clearer.

> In PSQL:
> --------------------------------------
> test=# create table foo (bit_val bit(5));
> CREATE TABLE
> test=# insert into foo (bit_val) values ('B10101');
> INSERT 0 1
> test=# insert into foo (bit_val) values ('11111');
> INSERT 0 1

The equivalent queries if you are using the JDBC driver and 
PreparedStatement.setString() look like this:

>> test=# create table foo (bit_val bit(5));
>> CREATE TABLE
>> test=# insert into foo (bit_val) values ('B10101'::varchar);
>> ERROR:  column "bit_val" is of type bit but expression is of type character varying
>> HINT:  You will need to rewrite or cast the expression.
>> test=# insert into foo (bit_val) values ('11111'::varchar);
>> ERROR:  column "bit_val" is of type bit but expression is of type character varying
>> HINT:  You will need to rewrite or cast the expression.

Does that error look familiar? :)

> With val = '11111'
> ------------------------------------
> PreparedStatement pstmt =    
>     con.prepareStatement(
> 		"insert into foo (bit_val) values (?)"
> 						);
> pstmt.setString(1, val);
> pstmt.executeUpdate();
> -----------------------------------
> 
> The same with val = 'B11111'
> The same with val = true
> ...etc...
> 
> SQLException: SQLState(42804)
> Exception in thread "main"
> org.postgresql.util.PSQLException: ERROR: column
> "bit_val" is of type bit but expression is of type
> character varying

It is a *type* issue, not a representation issue. That error is 
occurring before the server even looks at the parameter value you've 
passed -- the problem is that you're passing a varchar parameter 
(courtesy of using setString()) in a context where the server is 
expecting something that can be implicitly cast to a bit(n) value, and 
there is no such implicit conversion from varchar.

Use something like this to get an explicit type conversion from varchar 
to bit(5):

   INSERT INTO foo(bit_val) VALUES (CAST (? AS bit(5)))

or (nonstandard):

   INSERT INTO foo(bit_val) VALUES (?::bit(5))

Or turn on autocasting for string parameters (see the development driver 
docs).

If you want to support bit(n) directly, you could write an extension 
type (subclass of PGobject). AFAIK noone has written this yet.

Another possible mapping would be to/from a Java boolean array. This has 
the problem that a Java array of booleans passed to setObject() should 
probably be mapped to an array of booleans on the server side, not to a 
bit(n) type.

-O