File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes DB2 connectivity - strange error Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "DB2 connectivity - strange error" Watch "DB2 connectivity - strange error" New topic
Author

DB2 connectivity - strange error

preeti mansingh
Greenhorn

Joined: Jun 04, 2004
Posts: 2
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

Joined: Feb 17, 2003
Posts: 104
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

Joined: Jun 04, 2004
Posts: 2
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 29275
    
140

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Chris Mathews
Ranch Hand

Joined: Jul 18, 2001
Posts: 2712
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 ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: DB2 connectivity - strange error
 
Similar Threads
Getting Connection Establishing in Linux Server
Question on Unhandled Exception - please help!
JDBC Connection (or "I Am At My Wits End")
Prepared Statement problem in my servlet's data manager
Why do we need to close the ResultSet?