You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@tomcat.apache.org by Andrew Hughes - LISAsoft <ah...@lisasoft.com.au> on 2003/05/29 07:40:11 UTC
Re: Tomcat - JDBC - MS SQL Driver Recommendations?
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