Thank you for your time, but i think there is still a driver issue here:
If i use the same types as i sent in the email and execute
select * from bit_in_min(1::bit)
I have no problems and the table is correctly updated.
This would lead me to believe that the driver has a problem with
correctly mapping the setObect() of the String to a BIT which is
required conversion by the JDBC spec.
Regards
Lance
Kris Jurka wrote:
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 4: Have you searched our list archives?
http://archives.postgresql.org
