This week's book giveaway is in the Agile and Other Processes forum.
We're giving away four copies of The Little Book of Impediments (e-book only) and have Tom Perry on-line!
See this thread for details.
Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

java.sql.SQLException: Connection handle has been closed and is unusable

 
jaya kemmannu
Ranch Hand
Posts: 92
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello

We are getting SQLException like java.sql.SQLException: Connection handle has been closed and is unusable. We have Oracle 10g and using oracle_jdbc.jar and jboss 4.2.2 version.

I have attached complete stack trace, Java code where the exception is thrown and my ds file


2011-11-21 13:04:54,839 INFO [OSSBSS_ADAPTER] 8000|17680582|GenPortInAck : OSSBSSAdapterInsertPortInMessageDAO : The update DB status : 1
2011-11-21 13:04:54,839 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: java.sql.SQLException: Connection handle has been closed and is unusable
2011-11-21 13:04:54,839 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at org.jboss.resource.adapter.jdbc.WrappedConnection.checkStatus(WrappedConnection.java:537)
2011-11-21 13:04:54,839 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at org.jboss.resource.adapter.jdbc.WrappedConnection.checkTransaction(WrappedConnection.java:524)
2011-11-21 13:04:54,839 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at org.jboss.resource.adapter.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:184)
2011-11-21 13:04:54,839 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at com.hp.mnp.ossbss.dao.OSSBSSAdapterInsertPortInMessageDAO.updateGenPortInAck(OSSBSSAdapterInsertPortInMessageDAO.java:219)
2011-11-21 13:04:54,839 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at com.hp.mnp.ossbss.process.InsertPortInMessages.ProcessPortInAck(InsertPortInMessages.java:248)
2011-11-21 13:04:54,840 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at com.hp.mnp.ossbss.webservice.ProcessOSSBSSMessage.executeMessage(ProcessOSSBSSMessage.java:229)
2011-11-21 13:04:54,840 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at com.hp.np.sig.npwsimpl.HpnpOssBssAdapterServiceSkeleton.processRequest(HpnpOssBssAdapterServiceSkeleton.java:143)
2011-11-21 13:04:54,840 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at com.hp.np.mwfm.modules.SIGWebServiceModule.invoke(SIGWebServiceModule.java:202)
2011-11-21 13:04:54,840 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at com.hp.np.mwfm.NPWebServiceNode.nodeEntered(NPWebServiceNode.java:264)
2011-11-21 13:04:54,840 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at com.hp.ov.activator.mwfm.engine.WFMEngineWorker._step1(WFMEngineWorker.java:849)
2011-11-21 13:04:54,840 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at com.hp.ov.activator.mwfm.engine.WFMEngineWorker.access$300(WFMEngineWorker.java:107)
2011-11-21 13:04:54,840 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at com.hp.ov.activator.mwfm.engine.WFMEngineWorker$8.run(WFMEngineWorker.java:1388)
2011-11-21 13:04:54,840 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at com.hp.ov.activator.mwfm.engine.EngineWorkerThread.run(EngineWorkerThread.java:90)
2011-11-21 13:04:54,840 ERROR [OSSBSS_ADAPTER] 8000|17680582|GenPortInAck : OSSBSSAdapterInsertPortInMessageDAO : SQL Exception occurred in updateGenPortInAck mehtod
2011-11-21 13:04:54,840 WARN [org.jboss.resource.adapter.jdbc.WrappedConnection] Closing a statement you left open, please do your own housekeeping
2011-11-21 13:04:54,841 INFO [org.jboss.resource.connectionmanager.TxConnectionManager] Unregistered handle that was not registered! org.jboss.resource.adapter.jdbc.WrappedConnection@5df9c75f for managedConnection: org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@64f1161d
2011-11-21 13:04:54,843 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: com.hp.mnp.ossbss.exception.OSSBSSDBException: Connection handle has been closed and is unusable
2011-11-21 13:04:54,844 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at com.hp.mnp.ossbss.dao.OSSBSSAdapterInsertPortInMessageDAO.updateGenPortInAck(OSSBSSAdapterInsertPortInMessageDAO.java:321)
2011-11-21 13:04:54,844 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at com.hp.mnp.ossbss.process.InsertPortInMessages.ProcessPortInAck(InsertPortInMessages.java:248)
2011-11-21 13:04:54,844 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at com.hp.mnp.ossbss.webservice.ProcessOSSBSSMessage.processGenericMessage(ProcessOSSBSSMessage.java:313)
2011-11-21 13:04:54,844 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at com.hp.mnp.ossbss.webservice.ProcessOSSBSSMessage.executeMessage(ProcessOSSBSSMessage.java:229)
2011-11-21 13:04:54,844 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at com.hp.np.sig.npwsimpl.HpnpOssBssAdapterServiceSkeleton.processRequest(HpnpOssBssAdapterServiceSkeleton.java:143)
2011-11-21 13:04:54,844 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at com.hp.np.mwfm.modules.SIGWebServiceModule.invoke(SIGWebServiceModule.java:202)
2011-11-21 13:04:54,844 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at com.hp.np.mwfm.NPWebServiceNode.nodeEntered(NPWebServiceNode.java:264)
2011-11-21 13:04:54,844 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at com.hp.ov.activator.mwfm.engine.WFMEngineWorker._step1(WFMEngineWorker.java:849)
2011-11-21 13:04:54,844 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at com.hp.ov.activator.mwfm.engine.WFMEngineWorker.access$300(WFMEngineWorker.java:107)
2011-11-21 13:04:54,844 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at com.hp.ov.activator.mwfm.engine.WFMEngineWorker$8.run(WFMEngineWorker.java:1388)
2011-11-21 13:04:54,844 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at com.hp.ov.activator.mwfm.engine.EngineWorkerThread.run(EngineWorkerThread.java:90)
2011-11-21 13:04:54,844 ERROR [OSSBSS_ADAPTER] 1060|17680582|GenPortInAck : InsertPortInMessages : OSSBSSDBException occurred for Txn Id 17680582 : - Connection handle has been closed and is unusable
2011-11-21 13:04:54,845 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: com.hp.mnp.ossbss.exception.OSSBSSDBException: Connection handle has been closed and is unusable
2011-11-21 13:04:54,845 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at com.hp.mnp.ossbss.dao.OSSBSSAdapterInsertPortInMessageDAO.updateGenPortInAck(OSSBSSAdapterInsertPortInMessageDAO.java:321)
2011-11-21 13:04:54,845 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at com.hp.mnp.ossbss.process.InsertPortInMessages.ProcessPortInAck(InsertPortInMessages.java:248)
2011-11-21 13:04:54,845 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at com.hp.mnp.ossbss.webservice.ProcessOSSBSSMessage.processGenericMessage(ProcessOSSBSSMessage.java:313)
2011-11-21 13:04:54,845 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at com.hp.mnp.ossbss.webservice.ProcessOSSBSSMessage.executeMessage(ProcessOSSBSSMessage.java:229)
2011-11-21 13:04:54,845 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at com.hp.np.sig.npwsimpl.HpnpOssBssAdapterServiceSkeleton.processRequest(HpnpOssBssAdapterServiceSkeleton.java:143)
2011-11-21 13:04:54,845 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at com.hp.np.mwfm.modules.SIGWebServiceModule.invoke(SIGWebServiceModule.java:202)
2011-11-21 13:04:54,845 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at com.hp.np.mwfm.NPWebServiceNode.nodeEntered(NPWebServiceNode.java:264)
2011-11-21 13:04:54,845 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at com.hp.ov.activator.mwfm.engine.WFMEngineWorker._step1(WFMEngineWorker.java:849)
2011-11-21 13:04:54,845 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at com.hp.ov.activator.mwfm.engine.WFMEngineWorker.access$300(WFMEngineWorker.java:107)
2011-11-21 13:04:54,845 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at com.hp.ov.activator.mwfm.engine.WFMEngineWorker$8.run(WFMEngineWorker.java:1388)
2011-11-21 13:04:54,846 ERROR [STDERR] Nov 21, 2011 1:04:54 PM [Plug-in standard error]: at com.hp.ov.activator.mwfm.engine.EngineWorkerThread.run(EngineWorkerThread.java:90)
2011-11-21 13:04:54,846 DEBUG [OSSBSS_ADAPTER] 1060|1060|ProcessOSSBSSMessage : exit executeMessage method

