Connection conn = null;
Statement stmt = null; // Or PreparedStatement if needed
ResultSet rs = null;
try {
conn = //get connection from connection pool ...
stmt = conn.createStatement("select ...");
rs = stmt.executeQuery();
... iterate through the result set ...
rs.close();
rs = null;
stmt.close();
stmt = null;
conn.close(); // Return to connection pool
conn = null; // Make sure we don't close it twice
} catch (SQLException e) {
... deal with errors ...
} finally {
// Always make sure result sets and statements are closed,
// and the connection is returned to the pool
if (rs != null) {
try { rs.close(); } catch (SQLException e) { ; }
rs = null;
}
if (stmt != null) {
try { stmt.close(); } catch (SQLException e) { ; }
stmt = null;
}
if (conn != null) {
try { conn.close(); } catch (SQLException e) { ; }
conn = null;
}
}
On Tue, 28 Sep 2004 15:28:24 -0400, Phillip Qin
<[EMAIL PROTECTED]> wrote:
> Even though this resource is not in "Global" scope?
>
>
>
>
> -----Original Message-----
> From: Shapira, Yoav [mailto:[EMAIL PROTECTED]
> Sent: September 28, 2004 1:55 PM
> To: Tomcat Users List
> Subject: RE: Tomcat 4.1 Connection Pooling...
>
> Hi,
> Hmm ;) There's a good reason there's no close method on
> javax.sql.DataSource. There's also a good reason the J2EE spec calls for
> the container, not the user, to manage the lifecycle of JNDI resources such
> as DataSources. IF you go with this ServletContextListener approach to take
> over lifecycle management of a server-provided JNDI resource, you'll be in a
> murky space at best.
>
> Yoav Shapira
> Millennium Research Informatics
>
> >-----Original Message-----
> >From: Phillip Qin [mailto:[EMAIL PROTECTED]
> >Sent: Tuesday, September 28, 2004 1:27 PM
> >To: 'Tomcat Users List'
> >Subject: RE: Tomcat 4.1 Connection Pooling...
> >
> >For connecion pooling, when you close the connection, you don't
> actually
> >close the physical link. After several shutdown/sartup of your
> application,
> >open connection accumulates and exceeds Oracle's 200 thus you receive
> that
> >SQLException.
> >
> >To solve your problem, I would implement a ServletContextListener to
> >initialize connection pool when my context is in the initialization
> (public
> >method contextInitialized) and close data source when my context is
> >destroyed (public method contextDestroyed).
> >
> >If you have defined loadonstartup servlet in your web.xml, in the
> destroy
> >method, explicitly close your data source.
> >
> >-----Original Message-----
> >From: Shilpa Nalgonda [mailto:[EMAIL PROTECTED]
> >Sent: September 28, 2004 1:06 PM
> >To: Tomcat Users List
> >Subject: RE: Tomcat 4.1 Connection Pooling...
> >
> >
> >I was not aware that i should explicitly close the datasource, but i am
> >making sure that i close all the connections by saying Connection.close
> in
> >the finally block..can you tell me how to close the datasource and
> where to
> >do that...
> >
> >
> >-----Original Message-----
> >From: Phillip Qin [mailto:[EMAIL PROTECTED]
> >Sent: Tuesday, September 28, 2004 1:06 PM
> >To: 'Tomcat Users List'
> >Subject: RE: Tomcat 4.1 Connection Pooling...
> >
> >
> >This error happens when there are too many connections opened. There
> are
> >too
> >many connections opened because before you shut down your application,
> you
> >don't explicitly close your data source.
> >
> >-----Original Message-----
> >From: Shilpa Nalgonda [mailto:[EMAIL PROTECTED]
> >Sent: September 28, 2004 12:55 PM
> >To: Tomcat Users List
> >Subject: RE: Tomcat 4.1 Connection Pooling...
> >
> >
> >But my application runs fine even with out that entry in web.xml, i am
> >using Tomcats ConnectionPooling to connect to database..I only have
> scalability
> >issue, when i test my application with multiple users i get the ORAcle
> erro
> >below.. If only one users is there, then there is no problem...can you
> >please suggest if adding that entry to web.xml will help...
> >
> >java.sql.SQLException: ORA-00020: maximum number of processes (200)
> >exceeded
> >
> > at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java)
> > at oracle.jdbc.ttc7.O3log.receive1st(O3log.java)
> > at oracle.jdbc.ttc7.TTC7Protocol.logon(TTC7Protocol.java)
> > at
> >oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java)
> > at
> >oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.java
> )
> > at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java)
> > at
> >org.apache.commons.dbcp.DriverConnectionFactory.createConnection(Driver
> Conn
> >e
> >ctionFactory.java:82)
> > at
> >org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableCo
> nnec
> >t
> >ionFactory.java:300)
> > at
> >org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObje
> ctPo
> >o
> >l.java:816)
> > at
> >org.apache.commons.dbcp.AbandonedObjectPool.borrowObject(AbandonedObjec
> tPoo
> >l
> >.java:119)
> > at
> >org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSour
> ce.j
> >a
> >va:140)
> > at
> >org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.j
> ava:
> >5
> >18)
> > at
> >com.mypublisher.oemapi.DAO.DBAccess.getConnection(DBAccess.java:43)
> >
> >-----Original Message-----
> >From: Shapira, Yoav [mailto:[EMAIL PROTECTED]
> >Sent: Tuesday, September 28, 2004 12:53 PM
> >To: Tomcat Users List
> >Subject: RE: Tomcat 4.1 Connection Pooling...
> >
> >
> >
> >Hi,
> >Yes, it's needed if you're using server-provided connection pooling.
> Read
> >the Servlet Specification.
> >
> >Yoav Shapira
> >Millennium Research Informatics
> >
> >
> >>-----Original Message-----
> >>From: Shilpa Nalgonda [mailto:[EMAIL PROTECTED]
> >>Sent: Tuesday, September 28, 2004 12:47 PM
> >>To: Tomcat Users List
> >>Subject: RE: Tomcat 4.1 Connection Pooling...
> >>
> >>Is it necessary to add this in web.xml, i have configured datasource
> >for
> >>connectonpool only in server.xml...
> >>
> >>
> >>web.xml
> >>-----------
> >><resource-ref>
> >><res-ref-name>jdbc/myoracle</res-ref-name>
> >><res-type>javax.sql.DataSource</res-type>
> >><res-auth>Container</res-auth>
> >></resource-ref>
> >>
> >>
> >>
> >>-----Original Message-----
> >>From: Shilpa Nalgonda [mailto:[EMAIL PROTECTED]
> >>Sent: Wednesday, September 22, 2004 11:05 AM
> >>To: Tomcat Users List
> >>Subject: RE: Tomcat 4.1 Connection Pooling...
> >>
> >>
> >>Thanks.
> >>Maybe its the parameter configuration can you send me your
> >configuration
> >>for
> >>connection pooling...
> >>I am using these parameters...
> >>maxActive :20
> >>>maxIdle :10
> >>>maxWait :10000
> >>>removeAbandoned :true -- what exactly is MaxIdle parameter..????
> >>
> >>Also my application makes 7 database queries, each query gets
> >connection
> >>from datasource and closes() the connection in finally block. When it
> >gets
> >>connection from datasource it is supposed to get it from pool right???
> >>
> >>I noticed that after my application runs 3 times --(3x7 =21).. it
> gives
> >me
> >>this error
> >>
> >>org.apache.commons.dbcp.SQLNestedException: Cannot get a connection,
> >pool
> >>exhausted, cause:
> >>java.util.NoSuchElementException: Timeout waiting for idle object
> >> at
> >>org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericOb.
> .
> >>
> >>So can you send me ur config and code snippet to get datasource if
> >>possible...
> >>
> >>
> >>-----Original Message-----
> >>From: Shapira, Yoav [mailto:[EMAIL PROTECTED]
> >>Sent: Wednesday, September 22, 2004 10:55 AM
> >>To: Tomcat Users List
> >>Subject: RE: Tomcat 4.1 Connection Pooling...
> >>
> >>
> >>
> >>Hi,
> >>Nope, I can't help unfortunately. I used Tomcat 4.1 for years
> >>connecting to Oracle 8i and 9i, and I continue to do so now with
> Tomcat
> >>5.0 and 5.5. Maybe I'm just lucky, but the docs (specifically the
> JDBC
> >>DataSources how-to) have always worked for me out of the box. I've
> >>never seen the error you describe.
> >>
> >>Yoav Shapira
> >>Millennium Research Informatics
> >>
> >>
> >>>-----Original Message-----
> >>>From: Shilpa Nalgonda [mailto:[EMAIL PROTECTED]
> >>>Sent: Wednesday, September 22, 2004 10:44 AM
> >>>To: Tomcat Users List
> >>>Subject: RE: Tomcat 4.1 Connection Pooling...
> >>>
> >>>Hi Yoav Shapira,
> >>>Can you put some light on this pooling stuff...
> >>>Thanks.
> >>>Shilpa.
> >>>
> >>>-----Original Message-----
> >>>From: Shilpa Nalgonda [mailto:[EMAIL PROTECTED]
> >>>Sent: Wednesday, September 22, 2004 9:31 AM
> >>>To: Tomcat Users List
> >>>Subject: RE: Tomcat 4.1 Connection Pooling...
> >>>
> >>>
> >>>Hi Cary,
> >>>I am running my application on both windowsxp and Linux. And my
> Oracle
> >>>version is 8i. I have the same problem on both OS. I think
> increasing
> >>>the number of processes would not help. This has got something to do
> >>>with Tomcat connection pooling
> >>configuration.
> >>>I am missing some configuration stuff there...
> >>>if i use these below parameters in server.xml iget timeout
> >>exception...just
> >>>after couple of requests..
> >>>maxActive :20
> >>>maxIdle :10
> >>>mxWait :10000
> >>>removeAbandoned :true
> >>>"org.apache.commons.dbcp.SQLNestedException: Cannot get a connection,
> >>pool
> >>>exhausted, cause:
> >>>java.util.NoSuchElementException: Timeout waiting for idle object "
> >>>
> >>>And if i use this below configuration i get ORA:00020 exception...
> >>>maxActive :0 maxIdle :0 mxWait :0 removeAbandoned :true
> >>>
> >>>Can you please help---i have been looking into this since 2 days and
> >>still
> >>>it doesn't work...
> >>>
> >>>-----Original Message-----
> >>>From: Cary Conover [mailto:[EMAIL PROTECTED]
> >>>Sent: Tuesday, September 21, 2004 10:09 PM
> >>>To: Tomcat Users List
> >>>Subject: RE: Tomcat 4.1 Connection Pooling...
> >>>
> >>>
> >>>Shilpa,
> >>>
> >>>What OS are you running this configuration on? If it is AIX I would
> >>>investigate to see how the system is configured for the number of
> >>processes
> >>>per user on the system. If it is configured as a Workstation it is
> >>>defaulted to 128 processes / login. If it is configured as a server
> >it
> >>is
> >>>set to 500 processes / login. Plus Oracle support site suggests in
> >the
> >>>neighborhood of 2500 processes / login for Oracle 9i to function well
> >>if
> >>>you
> >>>are running 9i. These are all system settings that can be found in
> >>smitty
> >>>on the system and adjusted as root user.
> >>>
> >>>Hope this helps,
> >>>
> >>>Cary
> >>>
> >>>Shilpa Nalgonda <[EMAIL PROTECTED]> wrote:
> >>>Can someone please suggest how to achieve connection Pooling in
> tomcat
> >>4.1.
> >>>As per the docs i have
> >>>1) configured server.xml to include the resource parameters (
> >>datasource,
> >>>connection pool size etc)
> >>>2) In application code , retrieve connection using datasource
> >>>3) Every Method call to database retrieves connection
> >>>using datasource.getConnection ()
> >>>and closes connection everytime
> >>>
> >>>But still the pooling is not achieved, i get ORA-00020 error( maximim
> >>>proceses exceeded), i increased the size from 30 to 200) even then it
> >>is
> >>>not
> >>>working.
> >>>
> >>>Can someone please help----
> >>>
> >>>-----Original Message-----
> >>>From: Shilpa Nalgonda [mailto:[EMAIL PROTECTED]
> >>>Sent: Monday, September 20, 2004 12:55 PM
> >>>To: Tomcat Users List
> >>>Subject: org.apache.commons.dbcp.SQLNestedException: Cannot get a
> >>>connection, pool exhausted, cause:
> >>>
> >>>
> >>>Hi, I am getting the timeout exception for connection pooling, i am
> >>using
> >>>Tomca 4.1 and datasource to retrieve connections.
> >>>I am attaching all the soure code and configuration files used in my
> >>>application, please suggest where iam doing wrong...
> >>>
> >>>SEVERE: getConnection()--SQLException
> >>>org.apache.commons.dbcp.SQLNestedException: Cannot get a connection,
> >>pool
> >>>exhausted, cause:
> >>>java.util.NoSuchElementException: Timeout waiting for idle object at
> >>>org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericOb
> j
> >e
> >>ctPo
> >>>o
> >>>l.java:801)
> >>>at
> >>>org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSo
> u
> >r
> >>ce.j
> >>>a
> >>>va:140)
> >>>at
> >>>org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource
> .
> >j
> >>ava:
> >>>5
> >>>18)
> >>>at
> >>>com.mypublisher.oemapi.DAO.DBAccess.getConnection(DBAccess.java:47)
> >>>at com.mypublisher.oemapi.DAO.Persister.(Persister.java:46)
> >>>at
> >>>com.mypublisher.oemapi.SubmitOrder.SubmitOrder(SubmitOrder.java:61)
> >>>at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at
> >>>sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
> j
> >a
> >>va:3
> >>>9
> >>>)
> >>>at
> >>>sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
> s
> >o
> >>rImp
> >>>l
> >>>.java:25)
> >>>at java.lang.reflect.Method.invoke(Method.java:324)
> >>>at org.apache.xmlrpc.Invoker.execute(Invoker.java:168)
> >>>at
> >>>org.apache.xmlrpc.XmlRpcWorker.invokeHandler(XmlRpcWorker.java:123)
> >>>at org.apache.xmlrpc.XmlRpcWorker.execute(XmlRpcWorker.java:185)
> >>>at org.apache.xmlrpc.XmlRpcServer.execute(XmlRpcServer.java:151)
> >>>at org.apache.xmlrpc.XmlRpcServer.execute(XmlRpcServer.java:128)
> >>>at
> >>>com.mypublisher.oemapi.xmlrpc.XmlRpcServlet.doPost(XmlRpcServlet.java
> >>>=====================================================================
> =
> >=
> >>====
> >>>=
> >>>===========
> >>>server.xml
> >>>---------
> >>>xmlrpc-oemapi-->
> >>>debug="5" reloadable="true" crossContext="true" useNaming="true">
> >>>
> >>>prefix="localhost_rpcoemapi_log." suffix=".txt" timestamp="true"/>
> >>>auth="Container" type="javax.sql.DataSource"/>
> >>>
> >>>
> >>>
> >>>
> >>>factory
> >>>org.apache.commons.dbcp.BasicDataSourceFactory
> >>>
> >>>
> >>>
> >>> configure your mysqld max_connections large enough to handle
> >>> all of your db connections. Set to 0 for no limit.
> >>> -->
> >>>
> >>>
> >>>maxActive
> >>>5
> >>>
> >>>
> >>>
> >>> Set to 0 for no limit.
> >>> -->
> >>>
> >>>
> >>>maxIdle
> >>>2
> >>>
> >>>
> >>>
> >>> in ms, in this example 10 seconds. An Exception is thrown if
> >>> this timeout is exceeded. Set to -1 to wait indefinitely.
> >>> -->
> >>>
> >>>
> >>>maxWait
> >>>10000
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>username
> >>>ioeadmin
> >>>
> >>>
> >>>
> >>>
> >>>password
> >>>ioeadmin
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>driverClassName
> >>>oracle.jdbc.driver.OracleDriver
> >>>
> >>>
> >>>
> >>> The autoReconnect=true argument to the url makes sure that
> >the
> >>> mm.mysql JDBC Driver will automatically reconnect if mysqld
> >>closed
> >>>the
> >>> connection. mysqld by default closes idle connections after
> >8
> >>>hours.
> >>> -->
> >>>
> >>>
> >>>url
> >>>jdbc:oracle:thin:@ipaddress:1521:MYDB
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>=====================================================================
> =
> >=
> >>====
> >>>=
> >>>===================================
> >>>DatasourceFactory is a singleton class used to retrieve the
> datasource
> >>from
> >>>tomcat using JNDi..and connection is obtained from datasource...
> >>>public static DataSource getInstance() throws SystemException { if
> >>>(!isDataSrcAvaliable) {
> >>>if (dataSource == null)
> >>>{
> >>>new DataSourceFactory();
> >>>doLookUp();
> >>>isDataSrcAvaliable = true;
> >>>} //end if (dataSource == null)
> >>>}// end if (!isDataSrcAvaliable)
> >>>return dataSource;
> >>>} /* end getInstance() */
> >>>
> >>>doLookUp method does this:
> >>>Context ctx = new InitialContext();
> >>>String dataSrc =
> >>>msgResource.getMessage(OEMAPIConstants.DATA_SOURCE_NAME);
> >>>Context env = (Context)ctx.lookup("java:comp/env");
> >>>dataSource = (DataSource) env.lookup(dataSrc);
> >>>
> >>>
> >>>public static Connection getConnection() throws DAOException {
> >>>DataSource ds = (DataSource) DataSourceFactory.getInstance();
> >>>Connection con = ds.getConnection(); return ds.getConnection(); }
> >>>and every database lookup class, gets this connection and closes the
> >>>connection after the query to database...
> >>>
> >>>
> >>>
> >>>---------------------------------------------------------------------
> >>>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]
> >>>
> >>>
> >>>
> >>>
> >>>
> >>>---------------------------------
> >>>Do you Yahoo!?
> >>>New and Improved Yahoo! Mail - Send 10MB messages!
> >>>
> >>>
> >>>---------------------------------------------------------------------
> >>>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]
> >>
> >>
> >>
> >>
> >>This e-mail, including any attachments, is a confidential business
> >>communication, and may contain information that is confidential,
> >>proprietary and/or privileged. This e-mail is intended only for the
> >>individual(s)
> >to
> >>whom it is addressed, and may not be saved, copied, printed, disclosed
> >or
> >>used by anyone else. If you are not the(an) intended recipient,
> please
> >>immediately delete this e-mail from your computer system and notify
> the
> >>sender. Thank you.
> >>
> >>
> >>---------------------------------------------------------------------
> >>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]
> >>
> >>
> >>---------------------------------------------------------------------
> >>To unsubscribe, e-mail: [EMAIL PROTECTED]
> >>For additional commands, e-mail: [EMAIL PROTECTED]
> >
> >
> >
> >
> >This e-mail, including any attachments, is a confidential business
> >communication, and may contain information that is confidential,
> >proprietary and/or privileged. This e-mail is intended only for the
> >individual(s)
> to
> >whom it is addressed, and may not be saved, copied, printed, disclosed
> or
> >used by anyone else. If you are not the(an) intended recipient, please
> >immediately delete this e-mail from your computer system and notify the
> >sender. Thank you.
> >
> >
> >---------------------------------------------------------------------
> >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]
> >
> >
> >
> >
> >
> >---------------------------------------------------------------------
> >To unsubscribe, e-mail: [EMAIL PROTECTED]
> >For additional commands, e-mail: [EMAIL PROTECTED]
> >
> >
> >
>
>
> This e-mail, including any attachments, is a confidential business
> communication, and may contain information that is confidential, proprietary
> and/or privileged. This e-mail is intended only for the individual(s) to
> whom it is addressed, and may not be saved, copied, printed, disclosed or
> used by anyone else. If you are not the(an) intended recipient, please
> immediately delete this e-mail from your computer system and notify the
> sender. Thank you.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
> !DSPAM:4159a597165394739372390!
>
>
--
------------------------------------------------------------------
Carlos J, Bracho M.
"The significant problems we face cannot
be solved at the same level of thinking
we were at when we created them."
Albert Einstein
------------------------------------------------------------------
e-mail: [EMAIL PROTECTED]
MSN: [EMAIL PROTECTED]
------------------------------------------------------------------
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
