openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Hal Hildebrand <hal.hildebr...@me.com>
Subject Re: PostgreSQL BIT/Boolean madness
Date Wed, 08 Jan 2014 20:33:38 GMT
Anyone know if java.sql.DatabaseMetaData might have something to do with this?  The implementation
that runs in PL/Java, I have the source code to, so I can has change it.


In particular, I’m suspicious that DatabaseMetaData.getTypeInfo() might be messing with
me.

On Jan 8, 2014, at 10:19 AM, Hal Hildebrand <hal.hildebrand@me.com> wrote:

> 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 <curtisr7@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.hildebrand@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.hildebrand@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 <curtisr7@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.hildebrand@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*
> 


Mime
View raw message