1

We are moving from Oracle to PostgreSQL. Our web application server is WebSphere, and we are also using JPA in order to manage persistence.

After I have converted all the SQL queries to be aligned to PostgreSQL syntax, I started the server and got a weird exception. I have tried to convert every row on the Oracle data tables creation that should use boolean from smallint of Oracle to Boolean on PostgreSQL:

Exception data: <openjpa-2.2.3-SNAPSHOT-r422266:1737410 fatal general error> org.apache.openjpa.persistence.PersistenceException: Bad value for type int : f
FailedObject: com.entities.security.UserEntity-100 [java.lang.String]
at org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4998)
at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4958)
at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:136)
at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:86)
at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.initialize(JDBCStoreManager.java:311)
at com.ibm.ws.persistence.jdbc.kernel.WsJpaJDBCStoreManager.initialize(WsJpaJDBCStoreManager.java:178)
at org.apache.openjpa.kernel.DelegatingStoreManager.initialize(DelegatingStoreManager.java:112)
at org.apache.openjpa.kernel.ROPStoreManager.initialize(ROPStoreManager.java:57)
at org.apache.openjpa.kernel.BrokerImpl.initialize(BrokerImpl.java:1051)
at org.apache.openjpa.kernel.BrokerImpl.find(BrokerImpl.java:1009)
at org.apache.openjpa.kernel.BrokerImpl.find(BrokerImpl.java:931)
at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.load(JDBCStoreManager.java:1033)
at com.ibm.ws.persistence.jdbc.kernel.WsJpaJDBCStoreManager.load(WsJpaJDBCStoreManager.java:143)
at org.apache.openjpa.jdbc.sql.AbstractResult.load(AbstractResult.java:280)
at org.apache.openjpa.jdbc.sql.SelectImpl$SelectResult.load(SelectImpl.java:2381)
at org.apache.openjpa.jdbc.sql.AbstractResult.load(AbstractResult.java:274)
at org.apache.openjpa.jdbc.kernel.InstanceResultObjectProvider.getResultObject(InstanceResultObjectProvider.java:60)
at org.apache.openjpa.kernel.QueryImpl$PackingResultObjectProvider.getResultObject(QueryImpl.java:2082)
at org.apache.openjpa.lib.rop.EagerResultList.<init>(EagerResultList.java:36)
at org.apache.openjpa.kernel.QueryImpl.toResult(QueryImpl.java:1258)
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:1014)
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:870)
at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:801)
at org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:542)
at org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:286)
at org.apache.openjpa.persistence.QueryImpl.getResultList(QueryImpl.java:302)
at com.ibm.ws.persistence.QueryImpl.getResultList(QueryImpl.java:118)

The specific column is in type Boolean.

SELECT * FROM VariableEntity v is the query being executed. The entity definition is as follows:

@Id
private int pk;

@Version
private int version;

@Column(length = FieldLengths.MAX_NAME_LENGTH_IN_DB)
private String name;

@Column(nullable = false)
private int dataTypeId;

@Column(nullable = false)
private boolean archived;

@Temporal(TemporalType.TIMESTAMP)
private Date creationTime;

