Re: Pooling Prepared Statements - Mailing list pgsql-jdbc

I forgot to add, the resultsets are from different
statements. the code is actually

 getConnection()
 open Rs1 using separate statement

 while( Rs1.next() )
 {
    open Rs2 using another statement from same connection.
    while( Rs2.next() )
    {
      ...
    }
 }

This should give problems only when i reuse the same statement.
It means that my existing code will not give any problems.

Regards,
Nagarajan.

> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Dave Cramer
> Sent: Thursday, August 29, 2002 10:25 PM
> To: G.Nagarajan
> Cc: João Paulo Caldas Ribeiro;
> Subject: Re: [JDBC] Pooling Prepared Statements
>
>
> You are fortunate that this is an artifact of postgres.
>
> the spec says
>
> A ResultSet object is automatically closed when the Statement object
> that generated it is closed, re-executed, or used to retrieve the next
> result from a sequence of multiple results.
>
> Dave
> On Thu, 2002-08-29 at 14:52, G.Nagarajan wrote:
> > hi,
> > Thanks for your reply. Better I knew this early, else i would have
> > spent hours trying to understand what went wrong!
> >
> > I have some more questions.Is this a restriction of postgres,
> > the database driver or JDBC?
> >
> > I also have lots of code like this:
> >
> > sql = "select deptno, name from dept"
> > rs = stmt.executeQuery( sql );
> > while( rs.next() )
> > {
> >   rs2 = "select empno, empname from emp where deptno = 1".
> >   while( rs2.next() )
> >   {
> >      // sometimes rs3..
> >   }
> >   rs2.close()
> > }
> > rs.close()
> >
> > they seem to work fine without any error messages. Here i am actually
> > having two resultsets open in the same connection, but on different
> > tables. Does it mean a bug waiting to occur at the right time?
> >
> >  So, i think for implementing the prepared statement cache, i have to
> > create the statements for each connection. something like
> >
> >  connection1  - statement1, statement2, statement3
> >  connection2  - statement1, statement2, statement3
> >  connection3  - statement1, statement2, statement3
> >
> >  Then wrap the connection in a class along with the prepared statement.
> > This would avoid the creation of the statement object but will still
> > use many connections.
> >
> > Regards,
> > Nagarajan.
> >
> > > -----Original Message-----
> > > From: pgsql-jdbc-owner@postgresql.org
> > > [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of João Paulo Caldas
> > > Ribeiro
> > > Sent: Thursday, August 29, 2002 7:45 PM
> > > To: G.Nagarajan
> > > Cc: pgsql-jdbc@postgresql.org
> > > Subject: Re: [JDBC] Pooling Prepared Statements
> > >
> > >
> > > Hi!
> > >
> > > Im sorry but: 1 connection -> 1 resultset at time.
> > > You can have many statement associated to a connection but when you
> > > execute the statemente you can only have 1 resultset associated to the
> > > connection at time.
> > > You can execute 2 statement using the same connection but you have to:
> > >
> > > -> execute statement1
> > > -> get the resultset1 and use it
> > > -> close resultset
> > > -> execute statement2
> > > -> get the resultset2 and use it
> > > -> close resultset
> > >
> > >
> > > If you still using the resultset1 when you execute the
> statement2 you'll
> > > destroy it.
> > > This is why i told to close the resultset.
> > > Rule is : Every time you finish using a statement or a
> resultset close it.
> > > Exception: if you want to keep a cache of statements you only
> close them
> > > when you remove them from the cache or before closing the
> dbconnection.
> > >
> > > If think you are trying to make a statement cache. Take a look to the
> > > Enhydra (www.enhydra.org) DBLayer. They use preparedstatement
> cache for
> > > every dbconnection.
> > >
> > > Regards.
> > > João Paulo Ribeiro
> > >
> > >
> > > G.Nagarajan wrote:
> > >
> > > >hi,
> > > >i am planning to implement a pooling system for Prepared Statements.
> > > >It will open a single connection during initialization and create
> > > >many prepared statements using this connection. The connection will
> > > >be always kept open so that the prepared statements are valid. To
> > > >speed up things, there may be more than one prepared statement for
> > > >the same query, ie,
> > > >
> > > >stmt1 = "select from table where field = ?";
> > > >stmt2 = "select from table where field = ?";
> > > >
> > > >Will there be any issues when two statements are executed for the
> > > >same table using the same connection? has anyone tried doing
> something
> > > >like this or is there any open source tool which can do this?
> > > >
> > > >This will greatly improve the efficiency of the system as most
> > > of the time,
> > > >the connections are used for executing the same statements.
> Pooling the
> > > >statements instead of the connection will save the processing needed
> > > >for creating the statement objects for every query.
> > > >
> > > >Regards,
> > > >Nagarajan.
> > > >
> > > >
> > > >---------------------------(end of
> broadcast)---------------------------
> > > >TIP 2: you can get off all lists at once with the unregister command
> > > >    (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > > --
> > > ------------------------------------------------------------------
> > > ----------
> > > MobiComp - Mobile Computing & Wireless Solutions
> > > phone: +351 253 305 250     fax: +351 253 305 251
> > > web: http://www.mobicomp.com
> > > ------------------------------------------------------------------
> > > ----------
> > >
> > >
> > >
> > >
> > >
> > > ---------------------------(end of
> broadcast)---------------------------
> > > TIP 5: Have you checked our extensive FAQ?
> > >
> > > http://www.postgresql.org/users-lounge/docs/faq.html
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
> >
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster