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*
