java.sql.SQLException: Closed Resultset: next

[jaya kemmannu]

Hello

We are getting intermittent SQLException like Closed Resultset. We have Oracle 10g and using oracle_jdbc.jar and jboss 4.2.2 version. Find below the trace.

2011-10-11 10:11:20,861 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: java.sql.SQLException: Closed Resultset: next
2011-10-11 10:11:20,861 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:224)
2011-10-11 10:11:20,861 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at org.jboss.resource.adapter.jdbc.WrappedResultSet.next(WrappedResultSet.java:1184)
2011-10-11 10:11:20,861 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.mnp.ossbss.dao.OSSBSSAdapterInsertRecordDAO.updatePortCompleteNotification(Unknown Source)
2011-10-11 10:11:20,861 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.mnp.ossbss.process.InsertPortInMessages.ProcessPortCompleteNotification(Unknown Source)
2011-10-11 10:11:20,861 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.mnp.ossbss.webservice.ProcessOSSBSSMessage.processGenericMessage(Unknown Source)
2011-10-11 10:11:20,861 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.mnp.ossbss.webservice.ProcessOSSBSSMessage.executeMessage(Unknown Source)
2011-10-11 10:11:20,861 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.np.sig.npwsimpl.HpnpOssBssAdapterServiceSkeleton.processRequest(Unknown Source)
2011-10-11 10:11:20,862 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.np.mwfm.modules.LNPDBWebServiceModule.invoke(LNPDBWebServiceModule.java:184)
2011-10-11 10:11:20,862 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.np.mwfm.NPWebServiceNode.nodeEntered(NPWebServiceNode.java:312)
2011-10-11 10:11:20,862 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.ov.activator.mwfm.engine.WFMEngineWorker._step1(WFMEngineWorker.java:849)
2011-10-11 10:11:20,862 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.ov.activator.mwfm.engine.WFMEngineWorker._updateState(WFMEngineWorker.java:1412)
2011-10-11 10:11:20,862 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.ov.activator.mwfm.engine.WFMEngineWorker._step1(WFMEngineWorker.java:892)
2011-10-11 10:11:20,862 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.ov.activator.mwfm.engine.WFMEngineWorker.access$300(WFMEngineWorker.java:107)
2011-10-11 10:11:20,862 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.ov.activator.mwfm.engine.WFMEngineWorker$8.run(WFMEngineWorker.java:1388)
2011-10-11 10:11:20,862 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.ov.activator.mwfm.engine.EngineWorkerThread.run(EngineWorkerThread.java:90)
2011-10-11 10:11:20,862 ERROR [MPG_SIG] 8000|14572222|SQL Exception occurred in updatePortCompleteNotification mehtod
2011-10-11 10:11:20,862 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: com.mnp.ossbss.exception.OSSBSSDBException: Closed Resultset: next
2011-10-11 10:11:20,862 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.mnp.ossbss.dao.OSSBSSAdapterInsertRecordDAO.updatePortCompleteNotification(Unknown Source)
2011-10-11 10:11:20,862 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.mnp.ossbss.process.InsertPortInMessages.ProcessPortCompleteNotification(Unknown Source)
2011-10-11 10:11:20,862 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.mnp.ossbss.webservice.ProcessOSSBSSMessage.processGenericMessage(Unknown Source)
2011-10-11 10:11:20,863 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.mnp.ossbss.webservice.ProcessOSSBSSMessage.executeMessage(Unknown Source)
2011-10-11 10:11:20,863 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.np.sig.npwsimpl.HpnpOssBssAdapterServiceSkeleton.processRequest(Unknown Source)
2011-10-11 10:11:20,863 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.np.mwfm.modules.LNPDBWebServiceModule.invoke(LNPDBWebServiceModule.java:184)
2011-10-11 10:11:20,863 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.np.mwfm.NPWebServiceNode.nodeEntered(NPWebServiceNode.java:312)
2011-10-11 10:11:20,863 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.ov.activator.mwfm.engine.WFMEngineWorker._step1(WFMEngineWorker.java:849)
2011-10-11 10:11:20,863 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.ov.activator.mwfm.engine.WFMEngineWorker._updateState(WFMEngineWorker.java:1412)
2011-10-11 10:11:20,863 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.ov.activator.mwfm.engine.WFMEngineWorker._step1(WFMEngineWorker.java:892)
2011-10-11 10:11:20,863 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.ov.activator.mwfm.engine.WFMEngineWorker.access$300(WFMEngineWorker.java:107)
2011-10-11 10:11:20,863 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.ov.activator.mwfm.engine.WFMEngineWorker$8.run(WFMEngineWorker.java:1388)
2011-10-11 10:11:20,863 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.ov.activator.mwfm.engine.EngineWorkerThread.run(EngineWorkerThread.java:90)
2011-10-11 10:11:20,863 ERROR [MPG_SIG] 1060|14572222|OSSBSSDBException occurred for Txn Id 14572222 : - Closed Resultset: next
2011-10-11 10:11:20,863 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: com.mnp.ossbss.exception.OSSBSSDBException: Closed Resultset: next
2011-10-11 10:11:20,863 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.mnp.ossbss.dao.OSSBSSAdapterInsertRecordDAO.updatePortCompleteNotification(Unknown Source)
2011-10-11 10:11:20,863 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.mnp.ossbss.process.InsertPortInMessages.ProcessPortCompleteNotification(Unknown Source)
2011-10-11 10:11:20,863 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.mnp.ossbss.webservice.ProcessOSSBSSMessage.processGenericMessage(Unknown Source)
2011-10-11 10:11:20,863 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.mnp.ossbss.webservice.ProcessOSSBSSMessage.executeMessage(Unknown Source)
2011-10-11 10:11:20,864 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.np.sig.npwsimpl.HpnpOssBssAdapterServiceSkeleton.processRequest(Unknown Source)
2011-10-11 10:11:20,864 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.np.mwfm.modules.LNPDBWebServiceModule.invoke(LNPDBWebServiceModule.java:184)
2011-10-11 10:11:20,864 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.np.mwfm.NPWebServiceNode.nodeEntered(NPWebServiceNode.java:312)
2011-10-11 10:11:20,864 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.ov.activator.mwfm.engine.WFMEngineWorker._step1(WFMEngineWorker.java:849)
2011-10-11 10:11:20,864 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.ov.activator.mwfm.engine.WFMEngineWorker._updateState(WFMEngineWorker.java:1412)
2011-10-11 10:11:20,864 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.ov.activator.mwfm.engine.WFMEngineWorker._step1(WFMEngineWorker.java:892)
2011-10-11 10:11:20,864 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.ov.activator.mwfm.engine.WFMEngineWorker.access$300(WFMEngineWorker.java:107)
2011-10-11 10:11:20,864 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.ov.activator.mwfm.engine.WFMEngineWorker$8.run(WFMEngineWorker.java:1388)
2011-10-11 10:11:20,864 ERROR [STDERR] Oct 11, 2011 10:11:20 AM [Plug-in standard error]: at com.ov.activator.mwfm.engine.EngineWorkerThread.run(EngineWorkerThread.java:90)


