Thread: where to close statement ?
Hi.
Imagine the foolowing situation: i got one connection, and i have several
preparedStatements to execute, each with different parameters. Is is good
practice to close the connection in a finally() statement. But what about
statements and resultset ? Is there an advantage to close them explicitely :
-in the finally statement, before the connection.close() (see code A) ?
-in the try statement, between each statement execution for the resultset, and
between each redefinition for each preparedStatement (see code B) ?
code A:
Connection con=null;
PreparedStatement stmt=null;
Resulset rs=null;
try {
stmt=...
stmt.set...
rs=stmt.execute();
stmt.set...
rs=stmt.execute();
stmt=...
stmt.set...
rs=stmt.execute();
stmt.set...
rs=stmt.execute();
} finally {
rs.close();
stmt.close();
con.close()
}
code B:
Connection con=null;
try {
PreparedStatement stmt=...
stmt.set...
Resulset rs=stmt.execute();
rs.close();
stmt.set...
rs=stmt.execute();
rs.close();
stmt.close();
stmt=...
stmt.set...
rs=stmt.execute();
rs.close();
stmt.set...
rs=stmt.execute();
rs.close();
stmt;close();
} finally {
con.close()
}
--
Guillaume Rousse
Iremia - Universit� de la R�union
Sleep doesn't exists. Just lack of cafeine.
I test each one, and if not null, then call close, ie:
if(rs!=null) rs.close();
This allows for the statement to be open, but the failure being with the
ResultSet.
Peter
--
Peter Mount
Enterprise Support
Maidstone Borough Council
Any views stated are my own, and not those of Maidstone Borough Council.
-----Original Message-----
From: Guillaume Rousse [mailto:Guillaume.Rousse@univ-reunion.fr]
Sent: Tuesday, April 18, 2000 1:25 PM
To: pgsql-interfaces@postgresql.org
Subject: [INTERFACES] where to close statement ?
Hi.
Imagine the foolowing situation: i got one connection, and i have
several
preparedStatements to execute, each with different parameters. Is is
good
practice to close the connection in a finally() statement. But what
about
statements and resultset ? Is there an advantage to close them
explicitely :
-in the finally statement, before the connection.close() (see code A) ?
-in the try statement, between each statement execution for the
resultset, and
between each redefinition for each preparedStatement (see code B) ?
code A:
Connection con=null;
PreparedStatement stmt=null;
Resulset rs=null;
try {
stmt=...
stmt.set...
rs=stmt.execute();
stmt.set...
rs=stmt.execute();
stmt=...
stmt.set...
rs=stmt.execute();
stmt.set...
rs=stmt.execute();
} finally {
rs.close();
stmt.close();
con.close()
}
code B:
Connection con=null;
try {
PreparedStatement stmt=...
stmt.set...
Resulset rs=stmt.execute();
rs.close();
stmt.set...
rs=stmt.execute();
rs.close();
stmt.close();
stmt=...
stmt.set...
rs=stmt.execute();
rs.close();
stmt.set...
rs=stmt.execute();
rs.close();
stmt;close();
} finally {
con.close()
}
--
Guillaume Rousse
Iremia - Université de la Réunion
Sleep doesn't exists. Just lack of cafeine.
All right, that's another way to close them, but it doesn't answer to the
question : where to close ?
Le mar, 18 avr 2000, Peter Mount a �crit :
> I test each one, and if not null, then call close, ie:
>
> if(rs!=null) rs.close();
>
> This allows for the statement to be open, but the failure being with the
> ResultSet.
>
> Peter
>
> --
> Peter Mount
> Enterprise Support
> Maidstone Borough Council
> Any views stated are my own, and not those of Maidstone Borough Council.
>
>
>
> -----Original Message-----
> From: Guillaume Rousse [mailto:Guillaume.Rousse@univ-reunion.fr]
> Sent: Tuesday, April 18, 2000 1:25 PM
> To: pgsql-interfaces@postgresql.org
> Subject: [INTERFACES] where to close statement ?
>
>
> Hi.
> Imagine the foolowing situation: i got one connection, and i have
> several
> preparedStatements to execute, each with different parameters. Is is
> good
> practice to close the connection in a finally() statement. But what
> about
> statements and resultset ? Is there an advantage to close them
> explicitely :
> -in the finally statement, before the connection.close() (see code A) ?
> -in the try statement, between each statement execution for the
> resultset, and
> between each redefinition for each preparedStatement (see code B) ?
>
> code A:
> Connection con=null;
> PreparedStatement stmt=null;
> Resulset rs=null;
>
> try {
> stmt=...
> stmt.set...
> rs=stmt.execute();
> stmt.set...
> rs=stmt.execute();
>
> stmt=...
> stmt.set...
> rs=stmt.execute();
> stmt.set...
> rs=stmt.execute();
> } finally {
> rs.close();
> stmt.close();
> con.close()
> }
>
> code B:
> Connection con=null;
>
> try {
> PreparedStatement stmt=...
> stmt.set...
> Resulset rs=stmt.execute();
> rs.close();
> stmt.set...
> rs=stmt.execute();
> rs.close();
> stmt.close();
>
> stmt=...
> stmt.set...
> rs=stmt.execute();
> rs.close();
> stmt.set...
> rs=stmt.execute();
> rs.close();
> stmt;close();
>
> } finally {
> con.close()
> }
> --
> Guillaume Rousse
> Iremia - Universit� de la R�union
>
> Sleep doesn't exists. Just lack of cafeine.
--
Guillaume Rousse
Iremia - Universit� de la R�union
Sleep doesn't exists. Just lack of cafeine.
Ah, forgot that bit. I put them under finally as it always gets run,
although you have to wrap them in a try{} win finally if the parent
method doesn't throw SQLException.
Peter
--
Peter Mount
Enterprise Support
Maidstone Borough Council
Any views stated are my own, and not those of Maidstone Borough Council.
-----Original Message-----
From: Guillaume Rousse [mailto:Guillaume.Rousse@univ-reunion.fr]
Sent: Tuesday, April 18, 2000 2:57 PM
To: pgsql-interfaces@postgresql.org
Cc: petermount@it.maidstone.gov.uk
Subject: RE: [INTERFACES] where to close statement ?
All right, that's another way to close them, but it doesn't answer to
the
question : where to close ?
Le mar, 18 avr 2000, Peter Mount a écrit :
> I test each one, and if not null, then call close, ie:
>
> if(rs!=null) rs.close();
>
> This allows for the statement to be open, but the failure being with
the
> ResultSet.
>
> Peter
>
> --
> Peter Mount
> Enterprise Support
> Maidstone Borough Council
> Any views stated are my own, and not those of Maidstone Borough
Council.
>
>
>
> -----Original Message-----
> From: Guillaume Rousse [mailto:Guillaume.Rousse@univ-reunion.fr]
> Sent: Tuesday, April 18, 2000 1:25 PM
> To: pgsql-interfaces@postgresql.org
> Subject: [INTERFACES] where to close statement ?
>
>
> Hi.
> Imagine the foolowing situation: i got one connection, and i have
> several
> preparedStatements to execute, each with different parameters. Is is
> good
> practice to close the connection in a finally() statement. But what
> about
> statements and resultset ? Is there an advantage to close them
> explicitely :
> -in the finally statement, before the connection.close() (see code A)
?
> -in the try statement, between each statement execution for the
> resultset, and
> between each redefinition for each preparedStatement (see code B) ?
>
> code A:
> Connection con=null;
> PreparedStatement stmt=null;
> Resulset rs=null;
>
> try {
> stmt=...
> stmt.set...
> rs=stmt.execute();
> stmt.set...
> rs=stmt.execute();
>
> stmt=...
> stmt.set...
> rs=stmt.execute();
> stmt.set...
> rs=stmt.execute();
> } finally {
> rs.close();
> stmt.close();
> con.close()
> }
>
> code B:
> Connection con=null;
>
> try {
> PreparedStatement stmt=...
> stmt.set...
> Resulset rs=stmt.execute();
> rs.close();
> stmt.set...
> rs=stmt.execute();
> rs.close();
> stmt.close();
>
> stmt=...
> stmt.set...
> rs=stmt.execute();
> rs.close();
> stmt.set...
> rs=stmt.execute();
> rs.close();
> stmt;close();
>
> } finally {
> con.close()
> }
> --
> Guillaume Rousse
> Iremia - Université de la Réunion
>
> Sleep doesn't exists. Just lack of cafeine.
--
Guillaume Rousse
Iremia - Université de la Réunion
Sleep doesn't exists. Just lack of cafeine.
All right, we're near :-)
In this case, is there a benefice to close first resultset, then the statement,
then the connection over just closing the connection directly ?
Le mar, 18 avr 2000, Peter Mount a �crit :
> Ah, forgot that bit. I put them under finally as it always gets run,
> although you have to wrap them in a try{} win finally if the parent
> method doesn't throw SQLException.
>
> Peter
>
> --
> Peter Mount
> Enterprise Support
> Maidstone Borough Council
> Any views stated are my own, and not those of Maidstone Borough Council.
>
>
>
> -----Original Message-----
> From: Guillaume Rousse [mailto:Guillaume.Rousse@univ-reunion.fr]
> Sent: Tuesday, April 18, 2000 2:57 PM
> To: pgsql-interfaces@postgresql.org
> Cc: petermount@it.maidstone.gov.uk
> Subject: RE: [INTERFACES] where to close statement ?
>
>
> All right, that's another way to close them, but it doesn't answer to
> the
> question : where to close ?
>
> Le mar, 18 avr 2000, Peter Mount a �crit :
> > I test each one, and if not null, then call close, ie:
> >
> > if(rs!=null) rs.close();
> >
> > This allows for the statement to be open, but the failure being with
> the
> > ResultSet.
> >
> > Peter
> >
> > --
> > Peter Mount
> > Enterprise Support
> > Maidstone Borough Council
> > Any views stated are my own, and not those of Maidstone Borough
> Council.
> >
> >
> >
> > -----Original Message-----
> > From: Guillaume Rousse [mailto:Guillaume.Rousse@univ-reunion.fr]
> > Sent: Tuesday, April 18, 2000 1:25 PM
> > To: pgsql-interfaces@postgresql.org
> > Subject: [INTERFACES] where to close statement ?
> >
> >
> > Hi.
> > Imagine the foolowing situation: i got one connection, and i have
> > several
> > preparedStatements to execute, each with different parameters. Is is
> > good
> > practice to close the connection in a finally() statement. But what
> > about
> > statements and resultset ? Is there an advantage to close them
> > explicitely :
> > -in the finally statement, before the connection.close() (see code A)
> ?
> > -in the try statement, between each statement execution for the
> > resultset, and
> > between each redefinition for each preparedStatement (see code B) ?
> >
> > code A:
> > Connection con=null;
> > PreparedStatement stmt=null;
> > Resulset rs=null;
> >
> > try {
> > stmt=...
> > stmt.set...
> > rs=stmt.execute();
> > stmt.set...
> > rs=stmt.execute();
> >
> > stmt=...
> > stmt.set...
> > rs=stmt.execute();
> > stmt.set...
> > rs=stmt.execute();
> > } finally {
> > rs.close();
> > stmt.close();
> > con.close()
> > }
> >
> > code B:
> > Connection con=null;
> >
> > try {
> > PreparedStatement stmt=...
> > stmt.set...
> > Resulset rs=stmt.execute();
> > rs.close();
> > stmt.set...
> > rs=stmt.execute();
> > rs.close();
> > stmt.close();
> >
> > stmt=...
> > stmt.set...
> > rs=stmt.execute();
> > rs.close();
> > stmt.set...
> > rs=stmt.execute();
> > rs.close();
> > stmt;close();
> >
> > } finally {
> > con.close()
> > }
> > --
> > Guillaume Rousse
> > Iremia - Universit� de la R�union
> >
> > Sleep doesn't exists. Just lack of cafeine.
> --
> Guillaume Rousse
> Iremia - Universit� de la R�union
>
> Sleep doesn't exists. Just lack of cafeine.
--
Guillaume Rousse
Iremia - Universit� de la R�union
Sleep doesn't exists. Just lack of cafeine.
With our current implementation, no there would be no benefit.
However, it's neater to close then in the order ResultSet, Statement
then Connection, as thats closer to the standard.
Tip: If your application is running for long periods of time, it's
better to close everything manually, and even possibly calling the
connection's finalize() method. This is because the garbage collector is
not guaranteed to run often, and you can end up with lots of unused
connections to the backend.
Peter
--
Peter Mount
Enterprise Support
Maidstone Borough Council
Any views stated are my own, and not those of Maidstone Borough Council.
-----Original Message-----
From: Guillaume Rousse [mailto:Guillaume.Rousse@univ-reunion.fr]
Sent: Tuesday, April 18, 2000 4:14 PM
To: pgsql-interfaces@postgresql.org
Cc: Peter Mount
Subject: RE: [INTERFACES] where to close statement ?
All right, we're near :-)
In this case, is there a benefice to close first resultset, then the
statement,
then the connection over just closing the connection directly ?
Le mar, 18 avr 2000, Peter Mount a écrit :
> Ah, forgot that bit. I put them under finally as it always gets run,
> although you have to wrap them in a try{} win finally if the parent
> method doesn't throw SQLException.
>
> Peter
>
> --
> Peter Mount
> Enterprise Support
> Maidstone Borough Council
> Any views stated are my own, and not those of Maidstone Borough
Council.
>
>
>
> -----Original Message-----
> From: Guillaume Rousse [mailto:Guillaume.Rousse@univ-reunion.fr]
> Sent: Tuesday, April 18, 2000 2:57 PM
> To: pgsql-interfaces@postgresql.org
> Cc: petermount@it.maidstone.gov.uk
> Subject: RE: [INTERFACES] where to close statement ?
>
>
> All right, that's another way to close them, but it doesn't answer to
> the
> question : where to close ?
>
> Le mar, 18 avr 2000, Peter Mount a écrit :
> > I test each one, and if not null, then call close, ie:
> >
> > if(rs!=null) rs.close();
> >
> > This allows for the statement to be open, but the failure being with
> the
> > ResultSet.
> >
> > Peter
> >
> > --
> > Peter Mount
> > Enterprise Support
> > Maidstone Borough Council
> > Any views stated are my own, and not those of Maidstone Borough
> Council.
> >
> >
> >
> > -----Original Message-----
> > From: Guillaume Rousse [mailto:Guillaume.Rousse@univ-reunion.fr]
> > Sent: Tuesday, April 18, 2000 1:25 PM
> > To: pgsql-interfaces@postgresql.org
> > Subject: [INTERFACES] where to close statement ?
> >
> >
> > Hi.
> > Imagine the foolowing situation: i got one connection, and i have
> > several
> > preparedStatements to execute, each with different parameters. Is is
> > good
> > practice to close the connection in a finally() statement. But what
> > about
> > statements and resultset ? Is there an advantage to close them
> > explicitely :
> > -in the finally statement, before the connection.close() (see code
A)
> ?
> > -in the try statement, between each statement execution for the
> > resultset, and
> > between each redefinition for each preparedStatement (see code B) ?
> >
> > code A:
> > Connection con=null;
> > PreparedStatement stmt=null;
> > Resulset rs=null;
> >
> > try {
> > stmt=...
> > stmt.set...
> > rs=stmt.execute();
> > stmt.set...
> > rs=stmt.execute();
> >
> > stmt=...
> > stmt.set...
> > rs=stmt.execute();
> > stmt.set...
> > rs=stmt.execute();
> > } finally {
> > rs.close();
> > stmt.close();
> > con.close()
> > }
> >
> > code B:
> > Connection con=null;
> >
> > try {
> > PreparedStatement stmt=...
> > stmt.set...
> > Resulset rs=stmt.execute();
> > rs.close();
> > stmt.set...
> > rs=stmt.execute();
> > rs.close();
> > stmt.close();
> >
> > stmt=...
> > stmt.set...
> > rs=stmt.execute();
> > rs.close();
> > stmt.set...
> > rs=stmt.execute();
> > rs.close();
> > stmt;close();
> >
> > } finally {
> > con.close()
> > }
> > --
> > Guillaume Rousse
> > Iremia - Université de la Réunion
> >
> > Sleep doesn't exists. Just lack of cafeine.
> --
> Guillaume Rousse
> Iremia - Université de la Réunion
>
> Sleep doesn't exists. Just lack of cafeine.
--
Guillaume Rousse
Iremia - Université de la Réunion
Sleep doesn't exists. Just lack of cafeine.
Peter Mount wrote:
> Ah, forgot that bit. I put them under finally as it always gets run,
> although you have to wrap them in a try{} win finally if the parent
> method doesn't throw SQLException.
>
> Peter
>
The problem with that is that close() itself can throw a SQLException so
you have to wrap it twice.
Le mar, 18 avr 2000, vous avez �crit :
> Peter Mount wrote:
>
> > Ah, forgot that bit. I put them under finally as it always gets run,
> > although you have to wrap them in a try{} win finally if the parent
> > method doesn't throw SQLException.
> >
> > Peter
> >
>
> The problem with that is that close() itself can throw a SQLException so
> you have to wrap it twice.
so we have either :
finally {
try {
if (conn!=null) conn.close();
} catch (SQLException ignore) {}
}
or eventually :
inally {
try {
conn.close();
} catch (Exception ignore) {}
}
The second also deals with NullPointerException
--
Guillaume Rousse
Iremia - Universit� de la R�union
Sleep doesn't exists. Just lack of cafeine.
I wrap the closes within the finally() block because of this:
try {... code ...
} catch(SQLException e) {... handle exception here ...
} finally {try { if(rs!=null) rs.close(); if(st!=null) st.close();} catch(SQLException e1) {}
}
Peter
--
Peter Mount
Enterprise Support
Maidstone Borough Council
Any views stated are my own, and not those of Maidstone Borough Council.
-----Original Message-----
From: Joseph Shraibman [mailto:jks@p1.selectacast.net]
Sent: Tuesday, April 18, 2000 7:28 PM
To: Peter Mount
Cc: 'Guillaume Rousse'; pgsql-interfaces@postgresql.org
Subject: Re: [INTERFACES] where to close statement ?
Peter Mount wrote:
> Ah, forgot that bit. I put them under finally as it always gets run,
> although you have to wrap them in a try{} win finally if the parent
> method doesn't throw SQLException.
>
> Peter
>
The problem with that is that close() itself can throw a SQLException so
you have to wrap it twice.