Method Where Exception is thrown :


ds file :



Any guidance on fixing this would really help .

thanks & Regards,
Jaya
 
M. Phaneendra Vijay
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Check if you have closed the connection in the following method



Also, It is better you write a single method for each SQL statement you want to execute and call all those methods in a single method by passing the same connection object to all the methods
and close the resultset, statement objects in finally block of the individual methods instead of in the try block and close the connection object in the finally block of the parent method.
 
jaya kemmannu
Ranch Hand
Posts: 92
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Phaneendra,

I am not closing the Connection Object in checkRecordState_Ack... In that method i close only Preprared Statement and result set. Any problem in the below method which you mentioned ?


 
M. Phaneendra Vijay
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

It is not a good practice to close the ResultSet, PreparedStatement objects in a try block, because if a statement before closing them throws an exception, then the ResultSet and PreparedStatment objects will never be closed leaving them open.
Try to close them in a finally block.


try having a separate method for each SQL statement. It will help you identify where the problem is.
Check if this works for you.


 
M. Phaneendra Vijay
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Copy paste this and check if it works. it is the same code as yours (including the logging) but closed the statements in a finally block.

 
jaya kemmannu
Ranch Hand
Posts: 92
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I will try the changes suggested by you and update the result.Thank you for valuable information.

Regards,
Jaya
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic