Oracle JDBC pool documentation
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 namevalidationQuery/name valueselect 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 namefactory/name valueorg.apache.commons.dbcp.BasicDataSourceFactory/value /parameter parameter namedriverClassName/name valueoracle.jdbc.driver.OracleDriver/value /parameter parameter nameurl/name valuejdbc:oracle:thin:[EMAIL PROTECTED]:1521:mysid/value /parameter parameter nameusername/name valuescott/value /parameter parameter namepassword/name valuetiger/value /parameter parameter namemaxActive/name value20/value /parameter parameter namemaxIdle/name value10/value /parameter parameter namemaxWait/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 descriptionOracle Datasource example/description res-ref-namejdbc/myoracle/res-ref-name res-typejavax.sql.DataSource/res-type res-authContainer/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]
Re: Oracle JDBC pool documentation
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 namevalidationQuery/name valueselect 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 namefactory/name valueorg.apache.commons.dbcp.BasicDataSourceFactory/value /parameter parameter namedriverClassName/name valueoracle.jdbc.driver.OracleDriver/value /parameter parameter nameurl/name valuejdbc:oracle:thin:[EMAIL PROTECTED]:1521:mysid/value /parameter parameter nameusername/name valuescott/value /parameter parameter namepassword/name valuetiger/value /parameter parameter namemaxActive/name value20/value /parameter parameter namemaxIdle/name value10/value /parameter parameter namemaxWait/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 descriptionOracle Datasource example/description res-ref-namejdbc/myoracle/res-ref-name res-typejavax.sql.DataSource/res-type res-authContainer/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]
Re: Oracle JDBC pool documentation
Hi, One more thing, the query that is used should return atleast one row,. If the query returns no rows selected then again the you will be in the infinte loop. This is some more info from my exp.. Infinite loop on invalid validation SQL query If one is using DBCP package and enters an incorrect query (a query that would always fail) for the validation query, then when a connection is being requested the code will enter an infinite loop in GenericObjectPool.borrowObject(). The problem would happen for any other object (not only Connection) if the validation always fails. Regards, Pratt. - Original Message - From: Kwok Peng Tuck [EMAIL PROTECTED] To: Tomcat Users List [EMAIL PROTECTED] Sent: Tuesday, March 11, 2003 12:56 PM Subject: Re: Oracle JDBC pool documentation 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 namevalidationQuery/name valueselect 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 namefactory/name valueorg.apache.commons.dbcp.BasicDataSourceFactory/value /parameter parameter namedriverClassName/name valueoracle.jdbc.driver.OracleDriver/value /parameter parameter nameurl/name valuejdbc:oracle:thin:[EMAIL PROTECTED]:1521:mysid/value /parameter parameter nameusername/name valuescott/value /parameter parameter namepassword/name valuetiger/value /parameter parameter namemaxActive/name value20/value /parameter parameter namemaxIdle/name value10/value /parameter parameter namemaxWait/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 descriptionOracle Datasource example/description res-ref-namejdbc/myoracle/res-ref-name res-typejavax.sql.DataSource/res-type res-authContainer/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