Any guidance on fixing this would really help

Thanks

[Gopakumar Naryanan]

This exception is thrown when Statement or Connection object is closed prior to ResultSet object.(not sure!!)
Anyhow it could be better for us to help, if you share the code.

Thanks & Regards
Gopakumar

[jaya kemmannu]

Hi Gopakumar,

Thanks for your reply, below is the code where exception is thrown

public void updatePortCompleteNotification(PortOrderVO pPortOrderVO)throws OSSBSSDBException
{

Statement stmt = null;
ResultSet res = null;
PreparedStatement updStmt = null;
Connection vSqlConnection = null;
OSSBSSConnectionManager vDBConnectionManager = null;
try
{
vDBConnectionManager = new OSSBSSConnectionManager();
vSqlConnection = vDBConnectionManager.getOracleJDBCConnection();
vSqlConnection.setAutoCommit(false);

for(int vTNNumberRangeIndex = 0; vTNNumberRangeIndex <= vTNNumberRange; vTNNumberRangeIndex++)
{
stmt = vSqlConnection.createStatement();

vQuery = "SELECT STATUS_CHANGE from " +
"LOOP_MPG_OSSBSSGW_PORT_ORDERS WHERE " +vSubQuery1+ vSubQuery;

res = stmt.executeQuery(vQuery);
if(res != null)
{
while(res.next())
{
vStatusChange = res.getString("STATUS_CHANGE")+ "|" + vStatusChange ;
}
}
res.close();
stmt.close();

checkRecordState(vLogId, vSubQuery, vSubQuery1, vRequestType ,vSqlConnection);

vQuery = "UPDATE LOOP_MPG_OSSBSSGW_PORT_ORDERS SET " +
"REQUEST_TYPE='"+ vRequestType +"', " +
"LAST_UPDATE_TIMESTAMP=to_date('" +vReceivedTime +"','YYYY-MM-DD HH24:MI:SS'),"+
"RECORD_STATE='P' ," +
"STATUS_CHANGE='" + vStatusChange +"' " +
"WHERE "+ vSubQuery1 + vSubQuery;

updStmt = vSqlConnection.prepareStatement(vQuery);
vUpdateSucess = updStmt.executeUpdate();
updStmt.close();
} //end for loop

vSqlConnection.commit();

} //end try block
catch(SQLException exp)
{
exp.printStackTrace();
throw new OSSBSSDBException(exp.getMessage());
}
catch(OSSBSSDBException exp)
{
exp.printStackTrace();
throw exp;
}
finally
{
if(res != null)
{
try { res.close(); } catch(SQLException eSQLExp1) { eSQLExp1.printStackTrace(); }
}
if(updStmt != null)
{
try { updStmt.close(); } catch(SQLException eSQLExp2) { eSQLExp2.printStackTrace(); }
}
if(stmt != null)
{
try { stmt.close(); } catch(SQLException eSQLExp3) { eSQLExp3.printStackTrace(); }
}
if(vSqlConnection!=null)
{
try { vSqlConnection.close(); } catch(SQLException eSQLExp4) { eSQLExp4.printStackTrace(); }
}
} //end finally
} //end method


private void checkRecordState(String pTxnId, String pSubQuery, String pSubQuery1, String pInputMessage, Connection vSqlConnection)
{
Statement stmt = null;
ResultSet vDBSelectString = null;
try
{
stmt = vSqlConnection.createStatement();
String vQuery = "SELECT REQUEST_TYPE, RECORD_STATE FROM LOOP_MPG_OSSBSSGW_PORT_ORDERS " +
"WHERE " + pSubQuery1 + pSubQuery;
vDBSelectString = stmt.executeQuery(vQuery);

if(vDBSelectString != null)
{
while(vDBSelectString.next())
{
vCurrentRequestType = vDBSelectString.getString("REQUEST_TYPE");
vCurrentRecordStatus = vDBSelectString.getString("RECORD_STATE");

if(isMsgOutOfSeq(pTxnId, pInputMessage, vCurrentRequestType))
{
//code
}
if(vCurrentRecordStatus.equalsIgnoreCase("L"))
{
//code
}
} //end while
}end if
vDBSelectString.close();
stmt.close();
} //end try block

catch(SQLException eSQLException)
{
eSQLException.printStackTrace();
}
}

