Re: a little disillusioned - Mailing list pgsql-jdbc

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