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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