Regards,
Jay

[Jitesh Sinha]

Your exception stack trace does not mention line numbers?Do you know which line is throwing error?
Looking at the stack trace it seems,error is coming at res.next() statement.
In any case,I don't see why that exception would come.But it will be easy for you and others if you make your code a little simpler -

a.)Avoid firing queries in for loop.Not only it is inefficient,it is error prone too.Better modify your sql query so that you do not need a for loop.In most of the cases,it is very much possible .

b.)Each db query/transaction should have a different method.That will make code easier to maintain and more readable.just like you have checkrecordstate, have a separate method for your update statement.

c.)Use preparedstatement instead of simple Statement class.

[jaya kemmannu]

Hi Jitesh,

Thank you for the inputs to improve the coding standard . I will try to follow your inputs in my code. The Error is at below part of the code :

res = stmt.executeQuery(vQuery);
if(res != null)
{
while(res.next())
{
vStatusChange = res.getString("STATUS_CHANGE")+ "|" + vStatusChange ;
}
}

Any Idea why the Exception in this part of code ?

You also told me to use Prepared Statement instead of Statement. Is it OK to use Prepared Statement for simple SELECT Query ?

One more thing you pointed out is stack trace do not show the line numbers, I am fresher to java code. any Idea how can get displayed line numbers in trace ?

Thanks & Regards,
Jay

[Jitesh Sinha]

For line numbers in exception stack trace - you can search on web - there is a host of info available - one such link - http://blog.puneetlakhina.com/2008/02/line-numbers-in-java-exception-stack.html

It is always advisable to use PreparedStatement in place of statement.

About your actual problem- have you pasted all of your code?For example I do not see how vTNNumberRangeIndex is getting used inside for loop.

My guess(although it is not useful) is that resultset is getting closed in one of iterations of for loop and then code tries to execute res.next in next iteration.

[jaya kemmannu]

Hi Jitesh,

Stack trace line number issue was fixed. I have changed ant build.xml so that it will display line numbers.

I have not posted entire portion of the code, pasted here was part of the code where error occur.

Anyway i have attached the entire portion of the code for more analysis. One more thing i would like point out here is the issue occur when bulk requests are being processed at a time.

Thanks & Regards,
Jaya

[jaya kemmannu]

Looks like attachment is not uploaded. below is complete code.


