DB2 connectivity - strange error

[preeti mansingh]

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.

[Lu Battist]

It looked good to me, but maybe the connection is not open at the time.
Also, you might want to close the result set somewhere.

A few well placed debug statements might help:

public void setDBMethod1() {
   String query = "----";//the query to the databse
   Statement stmt = null;
   ResultSet rs = null;
   int rowCount = 0;
 
   try {
      if (dbCon == null) {
         log.err("dbCon is null");
      }
      stmt = dbCon.createStatement();
      if (stmt == null) {
         log.err("dbCon could not create a stmt.");
      }
      rs = stmt.executeQuery(query);
      while(rs.next) {
         //retrieve the column values here
      }
      //If you don't need the rs anymore close it here or in the finally block.
      rs.close();
 
      //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;
   }
}

[ June 04, 2004: Message edited by: Lu Battist ]

[preeti mansingh]

Originally posted by Lu Battist:
[QB]It looked good to me, but maybe the connection is not open at the time.
Also, you might want to close the result set somewhere.

A few well placed debug statements might help:


--------------

I liked the idea of debug statements, I will put that and try running to see if that is a possible scenario.

For the other suggestion of closing the result set, doesn't the underlying result set get close on closing the statement? That is what javadoc says or is it driver dependent?

Thanks for the suggestion.

[Jeanne Boyarsky]

Preeti,
It should get closed, but often there are strange errors if you don't close it explicitly. It's a good practice to get into.

[Chris Mathews]

I believe your handling of JDBC resources is what is causing the strange behavior. Take a look at this thread for a more detailed discussion about how I feel resources should be properly managed when using JDBC.
[ June 06, 2004: Message edited by: Chris Mathews ]