Here is some sample init() method code that I use to test the db connection on context startup:
try { /*- * The driver objects register themselves with the driver manager * at the time of loading, * and the following line forces the loading of the (MySQL or other) * driver * (it registers itself). */ Class.forName(driver).newInstance(); conn = DriverManager.getConnection(url); sc.log("Database connection successfully obtained."); conn.setAutoCommit(false); // Prepare the query: String qString = queryString.getQueryString(); sc.log("\n\n Using this query string: " + qString + "\n"); pstmt = conn.prepareStatement(qString); // Test db connection for integrity: stmt = conn.createStatement(); rs = stmt.executeQuery(highestUniqueId); if (rs.next()) { uniqueEntryId = rs.getInt(1); // Gets the highest unique survey i.d. } sc.log("uniqueEntryId is initialized as "+uniqueEntryId); // Close the statement object, which also closes the resultset. stmt.close(); if (uniqueEntryId < 0) { sc.log("Problem with database connection if " + "this is not a new db table. This db table has no " + "greatest unique entry id number." ); } } catch (ClassNotFoundException e) { String msg = "*** Couldn't load database driver."; sc.log(msg, e); throw new UnavailableException(msg); } catch (SQLException e) { String msg = "*** Couldn't get db connection."; sc.log(msg, e); throw new UnavailableException(msg); } catch (IllegalAccessException e) { String msg = "*** Couldn't get access to db connection."; sc.log(msg, e); throw new UnavailableException(msg); } catch (InstantiationException e) { String msg = "*** Couldn't create db connection."; sc.log(msg, e); throw new UnavailableException(msg); } Jacob Kjome wrote: > hmm.... > > I think in that case you would have to set a Timeout on the connection. > You might want to test the connection upon context startup and if the > connection times out, then throw an error or redirect to a static page > saying that the app is temporarily unavailable. > > Not sure what else you can do? Anyone? > > Jake > > At 01:00 PM 7/16/2002 -0700, you wrote: > >> Yes, it is important to close your connections when you're done with >> them, and this is considered in each of our methods. >> >> The problem we are seeing with the hung connections, however, occurs >> before any SQLException has been thrown, anywhere in the servlet >> engine. In fact, even if the very first connection attempted at >> startup is made to a server which is unavailable, every other >> connection will wait until that first connection times out (and then >> throws its SQLException). >> >> Thank you for your consideration of the problem. >> >> -Paul >> >> >> On Monday, July 15, 2002, at 10:25 PM, Jacob Kjome wrote: >> >>> BTW, in the code below, I forgot to create the connection at the >>> beginning of the try statment. Just wanted to note that you will >>> need to do that. >>> >>> conn = openConn(); //openConn() grabs the connection from a pool or >>> creates a brand new connection >>> >>> Just insert that and whatever you need to do to open your connection >>> at the beginning of the try statement below. >>> >>> jake >>> >>> >>> At 12:00 AM 7/16/2002 -0500, you wrote: >>> >>>> HI Paul, >>>> >>>> You simply need to make sure that you've let go of your resources >>>> such as connections, resultsets, etc.... >>>> >>>> Here is some sample code for that... >>>> >>>> Connection conn = null; >>>> PreparedStatement pstmt = null; >>>> ResultSet rs = null; >>>> >>>> String query = "SELECT * FROM mytable"; >>>> >>>> try { >>>> pstmt = conn.prepareStatement(query); >>>> rs = pstmt.executeQuery(); >>>> >>>> //do something with the ResultSet... >>>> >>>> rs.close(); >>>> pstmt.close(); >>>> conn.close(); >>>> } >>>> catch(SQLException e) { >>>> System.out.println(e.getMessage()); >>>> throw e; >>>> } >>>> finally { >>>> if (rs != null) try { rs.close(); } catch(Exception e2) {} >>>> if (pstmt != null) try { pstmt.close(); } catch(Exception e3) {} >>>> if (conn != null) try { conn.close(); } catch(Exception e4) {} >>>> } >>>> >>>> >>>> The key here is that if a SQLException is raised in the try >>>> statement, then the ResultSet, PreparedStatement, and Connection >>>> will not be successfully closed. In order to guarantee that they >>>> get closed, you need to have the finally statement which is >>>> guaranteed to run no matter what. Try that and I bet your >>>> connections won't get locked up until timeout. >>>> >>>> Jake >>>> >>>> >>>> At 02:27 PM 7/15/2002 -0700, you wrote: >>>> >>>>> Sorry it's been a week since you sent this message... Too much >>>>> traffic here for me to keep up. This problem is the only reason >>>>> I'm subscribed to this list at all. I normally only follow -dev >>>>> (and if I could point to a specific problem in Tomcat I'd ask there >>>>> instead). >>>>> >>>>> I have this problem with all JDBC accesses from Tomcat. At first I >>>>> was using a MySQL database call on every page, and found that if I >>>>> accessed a second MySQL server which became unavailable, every call >>>>> to the first one would be held up until it timed out, even in other >>>>> contexts. Then I discovered that we had the same problem when >>>>> accessing a MS-SQL server. >>>>> If the MS-SQL server was unavailable, every other page on the site >>>>> (each of them containing the call to MySQL) would wait until the >>>>> MS-SQL connection timed out. Finally, we incorporated an Oracle >>>>> connection with the same results. Any JDBC call to a database >>>>> which is unavailable holds up all other connections on the servlet >>>>> engine. >>>>> >>>>> This is an embarrassing threading issue. >>>>> >>>>> Although we asked for help on this list (and received some >>>>> well-thought-out responses from Bill Barker) we were unable to >>>>> resolve the problem. >>>>> Our current hack for this is: >>>>> >>>>> 1) DriverManager.setLoginTimeout(CONNECTION_TIMEOUT); >>>>> (The default timeout is extraordinarily long and for us a few >>>>> seconds will do for any one of the databases) >>>>> >>>>> 2) A connection wrapper, so that instead of calling >>>>> DriverManager.getConnection(connectionURL,ID,Pass) we call >>>>> DBConnWrapper.getConnection(connectionURL,ID,Pass) and the wrapper >>>>> remembers when a connection has failed and on each request for a >>>>> connection does >>>>> if(!databases.containsKey(DB) || (new Date()).getTime() - >>>>> ((Date)(databases.get(DB))).getTime() > RETRY_INTERVAL) >>>>> >>>>> If you come up with a better answer, let me know. Of course, it's >>>>> possible that you have a different problem than we do, but it >>>>> sounds similar. >>>>> >>>>> Paul >>>>> Unix System Admin/Webmaster >>>>> Seattle Public Schools >>>>> >>>>> For Reference: >>>>> >>>>> On Monday, July 8, 2002, at 11:05 AM, Bill D wrote: >>>>> >>>>>> Let me first set up my situation. On my server, I >>>>>> have two webapps running, webapp A and webapp B. >>>>>> Webapp A uses JDBC Thin driver to contact an Oracle >>>>>> database at remote location 1. Webapp B uses JDBC >>>>>> Thin driver to contact an Oracle database at remote >>>>>> location 2. >>>>>> >>>>>> If the internet connection at remote location 1 goes >>>>>> down, the attempt to connect to that database has the >>>>>> expected result of a connection to that IP in the >>>>>> SYN_SENT state in the netstat output on the server. >>>>>> >>>>>> While this connection is in the SYN_SENT state, any >>>>>> requests to webapp B that need to call the other >>>>>> database at location 2 which is still up and running >>>>>> are put on hold. Visitors to that site are left >>>>>> staring at a blank screen until the first connection >>>>>> from webapp A to location 1 times out. >>>>>> >>>>>> Seeing as how these are 2 seperate webapps connecting >>>>>> to 2 seperate remote locations, I did not expect the >>>>>> behavior to occur in a fashion where one will block >>>>>> the other. The behavior I expected was that they >>>>>> would operate independantly of each other, and webapp >>>>>> B would continue to work at a normal pace no matter >>>>>> what is occurring with webapp A. Am I looking at this >>>>>> wrong? Any help would be greatly appreciated. >>>>> >>>>> >>>>> >>>>> -- >>>>> To unsubscribe, e-mail: <mailto:tomcat-user->>> >>>>> [EMAIL PROTECTED]> >>>>> For additional commands, e-mail: <mailto:tomcat-user->>> >>>>> [EMAIL PROTECTED]> >>>> >> >> >> -- >> To unsubscribe, e-mail: >> <mailto:[EMAIL PROTECTED]> >> For additional commands, e-mail: >> <mailto:[EMAIL PROTECTED]> > > -- ". . . / This Cabinet is formd of Gold / And Pearl & Crystal shining bright And within it opens into a World / . . . Another England there I saw / Another London with its Tower Another Thames & other Hills / And another pleasant Surrey Bower . . ." - from "The Crystal Cabinet", a poem by William Blake. -- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>