public void updatePortCompleteNotification(PortOrderVO pPortOrderVO)throws OSSBSSDBException
{
String vNPTXNID = pPortOrderVO.getMNPTXNID();
String vOrderID = pPortOrderVO.getMOrderId();
boolean CheckOrderId = false;
String vMSISDN = null;
String vRequestType = null;
String vStatusChange = null;
String vTNTo = null;
String vQuery = null;
String vSubQuery = null;
String vLogId = null;
String vSubQuery1 = null;
Statement stmt = null;
ResultSet res = null;
PreparedStatement updStmt = null;
String vMessageTime = null;
String vReceivedTime = null;

try
{
if(vOrderID == null || vOrderID.equalsIgnoreCase("null"))
{
CheckOrderId = true;
}
if(CheckOrderId)
{
mLogger.logMessage(OSSBSSSystemConstants.DB_OPERATION, vNPTXNID,
MPGLogLevels.SEVERITY_INFO, MPGModuleNames.MODULE_SIG,
"OSSBSSInsertRecordDAO::The incoming XML is Port-Out,stop further processing");
return;
}
}catch(Exception e){ e.printStackTrace(); }

Connection vSqlConnection = null;
OSSBSSConnectionManager vDBConnectionManager = null;

vMSISDN = pPortOrderVO.getMMSISDN();
vRequestType = pPortOrderVO.getMRequestType();
vStatusChange = pPortOrderVO.getMStatusChange();
vTNTo = pPortOrderVO.getMTNTo();
vMessageTime = pPortOrderVO.getMLastUpdateTime();
//get only the date remove the 'T' from it and also the timezone from it
String vDate = vMessageTime.substring(0,10);
String vTime = vMessageTime.substring(11,19);

vReceivedTime = vDate+" "+vTime;

try
{
// Get Oracle Database connection
vDBConnectionManager = new OSSBSSConnectionManager();
vSqlConnection = vDBConnectionManager.getOracleJDBCConnection();

long vTNNumberRange = 0;
long vTNs = 0;
if(!(vMSISDN == null || vMSISDN == ""))
{
vTNNumberRange = Long.parseLong(vTNTo) - Long.parseLong(vMSISDN);
vTNs = new Long(vMSISDN).longValue();
}

vSqlConnection.setAutoCommit(false);
int vUpdateSucess = 0;

// to decide for the where clause parameters (ORDER_ID / NPTXN_ID)
if(vRequestType.equalsIgnoreCase(OSSBSSSystemConstants.GEN_PORT_COMPLETE_NOTIFICATION))
{
/** mLogger.logMessage(OSSBSSSystemConstants.DB_OPERATION, vNPTXNID,
MPGLogLevels.SEVERITY_INFO, MPGModuleNames.MODULE_SIG,
"OSSBSSInsertRecordDAO::Entered In case of In Message"); **/

vLogId = vNPTXNID;
vSubQuery = "NP_TXN_ID='"+ vNPTXNID +"'";
}

// update the record status
for(int vTNNumberRangeIndex = 0; vTNNumberRangeIndex <= vTNNumberRange; vTNNumberRangeIndex++)
{
vTNs = vTNs+vTNNumberRangeIndex;
String vTN = ""+vTNs;

if(vMSISDN != null || vMSISDN != "")
{
vSubQuery1 = "MSISDN='"+ vTN+"' AND ";
}

// create a sql statement to execute query on LOOP_MPG_OSSBSSGW_PORT_ORDERS
stmt = vSqlConnection.createStatement();

//Retrieve the current value of STATUS_CHANGE
vQuery = "SELECT STATUS_CHANGE from " +
"LOOP_MPG_OSSBSSGW_PORT_ORDERS WHERE " +vSubQuery1+ vSubQuery;

res = stmt.executeQuery(vQuery);
if(res != null)
{
while(res.next())
{
vStatusChange = res.getString("STATUS_CHANGE")+ "|" + vStatusChange ;
}
}
res.close();
stmt.close();

if(vRequestType.equalsIgnoreCase(OSSBSSSystemConstants.GEN_PORT_COMPLETE_NOTIFICATION)
|| vRequestType.equalsIgnoreCase(OSSBSSSystemConstants.GEN_PORT_PROCESS_COMPLETE))
{
String vPortType = getPortType(vStatusChange);
if( vRequestType.equalsIgnoreCase(OSSBSSSystemConstants.GEN_PORT_COMPLETE_NOTIFICATION) &&
(vPortType.equalsIgnoreCase("POUTRE") || vPortType.equalsIgnoreCase("POUTTERM")) )
return;
if( vRequestType.equalsIgnoreCase(OSSBSSSystemConstants.GEN_PORT_PROCESS_COMPLETE) &&
(vPortType.equalsIgnoreCase("PINRE") || vPortType.equalsIgnoreCase("PINTERM")) )
return;
if( ( vRequestType.equalsIgnoreCase(OSSBSSSystemConstants.GEN_PORT_COMPLETE_NOTIFICATION)
|| vRequestType.equalsIgnoreCase(OSSBSSSystemConstants.GEN_PORT_PROCESS_COMPLETE) )
&& ( vPortType.equalsIgnoreCase("POUTTERM") || vPortType.equalsIgnoreCase("PINTERM") ) )
return;
if( ( vRequestType.equalsIgnoreCase(OSSBSSSystemConstants.GEN_PORT_COMPLETE_NOTIFICATION)
|| vRequestType.equalsIgnoreCase(OSSBSSSystemConstants.GEN_PORT_PROCESS_COMPLETE) )
&& ( vPortType.equalsIgnoreCase("PINCOM") || vPortType.equalsIgnoreCase("POUTCOM")))
return;
}

// Check for record state
checkRecordState(vLogId, vSubQuery, vSubQuery1, vRequestType ,vSqlConnection);

vQuery = "UPDATE LOOP_MPG_OSSBSSGW_PORT_ORDERS SET " +
"REQUEST_TYPE='"+ vRequestType +"', " +
"LAST_UPDATE_TIMESTAMP=to_date('" +vReceivedTime +"','YYYY-MM-DD HH24:MI:SS'),"+
"RECORD_STATE='P' ," +
"STATUS_CHANGE='" + vStatusChange +"' " +
"WHERE "+ vSubQuery1 + vSubQuery;

updStmt = vSqlConnection.prepareStatement(vQuery);

// Execute Update query on LOOP_MPG_OSSBSSGW_PORT_ORDERS
vUpdateSucess = updStmt.executeUpdate();
updStmt.close();
} //end for loop

vSqlConnection.commit();

if(vUpdateSucess != 0)
{
mLogger.logMessage(OSSBSSSystemConstants.DB_OPERATION, vNPTXNID,
MPGLogLevels.SEVERITY_INFO, MPGModuleNames.MODULE_SIG,
"OSSBSSInsertRecordDAO:: Successfully updated the message with OrderID: '" +vOrderID+
"', NPTXNID : '" +vNPTXNID+ "' & RequestType '" +vRequestType+
"' into LOOP_MPG_OSSBSSGW_PORT_ORDERS ");
}
else
{
mLogger.logMessage(OSSBSSSystemConstants.DB_OPERATION, vNPTXNID,
MPGLogLevels.SEVERITY_WARN, MPGModuleNames.MODULE_SIG,
"OSSBSSInsertRecordDAO:: Failed to update the message with OrderID: '" +vOrderID+
"', NPTXNID : '" +vNPTXNID+ "' & RequestType '" +vRequestType+
"' into LOOP_MPG_OSSBSSGW_PORT_ORDERS ");
}
mLogger.logMessage(OSSBSSSystemConstants.DB_OPERATION, vNPTXNID,
MPGLogLevels.SEVERITY_DEBUG, MPGModuleNames.MODULE_SIG,
"OSSBSSInsertRecordDAO::Exiting updatePortCompleteNotification method");
} //end try block
catch(SQLException exp)
{
exp.printStackTrace();
throw new OSSBSSDBException(exp.getMessage());
}
catch(OSSBSSDBException exp)
{
exp.printStackTrace();
throw exp;
}
finally
{
if(res != null)
{
try { res.close(); } catch(SQLException eSQLExp1) { eSQLExp1.printStackTrace(); }
}
if(updStmt != null)
{
try { updStmt.close(); } catch(SQLException eSQLExp2) { eSQLExp2.printStackTrace(); }
}
if(stmt != null)
{
try { stmt.close(); } catch(SQLException eSQLExp3) { eSQLExp3.printStackTrace(); }
}
if(vSqlConnection!=null)
{
try { vSqlConnection.close(); } catch(SQLException eSQLExp4) { eSQLExp4.printStackTrace(); }
}
}
}





