HI,
I am using DB2 as database to connect from weblogic application server. Here is part of the code from the DAO class:
=======
public class TestDAO
{
private Connection dbCon;
private CustomDTO responseDTO;
public void setConnection() throws CustomException
{
//get the weblogic connection from the data source;
dbCon = WLUtil.getWLConnection("datasource_custom");
}
public void closeConnection()
{
//connection to be closed here
if (dbCon != null)
{
try
{
dbCon.close();
dbCon = null;
log.info("DB CONNECTION CLOSED");
}
catch (SQLException excp)
{
log.error("DB ERROR: Cannot close the connection :: Details: " +
excp);
}
}
}
public CustomDTO getResponse() throws CustomException
{
setConnection();
long startTime = 0;
long stopTime = 0;
if (dbCon == null)
{
log.fatal("DAO ERROR :Back end connection failed");
throw new CustomException("Connection failure");
}
responseDTO = new ExpressTrakAcctProfileResDTO();
try
{
if (log.isInfoEnabled())
{
startTime = System.currentTimeMillis();
}
setDBMethod1();
setDBMethod2();
setDBMethod3();
if (log.isInfoEnabled())
{
stopTime = System.currentTimeMillis();
log.info("DB FETCH :: TIME TAKEN: " + (stopTime - startTime) +" ms");
}
}
catch (Exception excp)
{
log.error("DAO ERROR :Failure in the Transaction" + excp);
hrow new CustomException("Transaction failure");
} finally {
//close the connection
closeConnection();
}
return responseDTO;
}
public void setDBMethod1()
{
String query = "----";//the query to the databse
Statement stmt = null;
ResultSet rs = null;
int rowCount = 0;
try
{
stmt = dbCon.createStatement();
rs = stmt.executeQuery(query);
while(rs.next)
{
//retrieve the column values here
}
//set the value object
responseDTO.set...;
}
catch(SQLException sqlExcp)
{
log.error("DB_ERROR : While doing query for method1 :: DETAILS: " + sqlExcp);
}
finally
{
if (stmt != null)
{
try
{
stmt.close();
}
catch (SQLException sqlExcp)
{
log.error(
"DB_ERROR : Cannot close the STATEMENT Object for Method1");
}
}
stmt = null;
}
}
//similar code goes for method2 method3
--
--
--
}
=============
The above code works fine for most of the calls but in some calls I get the following error that is caught in the respective "setDBMethods" :
DB_ERROR : While doing query for method1:: DETAILS: COM.ibm.db2.jdbc.DB2Exception: [IBM][
JDBC Driver] CLI0601E Invalid statement handle or statement is closed. SQLSTATE=S1000
The connection is opened and closed normally as i can see those messages in the log. The strange thing is when i am creating a new statement for each call, then I cant comprehend the cause of the error.
The DB2 driver being used is : COM.ibm.db2.jdbc.app.DB2Driver
Is there a problem with the code or am i missing something. Please help me.