SQL exception error in JDBC conection with servlet+ jsp

[morya123]

my code is like this




i could print from database to my user interface by select command but when i want to update to database through user interface i am getting this error


SQL exception: General error!!!!

i check this with MS accesss ORACLE and ORACLE Exprress as well!!!

import javax.servlet.*;
import javax.servlet.http.*;
import java.util.*;
import java.io.*;
import java.sql.*;
public class editServlet extends HttpServlet{
private Connection lCon;
	public void init(){
		try{
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			lCon=DriverManager.getConnection("jdbc:odbc:records","test","test");
		}catch(Exception e){
			e.printStackTrace();
		}
	}

	public void doPost(HttpServletRequest pRequest, HttpServletResponse pResponse)throws ServletException,IOException {
		String lId=pRequest.getParameter("id");
		String lFirstname=pRequest.getParameter("firstname");
		String lLastname=pRequest.getParameter("lastname");
		String lAddress=pRequest.getParameter("address");
		String lMessage="";
		List lRecordsList=null;
		try{
			if (lId != null ){
				//edit	
				if(lCon == null){
					init();
				}		
				PreparedStatement lPreparedSatement = lCon.prepareStatement("update Personal set firstname=? , lastname=?,address=? where id=?");
				lPreparedSatement.setString(1,lFirstname);
				lPreparedSatement.setString(2,lLastname);
				lPreparedSatement.setString(3,lAddress);
				lPreparedSatement.setString(4,lId);
				lPreparedSatement.executeUpdate();

				lMessage="Record Saved Successfully.";

				lPreparedSatement.close();
			
			}else{
				lMessage="Id attribute was null.";
			}


			//Select
			if(lCon == null){
				init();
			}		
			Statement lStatement = lCon.createStatement();
			ResultSet lResultSet = lStatement.executeQuery("Select * from Personal");		 

			lRecordsList = new ArrayList();

			while(lResultSet.next()){
				Map lMap = new HashMap();
	
				lMap.put("id",lResultSet.getString("id"));
				lMap.put("firstname",lResultSet.getString("firstname"));
				lMap.put("lastname",lResultSet.getString("lastname"));
				lMap.put("address",lResultSet.getString("address"));

				lRecordsList.add(lMap);
			}
		}catch(Exception e){
			e.printStackTrace();
			lMessage="There was some problem, while saving record.";
		}finally{
			try{lCon.close();}catch(Exception e){e.printStackTrace();}
		}


		RequestDispatcher lRequestDispatcher = pRequest.getRequestDispatcher("index.jsp");
		pRequest.setAttribute("Records"	,lRecordsList);
		pRequest.setAttribute("Message",lMessage);
		lRequestDispatcher .forward(pRequest,pResponse);
	}


	public void destroy(){
		if(lCon != null){
			try{lCon.close();}catch(Exception e){e.printStackTrace();}
		}		
	}
}

[travishein]

the ODBC JDBC driver probably does not have prepared statement support implemented.

That jdbc-odbc bridge is pretty old, and probably by design basic, most ODBC things used to be assumed to be read-only, such as exporting an excel file as an ODBC data source.

Try to use the ghetto Statement, with of course proper handling for sql injection and escaping of special characters and quotes. and if that is still not working, then for some reason this ODBC connection is behaving as if it is read only.

would it be possible to use a not-odbc connection? you mentioned you tried this with oracle and mssql. Both of these databases do come with their own JDBC drivers, connecting to the database using their JDBC implementation might work better.

[Tolls]

As a basic rule, never use the JDBC-ODBC bridge. It's rubbish.
I can't offhand think of a db that does not come with it's own proper driver, so use it.

You'll also possibly get a better exception thrown if there is a problem.