private void checkRecordState(String pTxnId, String pSubQuery, String pSubQuery1, String pInputMessage, Connection vSqlConnection)
{
mLogger.logMessage(OSSBSSSystemConstants.DB_OPERATION, pTxnId,
MPGLogLevels.SEVERITY_DEBUG, MPGModuleNames.MODULE_SIG,
"OSSBSSInsertRecordDAO::Inside checkRecordState method ");

//OSSBSSConnectionManager vDBConnectionManager = null;
//Connection vSqlConnection = null;
String vCurrentRequestType = null;
String vCurrentRecordStatus = null;
Statement stmt = null;
ResultSet vDBSelectString = null;
try
{
//vDBConnectionManager = new OSSBSSConnectionManager();
//vSqlConnection = vDBConnectionManager.getOracleJDBCConnection();
// Create a sql statement to execute query
stmt = vSqlConnection.createStatement();



// String vQuery = "SELECT REQUEST_TYPE, RECORD_STATE FROM LOOP_MPG_OSSBSSGW_PORT_ORDERS " +
// "WHERE MSISDN = '" +pTN+ "' AND " +pSubQuery;

String vQuery = "SELECT REQUEST_TYPE, RECORD_STATE FROM LOOP_MPG_OSSBSSGW_PORT_ORDERS " +
"WHERE " + pSubQuery1 + pSubQuery;

mLogger.logMessage(OSSBSSSystemConstants.DB_OPERATION, pTxnId,
MPGLogLevels.SEVERITY_WARN, MPGModuleNames.MODULE_SIG,
"checkRecordState::vQuery="+vQuery);

vDBSelectString = stmt.executeQuery(vQuery);
if(vDBSelectString != null){
while(vDBSelectString.next())
{
vCurrentRequestType = vDBSelectString.getString("REQUEST_TYPE");
vCurrentRecordStatus = vDBSelectString.getString("RECORD_STATE");

// Check for sequence of message
if(isMsgOutOfSeq(pTxnId, pInputMessage, vCurrentRequestType))
{
// Log Alarm here

mLogger.logMessage(OSSBSSSystemConstants.DB_OPERATION, pTxnId,
MPGLogLevels.SEVERITY_WARN, MPGModuleNames.MODULE_SIG,
"The record '"+ pTxnId + "' :: " +
"Request_Type is out of sequence and it is overwritten by message - "+pInputMessage );

mAlarmHandler.logAlarmMessage(OSSBSSSystemConstants.DB_OPERATION, "",MPGLogLevels.SEVERITY_WARN,
OSSBSSSystemConstants.MODULE_SIG, OSSBSSErrorConstants.OSSBSS_INIT_ERROR ,
"The record '"+ pTxnId + "' :: " +
"Request_Type is out of sequence and it is overwritten by message- "+pInputMessage,
"checkRecordState(String pTxnId, String pSubQuery, String pTN, String pInputMessage) " +
"method", getClass().getName(), "");
}

// Check for 'L' state
if(vCurrentRecordStatus.equalsIgnoreCase("L"))
{
// Log Alarm here

mLogger.logMessage(OSSBSSSystemConstants.DB_OPERATION, pTxnId,
MPGLogLevels.SEVERITY_WARN, MPGModuleNames.MODULE_SIG,
"The record '"+ pTxnId + "' :: is locked and it is overwritten by message- "+pInputMessage );

mAlarmHandler.logAlarmMessage(OSSBSSSystemConstants.DB_OPERATION, "",MPGLogLevels.SEVERITY_WARN,
OSSBSSSystemConstants.MODULE_SIG, OSSBSSErrorConstants.OSSBSS_INIT_ERROR ,
"The record '"+ pTxnId + "' :: is locked and it is overwritten by message- "+pInputMessage,
"checkRecordState(String pTxnId, String pSubQuery, String pTN, String pInputMessage) " +
"method", getClass().getName(), "");
}

// Check for 'P' state
/**if(vCurrentRecordStatus.equalsIgnoreCase("P"))
{
// Log Alarm here

mLogger.logMessage(OSSBSSSystemConstants.DB_OPERATION, pTxnId,
MPGLogLevels.SEVERITY_WARN, MPGModuleNames.MODULE_SIG,
"The record '"+ pTxnId + "' :: is pending and it is overwritten by message - "+ pInputMessage );

mAlarmHandler.logAlarmMessage(OSSBSSSystemConstants.DB_OPERATION, "",MPGLogLevels.SEVERITY_WARN,
OSSBSSSystemConstants.MODULE_SIG, OSSBSSErrorConstants.OSSBSS_INIT_ERROR ,
"The record '"+ pTxnId + "' :: is pending and it is overwritten by message - "+ pInputMessage,
"checkRecordState(String pTxnId, String pSubQuery, String pTN, String pInputMessage) " +
"method", getClass().getName(), "");
} **/
}
}
vDBSelectString.close();
stmt.close();
}

catch(SQLException eSQLException)
{
eSQLException.printStackTrace();

mLogger.logMessage(OSSBSSSystemConstants.DB_OPERATION, pTxnId,
MPGLogLevels.SEVERITY_ERROR, MPGModuleNames.MODULE_SIG,
"SQL Exception occurred in checkRecordState method ");

}
mLogger.logMessage(OSSBSSSystemConstants.DB_OPERATION, pTxnId,
MPGLogLevels.SEVERITY_DEBUG, MPGModuleNames.MODULE_SIG,
"OSSBSSInsertRecordDAO::Exiting checkRecordState method ");
}

