Re: How to close open connections after application stop?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Martin, On 2/22/2009 10:26 AM, Martin Gainty wrote: > catch (SQLException sqlEx) > { > try > { > resultset.close(); > statement.close(); > connection.close(); > } It's kind of silly to call close() on all these resources in the exception handles when you're just going to: > finally > { >try >{ > resultset.close(); > statement.close(); > connection.close(); > } > catch(java.sql.SQLException excp) >{ > log.debug("Exception on resultset/statement/connection close > message="=excp.getMessage()); > } >} //end finally.. ...close them all in the finally block. Also, each close() method should be surrounded by try/catch blocks and appropriate log messages. Finally, logging exception stack traces is always helpful. - -chris -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkmiDwQACgkQ9CaO5/Lv0PBHmQCfcUww2dA6wQqlY/1PWpxUGA2b XpsAn2Bg/V3AVSBWMDcFcax8A6DkUo50 =urPv -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: How to close open connections after application stop?
Mark Thomas ha scritto: Edoardo Panfili wrote: Using this code in destroy() method of a servlet marked as 1 Yep - that is the sort of code you'd need. Using a context listener would be a better solution as Tomcat is free to call destroy() on your Servlet whenever it likes. Thank again (to you and the list) edoardo - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: How to close open connections after application stop?
Edoardo Panfili wrote: > Using this code in destroy() method of a servlet marked as > 1 Yep - that is the sort of code you'd need. Using a context listener would be a better solution as Tomcat is free to call destroy() on your Servlet whenever it likes. Mark - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: How to close open connections after application stop?
Alan Chaney ha scritto: I don't think so. Let me recap your problem: When you undeploy an application from tomcat (using the DBCP pooling mechanism) you can't make STRUCTURAL changes to the database because it complains that connections are still in use. This is exactly what one would expect. I've encountered the same problem. When an application finishes with a database connection it is returned to the pool. That's exactly what a connection pool is for! As far as I can see by looking at the tomcat source code the connection pool is created at startup and remains active until TC shutdown. Once a connection has been obtained from the pool it may stay 'active' for the entire duration of the TC session (that is, from TC start to TC stop) Obviously, depending upon your usage, it is possible for more than one application in the same container to be reusing the same connection pool. Your original post indicates that only one app. is using the database. It seems to me that: . 3. As I assume you are using DBCP in Tomcat, carefully read the DBCP docs, configure your system so that you can directly access the POOLED connections, keep a list of ALL the connections you use and then shut them down at the end. This is fraught with difficulty. Using this code in destroy() method of a servlet marked as 1 seems work --- try { Context ambiente = (Context) new InitialContext().lookup("java:comp/env"); DataSource pool = (DataSource) ambiente.lookup("jdbc/anArchive"); if(pool instanceof org.apache.tomcat.dbcp.dbcp.BasicDataSource){ org.apache.tomcat.dbcp.dbcp.BasicDataSource source = (org.apache.tomcat.dbcp.dbcp.BasicDataSource) pool; System.out.println("closing source..."); source.close(); System.out.println("closed."); // System.out.println("maxIdle:"+source.getMaxIdle()); // source.setMaxIdle(0); // System.out.println("maxIdle:"+source.getMaxIdle()); } } catch (Exception e) { e.printStackTrace(); } --- "source.setMaxIdle(0);" is not necessary. also accessToUnderlyingConnectionAllowed="true" in context is not necessary. Edoardo - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: How to close open connections after application stop?
Alan Chaney ha scritto: Edoardo wrote I have resultset.close(); statement.close(); connection.close(); in my code. and connection = dataSource.getConnection(); seems very close to my ambiente = (Context) new InitialContext().lookup("java:comp/env"); pool = (DataSource) ambiente.lookup("jdbc/myApp); Connection conn = pool.getConnection(); there are a lot of debug information in my code and seems that nothing is going wrong (no exceptions). but... if you post that it means that I am doing something wrong. Edoardo I don't think so. Let me recap your problem: When you undeploy an application from tomcat (using the DBCP pooling mechanism) you can't make STRUCTURAL changes to the database because it complains that connections are still in use. Yes. This is exactly what one would expect. I've encountered the same problem. When an application finishes with a database connection it is returned to the pool. That's exactly what a connection pool is for! As far as I can see by looking at the tomcat source code the connection pool is created at startup and remains active until TC shutdown. Once a connection has been obtained from the pool it may stay 'active' for the entire duration of the TC session (that is, from TC start to TC stop) Obviously, depending upon your usage, it is possible for more than one application in the same container to be reusing the same connection pool. Your original post indicates that only one app. is using the database. Yes. It seems to me that: 1. you could just shutdown tomcat! If this is a production site the best plan would be to write a script which renames the database and does whatever else you need, test it on a development machine and just find a 'quiet' time to shutdown the site, update the db and restart. This way is usable (and is the easiest, i need less that 30sec). At the present time is also usable (not very high traffic) but I'd like to find a definitive solution. 2. Move the connection pool into your application. Thus shutting down the application would shutdown the pool. :-) some times ago the pool was managed by my application with some custom code, in the new version of the system I rely on tomcat (hope that this choice is not so bad!) 3. As I assume you are using DBCP in Tomcat, carefully read the DBCP docs, configure your system so that you can directly access the POOLED connections, keep a list of ALL the connections you use and then shut them down at the end. This is fraught with difficulty. Your assumption is right. This seems non so easy, but I will try. Thank you very much for your (and other users) help. Edoardo - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: How to close open connections after application stop?
Edoardo wrote I have resultset.close(); statement.close(); connection.close(); in my code. and connection = dataSource.getConnection(); seems very close to my ambiente = (Context) new InitialContext().lookup("java:comp/env"); pool = (DataSource) ambiente.lookup("jdbc/myApp); Connection conn = pool.getConnection(); there are a lot of debug information in my code and seems that nothing is going wrong (no exceptions). but... if you post that it means that I am doing something wrong. Edoardo I don't think so. Let me recap your problem: When you undeploy an application from tomcat (using the DBCP pooling mechanism) you can't make STRUCTURAL changes to the database because it complains that connections are still in use. This is exactly what one would expect. I've encountered the same problem. When an application finishes with a database connection it is returned to the pool. That's exactly what a connection pool is for! As far as I can see by looking at the tomcat source code the connection pool is created at startup and remains active until TC shutdown. Once a connection has been obtained from the pool it may stay 'active' for the entire duration of the TC session (that is, from TC start to TC stop) Obviously, depending upon your usage, it is possible for more than one application in the same container to be reusing the same connection pool. Your original post indicates that only one app. is using the database. It seems to me that: 1. you could just shutdown tomcat! If this is a production site the best plan would be to write a script which renames the database and does whatever else you need, test it on a development machine and just find a 'quiet' time to shutdown the site, update the db and restart. 2. Move the connection pool into your application. Thus shutting down the application would shutdown the pool. 3. As I assume you are using DBCP in Tomcat, carefully read the DBCP docs, configure your system so that you can directly access the POOLED connections, keep a list of ALL the connections you use and then shut them down at the end. This is fraught with difficulty. Regards Alan - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: How to close open connections after application stop?
Martin Gainty ha scritto: javax.sql.DataSource dataSource ; java.sql.Connection connection; java.sql.Statement statement; java.sqlResultset resultSet; code.. try{ connection = dataSource.getConnection(); statement = connection.prepareStatement("SELECT FU FROM BAR"); resultSet = statement.executeQuery(); } catch (SQLException sqlEx) { try { resultset.close(); statement.close(); connection.close(); } catch(java.sql.SQLException excp) { log.debug("A SQLException has been produced another Exception on resultset/statement/connection was produced where the message="=excp.getMessage()); } } catch (Exception ex) { try { resultset.close(); statement.close(); connection.close(); } catch(java.sql.SQLException excp) { log.debug("A General Exception has been produced another SQLException on resultset/statement/connection was produced where the message="=excp.getMessage()); } } catch(java.sql.SQLException excp) { log.debug("SQLException on resultset/statement/connection close message="=excp.getMessage()); } } finally { try { resultset.close(); statement.close(); connection.close(); } catch(java.sql.SQLException excp) { log.debug("Exception on resultset/statement/connection close message="=excp.getMessage()); } } //end finally.. I have resultset.close(); statement.close(); connection.close(); in my code. and connection = dataSource.getConnection(); seems very close to my ambiente = (Context) new InitialContext().lookup("java:comp/env"); pool = (DataSource) ambiente.lookup("jdbc/myApp); Connection conn = pool.getConnection(); there are a lot of debug information in my code and seems that nothing is going wrong (no exceptions). but... if you post that it means that I am doing something wrong. Edoardo - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
RE: How to close open connections after application stop?
javax.sql.DataSource dataSource ; java.sql.Connection connection; java.sql.Statement statement; java.sqlResultset resultSet; code.. try { connection = dataSource.getConnection(); statement = connection.prepareStatement("SELECT FU FROM BAR"); resultSet = statement.executeQuery(); } catch (SQLException sqlEx) { try { resultset.close(); statement.close(); connection.close(); } catch(java.sql.SQLException excp) { log.debug("A SQLException has been produced another Exception on resultset/statement/connection was produced where the message="=excp.getMessage()); } } catch (Exception ex) { try { resultset.close(); statement.close(); connection.close(); } catch(java.sql.SQLException excp) { log.debug("A General Exception has been produced another SQLException on resultset/statement/connection was produced where the message="=excp.getMessage()); } } catch(java.sql.SQLException excp) { log.debug("SQLException on resultset/statement/connection close message="=excp.getMessage()); } } finally { try { resultset.close(); statement.close(); connection.close(); } catch(java.sql.SQLException excp) { log.debug("Exception on resultset/statement/connection close message="=excp.getMessage()); } } //end finally.. Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. > Date: Sun, 22 Feb 2009 11:25:42 +0100 > From: edoa...@aspix.it > To: users@tomcat.apache.org > Subject: How to close open connections after application stop? > > Hy, > > I have one webapp in Tomcat 6.0.18 with this context: > > >type="javax.sql.DataSource" >maxActive="8" maxIdle="5" maxWait="300" >username="myApp" password="passwd" >driverClassName="org.postgresql.Driver" >url="jdbc:postgresql://127.0.0.1:5432/myApp" >removeAbandoned="true" >removeAbandonedTimeout="30" >logAbandoned="true" >/> > > > After application stop (using tomcat manager) I'd like to (drastic > example) rename the db, but I can't do it because there are open > connections. > > There are no other application using myApp db, but with "ps ax" I can see: > > 189 ?? Ss 0:02.67 postgres: myApp myApp 127.0.0.1(49162) idle > > Is there a way to close the connection without closing Toncat? > > thank you > Edoardo > > > - > To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org > For additional commands, e-mail: users-h...@tomcat.apache.org > _ Windows Live™ Hotmail®…more than just e-mail. http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t2_hm_justgotbetter_howitworks_022009
Re: How to close open connections after application stop?
Edoardo Panfili ha scritto: Mark Thomas ha scritto: Edoardo Panfili wrote: Hy, I have one webapp in Tomcat 6.0.18 with this context: After application stop (using tomcat manager) I'd like to (drastic example) rename the db, but I can't do it because there are open connections. There are no other application using myApp db, but with "ps ax" I can see: 189 ?? Ss 0:02.67 postgres: myApp myApp 127.0.0.1(49162) idle Is there a way to close the connection without closing Toncat? Hmm. If you were managing your own pool then you could call close() on the pool. Arguably, if Tomcat creates an context level pool, Tomcat should close it when the context stops but I don't think it does. That probably calls for some testing to check behaviour and possibly an enhancement request. this is my code to retrieve a connection this is the right one (not so different) ambiente = (Context) new InitialContext().lookup("java:comp/env"); pool = (DataSource) ambiente.lookup("jdbc/myApp); Connection conn = pool.getConnection(); .. conn.close(); // this in inside finally sorry for the wrong post Edoardo - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: How to close open connections after application stop?
Edoardo Panfili wrote: > Mark Thomas ha scritto: >> Edoardo Panfili wrote: >>> Hy, >>> >>> I have one webapp in Tomcat 6.0.18 with this context: >>> >>> >>> >> type="javax.sql.DataSource" >>> maxActive="8" maxIdle="5" maxWait="300" >>> username="myApp" password="passwd" >>> driverClassName="org.postgresql.Driver" >>> url="jdbc:postgresql://127.0.0.1:5432/myApp" >>> removeAbandoned="true" >>> removeAbandonedTimeout="30" >>> logAbandoned="true" >>> /> >>> >>> >>> After application stop (using tomcat manager) I'd like to (drastic >>> example) rename the db, but I can't do it because there are open >>> connections. >>> >>> There are no other application using myApp db, but with "ps ax" I can >>> see: >>> >>> 189 ?? Ss 0:02.67 postgres: myApp myApp 127.0.0.1(49162) idle >>> >>> Is there a way to close the connection without closing Toncat? >> >> Hmm. If you were managing your own pool then you could call close() on >> the pool. >> >> Arguably, if Tomcat creates an context level pool, Tomcat should close >> it when the context stops but I don't think it does. That probably calls >> for some testing to check behaviour and possibly an enhancement request. > this is my code to retrieve a connection > --- > ambiente = (Context) new InitialContext().lookup("java:comp/env"); > pool = (DataSource) ambiente.lookup("jdbc/anArchive"); > Connection conn = pool.getConnection(); > .. > conn.close(); // this in inside finally > --- > > but If my code is wrong removeAbandoned="true" is a guaranty to have the > connection closed after few time (30sec). is this right? Not really. It only closes connections your application has abandoned. It won't touch the idle ones in the pool. >> Can you close the connections from the database management tools? > I'm sorry but: where can I found it? (Do you mean from postgres? can't > figure how) Yes. Sorry, I don't know postgres. > I am searching for that also inside tomcatProbe but can't find it. I don't think it supports it. Mark - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: How to close open connections after application stop?
Mark Thomas ha scritto: Edoardo Panfili wrote: Hy, I have one webapp in Tomcat 6.0.18 with this context: After application stop (using tomcat manager) I'd like to (drastic example) rename the db, but I can't do it because there are open connections. There are no other application using myApp db, but with "ps ax" I can see: 189 ?? Ss 0:02.67 postgres: myApp myApp 127.0.0.1(49162) idle Is there a way to close the connection without closing Toncat? Hmm. If you were managing your own pool then you could call close() on the pool. Arguably, if Tomcat creates an context level pool, Tomcat should close it when the context stops but I don't think it does. That probably calls for some testing to check behaviour and possibly an enhancement request. this is my code to retrieve a connection --- ambiente = (Context) new InitialContext().lookup("java:comp/env"); pool = (DataSource) ambiente.lookup("jdbc/anArchive"); Connection conn = pool.getConnection(); .. conn.close(); // this in inside finally --- but If my code is wrong removeAbandoned="true" is a guaranty to have the connection closed after few time (30sec). is this right? Can you close the connections from the database management tools? I'm sorry but: where can I found it? (Do you mean from postgres? can't figure how) I am searching for that also inside tomcatProbe but can't find it. thank you Edoardo - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: How to close open connections after application stop?
Edoardo Panfili wrote: > Hy, > > I have one webapp in Tomcat 6.0.18 with this context: > > > type="javax.sql.DataSource" > maxActive="8" maxIdle="5" maxWait="300" > username="myApp" password="passwd" > driverClassName="org.postgresql.Driver" > url="jdbc:postgresql://127.0.0.1:5432/myApp" > removeAbandoned="true" > removeAbandonedTimeout="30" > logAbandoned="true" > /> > > > After application stop (using tomcat manager) I'd like to (drastic > example) rename the db, but I can't do it because there are open > connections. > > There are no other application using myApp db, but with "ps ax" I can see: > > 189 ?? Ss 0:02.67 postgres: myApp myApp 127.0.0.1(49162) idle > > Is there a way to close the connection without closing Toncat? Hmm. If you were managing your own pool then you could call close() on the pool. Arguably, if Tomcat creates an context level pool, Tomcat should close it when the context stops but I don't think it does. That probably calls for some testing to check behaviour and possibly an enhancement request. Can you close the connections from the database management tools? Mark - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org