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

Running against postgres 8.1

I have:

create table foo (bit_val bit);

If I now try to insert, via the driver's prepared
statement, a value of java boolean true into
the bit_val column, I get:

---------------------------------
ERROR COULD NOT SAVE.....
org.postgresql.util.PSQLException: ERROR: column
"bit_val" is of type bit but expression is of type
boolean
---------------------------------

This is totally wrong if my reading of the JDBC
spec is correct. Java boolean values should
be converted to bitval(1), possibly as '1' and
then converted back to boolean when read from
the database.

If I go to psql directly, the following works fine:

insert into foo (bit_val) values ('1');

THAT is what the driver should to as well.

This is really hokey since it's breaking my
O/R mapping tool.

Best regards,
--j

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

The problem is that we don't know in advance if the underlying column
is a bit, or a boolean. Postgresql supports both, the jdbc API
doesn't appear to.

We are now using bound variables and are binding that to a boolean
type, not a bit type. We had to choose one of them, and boolean seems
to be much more "boolean" than bit.

Dave
On 19-Dec-05, at 3:14 PM, j.random.programmer wrote:

> Running against postgres 8.1
>
> I have:
>
> create table foo (bit_val bit);
>
> If I now try to insert, via the driver's prepared
> statement, a value of java boolean true into
> the bit_val column, I get:
>
> ---------------------------------
> ERROR COULD NOT SAVE.....
> org.postgresql.util.PSQLException: ERROR: column
> "bit_val" is of type bit but expression is of type
> boolean
> ---------------------------------
>
> This is totally wrong if my reading of the JDBC
> spec is correct. Java boolean values should
> be converted to bitval(1), possibly as '1' and
> then converted back to boolean when read from
> the database.
>
> If I go to psql directly, the following works fine:
>
> insert into foo (bit_val) values ('1');
>
> THAT is what the driver should to as well.
>
> This is really hokey since it's breaking my
> O/R mapping tool.
>
> Best regards,
> --j
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

Dave:

> The problem is that we don't know in advance if the
> underlying column
> is a bit, or a boolean. Postgresql supports
> both, the jdbc API   doesn't appear to.

You can get from the database the actual
type defined in the database for that column
right ? (so if it's BIT your driver can tell it's
BIT in the database, I presume).

So then, as per the JDBC spec
1) while retrieving:
convert that BIT (however long it may be) into a
boolean [true, say, it it's all 1's else false].
2) while saving:
convert java true to a '1' and save that as a BIT
(convert java false to '0').

That's what the spec suggests from what I can
tell. Of course, you could also convert the
BIT into a string if the user wants it as a string).

Best regards,
--j

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

j.random.programmer wrote:
> Running against postgres 8.1
>
> I have:
>
> create table foo (bit_val bit);
>
> If I now try to insert, via the driver's prepared
> statement, a value of java boolean true into
> the bit_val column, I get:
>
> ---------------------------------
> ERROR COULD NOT SAVE.....
> org.postgresql.util.PSQLException: ERROR: column
> "bit_val" is of type bit but expression is of type
> boolean
> ---------------------------------

JDBC's "BIT" is actually a boolean type. In JDBC3, BIT and BOOLEAN are
effectively equivalent.

The backend's "bit" type is actually a bitstring that does not have a
direct equivalent in JDBC.

> This is totally wrong if my reading of the JDBC
> spec is correct. Java boolean values should
> be converted to bitval(1), possibly as '1' and
> then converted back to boolean when read from
> the database.

Actually, the spec says nothing about bitstring types AFAIK.

If you want to do this type conversion and still use setBoolean(), you
will need to modify your query to do the type conversion there.

-O

> JDBC's "BIT" is actually a boolean type. In
> JDBC3, BIT and BOOLEAN are  effectively
> equivalent.

Hmm.

> The backend's "bit" type is actually a bitstring
> that does not have a
> direct equivalent in JDBC.

