Stefan,

this isn't really related to the JayBird driver, but wouldn't it be
safer
to put close() statements in a finally block, so they are called even
when there was raised an exception?

For example:

try {
Connection c = ds.getConnection();
String sql = "SELECT USER_ID FROM US_USER WHERE USERNAME = ?;";
PreparedStatement ps = c.prepareStatement(sql);
ps.setString(1, "smue");
ResultSet rs = ps.executeQuery();
if (rs.next()) {
write("next() succeeded...");
}
} catch (SQLException e) {
write(e.getMessage());
} finally {
try {
if (rs!=null) {
rs.close();
}
if (ps!=null) {
ps.close();
}
if (c!=null) {
c.close();
}
} catch (SQLException e) {
.... something went wrong when closing result sets or the
connection
}
}

Regards,
Thomas Steinmaurer
IB LogManager 2.1 - The Logging/Auditing Tool for InterBase and
Firebird
http://www.iblogmanager.com

--- In Firebird-Java@y..., Stefan Mühlemann <smue@o...> wrote:
> Hello,
>
> > One thing you have to do with pooling is be sure to close all
statements and
> > result sets on a connection.
> That's what i do...
> try {
> Connection c = ds.getConnection();
> String sql = "SELECT USER_ID FROM US_USER WHERE USERNAME = ?;";
> PreparedStatement ps = c.prepareStatement(sql);
> ps.setString(1, "smue");
> ResultSet rs = ps.executeQuery();
> if (rs.next()) {
> write("next() succeeded...");
> }
> rs.close();
> ps.close();
> c.close();
> }
> catch (SQLException e) {
> write(e.getMessage());
> }
>
> > In a non-pooling environment closing a
> > connection closes all result sets and statements. If you close a
connection
> > in a pooling environment the connection is not actually closed
but returned
> > to the pool, so open statements and results sets survive.
> >
> > This will eat up all the available connections. To keep it from
tying up
> > your entire system you can set the number of open connections to
a non-zero
> > number. That way only Firebird will run out of resources rather
than your
> > system.
> >
> > The real fix is to go through your code and find all instances of
closing a
> > connection and add the code to explicitly close the statements
and result
> > sets used by the connection before closing the connection itself.
> >
>
> I don't agree with you.
> After executing the above code i do not have any open statements or
> resultsets.
> The connection returns to the pool and after idleTimeout in
> ConnectionPool is gone, the pool tries to return the connection to
> firebird (this is where the problem is, the connection is not
properly
> returned to the firebird engine).
> The connectionpool immediately tries to fetch a new connection
(because
> minSize is set to >0 and returns it again after idleTimeout is gone.
> Even if i do not use any connections they are created (because
minSize)
> and destroyed again (because idleTimeout).
> You can try to create a FBWrappingDataSource with idleTimeout set
to
> 1000 ms. After opening and closing a connection (you have to do
this
> only once for initialisation), your firebird-server will after 10
> seconds have 10 open connections!!
> I don't think that's correct.
>
> Greetings
>
> Stefan