Re: BUG #3751: Conversion error using PreparedStatement.setObject()

The test runs for me when I change all of the underlying types from bit
to boolean:

create table Bit_Tab (MAX_VAL boolean, MIN_VAL boolean, NULL_VAL boolean
NULL) ;

CREATE OR REPLACE FUNCTION Bit_In_Min (MIN_PARAM boolean) returns void as
'begin update Bit_Tab set MIN_VAL=MIN_PARAM; end;' language 'plpgsql' ;

Kris Jurka

Lance J. Andersen wrote:
> Sorry Bad, Cut and paste. This test is a strip down of much larger
> test. The reason the metadata is there as this gets run from a
> framework which exercises JDBC drivers from all of the major vendors
> which is also the reason for the Drivers class.
>
>
> As far as the INSERT, i did not look at the postgresql docs in enough
> detail probably given that it works against all of the other vendors
> who support BIT data types, so my mistake.
>
> Here is the the entire scenario:
>
> The table is created as
>
> create table Bit_Tab (MAX_VAL bit(1), MIN_VAL bit(1), NULL_VAL bit(1)
> NULL) ;
>
> and the stored procedure via
>
>
> CREATE OR REPLACE FUNCTION Bit_In_Min (MIN_PARAM bit(1)) returns void as
> 'begin update Bit_Tab set MIN_VAL=MIN_PARAM; end;' language 'plpgsql' ;
>
>
>
>
> even if i change the insert as you suggest, to
>
> insert into Bit_Tab values('1', '0', null )
>
> it still fails
>
> org.postgresql.util.PSQLException: ERROR: column "min_val" is of type
> bit but expression is of type boolean
> at
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
>
> at
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
>
> at
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
>
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
>
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:351)
>
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:302)
>
> at com.sun.jdbc.samples.BitTest.testSetObject48(BitTest.java:93)
> at com.sun.jdbc.samples.BitTest.runTest(BitTest.java:41)
> at com.sun.jdbc.samples.BitTest.main(BitTest.java:16)
> BUILD SUCCESSFUL (total time: 2 seconds)
>
>
> The failure now is on cstmt.executeUpdate() which i would infer either
> the driver is not doing the proper conversion or the function is having
> issues.
>
> The test is validating that a String can be sent as a BIT and returned
> as a Boolean per the JDBC specifcation.
>
> -lance
>
>
>
>
>
> Kris Jurka wrote:
>>
>>
>> On Thu, 15 Nov 2007, Lance Andersen wrote:
>>
>>>
>>> The following bug has been logged online:
>>>
>>> Bug reference: 3751
>>> PostgreSQL version: 8.2.x
>>> Description: Conversion error using PreparedStatement.setObject()
>>> Details:
>>>
>>> A PreparedStatement.setObject(1, "1", Types.Bit) will fail with the
>>> following Exception:
>>
>> This is not a great test case.
>>
>> 1) It doesn't contain the definition of the Drivers class so it
>> doesn't compile. The whole drivers class and dumping metadata is
>> needless complication for a simple test case.
>>
>> 2) It doesn't contain the definition of the bit_tab table, so it
>> doesn't run.
>>
>> 3) The error is actually coming from "stmt.executeUpdate(Min_Insert)",
>> not a PreparedStatement.
>>
>> So where does that leave us?
>>
>> 1) The raw insert fails. INSERT INTO bit_tab (1,0,null) fails because
>> 1 gets typed as an integer and there are no implicit or assignment
>> casts from integer to bit. You would need to say, '1' so it comes in
>> untyped and converted to bit, or be explicit about the type with a
>> cast, saying
>> 1::bit or CAST(1 AS bit).
>>
>> 2) There might be a problem with bit conversion in prepared
>> statements, but we didn't get that far.
>>
>> Other notes:
>>
>> In PG the bit type is really for multiple bits, not a single bit.
>> Consider SELECT 77::bit(8) results in "01001101". It's more likely
>> that you want to use boolean as the type instead although it doesn't
>> have any casts that will help you out in this situation either.
>>
>> Kris Jurka