SQL Exception on refeshing the servlet.

Ranch Hand

Posts: 91

posted 23 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

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

Posts: 91

posted 23 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

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

Posts: 5

posted 23 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

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

Posts: 3252

posted 23 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

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 | peterdenhaan.com | quantum computing specialist, Objectivity Ltd

Peter den Haan

author

Posts: 3252

posted 23 years ago

Number of slices to send:

Optional 'thank-you' note:

Send

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

Peter den Haan | peterdenhaan.com | quantum computing specialist, Objectivity Ltd

I can't beleive you just said that. Now I need to calm down with this tiny ad:

Gift giving made easy with the permaculture playing cards

https://coderanch.com/t/777758/Gift-giving-easy-permaculture-playing