java.sql.SQLException: General error

[vijiraghs]

I am doing a servlet program that connects to a MSACCESS database,retrieves data and redirects the user to another page. I find the following error

java.sql.SQLException: General error

Login.java accesses esql.java which retrieves data from MSACCESS server.

PLease help!!!

My Servlet code: Login.java

import java.io.*;
import java.sql.*;
import java.io.*;
import java.net.*;
import javax.servlet.*;
import javax.servlet.http.*;

public class Login extends HttpServlet
{
	public void doPost(HttpServletRequest request,HttpServletResponse response)throws IOException,ServletException
	{
		String str1="";
		String cat="";
		String s1=request.getParameter("name");
		String s2=request.getParameter("passwd");
		
			PrintWriter out=response.getWriter();
			response.setContentType("text/html");
			
		System.out.println(s1);
		System.out.println(s2);
		esql e = new esql();
		int x = e.method(s1,s2);
		System.out.println(x);
		if(x == 1)
		{
			response.sendRedirect("arakonnam.html");
			
		}
	}
}

esql.java

import java.io.*;
import java.sql.*;

public class esql
{
	int method(String name, String pass)
	{
		try
        {
			String str = "SELECT * FROM voterlogin where voterID=? and password=?";
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			Connection c =  DriverManager.getConnection("Jdbc:Odbc:db1","","");
            Statement S = c.createStatement(); 
			PreparedStatement smt = c.prepareStatement(str);
			smt.setString(1,name);
			smt.setString(2,pass);
			ResultSet r = smt.executeQuery();
            if(r == null)
				return 0;
			else
				return 1;
        }
        catch(Exception e)
        {
             System.out.println(e);
        }
		return 0;
	}
	String fetchConstituency(String name)
	{
		try
        {
			String str = "SELECT * FROM voterinfo where voterID=?";
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			Connection c =  DriverManager.getConnection("Jdbc:Odbc:db1","","");
            Statement S = c.createStatement(); 
			PreparedStatement smt = c.prepareStatement(str);
			smt.setString(1,name);
			ResultSet r = smt.executeQuery();
			String qs="";
			r.next();
				 qs = r.getString("constituency");
				 System.out.println(qs);
			return qs;
        }
        catch(Exception e)
        {
            System.out.println(e);
        }
		return null;
	}
	public static void main(String args[])
	{
		esql e = new esql();
		e.fetchConstituency("1");
	}
}

[javaAddict]

First of post what errors you get. Specifically the exception. Don't use: System.out.println(e) , use e.printStackTrace.
And show us the line where you get the error. Test that method in a main method first

Also this is wrong:
if(r == null)

ResultSet is never null.
Use:
if (r.next()) {
return 1;
}

You don't need to create a Statement since you don't use. And you need to close the connection, PreparedStatement, ResultSet after you are done, in a finally block.

[vijiraghs]

Sure, will follow all that you have said and give a feedback.

[javaAddict]

You might want to try this. You need to close everything in the finally block. But if you declare them in the try, they won't be visible in the finally, but you cannot declare them outside because, they throw an exception. So declare them outside and initialize them inside the try:

int method(String name, String pass) throws SQLException {


// declaration
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;

try {
String str = "SELECT 1 FROM voterlogin where voterID=? and password=?";

// initialization
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn = DriverManager.getConnection("Jdbc:Odbc:db1","","");

ps = conn.prepareStatement(str);
ps.setString(1,name);
ps.setString(2,pass);

rs = ps.executeQuery();
if (rs.next()) return 1;
else return 0;

} catch (SQLException e) {
   e.printStackTrace();

   //return 0;
   // or
   //throw e;

   // You can either return 0 or better yet -1 in case an exception occurred.
   // or rethrow the exception to be caught somewhere else in the code 

} finally { // the finally always executes even after the return statement.
  if (rs!=null) rs.close(); // the if is in case an exception occurred before ResultSet taking value

  if (ps!=null) ps.close();
  if (conn!=null) conn.close();
}

}

The whole method throws an SQLException because the close operations also throw exception. An alternative way would be in the finally:

} finally {

   try {if (rs!=null) rs.close();} catch(Exception e) {}
   try {if (ps!=null) ps.close();} catch(Exception e) {}
   try {if (conn!=null) conn.close();} catch(Exception e) {}

}

[vijiraghs]

I tried implementing the same code in another system. It worked. I still havent figured out what the problem with my system is. Will post the solution if i find out.

[javaAddict]

Do you have the driver installed. Have you set it at your classpath ?

[vijiraghs]

Here is what i did.

control panel->administrative tools->data sources(ODBC)->systemDSN->add->Microsoft access driver(*.mdb)->finish->

Data Source Name : db1 (my database name)
Description : C:\apache-tomcat-6.0.30\webapps\MyFiles1\db1

and OK.

Is there anything else I must do??

[javaAddict]

When you set this driver: "sun.jdbc.odbc.JdbcOdbcDriver" with: Class.forName("sun.jdbc.odbc.JdbcOdbcDriver") that is actually a class that you use to get the Connection instance as well as the others (PreparedStatement for example).

Do you have some jar downloaded or something that has the above class to put in the classpath.
I don't work with Access so I don't know the exact procedure. Is it necessary to use Access. Why not mySql ?

[vijiraghs]

I dont know if any such class exists. I had to do a mini-project as a lab excercise.This is the first time i am working with database and servlet and I had only two weeks to complete my work. So, i just went with MSAccess.. From next time, i will use mySQL.

Thanks for your help.