You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Hal Hildebrand <ha...@me.com> on 2014/01/08 02:38:26 UTC
PostgreSQL BIT/Boolean madness
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
Re: PostgreSQL BIT/Boolean madness
Posted by Hal Hildebrand <ha...@me.com>.
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 <cu...@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 <ha...@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 <ha...@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 <cu...@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 <ha...@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 <cu...@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*
>>>
>>
>>
>
>
> --
> *Rick Curtis*
Re: PostgreSQL BIT/Boolean madness
Posted by Rick Curtis <cu...@gmail.com>.
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 <ha...@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 <ha...@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 <cu...@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 <ha...@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 <cu...@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*
> >
>
>
--
*Rick Curtis*
Re: PostgreSQL BIT/Boolean madness
Posted by Hal Hildebrand <ha...@me.com>.
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 <ha...@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 <cu...@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 <ha...@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 <ha...@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 <cu...@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*
>
Re: PostgreSQL BIT/Boolean madness
Posted by Hal Hildebrand <ha...@me.com>.
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 <cu...@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 <ha...@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 <ha...@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 <cu...@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*
Re: PostgreSQL BIT/Boolean madness
Posted by Rick Curtis <cu...@gmail.com>.
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 <ha...@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 <ha...@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 <cu...@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*
Re: PostgreSQL BIT/Boolean madness
Posted by Hal Hildebrand <ha...@me.com>.
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 <ha...@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 <cu...@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 <ha...@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*
>
Re: PostgreSQL BIT/Boolean madness
Posted by Hal Hildebrand <ha...@me.com>.
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 <cu...@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 <ha...@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*
Re: PostgreSQL BIT/Boolean madness
Posted by Hal Hildebrand <ha...@me.com>.
Yea, I was wondering if this might be an angle of attack. Thanks, will try and see.
-Hal
On Jan 7, 2014, at 7:31 PM, Rick Curtis <cu...@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 <ha...@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*
Re: PostgreSQL BIT/Boolean madness
Posted by Rick Curtis <cu...@gmail.com>.
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 <ha...@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*