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.
