Sorry I didn’t make that clear.
The table is defined to have type “BOOLEAN”. The Java type is Boolean for the
column (the aptly named “boolean_value). This works, as is, when I from the
middle tier -> database, using PostgreSQL JDBC drivers, using OpenJPA, with no
changes to the postgres db dictionary.
When running inside a stored procedure (using PL/Java), I’m using a different
JDBC driver (i.e. the one integrated into PL/Java), and I’m still using
OpenJPA. However, this fails with the stack trace below, complaining the the
expression is of type BIT, but the column is defined as type BOOLEAN.
On Jan 8, 2014, at 9:53 AM, Rick Curtis <curti...@gmail.com> wrote:
> Is the problem that we're calling stmnt.setNull(idx, Types.BOOLEAN); and
> the driver wants us to call stmnt.setNull(idx, Types.BIT);? Just so we're
> on the same page, your Entity has a boolean field that maps to a BIT column?
>
>
> On Wed, Jan 8, 2014 at 11:18 AM, Hal Hildebrand <hal.hildebr...@me.com>wrote:
>
>> I tried this same trick in my app server -> DB test and oddly it had no
>> effect either way.
>>
>> I’m wondering if I’m actually doing anything here with this property.
>>
>> I do see the log line:
>>
>>
>> 606 CoRE INFO [main] openjpa.jdbc.JDBC - Using dictionary
>> class "org.apache.openjpa.jdbc.sql.PostgresDictionary”.
>>
>> So I’m assuming that this property will in fact, do something to the db
>> dictionary. But the default already is “BIT”, so it’s unclear that this is
>> where the problem lies.
>>
>>
>> On Jan 8, 2014, at 8:29 AM, Hal Hildebrand <hal.hildebr...@me.com> wrote:
>>
>>> So, I tried it with both “BIT” and “BOOLEAN” with the same result.
>>>
>>> Wonder where OpenJPA is getting this metadata from?
>>>
>>> Here’s the stack trace, in case that catches someone’s eye:
>>>
>>> Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: column
>> "boolean_value" is of type boolean but expression is of type bit {prepstmnt
>> 1254242409
>>> INSERT INTO ruleform.job_attribute (id, notes, update_date, binary_value,
>>> boolean_value, integer_value, numeric_value, sequence_number,
>>> text_value, timestamp_value, job, updated_by, attribute, unit)
>>> VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
>>> [params=(long) 301, (null) null, (null) null, (null) null, (null) null,
>> (int) 1500, (null) null, (int) 1, (null) null, (null) null, (long) 605,
>> (long) 4, (long) 56, (null) null]} [code=0, state=42804]
>>> at
>> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:219)
>>> at
>> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:195)
>>> at
>> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$1000(LoggingConnectionDecorator.java:59)
>>> at
>> org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$LoggingPreparedStatement.executeUpdate(LoggingConnectionDecorator.java:1134)
>>> at
>> org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:275)
>>> at
>> org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:275)
>>> at
>> org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.executeUpdate(JDBCStoreManager.java:1792)
>>> at
>> org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.executeUpdate(PreparedStatementManagerImpl.java:268)
>>> at
>> org.apache.openjpa.jdbc.kernel.PreparedStatementManagerImpl.flushAndUpdate(PreparedStatementManagerImpl.java:119)
>>>
>>> On Jan 7, 2014, at 7:31 PM, Rick Curtis <curti...@gmail.com> wrote:
>>>
>>>> It isn't clear to me what is going on, but with the DBDictionary you can
>>>> change the type of column that boolean / bit field types are mapped to.
>>>> Perhaps you can change the bitTypeName / booleanTypeName to see if you
>> can
>>>> get something working? To change these values you can change the type to
>>>> set the property openjpa.jdbc.DBDictionary=postgres(bitTypeName="BIT").
>>>>
>>>> HTH,
>>>> Rick
>>>>
>>>>
>>>> On Tue, Jan 7, 2014 at 7:38 PM, Hal Hildebrand <hal.hildebr...@me.com
>>> wrote:
>>>>
>>>>> So, I have a serious problem. I have tables that are defined to have
>>>>> BOOLEAN columns. When I run this with PostgreSQL JDBC drivers, from
>> the
>>>>> middle tier, this works just fine. Everything peachy keen. However….
>>>>>
>>>>> I’m also running this code inside the database via PL/Java. When
>> running
>>>>> inside the session as a stored procedure, a different JDBC driver is
>> used -
>>>>> i.e. the one integrated into PL/Java. This JDBC driver works fine for
>> the
>>>>> most part. But the problem is that when I try to set NULL to BOOLEAN
>>>>> columns, OpenJPA barfs:
>>>>>
>>>>> 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]
>>>>>
>>>>> I have created a test case where I create a simple table with a BOOLEAN
>>>>> column and then use the raw JDBC driver to insert a NULL value into a
>> row
>>>>> when running inside of PL/Java and it just works fine.
>>>>>
>>>>> So I’m guessing there’s some weird meta data thing going on. I did a
>> lot
>>>>> of googling to see what I could find out, and basically it seems like
>>>>> there’s an issue with type 2 drivers vs type 3 drivers with PostgreSQL
>> BIT
>>>>> and BOOLEAN.
>>>>>
>>>>> Thus, the question I have is there some setting in OpenJPA I can use to
>>>>> get around this? Things seem to turn nightmarish if I convert all my
>>>>> columns to BIT and try to deal with that. But hey, if there’s a SQL
>> way
>>>>> out of this level of hell, I’ll gladly do that as well.
>>>>>
>>>>> Any help would be appreciated.
>>>>>
>>>>> -Hal
>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> *Rick Curtis*
>>>
>>
>>
>
>
> --
> *Rick Curtis*
