Mailing-List: contact users-help@openjpa.apache.org; run by ezmlm
Precedence: bulk
Reply-To: users@openjpa.apache.org
Received-SPF: pass (athena.apache.org: domain of hal.hildebrand@me.com
 designates 17.158.236.236 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: <C5C7475B-AC56-44E0-B649-CACAFB62CCC4@me.com>
Date: Wed, 08 Jan 2014 09:18:29 -0800
Content-transfer-encoding: quoted-printable
Message-id: <339DBAB1-6652-4297-BE70-8FD8E20EE968@me.com>
References: <24B44489-BD08-4019-B166-B26987EB1E07@me.com>
 <CAP=qHCtbePYCtgajxH7tTEKCVz8+H0rgY0L1mUK5S79tJ+ZcYg@mail.gmail.com>
 <C5C7475B-AC56-44E0-B649-CACAFB62CCC4@me.com>
To: users@openjpa.apache.org

I tried this same trick in my app server -> DB test and oddly it had no =
effect either way.

I=92m wondering if I=92m 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=94.

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.


On Jan 8, 2014, at 8:29 AM, Hal Hildebrand <hal.hildebrand@me.com> =
wrote:

> 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=20
> INSERT INTO ruleform.job_attribute (id, notes, update_date, =
binary_value,=20
>        boolean_value, integer_value, numeric_value, sequence_number,=20=

>        text_value, timestamp_value, job, updated_by, attribute, unit)=20=

>    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)=20
> [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
>> --=20
>> *Rick Curtis*
>=20