You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@commons.apache.org by Je...@cox.com on 2007/12/07 21:37:44 UTC
Tomcat DBCP with Oracle JDBC OCI 10g driver
To Whom It May Concern,
Does anyone have a checklist for implementing Oracle JDBC OCI 10g driver
in the Tomcat server DBCP connection pool. It seems very simple
according to directions from Oracle but I must be missing one piece.
I have confirmed the external setup with tnsnames.ora and the oracle
client installation. I can connect with SQL Navigator and with a simple
java program that uses the oci driver but does not use the connection
pool. When I try to use this same driver and the connection pool it
crashes the JVM.
1. install oracle client -- done and verified
2. verify ORACLE_HOME/bin is in the PATH -- done and verified
3. set TNS_NAMES correctly -- done and verified
4. use the Oracle 10g driver ( ojdbc14.jar ver. 10.2.0.3 ) -- done,
downloaded and verified with thin client
5. configure the connection pool ( see below ) -- I think this is
correct, work with thin, not with oci
1. correct driver, type, and factory ( from examples )
The simple program uses the OracleDataSource to get the connection.
What does the DBCP do different than my simple program to get a physical
connection to the database?
Any suggestions are greatly appreciated.
Jeff
TNS_ADMAIN: is set to the directory with tnsnames.ora lives
TNSNAMES.ORA:
MYDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.62.37.78)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = SAMPF)
)
)
THIS WORKS:
<Resource name="jdbc/samp/hsd"
auth="Container"
type="oracle.jdbc.pool.OracleDataSource"
driverClassName="oracle.jdbc.driver.OracleDriver"
factory="oracle.jdbc.pool.OracleDataSourceFactory"
url="jdbc:oracle:thin:@MYDB"
user="scott"
password="tiger"
maxActive="20"
maxIdle="10"
maxWait="-1" />
THIS CRASHES THE JVM:
<Resource name="jdbc/samp/hsd"
auth="Container"
type="oracle.jdbc.pool.OracleDataSource"
driverClassName="oracle.jdbc.driver.OracleDriver"
factory="oracle.jdbc.pool.OracleDataSourceFactory"
url="jdbc:oracle:oci:@MYDB"
user="scott"
password="tiger"
maxActive="20"
maxIdle="10"
maxWait="-1" />
Simple Java Program
package com.cox.pap.paws.test;
/*
* This sample can be used to check the JDBC installation.
* Just run it and provide the connect information. It will select
* "Hello World" from the database.
*/
// You need to import the java.sql and JDBC packages to use JDBC
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import oracle.jdbc.pool.OracleDataSource;
public class DBConnectionTest
{
public static void main(String args[])
throws SQLException, IOException
{
// Prompt the user for connect information
String user = "scott";
String password = "tiger";
String database = "MYDB";
String url = "jdbc:oracle:oci:@" + database;
String sql = "select 'Hello World' from dual";
System.out.println("url = " + url);
System.out.println("sql = " + sql);
System.out.flush();
System.out.print("Connecting...");
// Open an OracleDataSource and get a connection
OracleDataSource ods = new OracleDataSource();
ods.setURL(url);
ods.setUser(user);
ods.setPassword(password);
Connection conn = ods.getConnection();
System.out.println("Connected.");
// Create a statement
Statement stmt = conn.createStatement();
// Do the SQL "Hello World" thing
ResultSet rset = stmt.executeQuery(sql);
System.out.print("Results from query >> ");
while (rset.next())
System.out.println(rset.getString(1));
// close the result set, the statement and connect
rset.close();
stmt.close();
conn.close();
System.out.println("Your JDBC installation is correct.");
}
}
Output
url = jdbc:oracle:oci:@MYDB
sql = select 'Hello World' from dual
Connecting...Connected.
Results from query >> Hello World
Your JDBC installation is correct.
RE: Tomcat DBCP with Oracle JDBC OCI 10g driver
Posted by Je...@cox.com.
To All,
Found it. Apparently you have to use the classes12.jar and the
ojdbc14.jar files provided with the Oracle client installation or it
crashes the JVM trying to talk to the OraClient10.dll with the wrong
number/type of parameters. After I added those 2 jars first in my
classpath everything works. I had to add them to the
TOMCAT_HOME/bin/setclasspath.bat file for them to be seen properly.
Thanks,
Jeff
-----Original Message-----
From: Henri Yandell [mailto:flamefew@gmail.com]
Sent: Friday, December 07, 2007 11:19 PM
To: Jakarta Commons Developers List
Subject: Re: Tomcat DBCP with Oracle JDBC OCI 10g driver
When you say it crashes the JVM; how is it crashing it?
Hen
On Dec 7, 2007 12:37 PM, <Je...@cox.com> wrote:
> To Whom It May Concern,
>
> Does anyone have a checklist for implementing Oracle JDBC OCI 10g
driver
> in the Tomcat server DBCP connection pool. It seems very simple
> according to directions from Oracle but I must be missing one piece.
> I have confirmed the external setup with tnsnames.ora and the oracle
> client installation. I can connect with SQL Navigator and with a
simple
> java program that uses the oci driver but does not use the connection
> pool. When I try to use this same driver and the connection pool it
> crashes the JVM.
>
> 1. install oracle client -- done and verified
> 2. verify ORACLE_HOME/bin is in the PATH -- done and verified
> 3. set TNS_NAMES correctly -- done and verified
> 4. use the Oracle 10g driver ( ojdbc14.jar ver. 10.2.0.3 ) --
done,
> downloaded and verified with thin client
> 5. configure the connection pool ( see below ) -- I think this is
> correct, work with thin, not with oci
>
> 1. correct driver, type, and factory ( from examples )
>
> The simple program uses the OracleDataSource to get the connection.
> What does the DBCP do different than my simple program to get a
physical
> connection to the database?
>
> Any suggestions are greatly appreciated.
>
> Jeff
>
>
> TNS_ADMAIN: is set to the directory with tnsnames.ora lives
>
> TNSNAMES.ORA:
>
> MYDB =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = 10.62.37.78)(PORT = 1521))
> )
> (CONNECT_DATA =
> (SERVICE_NAME = SAMPF)
> )
> )
>
> THIS WORKS:
>
> <Resource name="jdbc/samp/hsd"
> auth="Container"
> type="oracle.jdbc.pool.OracleDataSource"
> driverClassName="oracle.jdbc.driver.OracleDriver"
> factory="oracle.jdbc.pool.OracleDataSourceFactory"
> url="jdbc:oracle:thin:@MYDB"
> user="scott"
> password="tiger"
> maxActive="20"
> maxIdle="10"
> maxWait="-1" />
>
> THIS CRASHES THE JVM:
>
> <Resource name="jdbc/samp/hsd"
> auth="Container"
> type="oracle.jdbc.pool.OracleDataSource"
> driverClassName="oracle.jdbc.driver.OracleDriver"
> factory="oracle.jdbc.pool.OracleDataSourceFactory"
> url="jdbc:oracle:oci:@MYDB"
> user="scott"
> password="tiger"
> maxActive="20"
> maxIdle="10"
> maxWait="-1" />
>
> Simple Java Program
>
> package com.cox.pap.paws.test;
>
> /*
> * This sample can be used to check the JDBC installation.
> * Just run it and provide the connect information. It will select
> * "Hello World" from the database.
> */
>
> // You need to import the java.sql and JDBC packages to use JDBC
> import java.io.IOException;
> import java.sql.Connection;
> import java.sql.ResultSet;
> import java.sql.SQLException;
> import java.sql.Statement;
>
> import oracle.jdbc.pool.OracleDataSource;
>
> public class DBConnectionTest
> {
> public static void main(String args[])
> throws SQLException, IOException
> {
>
> // Prompt the user for connect information
> String user = "scott";
> String password = "tiger";
> String database = "MYDB";
> String url = "jdbc:oracle:oci:@" + database;
> String sql = "select 'Hello World' from dual";
>
> System.out.println("url = " + url);
> System.out.println("sql = " + sql);
> System.out.flush();
>
> System.out.print("Connecting...");
> // Open an OracleDataSource and get a connection
> OracleDataSource ods = new OracleDataSource();
> ods.setURL(url);
> ods.setUser(user);
> ods.setPassword(password);
> Connection conn = ods.getConnection();
> System.out.println("Connected.");
>
> // Create a statement
> Statement stmt = conn.createStatement();
>
> // Do the SQL "Hello World" thing
> ResultSet rset = stmt.executeQuery(sql);
>
> System.out.print("Results from query >> ");
> while (rset.next())
> System.out.println(rset.getString(1));
> // close the result set, the statement and connect
> rset.close();
> stmt.close();
> conn.close();
> System.out.println("Your JDBC installation is correct.");
> }
>
> }
>
> Output
>
> url = jdbc:oracle:oci:@MYDB
> sql = select 'Hello World' from dual
> Connecting...Connected.
> Results from query >> Hello World
> Your JDBC installation is correct.
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@commons.apache.org
For additional commands, e-mail: dev-help@commons.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@commons.apache.org
For additional commands, e-mail: dev-help@commons.apache.org
RE: Tomcat DBCP with Oracle JDBC OCI 10g driver
Posted by Je...@cox.com.
Core dump -- I have included the dump file.
Thanks,
Jeff
-----Original Message-----
From: Henri Yandell [mailto:flamefew@gmail.com]
Sent: Friday, December 07, 2007 11:19 PM
To: Jakarta Commons Developers List
Subject: Re: Tomcat DBCP with Oracle JDBC OCI 10g driver
When you say it crashes the JVM; how is it crashing it?
Hen
On Dec 7, 2007 12:37 PM, <Je...@cox.com> wrote:
> To Whom It May Concern,
>
> Does anyone have a checklist for implementing Oracle JDBC OCI 10g
driver
> in the Tomcat server DBCP connection pool. It seems very simple
> according to directions from Oracle but I must be missing one piece.
> I have confirmed the external setup with tnsnames.ora and the oracle
> client installation. I can connect with SQL Navigator and with a
simple
> java program that uses the oci driver but does not use the connection
> pool. When I try to use this same driver and the connection pool it
> crashes the JVM.
>
> 1. install oracle client -- done and verified
> 2. verify ORACLE_HOME/bin is in the PATH -- done and verified
> 3. set TNS_NAMES correctly -- done and verified
> 4. use the Oracle 10g driver ( ojdbc14.jar ver. 10.2.0.3 ) --
done,
> downloaded and verified with thin client
> 5. configure the connection pool ( see below ) -- I think this is
> correct, work with thin, not with oci
>
> 1. correct driver, type, and factory ( from examples )
>
> The simple program uses the OracleDataSource to get the connection.
> What does the DBCP do different than my simple program to get a
physical
> connection to the database?
>
> Any suggestions are greatly appreciated.
>
> Jeff
>
>
> TNS_ADMAIN: is set to the directory with tnsnames.ora lives
>
> TNSNAMES.ORA:
>
> MYDB =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = 10.62.37.78)(PORT = 1521))
> )
> (CONNECT_DATA =
> (SERVICE_NAME = SAMPF)
> )
> )
>
> THIS WORKS:
>
> <Resource name="jdbc/samp/hsd"
> auth="Container"
> type="oracle.jdbc.pool.OracleDataSource"
> driverClassName="oracle.jdbc.driver.OracleDriver"
> factory="oracle.jdbc.pool.OracleDataSourceFactory"
> url="jdbc:oracle:thin:@MYDB"
> user="scott"
> password="tiger"
> maxActive="20"
> maxIdle="10"
> maxWait="-1" />
>
> THIS CRASHES THE JVM:
>
> <Resource name="jdbc/samp/hsd"
> auth="Container"
> type="oracle.jdbc.pool.OracleDataSource"
> driverClassName="oracle.jdbc.driver.OracleDriver"
> factory="oracle.jdbc.pool.OracleDataSourceFactory"
> url="jdbc:oracle:oci:@MYDB"
> user="scott"
> password="tiger"
> maxActive="20"
> maxIdle="10"
> maxWait="-1" />
>
> Simple Java Program
>
> package com.cox.pap.paws.test;
>
> /*
> * This sample can be used to check the JDBC installation.
> * Just run it and provide the connect information. It will select
> * "Hello World" from the database.
> */
>
> // You need to import the java.sql and JDBC packages to use JDBC
> import java.io.IOException;
> import java.sql.Connection;
> import java.sql.ResultSet;
> import java.sql.SQLException;
> import java.sql.Statement;
>
> import oracle.jdbc.pool.OracleDataSource;
>
> public class DBConnectionTest
> {
> public static void main(String args[])
> throws SQLException, IOException
> {
>
> // Prompt the user for connect information
> String user = "scott";
> String password = "tiger";
> String database = "MYDB";
> String url = "jdbc:oracle:oci:@" + database;
> String sql = "select 'Hello World' from dual";
>
> System.out.println("url = " + url);
> System.out.println("sql = " + sql);
> System.out.flush();
>
> System.out.print("Connecting...");
> // Open an OracleDataSource and get a connection
> OracleDataSource ods = new OracleDataSource();
> ods.setURL(url);
> ods.setUser(user);
> ods.setPassword(password);
> Connection conn = ods.getConnection();
> System.out.println("Connected.");
>
> // Create a statement
> Statement stmt = conn.createStatement();
>
> // Do the SQL "Hello World" thing
> ResultSet rset = stmt.executeQuery(sql);
>
> System.out.print("Results from query >> ");
> while (rset.next())
> System.out.println(rset.getString(1));
> // close the result set, the statement and connect
> rset.close();
> stmt.close();
> conn.close();
> System.out.println("Your JDBC installation is correct.");
> }
>
> }
>
> Output
>
> url = jdbc:oracle:oci:@MYDB
> sql = select 'Hello World' from dual
> Connecting...Connected.
> Results from query >> Hello World
> Your JDBC installation is correct.
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@commons.apache.org
For additional commands, e-mail: dev-help@commons.apache.org
Re: Tomcat DBCP with Oracle JDBC OCI 10g driver
Posted by Henri Yandell <fl...@gmail.com>.
When you say it crashes the JVM; how is it crashing it?
Hen
On Dec 7, 2007 12:37 PM, <Je...@cox.com> wrote:
> To Whom It May Concern,
>
> Does anyone have a checklist for implementing Oracle JDBC OCI 10g driver
> in the Tomcat server DBCP connection pool. It seems very simple
> according to directions from Oracle but I must be missing one piece.
> I have confirmed the external setup with tnsnames.ora and the oracle
> client installation. I can connect with SQL Navigator and with a simple
> java program that uses the oci driver but does not use the connection
> pool. When I try to use this same driver and the connection pool it
> crashes the JVM.
>
> 1. install oracle client -- done and verified
> 2. verify ORACLE_HOME/bin is in the PATH -- done and verified
> 3. set TNS_NAMES correctly -- done and verified
> 4. use the Oracle 10g driver ( ojdbc14.jar ver. 10.2.0.3 ) -- done,
> downloaded and verified with thin client
> 5. configure the connection pool ( see below ) -- I think this is
> correct, work with thin, not with oci
>
> 1. correct driver, type, and factory ( from examples )
>
> The simple program uses the OracleDataSource to get the connection.
> What does the DBCP do different than my simple program to get a physical
> connection to the database?
>
> Any suggestions are greatly appreciated.
>
> Jeff
>
>
> TNS_ADMAIN: is set to the directory with tnsnames.ora lives
>
> TNSNAMES.ORA:
>
> MYDB =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = 10.62.37.78)(PORT = 1521))
> )
> (CONNECT_DATA =
> (SERVICE_NAME = SAMPF)
> )
> )
>
> THIS WORKS:
>
> <Resource name="jdbc/samp/hsd"
> auth="Container"
> type="oracle.jdbc.pool.OracleDataSource"
> driverClassName="oracle.jdbc.driver.OracleDriver"
> factory="oracle.jdbc.pool.OracleDataSourceFactory"
> url="jdbc:oracle:thin:@MYDB"
> user="scott"
> password="tiger"
> maxActive="20"
> maxIdle="10"
> maxWait="-1" />
>
> THIS CRASHES THE JVM:
>
> <Resource name="jdbc/samp/hsd"
> auth="Container"
> type="oracle.jdbc.pool.OracleDataSource"
> driverClassName="oracle.jdbc.driver.OracleDriver"
> factory="oracle.jdbc.pool.OracleDataSourceFactory"
> url="jdbc:oracle:oci:@MYDB"
> user="scott"
> password="tiger"
> maxActive="20"
> maxIdle="10"
> maxWait="-1" />
>
> Simple Java Program
>
> package com.cox.pap.paws.test;
>
> /*
> * This sample can be used to check the JDBC installation.
> * Just run it and provide the connect information. It will select
> * "Hello World" from the database.
> */
>
> // You need to import the java.sql and JDBC packages to use JDBC
> import java.io.IOException;
> import java.sql.Connection;
> import java.sql.ResultSet;
> import java.sql.SQLException;
> import java.sql.Statement;
>
> import oracle.jdbc.pool.OracleDataSource;
>
> public class DBConnectionTest
> {
> public static void main(String args[])
> throws SQLException, IOException
> {
>
> // Prompt the user for connect information
> String user = "scott";
> String password = "tiger";
> String database = "MYDB";
> String url = "jdbc:oracle:oci:@" + database;
> String sql = "select 'Hello World' from dual";
>
> System.out.println("url = " + url);
> System.out.println("sql = " + sql);
> System.out.flush();
>
> System.out.print("Connecting...");
> // Open an OracleDataSource and get a connection
> OracleDataSource ods = new OracleDataSource();
> ods.setURL(url);
> ods.setUser(user);
> ods.setPassword(password);
> Connection conn = ods.getConnection();
> System.out.println("Connected.");
>
> // Create a statement
> Statement stmt = conn.createStatement();
>
> // Do the SQL "Hello World" thing
> ResultSet rset = stmt.executeQuery(sql);
>
> System.out.print("Results from query >> ");
> while (rset.next())
> System.out.println(rset.getString(1));
> // close the result set, the statement and connect
> rset.close();
> stmt.close();
> conn.close();
> System.out.println("Your JDBC installation is correct.");
> }
>
> }
>
> Output
>
> url = jdbc:oracle:oci:@MYDB
> sql = select 'Hello World' from dual
> Connecting...Connected.
> Results from query >> Hello World
> Your JDBC installation is correct.
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@commons.apache.org
For additional commands, e-mail: dev-help@commons.apache.org