java.sql.SQLException: Invalid Oracle URL specified

[Sinan BALÇIN]

Hi all,

i have a problem with P6Spy.

This is my JNDI (in server.xml) : 

  <Resource name="jdbc/sampe_fname" auth="Container" defaultAutoCommit="false" driverClassName="com.p6spy.engine.spy.P6SpyDriver" factory="oracle.jdbc.pool.OracleDataSourceFactory" logAbandoned="true" maxActive="20" maxIdle="10" maxWait="-1" removeAbandoned="true" removeAbandonedTimeout="300" type="oracle.jdbc.pool.OracleDataSource" url="jdbc:p6spy:oracle:thin:@server:port:dbname" validationQuery="select 1 from dual" /> 

 <Context docBase="ecproject" path="/ecproject" reloadable="true" source="org.eclipse.jst.jee.server:ecproject"> <ResourceLink global="jdbc/sampe_fname" name="jdbc/sampe_fname" type="oracle.jdbc.pool.OracleDataSource" /> </Context>

and this is java code;

     Context initContext = null;

        Context envContext = null;

        OracleDataSource ds = null;

        try {

            initContext = new InitialContext();

            envContext  = (Context)initContext.lookup("java:comp/env");

            ds = (OracleDataSource) envContext.lookup("jdbc/sampe_fname");

        } catch (NamingException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

        String user_name = (String) CommonJava.iniParameters.get("USER_NAME");

        String user_pass = (String) CommonJava.iniParameters.get("PASSWORD");

        Connection conn = ds.getConnection(user_name, user_pass);

but, it gets error;

java.sql.SQLException: Invalid Oracle URL specified

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:207)

at oracle.jdbc.pool.OracleDataSource.getPhysicalConnection(OracleDataSource.java:300)

Where is my fault ? Actually how to configure p6spy in tomcat 7 with oracle ?

[Quinton McCombs]

First, make sure that you follow the instructions in the user's guide.  

http://p6spy.github.io/p6spy/2.0/install.html#tomcat7

I also see that you are using OracleDataSourceFactory instead of Tomcat's default datasource factory.  It looks like that is where the connection URL is being validated.  You should be able to remove the reference to the factory to get it working (along with the changes as described in the user's guide).

Below is a modified version of your configuration and Java code that *should* work...  If you need access to Oracle specific methods on the connection, you can unwrap the proxy.

Corrected:

  <Resource name="jdbc/sampe_fname" auth="Container" defaultAutoCommit="false" driverClassName="com.p6spy.engine.spy.P6SpyDriver" logAbandoned="true" maxActive="20" maxIdle="10" maxWait="-1" removeAbandoned="true" removeAbandonedTimeout="300" type="javax.sql.DataSource" url="jdbc:p6spy:oracle:thin:@server:port:dbname" validationQuery="select 1 from dual" /> 

 <Context docBase="ecproject" path="/ecproject" reloadable="true" source="org.eclipse.jst.jee.server:ecproject"> <ResourceLink global="jdbc/sampe_fname" name="jdbc/sampe_fname" type="javax.sql.DataSource" /> </Context>

     Context initContext = null;

        Context envContext = null;

        OracleDataSource ds = null;

        try {

            initContext = new InitialContext();

            envContext  = (Context)initContext.lookup("java:comp/env");

            ds = (DataSource) envContext.lookup("jdbc/sampe_fname");

        } catch (NamingException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

        String user_name = (String) CommonJava.iniParameters.get("USER_NAME");

        String user_pass = (String) CommonJava.iniParameters.get("PASSWORD");

        Connection conn = ds.getConnection(user_name, user_pass);