Skip site navigation (1) Skip section navigation (2)

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

From: "Lance J(dot) Andersen" <Lance(dot)Andersen(at)Sun(dot)COM>
To: Kris Jurka <books(at)ejurka(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #3751: Conversion error using PreparedStatement.setObject()
Date: 2007-11-16 00:28:38
Message-ID: 473CE436.1090505@sun.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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
>

In response to

Responses

pgsql-bugs by date

Next:From: Kris JurkaDate: 2007-11-16 01:02:13
Subject: Re: BUG #3751: Conversion error using PreparedStatement.setObject()
Previous:From: Kris JurkaDate: 2007-11-16 00:11:16
Subject: Re: BUG #3751: Conversion error using PreparedStatement.setObject()

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group