Re: Pooling Prepared Statements - Mailing list pgsql-jdbc

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