You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@commons.apache.org by Sergey Karpov <ks...@smtp.ru> on 2004/11/25 16:45:09 UTC
[dbcp] Call Oracle stored procedure via DBCP?
Hello!
Is Tomcat + DPCP + JNDI and Oracle + stored procedure...
When from the application on Tomcat the call of stored procedure is carried out through "direct" connection with Oracle - all normally... And results in ResultSet come back...
However when "direct" connection is replaced with the connection received from pool connection (DBCP) there is a mistake.
Thus all elementary searches (such as select...) through DBCP to base work normally...
For connection with base in all cases it is used JDBC thin driver.
What could it be?
KSV
Re: [dbcp] Call Oracle stored procedure via DBCP?
Posted by Sergey Karpov <ks...@smtp.ru>.
I shall try to describe more precisely a situation with call Oracle stored
procedure through DBCP.
During a call of stored procedure through DBCP there is a mistake of
reduction of type:
java.lang.ClassCastException
server.xml
<?xml version="1.0" encoding="UTF-8"?>
<Server debug="0" port="8081" shutdown="SHUTDOWN">
<GlobalNamingResources>
<Resource name="jdbc/IcIDB" auth="Container"
type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/IcIDB">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>5000</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>10</value>
</parameter>
<parameter>
<name>username</name>
<value>IcIDB</value>
</parameter>
<parameter>
<name>password</name>
<value>IcIDB</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:oracle:thin:@10.1.33.99:1521:ORATEST0</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>oracle.jdbc.OracleDriver</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>2</value>
</parameter>
</ResourceParams>
</GlobalNamingResources>
<Service name="Tomcat-Standalone">
<Connector acceptCount="10"
className="org.apache.catalina.connector.http.HttpConnector"
connectionTimeout="60000" debug="9" maxProcessors="75" minProcessors="5"
port="8080"/>
<Engine debug="9" defaultHost="localhost" name="Standalone">
<Host
appBase="D:\Barkov_PT\UVDGlobDB\Projects\LoginIcIDB\Tomcat\webapps"
debug="9" name="localhost" unpackWARs="false">
<Context debug="9"
docBase="D:\Barkov_PT\UVDGlobDB\Projects\LoginIcIDB\LoginIcIDB"
path="/LoginIcIDB" reloadable="false"
workDir="D:\Barkov_PT\UVDGlobDB\Projects\LoginIcIDB\Tomcat\work\LoginIcIDB">
<ResourceLink name="IcIDBLnk" global="jdbc/IcIDB"
type="javax.sql.DataSource"/>
</Context>
</Host>
</Engine>
</Service>
</Server>
web.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application
2.3//EN" "http://java.sun.com/dtd/web-app_2_3.dtd">
<web-app>
<display-name>LoginIcIDB</display-name>
<servlet>
<servlet-name>action</servlet-name>
<servlet-class>org.apache.struts.action.ActionServlet</servlet-class>
<init-param>
<param-name>config</param-name>
<param-value>/WEB-INF/struts-config.xml</param-value>
</init-param>
<init-param>
<param-name>debug</param-name>
<param-value>2</param-value>
</init-param>
<init-param>
<param-name>application</param-name>
<param-value>ApplicationResources</param-value>
</init-param>
<load-on-startup>2</load-on-startup>
</servlet>
<servlet>
<servlet-name>debugjsp</servlet-name>
<description>Added to compile JSPs with debug info</description>
<servlet-class>org.apache.jasper.servlet.JspServlet</servlet-class>
<init-param>
<param-name>classdebuginfo</param-name>
<param-value>true</param-value>
</init-param>
<load-on-startup>3</load-on-startup>
</servlet>
<servlet-mapping>
<servlet-name>action</servlet-name>
<url-pattern>*.do</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>debugjsp</servlet-name>
<url-pattern>*.jsp</url-pattern>
</servlet-mapping>
<taglib>
<taglib-uri>/WEB-INF/struts-bean.tld</taglib-uri>
<taglib-location>/WEB-INF/struts-bean.tld</taglib-location>
</taglib>
<taglib>
<taglib-uri>/WEB-INF/struts-html.tld</taglib-uri>
<taglib-location>/WEB-INF/struts-html.tld</taglib-location>
</taglib>
<taglib>
<taglib-uri>/WEB-INF/struts-logic.tld</taglib-uri>
<taglib-location>/WEB-INF/struts-logic.tld</taglib-location>
</taglib>
<taglib>
<taglib-uri>/WEB-INF/struts-template.tld</taglib-uri>
<taglib-location>/WEB-INF/struts-template.tld</taglib-location>
</taglib>
<taglib>
<taglib-uri>/WEB-INF/struts-tiles.tld</taglib-uri>
<taglib-location>/WEB-INF/struts-tiles.tld</taglib-location>
</taglib>
<taglib>
<taglib-uri>/WEB-INF/struts-nested.tld</taglib-uri>
<taglib-location>/WEB-INF/struts-nested.tld</taglib-location>
</taglib>
<taglib>
<taglib-uri>http://java.sun.com/jstl/core</taglib-uri>
<taglib-location>/WEB-INF/c.tld</taglib-location>
</taglib>
<taglib>
<taglib-uri>http://java.sun.com/jstl/core_rt</taglib-uri>
<taglib-location>/WEB-INF/c-rt.tld</taglib-location>
</taglib>
<taglib>
<taglib-uri>http://java.sun.com/jstl/xml</taglib-uri>
<taglib-location>/WEB-INF/x.tld</taglib-location>
</taglib>
<taglib>
<taglib-uri>http://java.sun.com/jstl/xml_rt</taglib-uri>
<taglib-location>/WEB-INF/x-rt.tld</taglib-location>
</taglib>
<taglib>
<taglib-uri>http://java.sun.com/jstl/fmt</taglib-uri>
<taglib-location>/WEB-INF/fmt.tld</taglib-location>
</taglib>
<taglib>
<taglib-uri>http://java.sun.com/jstl/fmt_rt</taglib-uri>
<taglib-location>/WEB-INF/fmt-rt.tld</taglib-location>
</taglib>
<taglib>
<taglib-uri>http://java.sun.com/jstl/sql</taglib-uri>
<taglib-location>/WEB-INF/sql.tld</taglib-location>
</taglib>
<taglib>
<taglib-uri>http://java.sun.com/jstl/sql_rt</taglib-uri>
<taglib-location>/WEB-INF/sql-rt.tld</taglib-location>
</taglib>
<resource-ref>
<res-ref-name>IcIDBLnk</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
Class of returning of connection from a pool.
public class OraConnection {
/ **
* The empty designer of a class
*/
public OraConnection () {
}
/ **
* M???? returning the established connection
* @param dataSourceName String - the name of resource JNDI (as a rule
links to a global resource in a context of the application)
* @param attemptCnt int - ???-in attempts to establish connection
* @param timeDelay long - time between attempts to establish connection
* @return Connection - returned connection
*/
public Connection getConnection (String dataSourceName, int attemptCnt,
long timeDelay) {
Connection con=null;
try {
// Initialization of a context and search of a context of resources:
components and an environment
Context init = new InitialContext ();
Context ctx = (Context) init.lookup ("java:comp/env");
// Return of parameters of a data set by results of search of a
resource with a name dataSourceName
DataSource dataSource = (DataSource) ctx.lookup (dataSourceName);
int curAttemptNum=0;
// Attempts to open connection
while (con == null ** curAttemptNum <attemptCnt) {
try {
con = (Connection) dataSource.getConnection ();
}
catch (Exception e) {
// Processing exception on installation of connection a delay
before the following attempt
con=null;
curAttemptNum ++;
System.out.println (" Attempt to open the connection is failed.
");
Thread.sleep (timeDelay);
}
}
}
catch (Exception ex) {
// Processing exception at job with JNDI
System.out.println (" Cannot retrieve java:comp/env "
+dataSourceName);
}
return con;
}
}
Method of a call of stored procedure.
Connection is passed in a method, and established with the help of class
OraConnection and his method getConnection
public ResultSet getAuthentification (Connection con,
String userName,
String userPassword) {
OracleResultSet rset = null;
try {
//!!!!!!!!!!!!!!!!!!!!!!!!!! The mistake of reduction of type arises
here!!!!!!!!!!!!!!!!!!!!!!!!!!
OracleCallableStatement cs = (OracleCallableStatement)
con.prepareCall (" {? = call jstpLoginicidb.getAuthentification
(??)} ");
cs.registerOutParameter (1, OracleTypes. CURSOR);
cs.setString (2, userName);
cs.setString (3, userPassword);
cs.execute ();
rset = (OracleResultSet) ((OracleCallableStatement) cs) .getCursor
(1);
}
catch (Exception e) {
System.out.println (
" IC: Runtime error: calling getAuthentification (params...) " +
e);
}
return rset;
}
----- Original Message -----
From: "Craig McClanahan" <cr...@gmail.com>
To: "Jakarta Commons Users List" <co...@jakarta.apache.org>
Sent: Thursday, November 25, 2004 9:44 PM
Subject: Re: [dbcp] Call Oracle stored procedure via DBCP?
> On Thu, 25 Nov 2004 18:45:09 +0300, Sergey Karpov <ks...@smtp.ru> wrote:
> > Hello!
> >
> > Is Tomcat + DPCP + JNDI and Oracle + stored procedure...
> >
> > When from the application on Tomcat the call of stored procedure is
carried out through "direct" connection with Oracle - all normally... And
results in ResultSet come back...
> >
> > However when "direct" connection is replaced with the connection
received from pool connection (DBCP) there is a mistake.
> >
> > Thus all elementary searches (such as select...) through DBCP to base
work normally...
> >
> > For connection with base in all cases it is used JDBC thin driver.
> >
> > What could it be?
>
> It could be one of many millions of possible mistakes. Without
> details about what you are doing (such as sample code and an exception
> message and stack trace when you try to do someting), it is impossible
> for anyone to help you.
>
> There is nothing in principle about using DBCP and Tomcat's JNDI
> support that should make this not work, *unless* you are trying to
> cast the Connection object (or some other JDBC object) to an
> Oracle-specifc class. You should not need to do that sort of thing --
> but if it is required there mght be some workarounds. I would ask
> that kind of question on the Tomcat User mailing list (to subscribe,
> send an empty message to <to...@jakarta.apache.org>)
> since it will be specific to this environment.
>
> Craig
>
>
> >
> > KSV
> >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-user-help@jakarta.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
Re: [dbcp] Call Oracle stored procedure via DBCP?
Posted by Sergey Karpov <ks...@smtp.ru>.
I shall try to describe more precisely a situation with call Oracle stored
procedure through DBCP.
During a call of stored procedure through DBCP there is a mistake of
reduction of type:
java.lang.ClassCastException
server.xml
<?xml version="1.0" encoding="UTF-8"?>
<Server debug="0" port="8081" shutdown="SHUTDOWN">
<GlobalNamingResources>
<Resource name="jdbc/IcIDB" auth="Container"
type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/IcIDB">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>5000</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>10</value>
</parameter>
<parameter>
<name>username</name>
<value>IcIDB</value>
</parameter>
<parameter>
<name>password</name>
<value>IcIDB</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:oracle:thin:@10.1.33.99:1521:ORATEST0</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>oracle.jdbc.OracleDriver</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>2</value>
</parameter>
</ResourceParams>
</GlobalNamingResources>
<Service name="Tomcat-Standalone">
<Connector acceptCount="10"
className="org.apache.catalina.connector.http.HttpConnector"
connectionTimeout="60000" debug="9" maxProcessors="75" minProcessors="5"
port="8080"/>
<Engine debug="9" defaultHost="localhost" name="Standalone">
<Host
appBase="D:\Barkov_PT\UVDGlobDB\Projects\LoginIcIDB\Tomcat\webapps"
debug="9" name="localhost" unpackWARs="false">
<Context debug="9"
docBase="D:\Barkov_PT\UVDGlobDB\Projects\LoginIcIDB\LoginIcIDB"
path="/LoginIcIDB" reloadable="false"
workDir="D:\Barkov_PT\UVDGlobDB\Projects\LoginIcIDB\Tomcat\work\LoginIcIDB">
<ResourceLink name="IcIDBLnk" global="jdbc/IcIDB"
type="javax.sql.DataSource"/>
</Context>
</Host>
</Engine>
</Service>
</Server>
web.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application
2.3//EN" "http://java.sun.com/dtd/web-app_2_3.dtd">
<web-app>
<display-name>LoginIcIDB</display-name>
<servlet>
<servlet-name>action</servlet-name>
<servlet-class>org.apache.struts.action.ActionServlet</servlet-class>
<init-param>
<param-name>config</param-name>
<param-value>/WEB-INF/struts-config.xml</param-value>
</init-param>
<init-param>
<param-name>debug</param-name>
<param-value>2</param-value>
</init-param>
<init-param>
<param-name>application</param-name>
<param-value>ApplicationResources</param-value>
</init-param>
<load-on-startup>2</load-on-startup>
</servlet>
<servlet>
<servlet-name>debugjsp</servlet-name>
<description>Added to compile JSPs with debug info</description>
<servlet-class>org.apache.jasper.servlet.JspServlet</servlet-class>
<init-param>
<param-name>classdebuginfo</param-name>
<param-value>true</param-value>
</init-param>
<load-on-startup>3</load-on-startup>
</servlet>
<servlet-mapping>
<servlet-name>action</servlet-name>
<url-pattern>*.do</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>debugjsp</servlet-name>
<url-pattern>*.jsp</url-pattern>
</servlet-mapping>
<taglib>
<taglib-uri>/WEB-INF/struts-bean.tld</taglib-uri>
<taglib-location>/WEB-INF/struts-bean.tld</taglib-location>
</taglib>
<taglib>
<taglib-uri>/WEB-INF/struts-html.tld</taglib-uri>
<taglib-location>/WEB-INF/struts-html.tld</taglib-location>
</taglib>
<taglib>
<taglib-uri>/WEB-INF/struts-logic.tld</taglib-uri>
<taglib-location>/WEB-INF/struts-logic.tld</taglib-location>
</taglib>
<taglib>
<taglib-uri>/WEB-INF/struts-template.tld</taglib-uri>
<taglib-location>/WEB-INF/struts-template.tld</taglib-location>
</taglib>
<taglib>
<taglib-uri>/WEB-INF/struts-tiles.tld</taglib-uri>
<taglib-location>/WEB-INF/struts-tiles.tld</taglib-location>
</taglib>
<taglib>
<taglib-uri>/WEB-INF/struts-nested.tld</taglib-uri>
<taglib-location>/WEB-INF/struts-nested.tld</taglib-location>
</taglib>
<taglib>
<taglib-uri>http://java.sun.com/jstl/core</taglib-uri>
<taglib-location>/WEB-INF/c.tld</taglib-location>
</taglib>
<taglib>
<taglib-uri>http://java.sun.com/jstl/core_rt</taglib-uri>
<taglib-location>/WEB-INF/c-rt.tld</taglib-location>
</taglib>
<taglib>
<taglib-uri>http://java.sun.com/jstl/xml</taglib-uri>
<taglib-location>/WEB-INF/x.tld</taglib-location>
</taglib>
<taglib>
<taglib-uri>http://java.sun.com/jstl/xml_rt</taglib-uri>
<taglib-location>/WEB-INF/x-rt.tld</taglib-location>
</taglib>
<taglib>
<taglib-uri>http://java.sun.com/jstl/fmt</taglib-uri>
<taglib-location>/WEB-INF/fmt.tld</taglib-location>
</taglib>
<taglib>
<taglib-uri>http://java.sun.com/jstl/fmt_rt</taglib-uri>
<taglib-location>/WEB-INF/fmt-rt.tld</taglib-location>
</taglib>
<taglib>
<taglib-uri>http://java.sun.com/jstl/sql</taglib-uri>
<taglib-location>/WEB-INF/sql.tld</taglib-location>
</taglib>
<taglib>
<taglib-uri>http://java.sun.com/jstl/sql_rt</taglib-uri>
<taglib-location>/WEB-INF/sql-rt.tld</taglib-location>
</taglib>
<resource-ref>
<res-ref-name>IcIDBLnk</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
Class of returning of connection from a pool.
public class OraConnection {
/ **
* The empty designer of a class
*/
public OraConnection () {
}
/ **
* Mетод returning the established connection
* @param dataSourceName String - the name of resource JNDI (as a rule
links to a global resource in a context of the application)
* @param attemptCnt int - кол-in attempts to establish connection
* @param timeDelay long - time between attempts to establish connection
* @return Connection - returned connection
*/
public Connection getConnection (String dataSourceName, int attemptCnt,
long timeDelay) {
Connection con=null;
try {
// Initialization of a context and search of a context of resources:
components and an environment
Context init = new InitialContext ();
Context ctx = (Context) init.lookup ("java:comp/env");
// Return of parameters of a data set by results of search of a
resource with a name dataSourceName
DataSource dataSource = (DataSource) ctx.lookup (dataSourceName);
int curAttemptNum=0;
// Attempts to open connection
while (con == null ** curAttemptNum <attemptCnt) {
try {
con = (Connection) dataSource.getConnection ();
}
catch (Exception e) {
// Processing exception on installation of connection a delay
before the following attempt
con=null;
curAttemptNum ++;
System.out.println (" Attempt to open the connection is failed.
");
Thread.sleep (timeDelay);
}
}
}
catch (Exception ex) {
// Processing exception at job with JNDI
System.out.println (" Cannot retrieve java:comp/env "
+dataSourceName);
}
return con;
}
}
Method of a call of stored procedure.
Connection is passed in a method, and established with the help of class
OraConnection and his method getConnection
public ResultSet getAuthentification (Connection con,
String userName,
String userPassword) {
OracleResultSet rset = null;
try {
//!!!!!!!!!!!!!!!!!!!!!!!!!! The mistake of reduction of type arises
here!!!!!!!!!!!!!!!!!!!!!!!!!!
OracleCallableStatement cs = (OracleCallableStatement)
con.prepareCall (" {? = call jstpLoginicidb.getAuthentification
(??)} ");
cs.registerOutParameter (1, OracleTypes. CURSOR);
cs.setString (2, userName);
cs.setString (3, userPassword);
cs.execute ();
rset = (OracleResultSet) ((OracleCallableStatement) cs) .getCursor
(1);
}
catch (Exception e) {
System.out.println (
" IC: Runtime error: calling getAuthentification (params...) " +
e);
}
return rset;
}
----- Original Message -----
From: "Craig McClanahan" <cr...@gmail.com>
To: "Jakarta Commons Users List" <co...@jakarta.apache.org>
Sent: Thursday, November 25, 2004 9:44 PM
Subject: Re: [dbcp] Call Oracle stored procedure via DBCP?
> On Thu, 25 Nov 2004 18:45:09 +0300, Sergey Karpov <ks...@smtp.ru> wrote:
> > Hello!
> >
> > Is Tomcat + DPCP + JNDI and Oracle + stored procedure...
> >
> > When from the application on Tomcat the call of stored procedure is
carried out through "direct" connection with Oracle - all normally... And
results in ResultSet come back...
> >
> > However when "direct" connection is replaced with the connection
received from pool connection (DBCP) there is a mistake.
> >
> > Thus all elementary searches (such as select...) through DBCP to base
work normally...
> >
> > For connection with base in all cases it is used JDBC thin driver.
> >
> > What could it be?
>
> It could be one of many millions of possible mistakes. Without
> details about what you are doing (such as sample code and an exception
> message and stack trace when you try to do someting), it is impossible
> for anyone to help you.
>
> There is nothing in principle about using DBCP and Tomcat's JNDI
> support that should make this not work, *unless* you are trying to
> cast the Connection object (or some other JDBC object) to an
> Oracle-specifc class. You should not need to do that sort of thing --
> but if it is required there mght be some workarounds. I would ask
> that kind of question on the Tomcat User mailing list (to subscribe,
> send an empty message to <to...@jakarta.apache.org>)
> since it will be specific to this environment.
>
> Craig
>
>
> >
> > KSV
> >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-user-help@jakarta.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
Re: [dbcp] Call Oracle stored procedure via DBCP?
Posted by Craig McClanahan <cr...@gmail.com>.
On Thu, 25 Nov 2004 18:45:09 +0300, Sergey Karpov <ks...@smtp.ru> wrote:
> Hello!
>
> Is Tomcat + DPCP + JNDI and Oracle + stored procedure...
>
> When from the application on Tomcat the call of stored procedure is carried out through "direct" connection with Oracle - all normally... And results in ResultSet come back...
>
> However when "direct" connection is replaced with the connection received from pool connection (DBCP) there is a mistake.
>
> Thus all elementary searches (such as select...) through DBCP to base work normally...
>
> For connection with base in all cases it is used JDBC thin driver.
>
> What could it be?
It could be one of many millions of possible mistakes. Without
details about what you are doing (such as sample code and an exception
message and stack trace when you try to do someting), it is impossible
for anyone to help you.
There is nothing in principle about using DBCP and Tomcat's JNDI
support that should make this not work, *unless* you are trying to
cast the Connection object (or some other JDBC object) to an
Oracle-specifc class. You should not need to do that sort of thing --
but if it is required there mght be some workarounds. I would ask
that kind of question on the Tomcat User mailing list (to subscribe,
send an empty message to <to...@jakarta.apache.org>)
since it will be specific to this environment.
Craig
>
> KSV
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org