• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

DB2 connectivity - strange error

 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 104
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.

 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 2713
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic