ODBC issue

[toyt78]

I have a JSP page that keeps getting an ODBC error on my Access 2000 database after I try and pull up the page on my Windows 2000 server that is using the JSP engine on our Cold Fusion MX web server:

[Microsoft][ODBC Microsoft Acess Driver] System resource exceeded.

This message goes away about once or twice a week and then comes back for several days. So the page is only working about 2 days a week and the rest of the days I get that ODBC error message.
From all my research in books it says our server ran out of memory because I am not closing my database connections correctly or I am creating objects and not setting them to Nothing.
I tried the below attempt where I thought I was doing everything correctly but still get the ODBC error several times a week.

<%@ page import="java.sql.*"%>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); %>
 
<form name="myNamehere" action="myactionpage.jsp" method="get">
<table>
<tr>
<td>City:<input type="Text" name="city" size="18"></td>
<td>State:<input type="Text" name="state" size="18"></td> 
</tr>
<tr>
<td colspan="2">Select Person:
 
<%
Connection connection = null;
Statement statement = null;
ResultSet resultset = null;
try 
{
connection = DriverManager.getConnection("jdbc:odbc:myDataSource", "", "");
statement = connection.createStatement();
resultset = statement.executeQuery("Select distinct Person from myTable where Person is not null");
%>
<select name="Person">
<option value=""></option>
<%
while(resultset.next())
{  
String myExp = resultset.getString("Person");
%>
	<option value="<%= myExp %>"><%= myExp %></option>
	<br>
<%
}
 
} 
//I added catch statements so users would not see the ODBC system resource error and it would take them to a page down message
catch (SQLException se) 
{
	response.sendRedirect("pageDown.html");
} 
catch (Exception e) 
{ 
	response.sendRedirect("pageDown.html");
}
finally
{
           
      if (resultset != null) resultset.close();
      if (statement != null)	statement.close();
      if (connection != null) connection.close();
}

%>
</select>
</td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="Submit" value="Submit">  
<input type="Reset" value="Clear"></td>
</tr>
</table>
</form>

Please advise because I have been trying for 3 months to solve this continuing issue and even created a new database but still cant solve the problem.

 

[sedj]

I think that the advice given to you in the java.sun.com forums (

http://forum.java.sun.com/thread.jspa?threadID=628655

) was excellant advice, which you should follow.

In effect :

1) Yes, use pooling - this will probably fix your problem, or at least highlight your errors more easily.
2) As a design point, you should certainly move all the JDBC code into a helper class.
3) Why not create a standalone java class, that hammers your db - then you can see whats going on !

--------------------------------------------------
Free Database Connection Pooling Software

http://www.primrose.org.uk