Thread: Exception when inserting boolean values into BIT columns

I have a problem with BIT datatypes accessed via JDBC. When I try to
insert a Boolean value via a PreparedStatement, I get the following
exception (using the newest JDBC driver, postgresql-8.0-311.jdbc3.jar,
against a 8.0.1 database on Windows, Java is 1.5.0_1):

Exception in thread "main" java.sql.SQLException: ERROR: column "isok"
is of type bit but expression is of type boolean
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1471)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1256)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:175)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:389)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:330)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:321)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:168)
    at Main.main(Main.java:27)


This is the corresponding test code:

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.Types;
import org.apache.commons.dbcp.BasicDataSource;

public class Main
{
    public static void main(String[] args) throws Exception
    {
        BasicDataSource dataSource = new BasicDataSource();

        dataSource.setDriverClassName("org.postgresql.Driver");
        dataSource.setUrl("jdbc:postgresql://localhost/test");
        dataSource.setUsername("postgres");
        dataSource.setPassword("root123");

        Connection connection = dataSource.getConnection();
        Statement  statement  = connection.createStatement();

        statement.execute("CREATE TABLE Test(id SERIAL, isOk BIT);");
        statement.close();

        PreparedStatement prepStatement =
connection.prepareStatement("INSERT INTO Test (isOk) VALUES (?);");

        prepStatement.setObject(1, Boolean.TRUE, Types.BIT);
        prepStatement.execute();
        prepStatement.close();
        connection.close();
    }
}

(btw, the same exception is generated when using setBoolean instead of
setObject.)

Any ideas ?

regards,
Tom

Thomas Dudziak wrote:
> I have a problem with BIT datatypes accessed via JDBC. When I try to
> insert a Boolean value via a PreparedStatement, I get the following
> exception (using the newest JDBC driver, postgresql-8.0-311.jdbc3.jar,
> against a 8.0.1 database on Windows, Java is 1.5.0_1):
>
> Exception in thread "main" java.sql.SQLException: ERROR: column "isok"
> is of type bit but expression is of type boolean

As I understand it, JDBC's BIT/BOOLEAN types (single boolean value)
aren't the same as PostgreSQL's BIT type (bit string). The JDBC driver
maps setBoolean(), setObject(...,Types.BIT), and
setObject(...,Types.BOOLEAN) to the 'boolean' datatype.

Try using the boolean type in your schema, assuming what you want to
store is a single boolean value. If you really want a bitstring, you'll
probably need to do something nasty like pass the value via setString()
and use 'pg_catalog.bit(?)' in your query to do the type conversion
(CAST AS doesn't seem to work from brief testing)

-O

On 6/20/05, Oliver Jowett <oliver@opencloud.com> wrote:
> As I understand it, JDBC's BIT/BOOLEAN types (single boolean value)
> aren't the same as PostgreSQL's BIT type (bit string). The JDBC driver
> maps setBoolean(), setObject(...,Types.BIT), and
> setObject(...,Types.BOOLEAN) to the 'boolean' datatype.
>
> Try using the boolean type in your schema, assuming what you want to
> store is a single boolean value. If you really want a bitstring, you'll
> probably need to do something nasty like pass the value via setString()
> and use 'pg_catalog.bit(?)' in your query to do the type conversion
> (CAST AS doesn't seem to work from brief testing)

Ah, yes, didn't notice that. Yep, I wanted to store a boolean value,
so I'll use postgresql's boolean type instead.

Thanks for pointing this out to me,
Tom