Aha !

So postgres really has a BITSTRING which is
conceptually different from a BIT type ? (since
BIT is also used instead of BITSTRING, you
can see why there is bound to be confusion).

Anywho, in the JDBC driver, why not convert
java booleans as follows:

true -> '1'
false -> '0'

and store that in the BIT column ?

While retrieving, do the reverse conversion.

Of course, if someone has the following
in the BIT column

'1001010'

then:
a) allow the entire value to be retrieved as a String
(getString...)
b) If there are any 1's present, return true
is retrieving it as boolean, false if all 0's.

Best,
-j


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

This isn't the problem. The problem is that was bind the parameter to
the type. So setBoolean binds the parameter to a boolean type.

putting ' quotes around it will not help matters.

Dave
On 19-Dec-05, at 6:41 PM, j.random.programmer wrote:

>> JDBC's "BIT" is actually a boolean type. In
>> JDBC3, BIT and BOOLEAN are  effectively
>> equivalent.
>
> Hmm.
>
>> The backend's "bit" type is actually a bitstring
>> that does not have a
>> direct equivalent in JDBC.
>
> Aha !
>
> So postgres really has a BITSTRING which is
> conceptually different from a BIT type ? (since
> BIT is also used instead of BITSTRING, you
> can see why there is bound to be confusion).
>
> Anywho, in the JDBC driver, why not convert
> java booleans as follows:
>
> true -> '1'
> false -> '0'
>
> and store that in the BIT column ?
>
> While retrieving, do the reverse conversion.
>
> Of course, if someone has the following
> in the BIT column
>
> '1001010'
>
> then:
> a) allow the entire value to be retrieved as a String
> (getString...)
> b) If there are any 1's present, return true
> is retrieving it as boolean, false if all 0's.
>
> Best,
> -j
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

j.random.programmer wrote:

> Anywho, in the JDBC driver, why not convert
> java booleans as follows:
>
> true -> '1'
> false -> '0'
>
> and store that in the BIT column ?

That wouldn't help you as the parameter is typed as boolean at the
protocol level, as is reflected in your error message -- it is a type
issue, not a representation issue.

If you really want to do a conversion, make it explicit in your query.

> Of course, if someone has the following
> in the BIT column
>
> '1001010'
>
> then:
> a) allow the entire value to be retrieved as a String
> (getString...)

We do that already.

> b) If there are any 1's present, return true
> is retrieving it as boolean, false if all 0's.

That seems very error-prone.

-O

Ok. This while BIT thing is very non-intuitive from my
perspective.

First consider this:
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
test=# select * from foo;
 bit_val
---------
 10101
 11111
(2 rows)
------------------------------------

Value 'B11110' IS 100% valid according to
the postgresql manual. (for a BIT(5) column
type).

Now, from JDBC this is impossible.

BOTH of the following give exceptions and
error out.

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
        at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1514)
        at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1299)

-------------------------------------

As far as I can tell, this is a big problem. Or
am I missing something ? [Is there *ANY* way
to get a value into a BIT column from JDBC
via preparedstatements ?]

Best regards,
--j

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

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

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

Oliver Jowett wrote:
> 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.

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

[snip]
> 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.

What about a java.util.BitSet ? Or does it have to extend PGObject ?

Cheers,
Csaba.

Hi, Csaba,

Csaba Nagy wrote:

>>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.
>
> What about a java.util.BitSet ? Or does it have to extend PGObject ?

Currently, all Objects have either to be special cased inside the
driver, or extend PGObject (which does not allow binary transfer).

There were several ideas to change this, but AFAIR none of them was
implemented.

Markus

--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

Csaba Nagy wrote:
> [snip]
>
>>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.
>
>
> What about a java.util.BitSet ? Or does it have to extend PGObject ?

BitSet would work. The only real reason to extend PGobject is so you can
implement support for extra types without modifying the driver itself.

-O