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

Author

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

Oliver Jowett

2005-12-21, 3:24 am

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:

Does that error look familiar? :)
[color=darkred]
> 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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Oliver Jowett

2005-12-21, 3:24 am

Oliver Jowett wrote:

> or (nonstandard):
>
> INSERT INTO foo(bit_val) VALUES (?::bit(5))

Actually, this doesn't appear to work. The CAST variant works fine, use
that instead.

-O

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Oliver Jowett

2005-12-21, 3:24 am

Oliver Jowett wrote:
> Oliver Jowett wrote:
>
>
>
> Actually, this doesn't appear to work. The CAST variant works fine, use
> that instead.

Bah, I'm really not having much luck with sending email before I've
completely checked things today, sorry about all the individual
corrections :/

CAST doesn't work either -- it seems there is no explicit cast from
varchar to bit(n) at all.

So you can either mess with input functions directly, or write a custom
PGobject type, or turn on string autocasting.

-O

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Sponsored Links