Rgds,
Jay

[Jitesh Sinha]

Aren't you closing res twice - once immediately after res.next and once in finally clause?

The way you are pasting your code here makes it difficult to find matching braces - use code tag to make your code more readable.

[jaya kemmannu]

Yes i am closing it twice. is this causing problem ? which one i need to remove ?

But the res.close() which is present in finally block will execute when if() condition is true.

public void updatePortCompleteNotification(PortOrderVO pPortOrderVO)throws OSSBSSDBException. 
	{
		String vNPTXNID = pPortOrderVO.getMNPTXNID();
		String vOrderID = pPortOrderVO.getMOrderId();
		boolean CheckOrderId = false;		
		String vMSISDN = null;
		String vRequestType = null;
		String vStatusChange = null;
		String vTNTo = null;
		String vQuery = null;
		String vSubQuery = null;
		String vLogId = null;
		String vSubQuery1 = null;
		Statement stmt = null;
		ResultSet res = null;
		PreparedStatement updStmt = null;
		String vMessageTime = null;
		String vReceivedTime = null;
		
		try
		{
			if(vOrderID == null || vOrderID.equalsIgnoreCase("null"))
			{
				CheckOrderId = true;
			}
			if(CheckOrderId)
			{
				mLogger.logMessage(OSSBSSSystemConstants.DB_OPERATION, vNPTXNID,
						MPGLogLevels.SEVERITY_INFO, MPGModuleNames.MODULE_SIG,
						"OSSBSSInsertRecordDAO::The incoming XML is Port-Out,stop further processing");
				return;
			}
		}catch(Exception e){ e.printStackTrace(); }
					
		Connection vSqlConnection = null;

		OSSBSSConnectionManager vDBConnectionManager = null;

		vMSISDN = pPortOrderVO.getMMSISDN();
		vRequestType = pPortOrderVO.getMRequestType();
		vStatusChange = pPortOrderVO.getMStatusChange();
		vTNTo = pPortOrderVO.getMTNTo();
		vMessageTime = pPortOrderVO.getMLastUpdateTime();
		//get only the date remove the 'T' from it and also the timezone from it
		String vDate = vMessageTime.substring(0,10);
		String vTime = vMessageTime.substring(11,19);
		
		vReceivedTime = vDate+" "+vTime;
		

		try
		{
			//	Get Oracle Database connection
			vDBConnectionManager = new OSSBSSConnectionManager();
			vSqlConnection = vDBConnectionManager.getOracleJDBCConnection();
            
			long vTNNumberRange = 0;
			long vTNs = 0;
			if(!(vMSISDN == null || vMSISDN == ""))
			{
				vTNNumberRange = Long.parseLong(vTNTo) - Long.parseLong(vMSISDN);
				vTNs = new Long(vMSISDN).longValue();
			}

			vSqlConnection.setAutoCommit(false);
			int vUpdateSucess = 0;

			// to decide for the where clause parameters (ORDER_ID / NPTXN_ID)			
			if(vRequestType.equalsIgnoreCase(OSSBSSSystemConstants.GEN_PORT_COMPLETE_NOTIFICATION))
			{
				vLogId = vNPTXNID;
				vSubQuery = "NP_TXN_ID='"+ vNPTXNID +"'";
			}

			// update the record status
			for(int vTNNumberRangeIndex = 0; vTNNumberRangeIndex <= vTNNumberRange; vTNNumberRangeIndex++)
			{
	    		vTNs = vTNs+vTNNumberRangeIndex;
	    		String vTN = ""+vTNs;
	
				if(vMSISDN != null || vMSISDN != "")	
				{
					vSubQuery1 = "MSISDN='"+ vTN+"' AND ";
				}

	    		// create a sql statement to execute query on LOOP_MPG_OSSBSSGW_PORT_ORDERS
	    		stmt = vSqlConnection.createStatement();

	    		//Retrieve the current value of STATUS_CHANGE
				vQuery = "SELECT STATUS_CHANGE from " +
								"LOOP_MPG_OSSBSSGW_PORT_ORDERS WHERE " +vSubQuery1+ vSubQuery;


			res = stmt.executeQuery(vQuery);
                       if(res != null)
                       {
                	while(res.next())
                	{
                		vStatusChange =  res.getString("STATUS_CHANGE")+ "|" + vStatusChange  ;
                	}
                        }
			res.close();
			stmt.close();

			if(vRequestType.equalsIgnoreCase(OSSBSSSystemConstants.GEN_PORT_COMPLETE_NOTIFICATION)
						|| vRequestType.equalsIgnoreCase(OSSBSSSystemConstants.GEN_PORT_PROCESS_COMPLETE))
				{
					String vPortType = getPortType(vStatusChange);
					if( vRequestType.equalsIgnoreCase(OSSBSSSystemConstants.GEN_PORT_COMPLETE_NOTIFICATION) &&
							(vPortType.equalsIgnoreCase("POUTRE") || vPortType.equalsIgnoreCase("POUTTERM")) )
						return;
					if( vRequestType.equalsIgnoreCase(OSSBSSSystemConstants.GEN_PORT_PROCESS_COMPLETE) &&
							(vPortType.equalsIgnoreCase("PINRE") || vPortType.equalsIgnoreCase("PINTERM")) )
						return;
					if( ( vRequestType.equalsIgnoreCase(OSSBSSSystemConstants.GEN_PORT_COMPLETE_NOTIFICATION)
							|| vRequestType.equalsIgnoreCase(OSSBSSSystemConstants.GEN_PORT_PROCESS_COMPLETE) )
							&& ( vPortType.equalsIgnoreCase("POUTTERM") || vPortType.equalsIgnoreCase("PINTERM") ) )
						return;
					if( ( vRequestType.equalsIgnoreCase(OSSBSSSystemConstants.GEN_PORT_COMPLETE_NOTIFICATION)
					   || vRequestType.equalsIgnoreCase(OSSBSSSystemConstants.GEN_PORT_PROCESS_COMPLETE) )
					   &&  ( vPortType.equalsIgnoreCase("PINCOM") || vPortType.equalsIgnoreCase("POUTCOM")))
					   return;
				}
				
				//  Check for record state
				checkRecordState(vLogId, vSubQuery, vSubQuery1, vRequestType ,vSqlConnection);

        		vQuery = "UPDATE LOOP_MPG_OSSBSSGW_PORT_ORDERS SET " +								
					       		"REQUEST_TYPE='"+ vRequestType +"', " +
					       		"LAST_UPDATE_TIMESTAMP=to_date('" +vReceivedTime +"','YYYY-MM-DD HH24:MI:SS'),"+
					       		"RECORD_STATE='P' ," +
					       		"STATUS_CHANGE='" + vStatusChange +"' " +
								"WHERE "+ vSubQuery1 + vSubQuery;

		    	updStmt = vSqlConnection.prepareStatement(vQuery);

		    	// Execute Update query on LOOP_MPG_OSSBSSGW_PORT_ORDERS
		    	vUpdateSucess = updStmt.executeUpdate();
		    	updStmt.close();
			 }
		    vSqlConnection.commit();

		    if(vUpdateSucess != 0)
		    {
		    	mLogger.logMessage(OSSBSSSystemConstants.DB_OPERATION, vNPTXNID,
		    			MPGLogLevels.SEVERITY_INFO, MPGModuleNames.MODULE_SIG,
		        		"OSSBSSInsertRecordDAO:: Successfully updated the message with OrderID: '" +vOrderID+
		        		"', NPTXNID : '" +vNPTXNID+ "' & RequestType '" +vRequestType+
		        		"' into LOOP_MPG_OSSBSSGW_PORT_ORDERS ");
		    }
		    else
		    {
		    	mLogger.logMessage(OSSBSSSystemConstants.DB_OPERATION, vNPTXNID,
		    			MPGLogLevels.SEVERITY_WARN, MPGModuleNames.MODULE_SIG,
		        		"OSSBSSInsertRecordDAO:: Failed to update the message with OrderID: '" +vOrderID+
		        		"', NPTXNID : '" +vNPTXNID+ "' & RequestType '" +vRequestType+
		        		"' into LOOP_MPG_OSSBSSGW_PORT_ORDERS ");
		    }
		}
		catch(SQLException exp)
		{
			exp.printStackTrace();

			mLogger.logMessage(OSSBSSSystemConstants.DB_OPERATION, vNPTXNID,
					MPGLogLevels.SEVERITY_ERROR, MPGModuleNames.MODULE_SIG,
					"SQL Exception occurred in updatePortCompleteNotification mehtod ");

			throw new OSSBSSDBException(exp.getMessage());
		}
		catch(OSSBSSDBException exp)
		{
			exp.printStackTrace();

			mLogger.logMessage(OSSBSSSystemConstants.DB_OPERATION, vNPTXNID,
					MPGLogLevels.SEVERITY_ERROR, MPGModuleNames.MODULE_SIG,
					"OSSBSS Exception occurred in updatePortCompleteNotification method ");

			throw exp;
		}
		finally
		{
			if(res != null)
			{
				try { res.close(); } catch(SQLException eSQLExp1) { eSQLExp1.printStackTrace(); }
			}
			if(updStmt != null)
			{
				try { updStmt.close(); } catch(SQLException eSQLExp2) { eSQLExp2.printStackTrace(); }				
			}
			if(stmt != null)
			{
				try { stmt.close(); } catch(SQLException eSQLExp3) { eSQLExp3.printStackTrace(); }				
			}
			if(vSqlConnection!=null)
			{
				try { vSqlConnection.close(); } catch(SQLException eSQLExp4) { eSQLExp4.printStackTrace(); }				
			}	
		}
	}

