*
The moose likes Servlets and the fly likes SQL Exception on refeshing the servlet. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Java » Servlets
Bookmark "SQL Exception on refeshing the servlet." Watch "SQL Exception on refeshing the servlet." New topic
Author

SQL Exception on refeshing the servlet.

Kavita Ghia
Ranch Hand

Joined: Oct 10, 2000
Posts: 91
Hello friends,
I have a servlet written which is making connection to my database(Ms Access)if it helps and then performing search query.
The connection is done in the init() method of the servlet and the query is executed in the doGet() method.If the search query contains the data then it displays the result else the message record not found.Now my problem is this-
the whole procedure is happening only once when my servlet is called for the first time.i.e. when my init method is invoked.Subsequently whenever I try to refresh the servlet for other search result I get an SQLException stating the "General Error".Basically it is not creating or executing the following:
Statement st=con.createStatement();.
Can anyone explain me the reason for such a weird behavior.
Thanking in advance,
Regards,
Kavita.
Kavita Ghia
Ranch Hand

Joined: Oct 10, 2000
Posts: 91
More information-I am closing the connection,statement and result set.My program is below:
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;
public class SearchServlet extends HttpServlet
{
//shared by all the requests
Statement st;
Connection dbConn;
String word;

public void init(ServletConfig config) throws
ServletException{
super.init(config);
try
{
//load jdbc-odbc bridge
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//establish database connection to netsparsh
dbConn=
DriverManager.getConnection"jdbc dbc:netsparsh");
System.out.println("connection established");
}
//thrown by Class.forName
catch(ClassNotFoundException e)
{
System.out.println("JDBC-ODBC bridge not found");
return;
}
catch(SQLException e)
{
System.out.println("SQL Exception thrown!");
}
}
public void doPost(HttpServletRequest req,HttpServletResponse res) throws
ServletException,IOException
{
try
{
System.out.println("in doPost method");
res.setContentType("text/html");

//get handle to output stream
PrintWriter out=res.getWriter();

out.println("<HTML>");
out.println("<BODY>");
word=req.getParameter("address");

//create statement
st=dbConn.createStatement();
//query database for result set
String query="select membership_no,name,url from member where lcase(name)='"+(word)+"' ";
ResultSet rs=st.executeQuery(query);

if(rs.next()==true) {
String temp="http://";
String temp1=rs.getString("url");
res.sendRedirect(temp+temp1);
}
else
out.println("NO URL Found");
out.println("</BODY>");
out.println("</HTML>");
rs.close();
st.close();
out.close();
dbConn.close();

}
catch(SQLException e)
{
e.printStackTrace();
}
}
public String getServletInfo()
{
return "SearchServlet........and enjoy!";
}
}
pk mast
Greenhorn

Joined: Jan 08, 2001
Posts: 5
put the connection part in the dopost method and it will work.well logically it doesnt make any sense but thats the way it goes.all the best kavita.
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
Originally posted by Kavita Ghia:
Hello friends,
Now my problem is this-
the whole procedure is happening only once when my servlet is called for the first time.i.e. when my init method is invoked.Subsequently whenever I try to refresh the servlet for other search result I get an SQLException stating the "General Error". [...] Can anyone explain me the reason for such a weird behavior.

Not weird, but perfectly normal...
You're opening your database just once, in the servlet intialisation code. Yet you are closing both your statement and your database connection in the request handler. After the first request, you will have closed your connection no more database interaction will be possible until you reconnect.
What you probably intend to do is not close your statement and connection in doPost(), but in destroy(). That way they stay open until the container cleans up your servlet.
But in applications that reach any kind of volume, you don't want to statically allocate a database connection for each servlet. Nor do you want to open and close one for each request. Instead, use a database connection pool. One easy way of achieving this is to move your database access layer to an EJB container.
Regards
- Peter
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
Originally posted by Peter den Haan:
What you probably intend to do is not close your statement and connection in doPost(), but in destroy(). That way they stay open until the container cleans up your servlet.

I should add that, if you do this, your servlet needs to implement the SingleThreadModel interface as (afaik) connection objects are not threadsafe.
- Peter
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL Exception on refeshing the servlet.
 
Similar Threads
JDBC in JSP/Servlet question
HTML FORM processing problem
SQL Exception on refeshing the servlet.
Connection Pool Design Issue
Abstract servlet