java.sql.SQLRecoverableException: Closed Connection; State=08003; ErrorCode=17008
I am getting the below error while inserting some rows in oracle database table by using loops java my loop will go nearly 25000 times:
java.sql.SQLRecoverableException: Closed Connection; State=08003; ErrorCode=1700
8
at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnec
tion.java:3331)
at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxy
Connection.java:275)
at org.compiere.db.PreparedStatementProxy.init(PreparedStatementProxy.ja
va:71)
at org.compiere.db.PreparedStatementProxy.<init>(PreparedStatementProxy.
java:44)
at org.compiere.db.ProxyFactory.newCPreparedStatement(ProxyFactory.java:
54)
at org.compiere.util.DB.executeUpdate(DB.java:1007)
at org.compiere.util.DB.executeUpdate(DB.java:877)
at org.compiere.util.DB.executeUpdate(DB.java:864)
at org.compiere.report.FinReport.insertLineSource(FinReport.java:1033)
at org.compiere.report.FinReport.insertLineDetail(FinReport.java:844)
at org.compiere.report.FinReport.doIt(FinReport.java:306)
at org.compiere.process.SvrProcess.process(SvrProcess.java:147)
at org.compiere.process.SvrProcess.startProcess(SvrProcess.java:105)
Below is my code:
private void insertLineSource (int line)
{
log.info("Line=" + line + " - " + m_lines[line]);
// No source lines
if (m_lines[line] == null || m_lines[line].getSources().length == 0)
return;
String variable = m_lines[line].getSourceColumnName();
if (variable == null)
return;
log.fine("Variable=" + variable);
// Insert
StringBuffer insert = new StringBuffer("INSERT INTO T_Report "
+ "(AD_PInstance_ID, PA_ReportLine_ID, Record_ID,Fact_Acct_ID,LevelNo ");
for (int col = 0; col < m_columns.length; col++)
insert.append(",Col_").append(col);
// Select
insert.append(") SELECT ")
.append(getAD_PInstance_ID()).append(",")
.append(m_lines[line].getPA_ReportLine_ID()).append(",")
.append(variable).append(",0,");
if (p_DetailsSourceFirst)
insert.append("-1 ");
else
insert.append("1 ");
// for all columns create select statement
for (int col = 0; col < m_columns.length; col++)
{
insert.append(", ");
// No calculation
if (m_columns[col].isColumnTypeCalculation())
{
insert.append("NULL");
continue;
}
// SELECT SUM()
StringBuffer select = new StringBuffer ("SELECT ");
if (m_lines[line].getPAAmountType() != null) // line amount type overwrites column
select.append (m_lines[line].getSelectClause (true));
else if (m_columns[col].getPAAmountType() != null)
select.append (m_columns[col].getSelectClause (true));
else
{
insert.append("NULL");
continue;
}
if (p_PA_ReportCube_ID > 0) {
select.append(" FROM Fact_Acct_Summary fb WHERE DateAcct ");
} //report cube
else {
// Get Period info
select.append(" FROM Fact_Acct fb WHERE TRUNC(DateAcct, 'DD') ");
}
FinReportPeriod frp = getPeriod (m_columns[col].getRelativePeriod());
if (m_lines[line].getPAPeriodType() != null) // line amount type overwrites column
{
if (m_lines[line].isPeriod())
select.append(frp.getPeriodWhere());
else if (m_lines[line].isYear())
select.append(frp.getYearWhere());
else if (m_lines[line].isNatural())
select.append(frp.getNaturalWhere("fb"));
else
select.append(frp.getTotalWhere());
}
else if (m_columns[col].getPAPeriodType() != null)
{
if (m_columns[col].isPeriod())
select.append(frp.getPeriodWhere());
else if (m_columns[col].isYear())
select.append(frp.getYearWhere());
else if (m_columns[col].isNatural())
select.append(frp.getNaturalWhere("fb"));
else
select.append(frp.getTotalWhere());
}
// Link
select.append(" AND fb.").append(variable).append("=x.").append(variable);
// PostingType
if (!m_lines[line].isPostingType()) // only if not defined on line
{
String PostingType = m_columns[col].getPostingType();
if (PostingType != null && PostingType.length() > 0)
select.append(" AND fb.PostingType='").append(PostingType).append("'");
// globalqss - CarlosRuiz
if (PostingType.equals(MReportColumn.POSTINGTYPE_Budget)) {
if (m_columns[col].getGL_Budget_ID() > 0)
select.append(" AND GL_Budget_ID=" + m_columns[col].getGL_Budget_ID());
}
// end globalqss
}
// Report Where
String s = m_report.getWhereClause();
if (s != null && s.length() > 0)
select.append(" AND ").append(s);
// Limited Segment Values
if (m_columns[col].isColumnTypeSegmentValue())
select.append(m_columns[col].getWhereClause(p_PA_Hierarchy_ID));
// Parameter Where
select.append(m_parameterWhere);
// System.out.println(" c=" + col + ", l=" + line + ": " + select);
//
insert.append("(").append(select).append(")");
}
// WHERE (sources, posting type)
StringBuffer where = new StringBuffer(m_lines[line].getWhereClause(p_PA_Hierarchy_ID));
String s = m_report.getWhereClause();
if (s != null && s.length() > 0)
{
if (where.length() > 0)
where.append(" AND ");
where.append(s);
}
if (where.length() > 0)
where.append(" AND ");
where.append(variable).append(" IS NOT NULL");
if (p_PA_ReportCube_ID > 0)
insert.append(" FROM Fact_Acct_Summary x WHERE ").append(where);
else
// FROM .. WHERE
insert.append(" FROM Fact_Acct x WHERE ").append(where);
//
insert.append(m_parameterWhere)
.append(" GROUP BY ").append(variable);
int no = DB.executeUpdate(insert.toString(), get_TrxName());
if (CLogMgt.isLevelFinest())
log.fine("Source #=" + no + " - " + insert);
if (no == 0)
return;
// Set Name,Description
StringBuffer sql = new StringBuffer ("UPDATE T_Report SET (Name,Description)=(")
.append(m_lines[line].getSourceValueQuery()).append("T_Report.Record_ID) "
//
+ "WHERE Record_ID <> 0 AND AD_PInstance_ID=").append(getAD_PInstance_ID())
.append(" AND PA_ReportLine_ID=").append(m_lines[line].getPA_ReportLine_ID())
.append(" AND Fact_Acct_ID=0");
no = DB.executeUpdate(sql.toString(), get_TrxName());
if (CLogMgt.isLevelFinest())
log.fine("Name #=" + no + " - " + sql.toString());
if (m_report.isListTrx())
insertLineTrx (line, variable);
} // insertLineSource
Answer
Application code should not pass a cached connection handle from one instance of a data access client to another client instance. Transferring the connection handle between client instances creates the problematic contingency of one instance using a connection handle that is referenced by another. For example, when the application code of a client instance that receives a transferred handle closes the handle and the client instance that retains the original reference to the handle tries to reclaim it, the application server issues an exception. shown some exceptions expected in this case. Exception description: An exception was detected cleaning up the ManagedConnection for a destroy operation. Refer to the error reported by the database software to help determine the cause of the error. Exception
0000004d WSRdbManagedC W DSRA0180W: Exception detected during
ManagedConnection.destroy(). The exception is:
com.ibm.ws.exception.WsException: DSRA0080E: An exception was received by
the Data Store Adapter Invalid operation: Connection is closed. ERRORCODE=-
4470, SQLSTATE=08003. With SQL State: 08003 SQL Code : -4470
Read this for more unertstanding