Postgres Issue with JDBC - Mailing list pgsql-jdbc

Hi All

I am a novice in using Postgres.I have been mainly using Postgres with Zope
installed on Mandrake Linux.PLEASE SPARE A LITTLE TIME TO READ MY MAIL since
it is a bit long.
I am currently having some Major difficulties in running my application
developed in java using Postgres as the back end.
I will explain the system I am using.The main application is devloped in
Java on Mandrake Linux8.2 <database is Postgres 7.2.2 and the pgjdbc2.jar
Driver.
My application is using connection pooling to get the connection from the
Connection Pool Class(This is my own class DBConnection() with a few
functions defined --
 getConnection()
freeConnection(objConnection)
)

 and then using the connection to execute the query.
Let me give a brief description about the application architecture.
I am trying to implement MVC in a way.
I have Java class files(MODEL) which are actually doing the actual select
and insert queries.
I have a control Servlet(CONTROLLER) which is the Servlet that is called by
all the JSp Pages .The servlet gets a connection form the Pool in the init()
and  then calls the function based on the request from the JSP Page and
passes the connection object to the Class File which does the query.

========================================================================
public RequestDispatcher objRD=null;
public void init(ServletConfig config) throws ServletException
    {
        super.init(config);
        objConnMgr = new DBConnection();
    }
 public void doPost(HttpServletRequest objRequest, HttpServletResponse
objResponse) throws ServletException, IOException
    {
     objConnection  = objConnMgr.getConnection();
     }

 public ArrayList someFunction()
    {
     ..........................
       ........................
    //calls to the functions in the MODEL java classes for:
   //select * from table
  //insert into table
 // delete from table
 // update table where
   ArrayList objArrayList = objAdmin.showAll();

   objRequest.setAttribute("COUNT", objArrayList.get(0));
   objRequest.setAttribute("USERS", objArrayList.get(1));
   objRequest.setAttribute("PageNum", objArrayList.get(2));

   objConnMgr.freeConnection(objConnection);
   objRD = getServletContext().getRequestDispatcher("/Admin/index.jsp");
   objRD.forward(objRequest, objResponse);
    }
=========================================================================

NOw the jsp page requests the values from the Request Object and does the
coresponding action.
The Problem:

The Server shows too many Clients Exception everytimw within half an hour or
two of starting Tomcat and Postgres.
Althought the number of users accessing the Site would be at the most 30
concurrent users.
THE Connection doesnt seem to be getting closed when I call the connection
Free Manager.The reason would be that the ResultSet is not null since I may
have done a select.But in this case the JSP page is dispalyed by requesting
the ResultSet
and then looping through.BUt I am not able to clse the cnnectionnhere either
as I do not know if that Connection has been threaded and is being used by
any other function.

SO in Short I am not able to close the connection in the class Files(MODEL)
because the Result set is to be sent back after the select .
I am not able to close the connection in the Control Servlet( CONTROL)
because I have to send the Result Set to the jSP page to be shown.So even if
I write connection.close() it will nto since the Result Se is not empty.
I am not being able to close the Connection in the JSP page(VIEW) since the
connection Object cannot be sent forward foimr the Servlet since I do not
know if the Object has been threaded and a thread is being used by another
function.CO I cannot close it in the JSP Page either.Also if I wanted to
close a particular connection I cannot gewt the connection Object by knwoing
the ResulSert obj.I can only get the statement.IN the JSP Page I am setting
the ResultSE and Statement Object to null.But stil I cannot close the
connection.

So in short the number of connections keep increasing and the Sytem crashes
or else Postgers shows an error of Too many Clients and too Many
Connections.Increasing the number oc connections is not a solution since I
have kept it at 256 but still with 5 users in a few hours the application
comes back to the same situation.

I am sure you may have come across such a problem may times.Please help me
out and guide me  as to how I should go about this isse .Where exactly to
close the connection.How to pass the cnnection Object to the JSP page and
make sure the right connection is closed.Or if it is POstgres which somehow
is holding connections persistently then how do I stop it.Thank s in
Advancew for reading this whole letter .Please point me in the right
direction.

Thanks and Best Regards
John Kunchandy



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.476 / Virus Database: 273 - Release Date: 4/24/2003