Re: bit datatype and getObject()

On Wed, 24 Nov 2010, Kris Jurka wrote:

> On Tue, 23 Nov 2010, Thomas Kellerer wrote:
>
>> the JDBC driver seems to map columns defined as "bit" to Boolean regardless
>> of the definition of the column. So even a bit(5) is returned as boolean
>> value which I think is not correct.
>>
>
> Yeah, losing data is not good. Previously the discussion on list was to use
> java.util.BitSet, but it never resulted in a patch.
>

I've looked at this in some more detail and I don't think BitSet matches
up closely enough with bit(n)/varbit to make it work. BitSet does not
store the total bit string length, only the length until the highest set
bit. So if you have a BitSet object you can make a safe roundtrip to the
database and back, but you can't make a safe roundtrip from the database
to a BitSet and back. If you have a bit/varbit field that has a zero
in the most significant bit, it will not go back to the database the same.

This causes some problems on the database side, for example:

CREATE TEMP TABLE bittest (a bit(3));
INSERT INTO bittest VALUES ('010');

Once you pull this into a BitSet object you can't reconstruct the original
and you are left with an error like this for bit types:

UPDATE bittest SET a = '10'::varbit;
ERROR: bit string length 2 does not match type bit(3)

For varbit strings you can store the new value, but then later operations
may fail:

CREATE TEMP TABLE varbittest(a varbit(3), b varbit(3));
INSERT INTO varbittest VALUES ('010', '111');
SELECT a & b FROM varbittest;
UPDATE varbittest SET a = '10'::varbit;
SELECT a & b FROM varbittest;
ERROR: cannot AND bit strings of different sizes

So if the problem was that tons of people on the Java side had BitSets
they wanted to put into the database and retrieve, this might be
tolerable. That's not our problem though, we're really looking at the
database side of things and trying to represent that in Java, so we need
another approach. At the moment, returing a String seems easiest, do
other people have ideas?

Kris Jurka