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-15 23:54:18
Message-ID: 473CDC2A.7000608@sun.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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

Attachment: BitTest.java
Description: www/unknown (4.3 KB)
Attachment: Drivers.java
Description: www/unknown (1.1 KB)

In response to

Responses

pgsql-bugs by date

Next:From: Michael CharnokyDate: 2007-11-16 00:00:25
Subject: BUG #3752: query yields "could not find block containing chunk", then server crashes
Previous:From: Kris JurkaDate: 2007-11-15 22:59:00
Subject: Re: BUG #3751: Conversion error using PreparedStatement.setObject()

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