Re: Oracle JDBC pool & documentation

[Kwok Peng Tuck]

Here's a link to the problem you had : 
http://www.mail-archive.com/[EMAIL PROTECTED]/msg74781.html
I'm glad you solved it, it was confusing me in the first place :)

Jean Marais wrote:

	Hi

	I struggled with the following and think it should be mentioned in the documentation... unless I missed it somehow. The pool connection seems to hang up when you have set it up and in actual fact an important part of the DataSource setup was missing. It seems from the documentation that it is optional, but it seems to be required. I tested the same thing on PostgreSQL and noticed a seemingly infinite loop of something that looked like a setup query or setup command.. something that sets up the character set or something along these lines. The same thing most probably happened with Oracle.

	Adding the following in the ResourceParams section fixed it. 
	I hope this is the place to raise it as something that should be added to the documentation if it really was the cause of my problem.

	Regards (& keep up the good work).

                    <parameter>
                      <name>validationQuery</name>
                      <value>select count(1) from xyz_table</value>
                    </parameter>
	*******************************************************************

	validationQuery - SQL query that can be used by the pool to validate connections before they are returned to the application. If specified, this query MUST be an SQL SELECT statement that returns at least one row.

	*******************************************************************

	Oracle 8i

	    0. Introduction

	    We would appreciate comments on this section as I'm not an Oracle DBA :-)

            Oracle requires minimal changes from the MySQL configuration except for 
the usual gotchas :-) Firstly by default, Tomcat will only use *.jar files installed 
in $CATALINA_HOME/common/lib therefore classes111.zip or classes12.zip will need to be 
renamed with a .jar extension. Since jarfiles are zipfiles, there is no need to unzip 
and jar these files - a simple rename will suffice. Also, you should be aware that 
some (early) versions of Tomcat 4.0 when used with JDK 1.4 will not load classes12.zip 
unless you unzip the file, remove the javax.sql.* class heirarchy and rejar.
            1. server.xml configuration
	    In a similar manner to the mysql config above, you will need to define your Datasource in your server.xml file. Here we define a Datasource called myoracle using the thin driver to connect as user scott, password tiger to the schema called myschema in the sid called mysid. (Note: with the thin driver this sid is not the same as the tnsname)

	    Use of the OCI driver should simply involve a changing thin to oci in the URL string.

	<Resource name="jdbc/myoracle" auth="Container"
	              type="javax.sql.DataSource"/> 

        <ResourceParams name="jdbc/myoracle">
          <parameter>
            <name>factory</name>
            <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
          </parameter>
          <parameter>
            <name>driverClassName</name>
            <value>oracle.jdbc.driver.OracleDriver</value>
          </parameter>
          <parameter>
            <name>url</name>
            <value>jdbc:oracle:thin:[EMAIL PROTECTED]:1521:mysid</value>
          </parameter>
          <parameter>
            <name>username</name>
            <value>scott</value>
          </parameter>
          <parameter>
            <name>password</name>
            <value>tiger</value>
          </parameter>
          <parameter>
            <name>maxActive</name>
            <value>20</value>
          </parameter>
          <parameter>
            <name>maxIdle</name>
            <value>10</value>
          </parameter>
          <parameter>
            <name>maxWait</name>
            <value>-1</value>
          </parameter>
        </ResourceParams>
	    2. web.xml configuration

	    You should ensure that you respect the elemeent ordering defined by the DTD when you create you applications web.xml file.

        <resource-ref>
         <description>Oracle Datasource example</description>
         <res-ref-name>jdbc/myoracle</res-ref-name>
         <res-type>javax.sql.DataSource</res-type>
         <res-auth>Container</res-auth>
        </resource-ref>
	    3. Code example

	    You can use the same example application as above (asuming you create the required DB instance, tables etc.) replacing the Datasource code with something like

        Context initContext = new InitialContext();
        Context envContext  = (Context)initContext.lookup("java:/comp/env");
        DataSource ds = (DataSource)envContext.lookup("jdbc/myoracle");
        Connection conn = ds.getConnection();
        //etc.


---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
 



---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]