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 <[email protected]>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 <[email protected]> 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 <[email protected]> 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 <[email protected] > >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*
