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

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