Hello,
The team I'm on is working on implementing a reporting infrastructure for the product that we work on. As part of this infrastructure, we are storing SQL text (to generate the report result set) and the JRXML (Jasper Reporting) as CLOB objects in our database.
While developing for this project we were using Tomcat 7, and all insert/update statements worked fine against the table. However, once we shipped the code to our QA team and deployed it in our main WAS7 development environment, the inserts and updates to the tables with CLOB objects fail (with WAS7). This happens against both DB2 and Oracle databases.
I created a question on StackOverflow but have not yet received an answer (it's only been a day): http://stackoverflow.com/questions/24071192/clob-object-inserts-updates-failing-in-websphere-7-using-hibernate
Relevant part of the logs:
Code:
[6/5/14 16:44:00:498 CDT] TRACE o.h.p.entity.AbstractEntityPersister - Inserting entity: [jpa.model.ReportQuery#component[entityType,reportType]{entityType=*I, reportType=CST001}]
[6/5/14 16:44:00:499 CDT] TRACE o.h.e.j.b.internal.BatchBuilderImpl - Building batch [size=1]
[6/5/14 16:44:00:499 CDT] DEBUG org.hibernate.SQL - insert into QC04COMN.REPORT_QUERY (SQL_TEXT, ENTITY_TYPE, REPORT_TYPE) values (?, ?, ?)
[6/5/14 16:44:00:499 CDT] TRACE o.h.e.j.internal.JdbcCoordinatorImpl - Registering statement [insert into QC04COMN.REPORT_QUERY (SQL_TEXT, ENTITY_TYPE, REPORT_TYPE) values (?, ?, ?)]
[6/5/14 16:44:00:501 CDT] TRACE o.h.p.entity.AbstractEntityPersister - Dehydrating entity: [jpa.model.ReportQuery#component[entityType,reportType]{entityType=*I, reportType=CST001}]
[6/5/14 16:44:00:502 CDT] TRACE o.h.type.descriptor.sql.BasicBinder - binding parameter [1] as [CLOB] - select this from that
[6/5/14 16:44:00:509 CDT] TRACE o.h.e.j.internal.JdbcCoordinatorImpl - Starting after statement execution processing [ON_CLOSE]
[6/5/14 16:44:00:512 CDT] DEBUG o.h.e.t.spi.AbstractTransactionImpl - rolling back
[6/5/14 16:44:00:565 CDT] DEBUG o.h.e.t.i.jdbc.JdbcTransaction - rolled JDBC Connection
[6/5/14 16:44:00:565 CDT] DEBUG o.h.e.t.i.jdbc.JdbcTransaction - re-enabling autocommit
[6/5/14 16:44:00:565 CDT] TRACE o.h.e.t.i.TransactionCoordinatorImpl - after transaction completion
[6/5/14 16:44:00:565 CDT] TRACE org.hibernate.internal.SessionImpl - after transaction completion
[6/5/14 16:44:00:566 CDT] TRACE org.hibernate.internal.SessionImpl - Closing session
[6/5/14 16:44:00:566 CDT] TRACE o.h.e.j.internal.JdbcCoordinatorImpl - Closing JDBC container [org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl@5720572]
[6/5/14 16:44:00:567 CDT] DEBUG o.h.e.j.internal.JdbcCoordinatorImpl - HHH000420: Closing un-released batch
[6/5/14 16:44:00:567 CDT] INFO o.h.e.j.b.internal.AbstractBatchImpl - HHH000010: On release of batch it still contained JDBC statements
[6/5/14 16:44:00:567 CDT] TRACE o.h.e.j.internal.JdbcCoordinatorImpl - Releasing statement [insert into QC04COMN.REPORT_QUERY (SQL_TEXT, ENTITY_TYPE, REPORT_TYPE) values (?, ?, ?)]
[6/5/14 16:44:00:567 CDT] TRACE o.h.e.j.internal.JdbcCoordinatorImpl - Closing prepared statement [insert into QC04COMN.REPORT_QUERY (SQL_TEXT, ENTITY_TYPE, REPORT_TYPE) values (?, ?, ?)]
[6/5/14 16:44:00:567 CDT] TRACE o.h.e.j.internal.JdbcCoordinatorImpl - Starting after statement execution processing [ON_CLOSE]
[6/5/14 16:44:00:567 CDT] TRACE o.h.e.j.internal.JdbcCoordinatorImpl - Closing prepared statement [select reportquer0_.ENTITY_TYPE as ENTITY1_26_0_, reportquer0_.REPORT_TYPE as REPORT2_26_0_, reportquer0_.SQL_TEXT as SQL3_26_0_ from QC04COMN.REPORT_QUERY reportquer0_ where reportquer0_.ENTITY_TYPE=? and reportquer0_.REPORT_TYPE=?]
[6/5/14 16:44:00:568 CDT] DEBUG o.h.e.j.internal.JdbcCoordinatorImpl - Exception clearing maxRows/queryTimeout [Closed Statement]
[6/5/14 16:44:00:568 CDT] TRACE o.h.e.j.i.LogicalConnectionImpl - Closing logical connection
[6/5/14 16:44:00:568 CDT] DEBUG o.h.e.j.i.LogicalConnectionImpl - Releasing JDBC connection
[6/5/14 16:44:00:568 CDT] DEBUG o.h.e.j.i.LogicalConnectionImpl - Released JDBC connection
[6/5/14 16:44:00:568 CDT] TRACE o.h.e.j.i.LogicalConnectionImpl - Logical connection closed
And here is how we define the column for our entity:
Code:
@Entity
@Table(name = "REPORT_QUERY")
public class ReportQuery implements java.io.Serializable {
private static final long serialVersionUID = 1L;
@EmbeddedId
private ReportQueryId id;
@Lob
@Column(name = "SQL_TEXT", nullable = false, length = 32768)
private String sqlText;
When we stop using Hibernate and just go directly to the database using BoneCP, the inserts and updates work fine. Is this a known issue with Hibernate/WAS7? We would like to continue using Hibernate as it is the backbone for our JPA2 entities, but now we have resorted to manually writing queries and directly executing them.
Other Info:
Hibernate 4.2.13 Final (tried with 4.2.0.Final but upgraded to see if there was a fix)
Spring Data JPA 1.0.2.RELEASE (using JPA 2)
Spring 3.1.1.RELEASE (we don't actually use Spring, but needed it for dependencies)
BoneCP 0.8.0-rc1
Java 1.6
WebSphere 7.0.0.29
I was going to try updating to Hibernate 4.3.5 to see if that fixes the issue but that uses JPA 2.1 which Spring Data JPA does not seem to support yet.
Any advice/help would be much appreciated.
Thanks,
Dan