Mailing-List: contact users-help@openjpa.apache.org; run by ezmlm
Precedence: bulk
Reply-To: users@openjpa.apache.org
Received-SPF: pass (nike.apache.org: domain of hal.hildebrand@me.com
 designates 17.158.236.237 as permitted sender)
Content-type: text/plain; charset=windows-1252
MIME-version: 1.0 (Mac OS X Mail 7.1 \(1827\))
Subject: Re: PostgreSQL BIT/Boolean madness
From: Hal Hildebrand <hal.hildebrand@me.com>
In-reply-to: 
 <CAP=qHCt84w6wBhinwQuhkymoe=t4DCKwMRG=dk2XL4DXg+v2SQ@mail.gmail.com>
Date: Wed, 08 Jan 2014 10:19:23 -0800
Content-transfer-encoding: quoted-printable
Message-id: <FF78BDA9-F943-4483-AA5F-9456B465BF22@me.com>
References: <24B44489-BD08-4019-B166-B26987EB1E07@me.com>
 <CAP=qHCtbePYCtgajxH7tTEKCVz8+H0rgY0L1mUK5S79tJ+ZcYg@mail.gmail.com>
 <C5C7475B-AC56-44E0-B649-CACAFB62CCC4@me.com>
 <339DBAB1-6652-4297-BE70-8FD8E20EE968@me.com>
 <CAP=qHCt84w6wBhinwQuhkymoe=t4DCKwMRG=dk2XL4DXg+v2SQ@mail.gmail.com>
To: users@openjpa.apache.org

Sorry I didn=92t make that clear.

The table is defined to have type =93BOOLEAN=94.  The Java type is =
Boolean for the column (the aptly named =93boolean_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=92m using a =
different JDBC driver (i.e. the one integrated into PL/Java), and I=92m =
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?
>=20
>=20
> On Wed, Jan 8, 2014 at 11:18 AM, Hal Hildebrand =
<hal.hildebrand@me.com>wrote:
>=20
>> I tried this same trick in my app server -> DB test and oddly it had =
no
>> effect either way.
>>=20
>> I=92m wondering if I=92m actually doing anything here with this =
property.
>>=20
>> I do see the log line:
>>=20
>>=20
>>       606  CoRE  INFO   [main] openjpa.jdbc.JDBC - Using dictionary
>> class "org.apache.openjpa.jdbc.sql.PostgresDictionary=94.
>>=20
>> So I=92m assuming that this property will in fact, do something to =
the db
>> dictionary.  But the default already is =93BIT=94, so it=92s unclear =
that this is
>> where the problem lies.
>>=20
>>=20
>> On Jan 8, 2014, at 8:29 AM, Hal Hildebrand <hal.hildebrand@me.com> =
wrote:
>>=20
>>> So, I tried it with both =93BIT=94 and =93BOOLEAN=94 with the same =
result.
>>>=20
>>> Wonder where OpenJPA is getting this metadata from?
>>>=20
>>> Here=92s the stack trace, in case that catches someone=92s eye:
>>>=20
>>> 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=3D(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=3D0, state=3D42804]
>>>     at
>> =
org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnect=
ionDecorator.java:219)
>>>     at
>> =
org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnect=
ionDecorator.java:195)
>>>     at
>> =
org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$1000(Logging=
ConnectionDecorator.java:59)
>>>     at
>> =
org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator$LoggingConnection$L=
oggingPreparedStatement.executeUpdate(LoggingConnectionDecorator.java:1134=
)
>>>     at
>> =
org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(Dele=
gatingPreparedStatement.java:275)
>>>     at
>> =
org.apache.openjpa.lib.jdbc.DelegatingPreparedStatement.executeUpdate(Dele=
gatingPreparedStatement.java:275)
>>>     at
>> =
org.apache.openjpa.jdbc.kernel.JDBCStoreManager$CancelPreparedStatement.ex=
ecuteUpdate(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)
>>>=20
>>> On Jan 7, 2014, at 7:31 PM, Rick Curtis <curtisr7@gmail.com> wrote:
>>>=20
>>>> 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=3Dpostgres(bitTypeName=3D"BIT").
>>>>=20
>>>> HTH,
>>>> Rick
>>>>=20
>>>>=20
>>>> On Tue, Jan 7, 2014 at 7:38 PM, Hal Hildebrand =
<hal.hildebrand@me.com
>>> wrote:
>>>>=20
>>>>> 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=85.
>>>>>=20
>>>>> I=92m 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:
>>>>>=20
>>>>> 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=3D(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=3D0,
>> state=3D42804]
>>>>>=20
>>>>> 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.
>>>>>=20
>>>>> So I=92m guessing there=92s 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=92s an issue with type 2 drivers vs type 3 drivers with =
PostgreSQL
>> BIT
>>>>> and BOOLEAN.
>>>>>=20
>>>>> 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=92s a =
SQL
>> way
>>>>> out of this level of hell, I=92ll gladly do that as well.
>>>>>=20
>>>>> Any help would be appreciated.
>>>>>=20
>>>>> -Hal
>>>>=20
>>>>=20
>>>>=20
>>>>=20
>>>> --
>>>> *Rick Curtis*
>>>=20
>>=20
>>=20
>=20
>=20
> --=20
> *Rick Curtis*