Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

DB2 connectivity - strange error

 
preeti mansingh
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 104
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:


[ June 04, 2004: Message edited by: Lu Battist ]
 
preeti mansingh
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34371
345
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 2712
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic