Re: Pooling Prepared Statements - Mailing list pgsql-jdbc

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