Implications of not closing db connection?

[vivienne wykes]

Hi All,

Could anyone explain simply the implications of not closing a
resultset,statement, and connection as shown below or provide a link to an
explanation ?

I had a problem on a shared server (resin) and I think my webapp was causing
an issue for other server users by not closing the db connection.

Is it a case of that there is certain amount of connections available to a
server and not closing the connection ?

Any guidance appreciated.

Regards


Code below

<%
//-------------------make connection-------------------------------------

Class.forName("org.gjt.mm.mysql.Driver");
java.sql.Connection connection =
java.sql.DriverManager.getConnection("jdbc:mysql://localhost/shop");
java.sql.Statement statement = connection.createStatement();%>
&nbsp;</td>
</tr>
<tr valign="top" class="footer">
<td height="25" colspan="2" class="bodytext"> <%
if(session.getAttribute("welcome") != null) { %> <h3> <%=
session.getAttribute("welcome") %> </h3>
<%String weddingid=request.getParameter("weddingid");
if (!weddingid.equals("paulonly")){%>
Click <a href="<%=
response.encodeURL("shop-products.jsp?weddingid="+weddingid) %>">here</a>
to view your wedding pictures
<%}
else {%>
Click <a href="<%=
response.encodeURL("pauls_jedi_interface.jsp") %>">here</a>
to enter JEDI
<%}
} else { %>
Please log in below to continue...
<form action="<%= response.encodeURL("db-result.jsp") %>"
method="get">
<table class="bodytext">
<tr>
<%java.sql.ResultSet RS3 = statement.executeQuery("SELECT
* FROM items ORDER BY weddingid");%>
<td nowrap>Select Wedding:</td>
<td><select name="weddings_to_view" class="dropdown"
onChange="MM_jumpMenu('parent',this,0)">
<option selected><%=selected%></option>
<%while(RS3.next()){//open while
String weddingid2 = RS3.getString("weddingid");
if (!last_wedding_id.equals(weddingid2)){// open if%>
<option
value="<%=response.encodeURL("db-login.jsp?selected="+weddingid2)%>"><%=wedd
ingid2%></option>
<%} //close if
last_wedding_id = weddingid2;
}//close while

//-------------------close result set close statement close
connection-------------------------------------

RS3.close();
statement.close();
connection.close();%>

 

[Paul Lutus]

Hi All,

Could anyone explain simply the implications of not closing a
resultset,statement, and connection as shown below or provide a link to an
explanation ?

Gee, why not save everyone a lot of trouble and close the connection?
Database connections are a limited resource, therefore conpetently written
software closes these connections as soon as possible. End of story.

I had a problem on a shared server (resin) and I think my webapp was
causing an issue for other server users by not closing the db connection.

Surprise, surprise. You could also open file handles until you brought the
entire system down. But this is a VBI of the same order.

Is it a case of that there is certain amount of connections available to a
server and not closing the connection ?

Yes, that would be the basic outline. This is like asking, "How can I be out
of money? I still have checks in my checkbook!"

 

[vivienne wykes]

Hi Paul,

Thanks for your time replying.

Much appreciated. This question may seem to have an obvious answer .....but
only if you know the answer.

Thanks again

 

[zoopy]

Surprise, surprise. You could also open file handles until you brought the
entire system down. But this is a VBI of the same order.

What is a VBI?

 

[Paul Lutus]

What is a VBI?

Very Bad Idea. It's a Usenet acronym.

 

[zoopy]

Very Bad Idea. It's a Usenet acronym.

Searched Google with "acronym VBI" but first pages came up with nothing
appropriate (Vertical Blanking Interval, vertebrobasilar insuffiency).

 

[vivienne wykes]

Very Batronising Ixplanation

Regards
Viv

Searched Google with "acronym VBI" but first pages came up with nothing
appropriate (Vertical Blanking Interval, vertebrobasilar insuffiency).

 

[zoopy]

Very Batronising Ixplanation

Regards
Viv

What do you mean?

 

[Chris Smith]

I had a problem on a shared server (resin) and I think my webapp was causing
an issue for other server users by not closing the db connection.

Is it a case of that there is certain amount of connections available to a
server and not closing the connection ?

Yes you should close your connections.

As for the exact effects, that depends on the database. Database are
*probably* limited, but not necessarily, except in the sense that
database connections are objects, and you can always run out of memory
with too many objects of any kind. If connections are limited, they may
be limited only by available resources on the box - for example many
UNIX databases will start a process per open database connection, and
the process table in the kernel can be overrun; or they may be
artificially limited by the database backend -- in which case the limit
may be much lower.

In any case, it certainly seems likely that a web app that doesn't close
its database connections was causing these problems. It would require
more investigation to *prove* that.

--
www.designacourse.com
The Easiest Way to Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation

 

[Andrew Thompson]

...
What do you mean?

<http://google.com/groups?th=36027af42a0c60cc#link2> ? ;-)

 

[zoopy]

<http://google.com/groups?th=36027af42a0c60cc#link2> ? ;-)

Possibly. Or link6... Just wanted Viv to clear that up.

 

[vivienne wykes]

Thanks Chris,

I am just trying to fix the problem with limited information from the hosts
(Lunarpages).
The error I got from them was that I had an applet re-initialising
constantly. I do not have any applets on my site. I then went through my
code for likely errors and noticed that I had not closed the db connection
on 2 JSPs and one Bean..... I would have liked to investigate the error
further but do not have access to server info.
This is my first JSP/Bean site and am enjoying a steep learning curve.


All the best.

 

[Scott Ellsworth]

Hi All,

Could anyone explain simply the implications of not closing a
resultset,statement, and connection as shown below or provide a link to an
explanation ?

Simply put, connections, like files, are limited resources. They may be
limited by process, by server, by vm, or by phase of the moon, but they
_are_ limited. Even worse - a connection may be kept open even after
the VM exits, as it may be limited by the db server. Thus, if you quit
with a connection still open, the db may not close it automatically, and
thus future users may not be able to connect.

Memory is also a limited resource, but it is one controlled almost
entirely by the vm, so if your vm process exits, all of the memory is
returned to the OS. DB connections, as mentioned above, may well be
controlled by the dbms, and thus even your process ending may not give
them back.

Aside: the underlying window pointer used by a JFrame is often a limited
resource as well, and once the OS runs out of them, all hell breaks
loose. Thus, calling dispose() on your jframes can be very important.
Most of the time, though, those window pointers are returned when the vm
exits if not before. Still, why be a poor citizen.

The exact details of the consequences of not closing an RS, Statement,
or Connection depend on the OS, the VM, and the DBMS, but in general, it
is a very bad thing. Oracle 7/Solaris combined with the Windows VM is a
particularly unfun combination. We have had Oracle instances that
needed rebooting by the dba to recover from dropped connections.

Scott

 

[vivienne wykes]

Thanks Scott,

All this information will assimilate in between long days enjoying the
Edinburgh Festival.

Time out to enjoy a holliday at home...

Cheers