Yea, that’s what I’d love to be able to do. Unfortunately, this is happening
inside the DB, so awesome. I think my strategy is to figure out where this is
happening while running on the middle tier, where it’s easy to debug. Then
perhaps I can get a better idea of what’s likely going on and see if that opens
up any avenues of attack..
-Hal
On Jan 8, 2014, at 1:14 PM, Rick Curtis <curti...@gmail.com> wrote:
> I'm not certain if DatabaseMetaData has anything to do with what is going
> on. I'm going to suggest to debug down to see what values OpenJPA is
> passing to the PL/Java JDBC driver, and then see what values need to be
> passed in. That might help us understand what is going on.
>
>
> On Wed, Jan 8, 2014 at 2:33 PM, Hal Hildebrand <hal.hildebr...@me.com>wrote:
>
>> 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.hildebr...@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 <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*
>>>
>>
>>
>
>
> --
> *Rick Curtis*