@Column(name = "COMMENT1", length = FieldLengths.MAX_COMMENT_LENGTH_IN_DB)
private String comment;`

Column | Type | Modifiers | Storag e | Stats target | Description ---------------------------+--------------------------------+-----------+------- ---+--------------+------------- pk | integer | not null | plain | | name | character varying(128) | | extend ed | | datatypeid | integer | not null | plain | | archived | boolean | not null | plain | | comment1 | character varying(3000) | | extend ed | | creationtime | timestamp(0) without time zone | | plain | | behaviorintimetemplate_pk | integer | | plain | | version | integer | | plain | | Indexes: "pk_variable1" PRIMARY KEY, btree (pk)

This is the JPA trace after i turn on the Websphere logs for JPA:

[10/31/17 14:53:33:772 IST] 000000a7 jdbc_SQL      3   openjpa.jdbc.SQL: Trace: <t -1083439659, conn 120055719> [0 ms] spent
[10/31/17 14:53:33:772 IST] 000000a7 jdbc_JDBC     3   openjpa.jdbc.JDBC: Trace: <t -1083439659, conn 120055719> [0 ms] close
[10/31/17 14:53:33:777 IST] 000000a7 Query         3   openjpa.Query: Trace: Executing query: Query: org.apache.openjpa.kernel.QueryImpl@6e1215c0; candidate class: class com.entities.datatable.VariableEntity; query: null
[10/31/17 14:53:33:778 IST] 000000a7 jdbc_SQL      3   openjpa.jdbc.SQL: Trace: <t -1083439659, conn 2146479213> executing prepstmnt 533235461 SELECT t0.pk, t0.version, t0.archived, t0.behaviorintimetemplate_pk, t0.comment1, t0.creationtime, t0.datatypeid, t0.name FROM ENTITIES.variable1 t0
[10/31/17 14:53:33:779 IST] 000000a7 jdbc_SQL      3   openjpa.jdbc.SQL: Trace: <t -1083439659, conn 2146479213> [1 ms] spent
[10/31/17 14:53:33:779 IST] 000000a7 jdbc_JDBC     3   openjpa.jdbc.JDBC: Trace: <t -1083439659, conn 2146479213> [0 ms] close
[10/31/17 14:53:33:780 IST] 000000a7 BusinessExcep E   CNTR0020E: EJB threw an unexpected (non-declared) exception during invocation of method "findAll" on bean "BeanId(ear#Entities3.jar#NavigatorDAOBean, null)". Exception data:     <openjpa-2.2.3-SNAPSHOT-r422266:1737410 fatal general error> org.apache.openjpa.persistence.PersistenceException: Bad value for type int : f
FailedObject: com.entities.datatable.VariableEntity-600 [java.lang.String]
    at org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.java:4998)
    at org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDictionary.java:4958)
    at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:136)
    at org.apache.openjpa.jdbc.sql.SQLExceptions.getStore(SQLExceptions.java:86)
    at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.initialize(JDBCStoreManager.java:311)
    at com.ibm.ws.persistence.jdbc.kernel.WsJpaJDBCStoreManager.initialize(WsJpaJDBCStoreManager.java:178)
    at org.apache.openjpa.kernel.DelegatingStoreManager.initialize(DelegatingStoreManager.java:112)
    at org.apache.openjpa.kernel.ROPStoreManager.initialize(ROPStoreManager.java:57)
    at org.apache.openjpa.kernel.BrokerImpl.initialize(BrokerImpl.java:1051)
    at org.apache.openjpa.kernel.BrokerImpl.find(BrokerImpl.java:1009)
    at org.apache.openjpa.kernel.BrokerImpl.find(BrokerImpl.java:931)
    at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.load(JDBCStoreManager.java:1033)
    at com.ibm.ws.persistence.jdbc.kernel.WsJpaJDBCStoreManager.load(WsJpaJDBCStoreManager.java:143)
    at org.apache.openjpa.jdbc.sql.AbstractResult.load(AbstractResult.java:280)
    at org.apache.openjpa.jdbc.sql.SelectImpl$SelectResult.load(SelectImpl.java:2381)
    at org.apache.openjpa.jdbc.sql.AbstractResult.load(AbstractResult.java:274)
    at org.apache.openjpa.jdbc.kernel.InstanceResultObjectProvider.getResultObject(InstanceResultObjectProvider.java:60)
    at org.apache.openjpa.kernel.QueryImpl$PackingResultObjectProvider.getResultObject(QueryImpl.java:2082)
    at org.apache.openjpa.lib.rop.EagerResultList.<init>(EagerResultList.java:36)
    at org.apache.openjpa.kernel.QueryImpl.toResult(QueryImpl.java:1258)
    at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:1014)
    at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:870)
    at org.apache.openjpa.kernel.QueryImpl.execute(QueryImpl.java:801)
    at org.apache.openjpa.kernel.DelegatingQuery.execute(DelegatingQuery.java:542)
    at org.apache.openjpa.persistence.QueryImpl.execute(QueryImpl.java:286)
    at org.apache.openjpa.persistence.QueryImpl.getResultList(QueryImpl.java:302)
    at com.ibm.ws.persistence.QueryImpl.getResultList(QueryImpl.java:118)
    at com.entities.BaseDAOBean$CriteriaHelper.getResultList(BaseDAOBean.java:232)
    at com.entities.BaseDAOBean.findAll(BaseDAOBean.java:184)
    at com.entities.navigator.EJSLocal0SLNavigatorDAOBean_c0b845b5.findAll(EJSLocal0SLNavigatorDAOBean_c0b845b5.java)
    at com.sessions.login.LoginGateBean.createDefaultVariablesIfNeeded(LoginGateBean.java:567)
    at com.sessions.login.LoginGateBean.systemInitialization(LoginGateBean.java:538)
    at com.sessions.login.LoginGateBean.initializeSystem(LoginGateBean.java:345)
    at com.sessions.login.EJSRemoteStatelessLoginGate_7faeb7fd.initializeSystem(Unknown Source)
    at com.sessions.login._EJSRemoteStatelessLoginGate_7faeb7fd_Tie.initializeSystem(Unknown Source)
    at com.sessions.login._EJSRemoteStatelessLoginGate_7faeb7fd_Tie._invoke(Unknown Source)
    at com.ibm.CORBA.iiop.ServerDelegate.dispatchInvokeHandler(ServerDelegate.java:628)
    at com.ibm.CORBA.iiop.ServerDelegate.dispatch(ServerDelegate.java:510)
    at com.ibm.rmi.iiop.ORB.process(ORB.java:607)
    at com.ibm.CORBA.iiop.ORB.process(ORB.java:1583)
    at com.ibm.rmi.iiop.Connection.doRequestWork(Connection.java:3166)
    at com.ibm.rmi.iiop.Connection.doWork(Connection.java:3030)
    at com.ibm.rmi.iiop.WorkUnitImpl.doWork(WorkUnitImpl.java:64)
    at com.ibm.ejs.oa.pool.PooledThread.run(ThreadPool.java:118)
    at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1892)
Caused by: org.postgresql.util.PSQLException: Bad value for type int : f
    at org.postgresql.jdbc2.AbstractJdbc2ResultSet.toInt(AbstractJdbc2ResultSet.java:2955)
    at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getInt(AbstractJdbc2ResultSet.java:2138)
    at com.ibm.ws.rsadapter.jdbc.WSJdbcResultSet.getInt(WSJdbcResultSet.java:1480)
    at org.apache.openjpa.lib.jdbc.DelegatingResultSet.getInt(DelegatingResultSet.java:137)
    at org.apache.openjpa.jdbc.sql.BooleanRepresentationFactory$Int10BooleanRepresentation.getBoolean(BooleanRepresentationFactory.java:207)
    at org.apache.openjpa.jdbc.sql.DBDictionary.getBoolean(DBDictionary.java:707)
    at org.apache.openjpa.jdbc.sql.ResultSetResult.getBooleanInternal(ResultSetResult.java:280)
    at org.apache.openjpa.jdbc.sql.AbstractResult.getBoolean(AbstractResult.java:402)
    at org.apache.openjpa.jdbc.meta.strats.PrimitiveFieldStrategy.load(PrimitiveFieldStrategy.java:201)
    at org.apache.openjpa.jdbc.meta.FieldMapping.load(FieldMapping.java:930)
    at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.load(JDBCStoreManager.java:1111)
    at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.load(JDBCStoreManager.java:1059)
    at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.initializeState(JDBCStoreManager.java:411)
    at org.apache.openjpa.jdbc.kernel.JDBCStoreManager.initialize(JDBCStoreManager.java:306)
    ... 40 more
Mickey Hovel
  • 982
  • 1
  • 15
  • 31
  • Seems like the column is `integer` and not `boolean` after all. You'll have to share the statement and its parameters for more. – Laurenz Albe Oct 31 '17 at 08:06
  • The column is for sure a boolean i've viewd the db data type of the column – Mickey Hovel Oct 31 '17 at 08:10
  • 1
    The column is for sure NOT a boolean, because the error message is telling you it is an integer. Check the postgresql server logs and make sure the error is talking about the same column you are. – Richard Huxton Oct 31 '17 at 08:16
  • In a nutshell: If you know better than we do, why do you ask? As I said, please share the statement and its parameters. – Laurenz Albe Oct 31 '17 at 08:18
  • I didn't say i know the reason, i sayed i know that the column type is for sure boolean cause i see it in the db. adding above all additional neccessary data, and again thanks for trying to help – Mickey Hovel Oct 31 '17 at 08:42
  • 1
    To troubleshoot the actual cause and eliminate the guesswork, turn on WAS tracing of JPA JDBC statements using the tracespec "openjpa.jdbc.SQL=all". If you're not using container managed persistence, instructions for enabling JPA trace can be found at https://stackoverflow.com/questions/39117510/websphere-8-5-5-does-not-logging-openjpa – F Rowe Oct 31 '17 at 12:45
  • truned it on, and attaching the trace to the original issue above – Mickey Hovel Oct 31 '17 at 12:59
  • did i provide sufficient data to have an idea about the probelm? – Mickey Hovel Nov 02 '17 at 12:18

0 Answers0