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