Re: Tomcat / JDBC / MS SQL / Connection lost and won't reconnect

[Patricio Keilty <pa...@infovia.com.ar>]

Hi Paul,
in order to make Tomcat connection pool detect your stale connections,
you should set validationQuery parameter on your Datasource definition
to something like "SELECT 1". This query is used to validate a
connection state when retrieving it from the pool, in case it fails the
connection is recycled.
For further reference see
http://nagoya.apache.org/bugzilla/show_bug.cgi?id=1879 .

You should also be able to see this query hitting your DB with MS SQL
Profiler tool.

Hope this help,
Patricio.

El vie, 23-05-2003 a las 12:28, Paul (BRI) escribió:
> I am having a problem that I am guessing can't be uncommon. I wanted to
> provide details on my exact setup to see if anyone has solved this...or
> if I am doing something stupid..
> 
> I have: Tomcat 4.1.x, Microsoft's JDBC Type 4 driver, and MS SQL Server
> 2000. Tomcat is running on a linux (red hat) box and SQL Server is
> running on a separate box (with Windows 2000 Server). The application
> sitting on the linux box is a web application. Tomcat is the only server
> (for the web and servlets).
> 
> I noticed that if there is any interruption in the network connection
> between the two boxes the connection is dropped and Tomcat (or the
> driver) will not reconnect until I shutdown/startup Tomcat. I have seen
> some code for autoReconnect for mySQL and wasn't clear if that was the
> answer for MS drivers. If so, what is the syntax for this? 
> 
> I am curious if this is common. Or, is using a Microsoft Driver the
> problem? :o It seens hard to believe that the default behavior of the
> driver allows for this connection drop. Am I doing something else wrong?
> 
> Below is the resource entry in my server.xml file. Thank you very much
> for your help. Paul
> 
> <!-- connection pooling -->
> <Resource name="jdbc/myName" auth="Container"
> type="javax.sql.DataSource"></Resource>
> 
> 	<ResourceParams name="jdbc/myName">
> 	<parameter>
> 		<name>factory</name>
> 	
> <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
> 	</parameter>
> 
> 	<!--MS SQL User name-->
> 	<parameter>
> 		<name>username</name>
> 		<value>myUserName/value>
> 	</parameter>
> 
> 	<!--MS SQL Password-->
> 	<parameter>
> 		<name>password</name>
> 		<value>myPassword</value>
> 	</parameter>
> 
> 	<!--JDBC Driver Class Name-->
> 	<parameter>
> 		<name>driverClassName</name>
> 	
> <value>com.microsoft.jdbc.sqlserver.SQLServerDriver</value>
> 	</parameter>
> 
> 	<!--MS SQL Server Url*-->
> 	<parameter>
> 		<name>url</name>
> 	
> <value>jdbc:microsoft:sqlserver://xxx.xxx.xxx.xxx:xxxx;DatabaseName=myNa
> me</value>
> 	</parameter>
> 
> 	<!--To configure a DBCP DataSource so that abandoned dB
> connections are removed and recycled -->
> 	<parameter>
> 		<name>removeAbandoned</name>
> 		<value>true</value>
> 	</parameter>
> 
> 	<!--Use the removeAbandonedTimeout parameter / seoonds.-->
> 	<parameter>
> 		<name>removeAbandonedTimeout</name>
> 		<value>60</value>
> 	</parameter>
> 
> 	<!-- Maximum number of dB connections in pool.  Set to 0 for no
> limit. -->
> 	<parameter>
> 		<name>maxActive</name>
> 		<value>100</value>
> 	</parameter>
> 	
> 	<!-- Maximum number of idle dB connections to retain in pool.
> Set to 0 for no limit.-->
> 	<parameter>
> 		<name>maxIdle</name>
> 		<value>30</value>
> 	</parameter>
> 
> 	<!-- Maximum time to wait for a dB connection to become
> available in ms,  Set to -1 to wait indefinitely. -->
> 	<parameter>
> 		<name>maxWait</name>
> 		<value>-1</value>
> 	</parameter>
> </ResourceParams> 
> <-- end //-->
> 
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: tomcat-user-help@jakarta.apache.org
> 








---------------------------------------------------------------------
To unsubscribe, e-mail: tomcat-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: tomcat-user-help@jakarta.apache.org