Hi Juan,

One other thought: DBCP pools prepared statements too, so be sure to close
the prepared statement before retrying.

Rick

----- Original Message -----

> Hi,
>
> > After you have shut down the db, you need to discard all the
> pre-existing
> > connections in the pool. If there is no way to do this with DBCP
> you could
> > consider using the built in jca based connection pooling which should
> > discard connections if there is a fatal error (such as the db being shut
> > down).
> >
>
> So, if the solution is discarding pre-existing connections, does
> anybody know how to do it with DBCP?
>
> What about using FBWrappingDataSource? I have tested my problem whit
> it and it keep the same, so, is there any way to discard connections
> with this pooling mechanism?
>
> What does it exactly mean to use the built-in jca connection pooling?
> Can I use it with Tomcat or do I need a full J2EE application server?
>
> I could argue that after receiving a FBResourceEception, because of
> any cause, the connection becomes "corrupted" and it's necessary to
> close it. So a FBResourceEception can be considered a fatal exception
> in any case. The question is: why is this exception thrown?
>
> Thanks.
>
> Juan Pedro Lopez
>
> >
> > On 2003.07.31 04:05 Juan Pedro López Sáez wrote:
> > > Hello all,
> > >
> > > I'm using Jaybird 1.0 and Firebird 1.0.3 in a Linux Machine.
> > >
> > > I've a JSP web application that access a Firebird database by means of
> > > DBCP pooling mechanism. The database pool is set up in the init method
> > > of a servlet filter (GetDbConnection). Every new http request gets
> a new
> > > connection from the pool in the doFilter method of the same
> filter. The
> > > filter passes the connection as a request attribute, so every JSP
> in my
> > > web application can retrieve it when needed. Finally, the
> connection is
> > > released to the pool, after the chain.doFilter method of the filter,
> > > performing a java.sql.Connection.close(). Every Statement is closed in
> > > the web application so the connection is supposed to be correcly
> > > released to the pool.
> > >
> > > This schema seems to work fine, but I have a problem. Sometimes I have
> > > to shutdown the database to perform some manteinance tasks. If the web
> > > application tries to connect meanwhile, it gets a new connection from
> > > the pool. When it calls java.sql.Connection.prepareStatement(String),
> > > an FBResourceEception is thrown as it is expected (the database in
> shut
> > > down). The problem is the web application can't ever succeed calling a
> > > conn.prepareStatement(strSel), even if the database is brought on
> line.
> > > It gets new connections from the pool, but
> > >
> > > org.firebirdsql.jca.FBResourceException: local transaction active:
> can't
> > > begin another
> > >
> > > is thrown again a again after every prepareStatement call.
> > >
> > > I must reload the web application in order to make things work.
> > >
> > > I think the problem is related to a kind of connection state
> corruption.
> > > After the first exception, because of the database shutdown,
> connection
> > > is released to the pool but it keeps in a wrong state. When the next
> > > request retrieves the same pooled connection, it gets the
> > > FBResourceException. When the application is reloaded, tha
> database pool
> > > is restarted, so new database connections are in a correct state.
> > >
> > > I have tested the same situation without pooled connections. After the
> > > first exception, connection is closed. When the database is again on
> > > line, new connections are error free and everything works fine.
> > >
> > > See below the web application's stack trace.
> > >
> > > Thank you in advance.
> > >
> > > Juan Pedro Lopez
> > >
> > >
> > > 2003-07-29 20:50:36,664 FATAL com.altiria.wasp.mktSMS.beans.Promotion
> > > Client:1 User:1 sId:9625C9334E83704AB0B018DAFB3C0B4E - .getBasicPromo:
> > > Exception searching for K_PROMOTION field.
> > > java.sql.SQLException: ResourceException:
> > > org.firebirdsql.jca.FBResourceException: database
> > > /var/lib/firebird/data/test_mktsms_R_1_0.gdb shutdown
> > > Reason: database /var/lib/firebird/data/test_mktsms_R_1_0.gdb shutdown
> > > at
> > >
>
org.firebirdsql.jdbc.FBConnection.ensureInTransaction(FBConnection.java:100=
> 3)
> > > at
> > >
>
org.firebirdsql.jdbc.FBPreparedStatement.<init>(FBPreparedStatement.java:77=
> )
> > > at
> > >
> org.firebirdsql.jdbc.FBConnection.prepareStatement(FBConnection.java:243)
> > > at
> > >
>
org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingCon=
> nection.java:187)
> > > at
> > >
> com.altiria.wasp.mktSMS.beans.Promotion.getBasicPromo(Promotion.java:834)
> > > at
> > >
>
org.apache.jsp.instantPushSimple_jsp._jspService(instantPushSimple_jsp.java=
> :146)
> > > at
> org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:137)
> > > at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
> > > at
> > >
>
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:=
> 204)
> > > at
> > >
> org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:295)
> > > at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
> > > at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
> > > at
> > >
>
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Applicatio=
> nFilterChain.java:247)
> > > at
> > >
>
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterC=
> hain.java:193)
> > > at
> > >
>
com.altiria.wasp.mktSMS.filters.GetDbConnection.doFilter(GetDbConnection.ja=
> va:103)
> > > at
> > >
>
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Applicatio=
> nFilterChain.java:213)
> > > at
> > >
>
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterC=
> hain.java:193)
> > > at
> > >
>
com.altiria.wasp.mktSMS.filters.CheckActivity.doFilter(CheckActivity.java:5=
> 5)
> > > at
> > >
>
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Applicatio=
> nFilterChain.java:213)
> > > at
> > >
>
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterC=
> hain.java:193)
> > > at
> > >
>
com.altiria.wasp.mktSMS.filters.SetLoggingContext.doFilter(SetLoggingContex=
> t.java:77)
> > > at
> > >
>
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Applicatio=
> nFilterChain.java:213)
> > > at
> > >
>
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterC=
> hain.java:193)
> > > at
> > >
>
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.j=
> ava:260)
> > > at
> > >
>
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invo=
> keNext(StandardPipeline.java:643)
> > > at
> > >
>
org.apache.catalina.core.StandardPipeline.0invoke(StandardPipeline.java:480=
> )
> > > at
> > > org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
> > > at
> > >
>
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.j=
> ava:191)
> > > at
> > >
>
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invo=
> keNext(StandardPipeline.java:643)
> > > at
> > >
>
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)=
>
> > > at
> > > org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
> > > at
> > >
> org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2415)
> > > at
> > >
>
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:18=
> 0)
> > > at
> > >
>
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invo=
> keNext(StandardPipeline.java:643)
> > > at
> > >
>
org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve=
> .java:170)
> > > at
> > >
>
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invo=
> keNext(StandardPipeline.java:641)
> > > at
> > >
>
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:17=
> 2)
> > > at
> > >
>
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invo=
> keNext(StandardPipeline.java:641)
> > > at
> > >
>
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)=
>
> > > at
> > > org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
> > > at
> > >
>
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.jav=
> a:174)
> > > at
> > >
>
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invo=
> keNext(StandardPipeline.java:643)
> > > at
> > >
>
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)=
>
> > > at
> > > org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
> > > at
> > >
> org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:223)
> > > at
> > >
> org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:432)
> > > at
> > >
>
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConn=
> ection(Http11Protocol.java:386)
> > > at
> > >
> org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:534)
> > > at
> > >
>
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.ja=
> va:530)
> > > at java.lang.Thread.run(Thread.java:536)
> > > 2003-07-29 20:50:38,490 DEBUG com.altiria.wasp.mktSMS.beans.User
> > > Client:1 User:1 sId:9625C9334E83704AB0B018DAFB3C0B4E - In error.jsp
> > > 2003-07-29 20:50:38,490 ERROR com.altiria.wasp.mktSMS.beans.User
> > > Client:1 User:1 sId:9625C9334E83704AB0B018DAFB3C0B4E - Error page
> shown.
> > > 2003-07-29 20:50:38,491 DEBUG
> > > com.altiria.wasp.mktSMS.filters.GetDbConnection Client:1 User:1
> > > sId:9625C9334E83704AB0B018DAFB3C0B4E - GetDbConnection: Connection
> > > closed.
> > > 2003-07-29 20:50:38,491 DEBUG
> > > com.altiria.wasp.mktSMS.filters.SetLoggingContext Client:1 User:1
> > > sId:9625C9334E83704AB0B018DAFB3C0B4E - SetLoggingContext: Removed
> > > logging context
> > >
> > > ------ Now database in on line --------
> > >
> > > 2003-07-29 20:52:00,083 DEBUG
> > > com.altiria.wasp.mktSMS.filters.GetDbConnection Client: User: sId: -
> > > GetDbConnection: Connection opened
> > > 2003-07-29 20:52:00,088 DEBUG com.altiria.wasp.mktSMS.beans.User
> Client:
> > > User: sId: - Enter isClientValid
> > > 2003-07-29 20:52:00,088 FATAL com.altiria.wasp.mktSMS.beans.User
> Client:
> > > User: sId: - .isClientValid: Exception checking client id.
> > > java.sql.SQLException: ResourceException:
> > > org.firebirdsql.jca.FBResourceException: local transaction active:
> can't
> > > begin another
> > > Reason: local transaction active: can't begin another
> > > at
> > >
>
org.firebirdsql.jdbc.FBConnection.ensureInTransaction(FBConnection.java:100=
> 3)
> > > at
> > >
>
org.firebirdsql.jdbc.FBPreparedStatement.<init>(FBPreparedStatement.java:77=
> )
> > > at
> > >
> org.firebirdsql.jdbc.FBConnection.prepareStatement(FBConnection.java:243)
> > > at
> > >
>
org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingCon=
> nection.java:187)
> > > at com.altiria.wasp.mktSMS.beans.User.isClientValid(User.java:453)
> > > at org.apache.jsp.dirlogin_jsp._jspService(dirlogin_jsp.java:84)
> > > at
> org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:137)
> > > at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
> > > at
> > >
>
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:=
> 204)
> > > at
> > >
> org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:295)
> > > at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
> > > at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
> > > at
> > >
>
org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher=
> .java:684)
> > > at
> > >
>
org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatc=
> her.java:432)
> > > at
> > >
>
org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatche=
> r.java:356)
> > > at
> > >
>
org.apache.jasper.runtime.PageContextImpl.forward(PageContextImpl.java:430)=
>
> > > at org.apache.jsp.index_jsp._jspService(index_jsp.java:52)
> > > at
> org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:137)
> > > at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
> > > at
> > >
>
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:=
> 204)
> > > at
> > >
> org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:295)
> > > at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
> > > at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
> > > at
> > >
>
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Applicatio=
> nFilterChain.java:247)
> > > at
> > >
>
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterC=
> hain.java:193)
> > > at
> > >
>
com.altiria.wasp.mktSMS.filters.GetDbConnection.doFilter(GetDbConnection.ja=
> va:103)
> > > at
> > >
>
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Applicatio=
> nFilterChain.java:213)
> > > at
> > >
>
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterC=
> hain.java:193)
> > > at
> > >
>
com.altiria.wasp.mktSMS.filters.SetLoggingContext.doFilter(SetLoggingContex=
> t.java:77)
> > > at
> > >
>
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Applicatio=
> nFilterChain.java:213)
> > > at
> > >
>
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterC=
> hain.java:193)
> > > at
> > >
>
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.j=
> ava:260)
> > > at
> > >
>
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invo=
> keNext(StandardPipeline.java:643)
> > > at
> > >
>
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)=
>
> > > at
> > > org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
> > > at
> > >
>
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.j=
> ava:191)
> > > at
> > >
>
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invo=
> keNext(StandardPipeline.java:643)
> > > at
> > >
>
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)=
>
> > > at
> > > org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
> > > at
> > >
> org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2415)
> > > at
> > >
>
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:18=
> 0)
> > > at
> > >
>
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invo=
> keNext(StandardPipeline.java:643)
> > > at
> > >
>
org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve=
> .java:170)
> > > at
> > >
>
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invo=
> keNext(StandardPipeline.java:641)
> > > at
> > >
>
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:17=
> 2)
> > > at
> > >
>
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invo=
> keNext(StandardPipeline.java:641)
> > > at
> > >
>
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)=
>
> > > at
> > > org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
> > > at
> > >
>
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.jav=
> a:174)
> > > at
> > >
>
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invo=
> keNext(StandardPipeline.java:643)
> > > at
> > >
>
org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)=
>
> > > at
> > > org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
> > > at
> > >
> org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:223)
> > > at
> > >
> org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:432)
> > > at
> > >
>
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConn=
> ection(Http11Protocol.java:386)
> > > at
> > >
> org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:534)
> > > at
> > >
>
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.ja=
> va:530)
> > > at java.lang.Thread.run(Thread.java:536)
> > > 2003-07-29 20:52:01,198 ERROR com.altiria.wasp.mktSMS.beans.User
> Client:
> > > User: sId: - Error page shown
> > > 2003-07-29 20:52:01,199 DEBUG
> > > com.altiria.wasp.mktSMS.filters.GetDbConnection Client: User: sId: -
> > > GetDbConnection: Connection closed.
> > > 2003-07-29 20:52:01,199 DEBUG
> > > com.altiria.wasp.mktSMS.filters.SetLoggingContext Client: User: sId: -
> > > SetLoggingContext: Removed logging context
> > >
> > > --
> > > Juan Pedro López Sáez
> > > ALTIRIA TIC, S.L.L.
> > > www.altiria.com
> > >
> > >
> > >
> > >
> > > To unsubscribe from this group, send an email to:
> > > Firebird-Java-unsubscribe@yahoogroups.com
> > >
> > >
> > >
> > > Your use of Yahoo! Groups is subject to
> http://docs.yahoo.com/info/terms/
> > >
> > >
> > >
> > >
>
>
>
> To unsubscribe from this group, send an email to:
> Firebird-Java-unsubscribe@yahoogroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>