I've revised the Connection Pooling example for Oracle to use the
OracleConnectionCacheImpl class.
I've attached an updated readme file.
Regards
Tony
----- Original Message -----
From: "John McAuley" <[EMAIL PROTECTED]>
To: "Tomcat Users List" <[EMAIL PROTECTED]>; "anthony.dodd"
<[EMAIL PROTECTED]>
Sent: Tuesday, June 11, 2002 1:14 AM
Subject: Re: JNDI lookup connection datasource microsoft sql server
> Cheers Anthony
>
> I was looking at that but needed confirmation.
>
> Regards
> john
> ----- Original Message -----
> From: "anthony.dodd" <[EMAIL PROTECTED]>
> To: "Tomcat Users List" <[EMAIL PROTECTED]>
> Sent: Tuesday, June 11, 2002 5:08 AM
> Subject: Re: JNDI lookup connection datasource microsoft sql server
>
>
> > Hi
> >
> > I've posted a solution see "Generic DataSource Resource Factory
> > Available. JDBC Data Source" on the tomcat user maillist. I used the
> > datadirect ConnectJDBC as I found the Microsoft implementation was very
> > poor. The Microsoft JDBC is just an older version of ConnectJDBC which
> > datadirect felt was'nt going the right direction.
> >
> > Tony
> >
> > ----- Original Message -----
> > From: "John McAuley" <[EMAIL PROTECTED]>
> > To: <[EMAIL PROTECTED]>
> > Sent: Monday, June 10, 2002 2:17 AM
> > Subject: JNDI lookup connection datasource microsoft sql server
> >
> >
> > Hi folks,
> >
> > I am trying to use Microsoft's jdbc driver for ms sql with Tomcat
and
> am
> > running into trouble with the JNDI lookup method. I look for a
datasource
> > and get null. If I use the class.forname method the driver is found and
> > works perfectly but I am unable to get it working with the JNDI look up,
> > This is very frustrating and seems to crop up a couple of times with
> fellow
> > users. Any thoughts ??
> >
> > Help much appreciated.
> >
> > John
> >
> > Server.xml
> >
> > <Context path="/tools" docBase="tools" debug="0" reloadable="true">
> > <Resource name="jdbc/ToolsDB" auth="Container"
> > type="javax.sql.DataSource"/>
> > <ResourceParams name="jdbc/ToolsDB">
> > <parameter>
> > <name>user</name>
> > <value>user</value>
> > </parameter>
> > <parameter>
> > <name>password</name>
> > <value>password</value>
> > </parameter>
> > <parameter>
> > <name>driverClassName</name>
> >
> > <value>com.microsoft.jdbc.sqlserver.SQLServerDriver</value>
> > </parameter>
> > <parameter>
> > <name>driverName</name>
> >
> > <value>jdbc:microsoft:sqlserver://IP-ADDRESS-of-server:1433</value>
> > </parameter>
> > </ResourceParams>
> > </Context>
> >
> > web.xml
> >
> > <resource-ref>
> > <description>
> > Resource reference to a factory for
java.sql.Connection
> > instances that may be used for talking to a particular
> > database that is configured in the server.xml file.
> > </description>
> > <res-ref-name>
> > jdbc/ToolsDB
> > </res-ref-name>
> > <res-type>
> > javax.sql.DataSource
> > </res-type>
> > <res-auth>
> > Container
> > </res-auth>
> > </resource-ref>
> >
> > Code Snippet
> >
> > Context ctx = new InitialContext();
> > Context envCtx = (Context)ctx.lookup("java:comp/env");
> > out.println("Got first context.");
> >
> > NamingEnumeration enum
> > =ctx.listBindings("java:comp/env/jdbc");
> > while( enum.hasMore() ) {
> > out.println("Binding: " +
> > ((Binding)enum.next()).toString() + "<br>");
> > }
> > DataSource ds =
> > (DataSource)envCtx.lookup("jdbc/ToolsDB");
> > out.println("Got the second context." + "<br>" +
> > "<br>");
> >
> > if (ds != null)
> > {
> > out.println("Getting the connection from the
> > context.");
> > Connection connection = ds.getConnection();
> > }else{
> > out.println("not working");
> > }
> >
> >
> >
> >
> > --
> > To unsubscribe, e-mail:
> <mailto:[EMAIL PROTECTED]>
> > For additional commands, e-mail:
> <mailto:[EMAIL PROTECTED]>
> >
> >
>
>
>
> --
> To unsubscribe, e-mail:
<mailto:[EMAIL PROTECTED]>
> For additional commands, e-mail:
<mailto:[EMAIL PROTECTED]>
>
CONTENTS
1. INTRODUCTION
2. CREATING AN ORACLE DATASOURCE
3. CREATING A SQL SERVER DATASOURCE USING DATADIRECTS' CONNECTJDBC
4. CREATING AN ORACLE CONNECTION POOLED DATASOURCE
5. CREATING A SQL SERVER CONNECTION POOLED DATA SOURCE USING DATADIRECTS' CONNECTJDBC
6. CLASSCASTEXCEPTION WHEN NARROWING JNDI REFERENCED OBJECTS
7. FINALLY
1. INTRODUCTION
Due to the lack of support for certain data base types with the default DataSource
resource factory, I developed a generic datasource resource
factory org.apache.naming.factory.DataSourceFactory. This resource factor allows you
to create any object which implements
the javax.sql.DataSource interface.
It uses a simple introspection techique discover the properties of the DataSource
implementation your trying to instantiate and
then matches these properties with the values supplied in the <ResourceParams>
<parameter> elements.
The version supplied with this e-mail supports properties of the following types
String, int, short, long, float, double,
byte, char and boolean.
If you supply the following parameter
<parameter>
<name>debug</name>
<value>true</true>
</parameter>
it will trace out its activity to the jvm.stdout, or where ever System.out has been
redirected.
2. CREATING AN ORACLE DATASOURCE
The following example shows the server.xml and web.xml (WEB-INF subdirectory of your
web application) needed to create a
datasource from Oracles' DataSource implementation 'oracle.jdbc.pool.OracleDataSource'.
server.xml
---------------
<Context ...
<!-- Create DataSource -->
<Resource name="jdbc/MYORACLEDATASOURCE" reloadable="true" auth="Container"
type="oracle.jdbc.pool.OracleDataSource"/>
<ResourceParams name="jdbc/MYORACLEDATASOURCE">
<parameter>
<name>factory</name>
<value>org.apache.naming.factory.DataSourceFactory</value>
</parameter>
<parameter>
<name>description</name>
<value>Oracle DataSource</value>
</parameter>
<parameter>
<name>serverName</name>
<value>192.168.152.2</value>
</parameter>
<parameter>
<name>portNumber</name>
<value>1521</value>
</parameter>
<parameter>
<name>databaseName</name>
<value>mydatabase</value>
</parameter>
<parameter>
<name>driverType</name>
<value>thin</value>
</parameter>
<parameter>
<name>user</name>
<value>scott</value>
</parameter>
<parameter>
<name>password</name>
<value>tiger</value>
</parameter>
</ResourceParams>
</Context>
web.xml
---------
<?xml version="1.0"?>
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.2//EN"
"http://java.sun.com/j2ee/dtds/web-app_2_2.dtd";>
<web-app>
<resource-ref>
<description>Oracle DataSource</description>
<res-ref-name>jdbc/MYORACLEDATASOURCE</res-ref-name>
<res-type>oracle.jdbc.pool.OracleDataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
3. CREATING A SQL SERVER DATASOURCE USING DATADIRECTS' CONNECTJDBC
The following example shows the server.xml and web.xml (WEB-INF subdirectory of your
web application) needed to create a
datasource from DataDirects' ConnectJDBC DataSurce implementation
'com.ddtek.jdbcx.sqlserver.SQLServerDataSource'. DataDirect/Merant were the
people who developed the odbcjdbc bridge used in the early days when connecting to SQL
7.0 databases. Microsoft are currently using
a version of DataDirects ConnectJDBC to deliver there own JDBC 2 implementation for
SQL Server 2000. At the time of writing the
Microsoft version appears to be extremely problematic. I've also shown a sample JSP
page using this resource.
server.xml
-------------
<Context ...
<!-- Create DataSource -->
<Resource name="jdbc/MYSSQLDATASOURCE" reloadable="true" auth="Container"
type="com.ddtek.jdbcx.sqlserver.SQLServerDataSource"/>
<ResourceParams name="jdbc/MYSSQLDATASOURCE">
<parameter>
<name>factory</name>
<value>org.apache.naming.factory.DataSourceFactory</value>
</parameter>
<parameter>
<name>description</name>
<value>SQL Server DataSource</value>
</parameter>
<parameter>
<name>serverName</name>
<value>192.168.152.2</value>
</parameter>
<parameter>
<name>portNumber</name>
<value>1433</value>
</parameter>
<parameter>
<name>databaseName</name>
<value>mydatabase</value>
</parameter>
<parameter>
<name>user</name>
<value>sa</value>
</parameter>
<parameter>
<name>password</name>
<value/>
</parameter>
</ResourceParams>
</Context>
web.xml
----------
<?xml version="1.0"?>
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.2//EN"
"http://java.sun.com/j2ee/dtds/web-app_2_2.dtd";>
<web-app>
<resource-ref>
<description>SQL Server DataSource</description>
<res-ref-name>jdbc/MYSSQLDATASOURCE</res-ref-name>
<res-type>com.ddtek.jdbcx.sqlserver.SQLServerDataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
sample jsp page
-------------------
<%@ page import="java.sql.*" %>
<%@ page import="javax.sql.*" %>
<%@ page import="javax.naming.*" %>
<%
//Reference holder for connection
Connection conn = null;
try {
Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
out.println("Initial context established.<br/>");
out.println("envCtx.lookup(\"jdbc/MYSSQLDATASOURCE\").getClass().getName()="+envCtx.lookup("jdbc/MYSSQLDATASOURCE").getClass().getName()+"<br/>");
javax.sql.DataSource ds =
(javax.sql.DataSource)envCtx.lookup("jdbc/MYSSQLDATASOURCE");
conn = ds.getConnection();
Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery("select * from CATEGORIES");
out.println("<h1>Categories</h1>");
while (rs.next()) {
out.println(rs.getString("TextE")+"</br>");
}
} catch (java.lang.Exception e) {
out.println("A "+e.getClass().getName()+" exception has occured :
"+e.getMessage()+"<br/>");
e.printStackTrace(System.out);
} finally {
if (conn != null)
conn.close();
}
%>
4. CREATING AN ORACLE CONNECTION POOLED DATASOURCE
The following example shows the server.xml and web.xml (WEB-INF subdirectory of your
web application) needed to create a
datasource from Oracles' ConnectionPoolDataSource implementation
'oracle.jdbc.pool.OracleConnectionCacheImpl'.
server.xml
---------------
<Context ...
<!-- Create DataSource -->
<Resource name="jdbc/MYORACLEPOOLEDDATASOURCE" reloadable="true" auth="Container"
type="oracle.jdbc.pool.OracleConnectionCacheImpl"/>
<ResourceParams name="jdbc/MYORACLEPOOLEDDATASOURCE">
<parameter>
<name>factory</name>
<value>org.apache.naming.factory.DataSourceFactory</value>
</parameter>
<parameter>
<name>description</name>
<value>Oracle Connection Pool DataSource</value>
</parameter>
<!-- Connection Pooling Parameters -->
<parameter>
<name>maxLimit</name>
<value>10</value>
</parameter>
<parameter>
<name>minLimit</name>
<value>1</value>
</parameter>
<parameter>
<!--
1 - DYNAMIC_SCHEME
In this default scheme, you can create new pooled connections above
and beyond the maximum limit,
but each one is automatically closed and freed as soon as the logical
connection instance that it
provided is no longer in use. (As opposed to the normal scenario when
a pooled connection instance is
finished being used, where it is returned to the available cache.)
2 - FIXED_WAIT_SCHEME
Same as the "fixed with no wait" scheme except that a request for a
new connection will wait if the limit for
the number of connections has been reached. In this case, the
connection request waits until another
client releases a connection.
3 - FIXED_RETURN_NULL_SCHEME
In this scheme, the maximum limit cannot be exceeded. Requests for
connections when the
maximum has already been reached will return null
-->
<name>cacheScheme</name>
<value>1</value>
</parameter>
<!-- Connection Parameters -->
<parameter>
<name>serverName</name>
<value>192.168.152.2</value>
</parameter>
<parameter>
<name>portNumber</name>
<value>1521</value>
</parameter>
<parameter>
<name>databaseName</name>
<value>mydatabase</value>
</parameter>
<parameter>
<name>driverType</name>
<value>thin</value>
</parameter>
<parameter>
<name>user</name>
<value>scott</value>
</parameter>
<parameter>
<name>password</name>
<value>tiger</value>
</parameter>
</ResourceParams>
</Context>
web.xml
---------
<?xml version="1.0"?>
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application
2.2//EN"
"http://java.sun.com/j2ee/dtds/web-app_2_2.dtd";>
<web-app>
<resource-ref>
<description>Oracle Connection Pool DataSource</description>
<res-ref-name>jdbc/MYORACLEPOOLEDDATASOURCE</res-ref-name>
<res-type>oracle.jdbc.pool.OracleConnectionCacheImpl</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
Use the getConnection method to get a connection from the pool.
5. CREATING A SQL SERVER CONNECTION POOLED DATA SOURCE USING DATADIRECTS' CONNECTJDBC
The following example shows the server.xml and web.xml (WEB-INF subdirectory of your
web application) needed to create a
pooled datasource from DataDirects ConnectJDBC ConnectionPoolDataSource
implenmentation 'com.ddtek.pool.PooledConnectionDataSource'.
Since this implementation does not implement javax.sql.ConnectionPoolDataSource, but
instead expects to be supplied with a datasource
which it can use to create a pooled instance, a helper resource factory has been
included 'com.ddtek.pool.PooledConnectionDataSourceFactory'.
You still need to create a datasource as with 3 above, then create a
'com.ddtek.pool.PooledConnectionDataSource' which is passed the jndi name
of the first.
I've also shown a sample jsp page using the pooled connection resource, identical in
aspects to that shown in two above, apart from the jndi name
of the pooled resource being used. Note the use of getConnection as
'com.ddtek.pool.PooledConnectionDataSource' doesn't implement
javax.sql.ConnectionPoolDataSource.
server.xml
-------------
<Context ...
<!-- Create DataSource -->
<Resource name="jdbc/MYSQLDATASOURCE" reloadable="true" auth="Container"
type="com.ddtek.jdbcx.sqlserver.SQLServerDataSource"/>
<ResourceParams name="jdbc/MYSQLDATASOURCE">
<parameter>
<name>factory</name>
<value>org.apache.naming.factory.DataSourceFactory</value>
</parameter>
<parameter>
<name>description</name>
<value>SQL Server DataSource</value>
</parameter>
<parameter>
<name>serverName</name>
<value>192.168.152.2</value>
</parameter>
<parameter>
<name>portNumber</name>
<value>1433</value>
</parameter>
<parameter>
<name>databaseName</name>
<value>mydatabase</value>
</parameter>
<parameter>
<name>user</name>
<value>sa</value>
</parameter>
<parameter>
<name>password</name>
<value/>
</parameter>
<parameter>
<name>debug</name>
<value>true</value>
</parameter>
</ResourceParams>
<!-- Create ConnectionPoolDataSource -->
<Resource name="jdbc/MYPOOLEDSQLDATASOURCE" reloadable="true" auth="Container"
type="com.ddtek.pool.PooledConnectionDataSource"/>
<ResourceParams name="jdbc/MYPOOLEDSQLDATASOURCE">
<parameter>
<name>factory</name>
<value>com.ddtek.pool.PooledConnectionDataSourceResourceFactory</value>
</parameter>
<parameter>
<name>description</name>
<value>SQL Server Connection Pool DataSource</value>
</parameter>
<parameter>
<name>dataSourceName</name>
<value>jdbc/MYSQLDATASOURCE</value>
</parameter>
<parameter>
<name>initialPoolSize</name>
<value>5</value>
</parameter>
<parameter>
<name>minPoolSize</name>
<value>5</value>
</parameter>
<parameter>
<name>maxPoolSize</name>
<value>10</value>
</parameter>
<parameter>
<name>maxIdleTime</name>
<value>300</value>
</parameter>
</ResourceParams>
</Context>
web.xml
----------
<?xml version="1.0"?>
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.2//EN"
"http://java.sun.com/j2ee/dtds/web-app_2_2.dtd";>
<web-app>
<resource-ref>
<description>SQL Server Connection Pool DataSource</description>
<res-ref-name>jdbc/MYPOOLEDSQLDATASOURCE</res-ref-name>
<res-type>com.ddtek.pool.PooledConnectionDataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
<resource-ref>
<description>SQL Server DataSource</description>
<res-ref-name>jdbc/MYSQLDATASOURCE</res-ref-name>
<res-type>com.ddtek.jdbcx.sqlserver.SQLServerDataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
sample jsp page
-------------------
<%@ page import="java.sql.*" %>
<%@ page import="javax.sql.*" %>
<%@ page import="javax.naming.*" %>
<%
//Reference holder for connection
Connection conn = null;
try {
Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
out.println("Initial context established.<br/>");
out.println("envCtx.lookup(\"jdbc/MYPOOLEDSQLDATASOURCE\").getClass().getName()="+envCtx.lookup("jdbc/MYPOOLEDSQLDATASOURCE").getClass().getName()+"<br/>");
javax.sql.DataSource ds =
(javax.sql.DataSource)envCtx.lookup("jdbc/MYPOOLEDSQLDATASOURCE");
out.println("Acquiring Connection</br>");
conn = ds.getConnection();
out.println("Connection Acquired</br>");
Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery("select * from CATEGORIES");
out.println("<h1>Categories</h1>");
while (rs.next()) {
out.println(rs.getString("TextE")+"</br>");
}
} catch (java.lang.Throwable t) {
out.println("A "+t.getClass().getName()+" exception has occured :
"+t.getMessage()+"<br/>");
t.printStackTrace(System.out);
} finally {
if (conn != null)
conn.close();
}
%>
6. CLASSCASTEXCEPTION WHEN NARROWING JNDI REFERENCED OBJECTS
Something to be aware of. One of the problems I constantly battled with was the
ClassCastException been thrown when I tried to narrow the returned resource to a
javax.sql.DataSource.
The reason for this turned out to be multiple occurrences of javax.sql.DataSource. For
example if using the jdbc2 extensions and oracles classes12.jar both contain
javax.sql.DataSource.
I'm not sure if this is just a tomcat problem.
7. FINALLY
Hope you find these code snippets useful, any feed back would be grateful. Do let's us
know of any problems encountered.
[EMAIL PROTECTED]
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
