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*
