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

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