Solved

Checking the validity of an Oracle Connection

Posted on 2004-09-06
10
272 Views
Last Modified: 2012-05-05
HI,

I use Oracle JDBC Driver's OracleConnection class in one of my applications where I have implemented connection pooling.

Now I faced a problem once. It is as follows:

When I once shutdown the oracle server when my application was up, I was unbale to get data from the databse, which is correct.

But ideally, the connection pool should not be affected in such circumstances.

If I get a connection form the pool and this connection is no more valid, then the conenction should be reinitialized.

How I can do this?

How do I know(in my ocde) that the oracle server is restarted?

Thanks
Regards
Jas


0
Comment
Question by:jas123
  • 3
  • 2
  • 2
  • +1
10 Comments
 
LVL 92

Accepted Solution

by:
objects earned 168 total points
ID: 11994266
if an error occurs on the connection then it should be considered corrupt and removed from pool.
0
 
LVL 7

Expert Comment

by:grim_toaster
ID: 11994287
Some application servers (i.e. weblogic) have an additional step that may be worth considering, they have a test connection on reserve against a well-known table that does exist, if any error occurs then the connection is considered invalid (for Oracle the test query would be "SELECT 1 FROM dual").
0
 
LVL 92

Expert Comment

by:objects
ID: 11994296
the isClosed() and getWarnings() methods may help you.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 7

Assisted Solution

by:grim_toaster
grim_toaster earned 166 total points
ID: 11994304
isClosed won't help, from JavaDoc:

     * This method generally cannot be called to determine whether a
     * connection to a database is valid or invalid.  A typical client
     * can determine that a connection is invalid by catching any
     * exceptions that might be thrown when an operation is attempted.

And a SQL statement is more likely to throw a fatal SQLException for invalid connection than create a warning object.
0
 
LVL 35

Assisted Solution

by:girionis
girionis earned 166 total points
ID: 11994309
> If I get a connection form the pool and this connection is no more valid, then the conenction should be reinitialized.

If Oracle is down will you still be able to access the connection pool? In WLS there are exceptions thrown if the db is done while the connection pool was active.

> How I can do this?

Not sure if this will help, but what will happen if you call the isClosed() method on the connection?

> How do I know(in my ocde) that the oracle server is restarted?

If the server is restarted then the connection pool should be re-created and initialized, so you shouldn't have any problems.
0
 
LVL 35

Expert Comment

by:girionis
ID: 11994318
> Some application servers (i.e. weblogic) have an additional step that may be worth considering, they have a test
>connection on reserve against a well-known table that does exist, if any error occurs then the connection is considered
>invalid (for Oracle the test query would be "SELECT 1 FROM dual").

Yes, this one also. I completely forgot about this.
0
 
LVL 92

Expert Comment

by:objects
ID: 11994332
> A typical client
> * can determine that a connection is invalid by catching any
> * exceptions that might be thrown when an operation is attempted.

yes as i originally said, if the connection fails then it should be removed from pool.


0
 

Expert Comment

by:jerra
ID: 12061750
I made a CachedConnectionFactory class based on the Oracle classes OracleConnectionCacheImpl and OracleConnectionPoolDataSource.

In my CachedConnectionFactory.getConnection() method I use:

      try {
        tmpConnection = oracleConnectionCacheImpl.getConnection();
      }
      catch (SQLException ex1) {
        logger.error("SQLException while getting connection.",ex1);
        resetConnectionFactory();
        tmpConnection = oracleConnectionCacheImpl.getConnection();
      }

In resetConnectionFactory() I close the oracleConnectionCacheImpl object and create a new one. This works just fine for me.
If the second try does not work (SQLException) then the routing returns null.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
servlet requestdispatcher include and forward differences 1 39
Maven Project: Hibernate Dependencies Conflict 10 40
map related example 6 55
MySqlDump not dumping triggers 1 43
For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question