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

Author

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

j.random.programmer

2005-12-20, 3:24 am

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 Cramer

2005-12-20, 3:24 am

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
>

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

j.random.programmer

2005-12-20, 3:24 am

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

j.random.programmer

2005-12-20, 3:24 am

> 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

Dave Cramer

2005-12-20, 3:24 am

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:

>
> Hmm.
>
>
> 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
>

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

j.random.programmer

2005-12-21, 3:24 am

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(Query
ExecutorImpl.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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Sponsored Links