Thread: Impact of open ResultSets and PreparedStatements ?

Impact of open ResultSets and PreparedStatements ?

From
Antony Paul
Date:
Hi all,
    What will happen if I leave ResultSet and PreparedStatement not
closed and I use a connection pool which is not closing the ResultSet
and PreparedStatement. Will it cause resource leak in the server ?.
--
rgds
Antony Paul
http://www.geocities.com/antonypaul24/

Re: Impact of open ResultSets and PreparedStatements ?

From
Kris Jurka
Date:

On Tue, 26 Apr 2005, Antony Paul wrote:

>     What will happen if I leave ResultSet and PreparedStatement not
> closed and I use a connection pool which is not closing the ResultSet
> and PreparedStatement. Will it cause resource leak in the server ?.

It depends there are two types of PreparedStatements and two types of
ResultSets each one has a version that takes no permanent server resources
and a version that does.  A PreparedStatement may keep a permanent
prepared plan on the server and a ResultSet may be backed by a cursor
which also has server state.  Its possible you can get away with it, but
it's best to close everything.

Kris Jurka


Re: Impact of open ResultSets and PreparedStatements ?

From
Antony Paul
Date:
On 4/29/05, Kris Jurka <books@ejurka.com> wrote:
>
>
> On Tue, 26 Apr 2005, Antony Paul wrote:
>
> >     What will happen if I leave ResultSet and PreparedStatement not
> > closed and I use a connection pool which is not closing the ResultSet
> > and PreparedStatement. Will it cause resource leak in the server ?.
>
> It depends there are two types of PreparedStatements and two types of
> ResultSets each one has a version that takes no permanent server resources
> and a version that does.  A PreparedStatement may keep a permanent
> prepared plan on the server and a ResultSet may be backed by a cursor
> which also has server state.  Its possible you can get away with it, but
> it's best to close everything.
>
> Kris Jurka
>
>

Thanks for the reply.
I am using PostgreSQL 7.3 (server and JDBC driver) and no stored
procedures are used but uses the built in functions. I know that in
this version PreparedStatements are not kept in the server. What about
the ResultSet ?. Does all the data is transferred to application right
after the query is executed or only a predefined no of records are
returned while going through the rs.next() call ?.

--
rgds
Antony Paul
http://www.geocities.com/antonypaul24/

Re: Impact of open ResultSets and PreparedStatements ?

From
Kris Jurka
Date:

On Fri, 29 Apr 2005, Antony Paul wrote:

> I am using PostgreSQL 7.3 (server and JDBC driver) and no stored
> procedures are used but uses the built in functions. I know that in
> this version PreparedStatements are not kept in the server. What about
> the ResultSet ?. Does all the data is transferred to application right
> after the query is executed or only a predefined no of records are
> returned while going through the rs.next() call ?.
>

In 7.3 all ResultSets fetch the entire result immediately leaving
nothing on the server.

Kris Jurka