Re: Pooling Prepared Statements

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(at)postgresql(dot)org
> [mailto:pgsql-jdbc-owner(at)postgresql(dot)org]On Behalf Of João Paulo Caldas
> Ribeiro
> Sent: Thursday, August 29, 2002 7:45 PM
> To: G.Nagarajan
> Cc: pgsql-jdbc(at)postgresql(dot)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(at)postgresql(dot)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