Re: a little disillusioned

On 27/01/2004 19:07 David Wilbur wrote:
>
> this may end up being a double post... my apologies if it is. i wasn't
> registered and it stalled the post.
>
> just wanted to mention that the person that was having tomcat issues is
> not alone here. unfortunately the docs as far as tomcat and postgres
> boils down to some major hand waving re the two. each relying on the
> other to work things out for the other. mysql getting the best
> treatment from the tomcat people.
>
> the os that i am running this on is:
>
> mac os 10.3.2,
>
> java version "1.4.1_01"
> Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.1_01-99)
> Java HotSpot(TM) Client VM (build 1.4.1_01-27, mixed mode)
>
> tomcat was compiled from jakarta-tomcat-5.0.16-src.tar.gz where it
> pulls all the files off the jakarta site.
>
> postgresql 7.4.1
>
> the following is a really trimmed down version of code from the tomcat
> examples re jdbc connection pools:
>
> Context initCtx = new InitialContext();
> Context envCtx = (Context) initCtx.lookup("java:comp/env");
> DataSource ds = (DataSource)envCtx.lookup("jdbc/postgres");
> try {
> Connection conn = ds.getConnection();
> if(conn != null) {
> foo = "Got Connection "+conn.toString();
> Statement stmt = conn.createStatement();
> ResultSet rst = stmt.executeQuery( "select id, foo, bar
> from testdata" );
> if(rst.next()) {
> foo=rst.getString(2);
> bar=rst.getInt(3);
> }
> conn.close();
> } catch (SQLException e) {
> foo = "SQLException: " + e;
> }
>
> results in a index.jsp showing this for foo...
>
> SQLException: org.apache.commons.dbcp.SQLNestedException: Cannot load
> JDBC driver class 'org.postgres.Driver', cause: org.postgres.Driver
>
> when i go into the administration utility i find a definition for the
> data source where the web app is defined under
>
> "/Tomcat Server/Catalina/localhost/DBTest/Data Sources"
>
> JNDI Name JDBC Driver Class
> jdbc/postgres   org.postgres.Driver
>
> delving into that i see this:
>
> JNDI Name: jdbc/postgres
> Data Source URL: jdbc:postgresql://127.0.0.1:5432/test
> JDBC Driver Class: org.postgres.Driver
> User Name: jakarta
> Password: "the correct password"
> Max. Active Connections: 20
> Max. Idle Connections: 10
> Max. Wait for Connection: 10000
> Validation Query:
>
>
> i modified the basic.java file that comes with the postgres jdbc driver
> and ran it using the connection string above
>
> jdbc:postgresql://127.0.0.1:5432/test
>
> and that works fine using the same jar file that tomcat is using.
>
>
> there is only one jar file in the tomcat directorys and it is located
> at:
>
> find $CATALINA_HOME -name "postgresql.jar"
> /usr/local/jakarta/tomcat/common/lib/postgresql.jar
>
> my class path has only ant and java home on it and i have checked to
> see if the jar is there.
>
> what i think would be really awesome is if one of you that have this
> working could look at the tomcat "documentation" for setting up
> postgres and tomcat and maybe helping them and us by maybe making it
> more intelligible... cause it is a mess as it is now. in fact if
> someone helps me work this out and they don't want to do it i will...
> see:
>
> http://jakarta.apache.org/tomcat/tomcat-5.0-doc/jndi-datasource-
> examples-howto.html
>
> maybe even adding the same advice to the postgres jdbc examples so that
> we would have a starting point that we knew was working for someone...
> this is a nice starting point, but... it might be nice to add in
> something along the line of how to set up a java web server to use
> postgres.

The Tomcat 5.0 docs gave the stupid (and I would have thought obvious)
typo that exists in the 4.1 doc for PostgreSQL. The web.xml res-ref-name
should refer to the matching name in the ResourceParams tag _not_ the
physical db name which appears in the JDBC url.

>
> http://www.postgresql.org/docs/current/static/jdbc-datasource.html
>
>
> i am suspecting that maybe is one of the xml files... either
> $CATALINA_HOME/conf/server.xml or the apps WEB-INF/web.xml... but i
> really don't know for sure what i might have wrong there. in the mean
> time i guess i will try the mysql setup the tomcat people define to see
> if that works... since i have to do that too.

FWIW, you don't really need to edit server.xml. Create a <app.,xml file
defining the context of you web app and put it in the webapps directory.
Here's a sample which also defines a JDBC realm.

<!-- Tomcat XML file for MyApp context -->
<!-- this file is to be placed in the webapps direcory -->

<!-- MyApp context -->
<Context path="/myapp" docBase="myapp.war" debug="0"
reloadable="true"
crossContext="true">

<Realm className="org.apache.catalina.realm.JDBCRealm" debug="0"
driverName="org.postgresql.Driver"
digest="MD5"
connectionURL="jdbc:postgresql://127.0.0.1:5432/my_physical_db"
connectionName="dbuser" connectionPassword="dbpasswd"
userTable="user_table" userNameCol="login_name"
userCredCol="password"
userRoleTable="user_roles" roleNameCol="role" />

<Resource name="jdbc/aniceDB" auth="Container"
type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/aniceDB">
<parameter>
<name>factory</name>

<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>org.postgresql.Driver</value>
</parameter>
<parameter>
<name>url</name>

<value>jdbc:postgresql://127.0.0.1:5432/my_physical_db</value>
</parameter>
<parameter>
<name>username</name>
<value>dbuser</value>
</parameter>
<parameter>
<name>password</name>
<value>dbpasswd</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>10</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>1</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>30000</value>
</parameter>
<parameter>
<name>removeAbandoned</name>
<value>true</value>
</parameter>
<parameter>
<name>removeAbandonedTimeout</name>
<value>60</value>
</parameter>
<parameter>
<name>logAbandoned</name>
<value>true</value>
</parameter>
</ResourceParams>
</Context>

And here's part of the app's web.xml which defines the datasource

<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/aniceDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

HTH

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants |
http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+---------------------------------------------+