private  void checkRecordState(String pTxnId, String pSubQuery, String pSubQuery1, String pInputMessage, Connection vSqlConnection)
	{
		String vCurrentRequestType = null;
		String vCurrentRecordStatus = null;
		Statement stmt = null;
		ResultSet vDBSelectString = null;
		try
		{
			// Create a sql statement to execute query
			stmt = vSqlConnection.createStatement();
		   
		    String vQuery = "SELECT REQUEST_TYPE, RECORD_STATE FROM LOOP_MPG_OSSBSSGW_PORT_ORDERS " +
								"WHERE " + pSubQuery1 + pSubQuery;

			vDBSelectString = stmt.executeQuery(vQuery);
			if(vDBSelectString != null){
			while(vDBSelectString.next())
			{
				vCurrentRequestType = vDBSelectString.getString("REQUEST_TYPE");
				vCurrentRecordStatus = vDBSelectString.getString("RECORD_STATE");

				//	Check for sequence of message
				if(isMsgOutOfSeq(pTxnId, pInputMessage, vCurrentRequestType))
				{
					// Log Alarm here

					mLogger.logMessage(OSSBSSSystemConstants.DB_OPERATION, pTxnId,
		    			MPGLogLevels.SEVERITY_WARN, MPGModuleNames.MODULE_SIG,
		        		"The record '"+ pTxnId + "' :: " +
		        		"Request_Type is out of sequence and it is overwritten by message - "+pInputMessage );

					mAlarmHandler.logAlarmMessage(OSSBSSSystemConstants.DB_OPERATION, "",MPGLogLevels.SEVERITY_WARN,
							OSSBSSSystemConstants.MODULE_SIG, OSSBSSErrorConstants.OSSBSS_INIT_ERROR ,
							"The record '"+ pTxnId + "' :: " +
			        		"Request_Type is out of sequence and it is overwritten by message- "+pInputMessage,
							"checkRecordState(String pTxnId, String pSubQuery, String pTN, String pInputMessage) " +
							"method", getClass().getName(), "");
				}

				//	Check for 'L' state
				if(vCurrentRecordStatus.equalsIgnoreCase("L"))
				{
					// Log Alarm here

					mLogger.logMessage(OSSBSSSystemConstants.DB_OPERATION, pTxnId,
		    			MPGLogLevels.SEVERITY_WARN, MPGModuleNames.MODULE_SIG,
		        		"The record '"+ pTxnId + "' :: is locked and it is overwritten by message- "+pInputMessage );

					mAlarmHandler.logAlarmMessage(OSSBSSSystemConstants.DB_OPERATION, "",MPGLogLevels.SEVERITY_WARN,
							OSSBSSSystemConstants.MODULE_SIG, OSSBSSErrorConstants.OSSBSS_INIT_ERROR ,
							"The record '"+ pTxnId + "' :: is locked and it is overwritten by message- "+pInputMessage,
							"checkRecordState(String pTxnId, String pSubQuery, String pTN, String pInputMessage) " +
							"method", getClass().getName(), "");
				}

				//	Check for 'P' state
				/**if(vCurrentRecordStatus.equalsIgnoreCase("P"))
				{
					// Log Alarm here

					mLogger.logMessage(OSSBSSSystemConstants.DB_OPERATION, pTxnId,
		    			MPGLogLevels.SEVERITY_WARN, MPGModuleNames.MODULE_SIG,
		        		"The record '"+ pTxnId + "' :: is pending and it is overwritten by message - "+ pInputMessage );

					mAlarmHandler.logAlarmMessage(OSSBSSSystemConstants.DB_OPERATION, "",MPGLogLevels.SEVERITY_WARN,
							OSSBSSSystemConstants.MODULE_SIG, OSSBSSErrorConstants.OSSBSS_INIT_ERROR ,
							"The record '"+ pTxnId + "' :: is pending and it is overwritten by message - "+ pInputMessage,
							"checkRecordState(String pTxnId, String pSubQuery, String pTN, String pInputMessage) " +
							"method", getClass().getName(), "");
				} **/
			}
			}
			vDBSelectString.close();
			stmt.close();			
		}

		catch(SQLException eSQLException)
		{
			eSQLException.printStackTrace();

			mLogger.logMessage(OSSBSSSystemConstants.DB_OPERATION, pTxnId,
					MPGLogLevels.SEVERITY_ERROR, MPGModuleNames.MODULE_SIG,
					"SQL Exception occurred in checkRecordState method ");

		}			
		mLogger.logMessage(OSSBSSSystemConstants.DB_OPERATION, pTxnId,
				MPGLogLevels.SEVERITY_DEBUG, MPGModuleNames.MODULE_SIG,
				"OSSBSSInsertRecordDAO::Exiting checkRecordState method ");	
	}

