Re: Problems with BIT datatype and preparedStatment

Hello,

A search of the forum may turn up a better answer, but:

Ermengol Bota wrote:
> Hello,
>
> We are having problems when using BIT datatype. Mainly when using it on
> prepared statement.
>
> The code below is a copy of the one send at:
> pgsql-patches on 2003
> http://archives.postgresql.org/pgsql-patches/2003-06/msg00402.php
>
> sql.execute("Create Table jdbc_demo (b bit)");
> PreparedStatement pstmt = conn.prepareStatement("insert into jdbc_demo
> values (?)");
> pstmt.setBoolean(1,true); // There is no way to set a value for the bit
> field (or I don't know ... :-)
> pstmt.execute();

PostreSQL actually has the data types: boolean and bit. From
the information show below they are defined the same from a
java.sql.boolean Datatype, but actually the bit type in PostgreSQL does
not need be one bit.

columnName, columnClass, columnType, columnSize

boolean_type java.lang.Boolean bool 1
bit2_type java.lang.Boolean bit 2

So the first problem with your execution above is you have tried to
insert a PostgreSQL boolean type into a defined PostgreSQL bit type.
You definition is actually by default bit(1), it could have been
bit(2) has the example bit2_type above. Can not use setBoolean() with
bit type in the way you have tried.

The one way that it can be done has I have seen before would be to
do a CAST.

pstmt = con.prepareStatement("INSERT INTO jdbc_demo VALUES (?::bit)");
pstmt.setString(1, "1");

>
> Theorically (I think) it should works, but it answers:
>
> ERROR: column"b" is of type bit but expression is of type boolean
>
> We are working with PG 9.2.1 (one-click-installer on Ubuntu) and JDBC4
> 9.2-1002
>
> After trying a lot of different types for the pstmt.setXXXX(), we think
> that there is something not working well, or we are missing something :-)
>
Perhaps I can not say, with my limited knowledge.

> BIT datatype works well with statement like it:
> sqlStmt = "insert into table values ('1')";
>

My limited understanding here is that a cast is taking place on the fly
while with the PreparedStatement the engine is expecting the specifiy
data type to be sent.

> I know that we can do the same behavior with a boolean datatype instead
> of BIT, and at the end we did this way, but I'm still curious about how
> should it be done using bit
>
As indicated above because you are now using a boolean type.
> Thank you for any help!

danap.