Is there a reason to use the OCI driver? Try the thin driver, it always
works.
"Paul Shields" <shields_at_passport.ca> wrote in message
news:68e612a4.0210241512.3015b81a_at_posting.google.com...
> I am trying to get tomcat to talk to an Oracle 9.2 with oci driver and
> database connection pooling and the JSTL (standard taglibs 1.0.1),
> and I am getting a message
> java.sql.SQLException: closed connection
> when I hit it from a JSP page.
>
>
> Notes:
> It works if I use the thin driver, but I have reason to want
> to use the oci driver;
> I've successfully tested database connection pooling with Postgres;
> Tomcat has had LD_LIBRARY_PATH=/home/oracle/lib added to its
> startup,
> and ~tomcat/common/lib has the ojdbc14.jar file in it.
> Base system is a Mandrake 9.0 linux (2.4.19-16mdkenterprise)
>
> I have scanned the tomcat 4.1 docs...
>
http://jakarta.apache.org/tomcat/tomcat-4.1-doc/jndi-datasource-examples-how
to.html
>
> I am out of ideas. Any help would be appreciated.
>
> thanks in advance,
> Paul Shields
>
> shields at passport.ca
>
>
> == Oracle database is operational; listener is active on port 1521;
>
> $ ORACLE_SID=orcl sqlplus scott/tiger
>
> SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 24 18:23:13 2002
>
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
> Connected to:
> Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
> With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.1.0 - Production
>
> SQL> select * from paul;
>
> PAUL_ID PAUL_NAME
> ------- --------------------------------------------
> 1 paul
> 2 shields
>
> SQL> exit;
> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 -
> Production
> With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.1.0 - Production
>
>
> == server.xml contains ==
>
> <Resource name="jdbc/orcl" auth="Container" scope="Shareable"
> type="javax.sql.DataSource"/>
> <ResourceParams name="jdbc/orcl">
> <parameter>
> <name>url</name>
> <value>jdbc:oracle:oci8:@localhost:1521:orcl</value>
> </parameter>
> <parameter>
> <name>validationQuery</name>
> <value>select sysdate from dual</value>
> </parameter>
> <parameter>
> <name>maxIdle</name>
> <value>2</value>
> </parameter>
> <parameter>
> <name>maxActive</name>
> <value>4</value>
> </parameter>
> <parameter>
> <name>driverClassName</name>
> <value>oracle.jdbc.driver.OracleDriver</value>
> </parameter>
> <parameter>
> <name>maxWait</name>
> <value>5000</value>
> </parameter>
> <parameter>
> <name>username</name>
> <value>scott</value>
> </parameter>
> <parameter>
> <name>factory</name>
> <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
> </parameter>
> <parameter>
> <name>password</name>
> <value>tiger</value>
> </parameter>
> </ResourceParams>
>
> == /home/tomcat/webapps/wpstest/jstlOracleSample.jsp contains...
>
>
> <%@ taglib uri="http://java.sun.com/jstl/core" prefix="c" %>
> <%@ taglib uri="http://java.sun.com/jstl/sql" prefix="sql" %>
>
> <html>
> <head>
> <title>JSP JSTL Oracle DB Sample</title>
> </head>
> <body bgcolor="white">
>
> <sql:query var="result" dataSource="jdbc/orcl">
> select * from paul
> </sql:query>
>
> <c:forEach items="${result.rows}" var="row">
> <c:out value="${row.paul_id}"/>
> <c:out value="${row.paul_name}"/><BR>
> </c:forEach>
>
> </body>
> </html>
>
>
> == Searching thru Oracle documentation at
> http://tahiti.oracle.com/pls/db92/db92.homepage
> I found the following...
>
> Oracle9i Database Search Results: 'closed connection' in
> Troubleshooting Topics
>
> java.sql.SQLException: closed connection
>
> SQL State: 08000
>
> Cause: An attempt was made to close a connection context object
> whose
> underlying JDBC connection object was already closed.
>
> Action: Ensure that the underlying JDBC connection was not
> inadvertently closed. Also, if several SQLJ connection contexts
> share
> the same underlying JDBC connection, you have to ensure that when
> you close the connection context objects all but the last close()
> invokes the method close(ConnectionContext.KEEP_CONNECTION). This
> ensures that the underlying JDBC connection remains open for the
> duration and all associated JDBC resources -such as JDBC statement
> objects- can be properly released.
>
>
>
>
>
> == web server output follows ==
>
>
> <html><head><title>Apache Tomcat/4.1.12 - Error report</title></head>
> <body><h1>HTTP Status 500 - </h1><hr size="1" noshade><p><b>type</b>
> Exception report</p><p><b>message</b> <u></u></p><p><b>description</b>
> <u>The server encountered an internal error () that prevented it from
> fulfilling this request.</u></p><p><b>exception</b>
> <pre>org.apache.jasper.JasperException: Unable to get connection,
> DataSource invalid: "java.sql.SQLException: Closed Connection"
> at
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:2
48)
> at
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:289)
> at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:240)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
> at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Application
FilterChain.java:247)
> at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterCh
ain.java:193)
> at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.ja
va:260)
> at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
eNext(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.ja
va:191)
> at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
eNext(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:2396)
> at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180
)
> at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
eNext(StandardPipeline.java:643)
> at
org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.
java:170)
> at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
eNext(StandardPipeline.java:641)
> at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172
)
> at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
eNext(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.java
:174)
> at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
eNext(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:405)
> at
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConne
ction(Http11Protocol.java:380)
> at
org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:508)
> at
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.jav
a:533)
> at java.lang.Thread.run(Thread.java:536)
> </pre></p><p><b>root cause</b> <pre>javax.servlet.ServletException:
> Unable to get connection, DataSource invalid: "java.sql.SQLException:
> Closed Connection"
> at
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImp
l.java:494)
> at
org.apache.jsp.jstlOracleSample_jsp._jspService(jstlOracleSample_jsp.java:10
8)
> at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:136)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
> at
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:2
04)
> at
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:289)
> at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:240)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
> at
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Application
FilterChain.java:247)
> at
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterCh
ain.java:193)
> at
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.ja
va:260)
> at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
eNext(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.ja
va:191)
> at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
eNext(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:2396)
> at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180
)
> at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
eNext(StandardPipeline.java:643)
> at
org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.
java:170)
> at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
eNext(StandardPipeline.java:641)
> at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172
)
> at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
eNext(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.java
:174)
> at
org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invok
eNext(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:405)
> at
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConne
ction(Http11Protocol.java:380)
> at
org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:508)
> at
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.jav
a:533)
> at java.lang.Thread.run(Thread.java:536)
>
> </pre></p><hr size="1" noshade><h3>Apache
> Tomcat/4.1.12</h3></body></html>
Received on Thu Oct 24 2002 - 20:06:00 CDT
