From: hal.hildebrand at me.com (Hal Hildebrand)
Date: Tue, 06 Aug 2013 14:25:28 -0700
Subject: [Pljava-dev] Boolean NULL translation in PL/Java JDBC Driver
Message-ID: <5AE94290-2C0F-4B36-9E7F-9209E7AC692E@me.com>

So, I've run into a bit of a problem.  I'm using JPA in the database, which means I'm not directly manipulating the JDBC connection.  Here's what I believe is happening:

First, the error message I'm getting:

Caused by: <openjpa-2.2.2-r422266:1468616 fatal general error> org.apache.openjpa.persistence.PersistenceException: column "boolean_value" is of type boolean but expression is of type bit {prepstmnt 108675190 
INSERT INTO ruleform.job_attribute (id, notes, update_date, binary_value, 
        boolean_value, integer_value, numeric_value, sequence_number, 
        text_value, timestamp_value, job, research, updated_by, attribute, 
        unit) 
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) 
[params=(long) 1, (null) null, (null) null, (null) null, (null) null, (null) null, (BigDecimal) 1500, (int) 1, (null) null, (null) null, (long) 55, (null) null, (long) 3, (long) 56, (null) null]} [code=0, state=42804]


Note that the statement is setting the boolean_value column to NULL.  So, obviously, there isn't a problem with the value, it's the type that's being set by the prepared statement.

When I run this exact same code using the PostgreSQL JDBC driver, outside of a Java Stored Procedure, this all works fine.  Everything commits, life is good, democracy is saved.

However, when I run this using the PL/Java JDBC driver, inside of a Java Stored Procedure, I get this failure.

Googling around, this error is caused because the prepared statement trying to set the NULL value of the column "boolean_value" to - I believe a NULL STRING.

The column is declared as "boolean" and the value really is a Boolean NULL that's being set for that column in the prepared statement.

Looking at the logic of what happens when the JPA layer tries to set a boolean null, I believe this eventually grounds out to the method:

	SPIPreparedStatement
		public void setObject(int columnIndex, Object value, int sqlType)

And I believe the failing logic is around line 426 with this logic:

        // Default to String.
        //
        if (id == null) {
            id = Oid.forSqlType(Types.VARCHAR);
        }

I have set up a simple table with a boolean column and tried the same thing using raw JDBC and got the same result, so I'm not sure what's going on.

From the logic, I tried to set up the Oid mapping with:

        Oid.registerType(Boolean.class, new Oid(Types.BOOLEAN));

But that didn't work.

Am I missing something obvious here?  Is there a work around for this that I can do that won't require me fixing C and rebuilding the system again?

In any event, any help anyone can provide would be most appreciated.

-Hal