Re: Postgres Issue with JDBC

Hi Tony

Thanks for your prompt Reply.Could you pease guide me in closing the
connection without affecting the functioning of my application elsewhere.
what should I do??I am giving the step by step procedure followed.please
mark against the step where I shoudl close the connection and how I shoudl
close the correct connection .
1)The JSP Page sends a request
2)The Control Servlet understands the Request retrives a Connection from the
Connection class and calls teh Particular Java Class and passes the
Connection Object as a parameter.
3)The Java Class executes the Queries and sends back the Result or ResultSet
to the Control Servlet
4) The Control Servlet now makes a call to the ConnectionManager to Free the
Connection .(but since the Result Set still has values Connection is not
freed).
5)The JSp Page retrieves the values from the Request Object and Displays the
values .

IN between these steps where can I close the connection and whne .If I am to
close the connection in a JSP page how will I sned the correct Connection
Object to the JSP page and how will I close it in the JSP Page.
Kindly help me out.
Thanks once again for your Prompt reply

Best Regards
John
----- Original Message -----
From: "Tony Grant"
To: "John Kunchandy"
Sent: Monday, May 05 2003 2:35 PM
Subject: Re: [JDBC] Postgres Issue with JDBC


: On Mon, 2003-05-05 at 10:41, John Kunchandy wrote:
:
: > 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
:
: Bad JSP design - nothing to do with PostgreSQL..
:
: </html>
: <%
: connection_name.close();
: Connconnection_name.close();
: %>
:
: I show I do it
:
: Cheers
:
: Tony Grant
: --
============================================================================
===
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