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

Thank you for your time, but i think there is still a driver issue here:

If i use the same types as i sent in the email and execute

select * from bit_in_min(1::bit)

I have no problems and the table is correctly updated.

This would lead me to believe that the driver has a problem with
correctly mapping the setObect() of the String to a BIT which is
required conversion by the JDBC spec.

Regards
Lance

Kris Jurka wrote:
> 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
>