[Gopakumar Naryanan]

Hi Jaya,

I would like to suggest the following things:

stmt = vSqlConnection.createStatement();  
  
                //Retrieve the current value of STATUS_CHANGE  
                vQuery = "SELECT STATUS_CHANGE from " +  
                                "LOOP_MPG_OSSBSSGW_PORT_ORDERS WHERE " +vSubQuery1+ vSubQuery;  
  
  
            res = stmt.executeQuery(vQuery);  
                       if(res != null)  
                       {  
                    while(res.next())  
                    {  
                        vStatusChange =  res.getString("STATUS_CHANGE")+ "|" + vStatusChange  ;  
                    }  
                        }  
            res.close();  
            stmt.close();  

don't create Statement object in for loop. Any how what ever the value may be only Query will change, not the Statement object.
and don't close both of them in loop.

Why are you using both PreparedStatement & Statement objects???

 vQuery = "UPDATE LOOP_MPG_OSSBSSGW_PORT_ORDERS SET " +                                
                                "REQUEST_TYPE='"+ vRequestType +"', " +  
                                "LAST_UPDATE_TIMESTAMP=to_date('" +vReceivedTime +"','YYYY-MM-DD HH24:MI:SS'),"+  
                                "RECORD_STATE='P' ," +  
                                "STATUS_CHANGE='" + vStatusChange +"' " +  
                                "WHERE "+ vSubQuery1 + vSubQuery;  
  
                updStmt = vSqlConnection.prepareStatement(vQuery);  
  
                // Execute Update query on LOOP_MPG_OSSBSSGW_PORT_ORDERS  
                vUpdateSucess = updStmt.executeUpdate();  
                updStmt.close();  

How can you set the parameters to a PreparedStatement directly?
you have to use setter methods for the specific parameter indicating the index.
like updStmt.setInt(1,variablename);

Please Try the above and give us an update.

[jaya kemmannu]

Hi Gopakumar,

I did the changes suggested by you. from two days there is closed connection/resultset/statement exception. I also removed for {} loop from the condition since it is not required here at all.

Regards,
Jaya

[jaya kemmannu]

Hi,

Sorry....there was NO exception from last tow days !!!....


Regards,
Jaya