Thread: Problems with BIT datatype and preparedStatment

Ermengol Bota, 26.11.2012 14:00:
> Hello,
>
> 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();
>
> 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
weare missing something :-) 
>

did you try

pstmt.setString(1, "1");

(I find the boolean type much more "descriptive" anyway for true/false columns)

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.

Ermengol Bota wrote:
> 2012/11/26 dmp <danap@ttc-cmc.net <mailto:danap@ttc-cmc.net>>
>
>     Hello,
>
>     A search of the forum may turn up a better answer, but:
>
>
> I searched "the web" :-D and I just found the code below that it's
> supposed to work. I thought that the patch sended was included "by
> default" in newer jdbc versions...
>
> http://archives.postgresql.__org/pgsql-patches/2003-06/__msg00402.php
> <http://archives.postgresql.org/pgsql-patches/2003-06/msg00402.php>
>
> btw, which forum do you refer?

This forum, jdbc.postgresql. The search you turned up for the example given
appears to be from the server forum. The patch you refer to:

/projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Statement.java,v

seems to be for the server not the JDBC code. It is also very old, 2003,
while might have worked then does not now it appear. There has many changes
I suspect in the last 8,9 years.

>     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");
>
>
> This way works! great :-)
>
> I'm not used to do cast conversion on the prepareStatement definition,
> thank you for the clue!
>
> Now I see that I didn't understand the hint at the error message "Hint:
> You will need to rewrite or cast the expression."
>
> Thanks a lot.

The documentation
http://www.postgresql.org/files/documentation/pdf/9.2/postgresql-9.2-A4.pdf I
think chapter 8 discusses data types in
PostgreSQL. The manual is your friend.

danap.

> --
> * Ermengol *
> *************************************************
> * Els ordinadors no resolen problemes, *
> * simplement executen solucions           *
> *************************************************