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 >> >> >