tomcat-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrew Hughes - LISAsoft <ahug...@lisasoft.com.au>
Subject Re: Tomcat - JDBC - MS SQL Driver Recommendations?
Date Thu, 29 May 2003 05:40:11 GMT
I was using the microsoft driver and had something that might be what 
your talking about.

Basically, I was attempting to re-use the preparedStatement's I had 
created. This is what they are for (ie reset parameters and re-use). Due 
to recursion in my program it would eventually build up enough prepared 
statments it would crash. There is no error message for this happeneing, 
all it said was "connection reset". From this point on I COULD NOT 
reconnect to sql server until I restarted tomcat.

With that said.... each time you run a query you actually create another 
new connection. This is because of the caching on the server side. Given 
the way this works, I changed my code so that everytime a statement is 
executed it is closed directly after. This in theory defeats the purpose 
of prepared statements (somewhat). But it was the only way I could get 
it to work. Its really ineffective but it works!!!!


Summary of my point is that when you create too many connections the 
microsoft driver doesn't tell you that. Its exception just says the 
connection was reset. From this point one you can't connect (until you 
restart tomcat). Sounds like what your talking about.  I found out as I 
looked at the server configuration and logged how deep I went into 
recursion (ie how many times I re-used a preparedStatement). Wollah! The 
two figures matched.



Hope this helps you out!!!!!




Paul (BRI) wrote:

>I had posted this question about reconnection problems with MS's JDBC
>driver. While it turned out that no one had the same issue on this, I
>wondered what drivers are best for Tomcat -> SQL Server. Are there other
>suitable free drivers? Are others using commercial drivers? I have read
>some of the reviews on various sites - but words from those actually
>using the drivers would be appreciated.
>
>If this question seems to afar from tomcat I would be happy to post
>elsewhere. But it is nice to find a good group dedicated to tomcat -
>this is new to me (coming from MS) and I have been happy so far learning
>more each day.
>
>Thanks for your help,
>
>Paul
>
>-----Original Message-----
>From: Paul (BRI) [mailto:phishmeh@briconsultingroup.com] 
>Sent: Friday, May 23, 2003 10:28 AM
>To: tomcat-user@jakarta.apache.org
>Subject: Tomcat / JDBC / MS SQL / Connection lost and won't reconnect
>
>
>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
>
>  
>


-- 
-------------------------------------------------------------
Andrew Hughes                                            
Software Engineer / Application Developer, LISAsoft Pty Ltd             
B. Comp. Sc.                                             

http://www.lisasoft.com
http://www.ardec.com.au

Sydney                        Adelaide
-----------------------       -----------------------
Level 3 228 Pitt Street       38 Greenhill Road
Sydney  NSW  2000             Wayville  SA  5034

Ph:  +61 2 9283 0855          Ph:  +61 8 8272 1555
Fax: +61 2 9283 0866          Fax: +61 8 8271 1199
-----------------------       -----------------------

The contents of this e-mail are confidential and may be subject to legal or professional privilege
and copyright. No representation is made that this e-mail is free of viruses or other defects.
If you have received this communication in error, you may not copy or distribute any part
of it or otherwise disclose its contents to anyone. Please advise the sender of your incorrect
receipt of this correspondence.



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


Mime
View raw message