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]>
