Thread: Problem with postgresql tomcat datasource

Problem with postgresql tomcat datasource

From
Andrea.Ferrando@marconimobile.com
Date:
I am using Tomcat 4 on a linux-box with postgresql. I'm trying to set up a
data source.
I followed the instructions on
http://jakarta.apache.org/tomcat/tomcat-4.1-doc/jndi-datasource-examples-howto.html
But the ds.getConnection method waits for an undefined time to give me a
connection.

Any suggestion will be appreciated.
Thanks
Andrea


This is a few lines from my server.xml:
<Resource name="jdbc/mydb" scope="Shareable" type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/mydb">
      <parameter>
        <name>validationQuery</name>
        <value></value>
      </parameter>
      <parameter>
        <name>maxWait</name>
        <value>5000</value>
      </parameter>
      <parameter>
        <name>maxActive</name>
        <value>4</value>
      </parameter>
      <parameter>
        <name>password</name>
        <value>password</value>
      </parameter>
      <parameter>
        <name>url</name>
        <value>jdbc:postgresql://myserver:5432/mydb</value>
      </parameter>
      <parameter>
        <name>driverClassName</name>
        <value>org.postgresql.Driver</value>
      </parameter>
      <parameter>
        <name>maxIdle</name>
        <value>2</value>
      </parameter>
      <parameter>
        <name>username</name>
        <value>username</value>
      </parameter>
    </ResourceParams>

and some more from web.xml of my web-app

<resource-ref>
 <description>postgreSQL Datasource</description>
 <res-ref-name>jdbc/mydb</res-ref-name>
 <res-type>javax.sql.DataSource</res-type>
 <res-auth>Container</res-auth>
</resource-ref>

I access to the db with this servlet:

public class ProvaConnessioneAlDb extends HttpServlet {
  static final private String CONTENT_TYPE = "text/html";
  //Initialize global variables
  public void init() throws ServletException {
  }
  //Process the HTTP Post request
  public void doPost(HttpServletRequest request, HttpServletResponse
response) throws ServletException, IOException {
    response.setContentType(CONTENT_TYPE);
    PrintWriter out = response.getWriter();


    out.println("<html>");
    out.println("<head><title>ProvaConnessioneAlDb</title></head>");
    out.println("<body>");
    out.println("<p>The servlet has received a POST. This is the
reply.</p>");
    out.println("</body></html>");
  }
  public void doGet(HttpServletRequest request, HttpServletResponse
response) throws ServletException, IOException {
    response.setContentType(CONTENT_TYPE);
    PrintWriter out = response.getWriter();
    String foo=new String();


 try {

   Context initContext = new InitialContext();
   log("initcontext");
   if(initContext == null )
            throw new Exception("Boom - No Context");


  Context envContext  = (Context)initContext.lookup("java:/comp/env");
 log("envcontext");
  if(envContext == null )
         throw new Exception("Boom - No Context");

   DataSource ds = (DataSource)envContext.lookup("/jdbc/mydb");
  log("datasource");
   if (ds != null) {
     log("if");
     log("Ciao "+ ds.toString());
     Connection conn = ds.getConnection();
     log("connection");
     if(conn != null)  {
       foo = "Got Connection "+conn.toString();
     }
    conn.close();

 }
 } catch (SQLException e) {
   e.printStackTrace(out);
   out.println("<p>"+e.toString()+"</p>");
 }catch (NamingException e) {

out.println("<p>"+e.toString()+"</p>");
 }
 catch (Exception e){
e.printStackTrace(out);
out.println("<p>"+e.toString()+"</p>");
 }
    out.println("<html>");
    out.println("<head><title>ProvaConnessioneAlDb</title></head>");
    out.println("<body>");
    out.println("<p>The servlet has received a GET. This is the
reply.</p>");
    out.println("<p>"+foo+"</p>");
    out.println("</body></html>");
  }
  //Clean up resources
  public void destroy() {
  }
}

and this is the localhost_myapp.txt log

2002-11-21 10:01:46 StandardContext[/myapp]:  Mapped to servlet
'provaconnessionealdb' with servlet path '/provaconnessionealdb' and path
info 'null' and update=true
2002-11-21 10:01:46 provaconnessionealdb: initcontext
2002-11-21 10:01:46 provaconnessionealdb: envcontext
2002-11-21 10:01:46 provaconnessionealdb: datasource
2002-11-21 10:01:46 provaconnessionealdb: if
2002-11-21 10:01:46 provaconnessionealdb: Ciao
org.apache.commons.dbcp.BasicDataSource@dd75a4






Re: Problem with postgresql tomcat datasource

From
Date:
- Your pg-db needs to be accepting database connections, set tcpip to yes in your postgres.conf.
- Check that your pg_hba.conf is set correctly.
- Check that your any packetfilter or iptables is functioning correctly.
- Set <MaxIdle> to a relatively high level compare to <MaxActive>, <MaxActive is the actual number of tcp/ip
connectionsopenend to your pg-database. <MaxIdle> is the number of open database connections your web applications can
handle.
 
- Never call connection.close(). The DataSource implementation should be handling that. 
- Be sure to have defined the <resource> in the right place.