Hi,
Now it's working! Follows the script:
1) Tomcat 6.0.35: copy tomcat-jdbc.jar to CATALINA_HOME/lib. Tomcat 7.0.x
is ready.
2) Create a global resource in CATALINA_HOME/conf/server.xml. Attributes
in bold *MUST *be present:
<Resource name="jdbc/pgserver" auth="Container"
type="javax.sql.DataSource" removeAbandoned="true"
removeAbandonedTimeout="300"
maxActive="200" maxIdle="10"
maxWait="10000"
validationQuery="select 1"
validationInterval="10000"
testOnBorrow="true"
*
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"*
driverClassName="org.postgresql.Driver"
url="jdbc:postgresql://localhost:5432/tjse"
username="globaluser"
password="globalpassword"
* alternateUsernameAllowed="true"*
jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
/>
3) Create a context.xml for apps;
The attribute "name" need not possess the same value of the attribute
"global".
--> CATALINA_HOME/conf/Catalina/localhost/app1.xml (shared connection with
different credential)
<Context>
<ResourceLink name="jdbc/pgserver"
global="jdbc/pgserver"
type="javax.sql.DataSource"
* factory="org.apache.naming.factory.DataSourceLinkFactory"*
* username="userapp1"
password="passwordapp1"*
/>
</Context>
--> CATALINA_HOME/conf/Catalina/localhost/app2.xml (shared connection with
default credential)
<Context>
<ResourceLink name="jdbc/pgserver"
global="jdbc/pgserver"
type="javax.sql.DataSource"
/>
</Context>
4) JSP to test this feature (in both CATALINA_HOME/webapps/app1 and
CATALINA_HOME/webapps/app2):
<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd";>
<%@ page session="false" import="javax.naming.*, java.sql.*, javax.sql.*" %>
<html xmlns="http://www.w3.org/1999/xhtml"; xml:lang="en" lang="en">
<head>
<title>Test shared data source</title>
</head>
<body>
<%
Context ctx = null;
DataSource ds = null;
Connection c = null;
Statement stm = null;
ResultSet rs = null;
try {
ctx = new InitialContext();
ds = (DataSource)ctx.lookup("java:comp/env/jdbc/pgserver");
c = ds.getConnection();
stm = c.createStatement();
rs = stm.executeQuery("select current_user");
rs.next();
%>
Current user: <%= rs.getString(1) %><br/>
<%
} catch (Exception e) {
%>
Error: <%= e.getMessage() %>
<%
} finally {
if (rs!=null) try { rs.close(); } catch (Exception ignore){}
if (stm!=null) try { stm.close(); } catch (Exception ignore){}
if (c!=null) try { c.close();} catch (Exception ignore){} }
%>
</body>
</html>
5) App1 output:
Current user: userapp1
6) App2 output:
Current user: globaluser
Thanks and I hope this script can help others with the same problem.
Cheers,
Robert
On Wed, May 23, 2012 at 8:38 PM, Robert Anderson <ranom...@gmail.com> wrote:
> Sorry, for the wall of text. :)
>
>
> "IIRC there is no support for getConnection(username, password) in
> Apache Commons DBCP pool at all, and it was a new feature in Tomcat
> JDBC pool at that time."
>
> Yes, it is the problem. I've lost many hours following an example in
> documentation about ResourceLink and DataSource that does not work as
> expected/described.
>
>
> Best regards,
>
> Robert
>
> On Wed, May 23, 2012 at 8:26 PM, Konstantin Kolinko <
> knst.koli...@gmail.com> wrote:
>
>> 2012/5/24 Robert Anderson <ranom...@gmail.com>:
>> >
>> > 2. You need to set alternateUsernameAllowed="
>> >>
>> >> true" on Tomcat JDBC pool [1]
>> >> Otherwise arguments in ds.getConnection(user,password) method on that
>> >> datasource are ignored.
>> >
>> >
>> > Good, I'll test it. Anyway, the following description and example in
>> > documentation is not valid (
>> > tomcat.apache.org/tomcat-6.0-doc/config/context.html#Resource_Links,
>> > tomcat.apache.org/tomcat-7.0-doc/config/context.html#Resource_Links):
>> >
>> > "When the attribute
>> > factory="org.apache.naming.factory.DataSourceLinkFactory" the resource
>> link
>> > can be used with two additional attributes to allow a shared data
>> source to
>> > be used with different credentials. When these two additional attributes
>> > are used in combination with the javax.sql.DataSource type, different
>> > contexts can share a global data source with different credentials.
>> Under
>> > the hood, what happens is that a call to
>> > getConnection()<
>> http://download.oracle.com/javase/6/docs/api/javax/sql/DataSource.html#getConnection%28%29
>> >is
>> > simply translated to a call getConnection(username,
>> > password)<
>> http://download.oracle.com/javase/6/docs/api/javax/sql/DataSource.html#getConnection%28java.lang.String,%20java.lang.String%29
>> >on
>> > the global data source. This is an easy way to get code to be
>> > transparent to what schemas are being used, yet be able to control
>> > connections (or pools) in the global configuration. "
>> >
>> > <GlobalNamingResources>
>> > ...
>> > <Resource name="sharedDataSource"
>> > global="sharedDataSource"
>> > type="javax.sql.DataSource"
>> > username="bar"
>> > password="barpass"
>> >
>> > ...
>> > ...
>> > </GlobalNamingResources>
>> >
>> > <Context path="/foo"...>
>> > ...
>> > <ResourceLink
>> > name="appDataSource"
>> > global="sharedDataSource"
>> > type="javax.sql.DataSource"
>> > factory="org.apache.naming.factory.DataSourceLinkFactory"
>> > username="foo"
>> > password="foopass"
>> > ...
>> > </Context>
>> > <Context path="/bar"...>
>> > ...
>> > <ResourceLink
>> > name="appDataSource"
>> > global="sharedDataSource"
>> > type="javax.sql.DataSource"
>> > ...
>> > </Context>
>> >
>> >
>> >
>> > That way, just does not work.
>> >
>>
>> Can you be more specific? You are just citing a wall of text and it is
>> hard to digest.
>>
>> Can you open an issue in Bugzilla?
>>
>> IIRC there is no support for getConnection(username, password) in
>> Apache Commons DBCP pool at all, and it was a new feature in Tomcat
>> JDBC pool at that time.
>>
>> Is it the problem, or something else is wrong?
>>
>> Best regards,
>> Konstantin Kolinko
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
>> For additional commands, e-mail: users-h...@tomcat.apache.org
>>
>>
>
