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:[EMAIL PROTECTED] Sent: Friday, May 23, 2003 10:28 AM
To: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--
-------------------------------------------------------------
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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
