commons-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <Jeffrey.Constan...@cox.com>
Subject Tomcat DBCP with Oracle JDBC OCI 10g driver
Date Fri, 07 Dec 2007 20:37:44 GMT
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.


Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message