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

[Lance J. Andersen]

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.

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


       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)






-lance





Kris Jurka wrote:

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. 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) 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.

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.







So where does that leave us?


1::bit or CAST(1 AS bit).



Other notes:



Kris Jurka

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. 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 2) There might be a problem with bit conversion in prepared statements, but we didn't get that far. 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.

Drivers.java
Description: www/unknown

BitTest.java
Description: www/unknown

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

                http://www.postgresql.org/about/donate