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

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

From: Kris Jurka <books(at)ejurka(dot)com>
To: "Lance J(dot) Andersen" <Lance(dot)Andersen(at)Sun(dot)COM>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #3751: Conversion error using PreparedStatement.setObject()
Date: 2007-11-16 00:11:16
Message-ID: 473CE024.5050009@ejurka.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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: Lance J. AndersenDate: 2007-11-16 00:28:38
Subject: Re: BUG #3751: Conversion error using PreparedStatement.setObject()
Previous:From: Michael CharnokyDate: 2007-11-16 00:00:25
Subject: BUG #3752: query yields "could not find block containing chunk", then server crashes

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