JDBC - SQLSTATE : 24000 java.sql.SQLException: [Microsoft][ODBC Driver Manager] Invalid cursor state

[Vikramjit Saha]

Hi,

I have the following method:

@SuppressWarnings("finally")
	public ArrayList<ApplicationModel> getAllApplicationListForQueryParam(String queryParam, Character del_ind, HttpServletRequest req, HttpServletResponse res){
		IssueTrackerLoggerUtil.writeLog(Level.INFO, "Inside getAllApplicationListForQueryParam method of ApplicationDAO.",req);
		ArrayList<ApplicationModel> aLst = new ArrayList<ApplicationModel>();
		Connection connection = null;
		Statement stmt = null;
	    ResultSet rs = null;
	    try{
	    	connection = dbObj.getConnection1(req);
	    	stmt = connection.createStatement();
	    	String query = null;
	    	if(del_ind != null){
	    		if(queryParam != null){
	    			query = "SELECT cts_it_app_dtl_pk,app_cd,app_nm,app_stts,
	    			app_lst_upd_ts,app_lst_upd_by,cts_it_app_dtl.edit_ind,cts_it_app_dtl.del_ind,cts_it_prj_dtl_fk
	    			FROM cts_it_app_dtl,cts_it_prj_dtl 
	    			where cts_it_app_dtl.cts_it_prj_dtl_fk = cts_it_prj_dtl.cts_it_prj_dtl_pk 
	    			and cts_it_app_dtl.del_ind = '"+del_ind+"' and " + queryParam;
	    		}else{
		    		query = "SELECT * FROM cts_it_app_dtl WHERE del_ind = '"+del_ind+"';";
	    		}
	    	}else{
	    		query = "SELECT * FROM cts_it_app_dtl";
	    	}
	    	rs = stmt.executeQuery(query);
	    	if(rs != null){
		    	while(rs.next()){
			    		ApplicationModel applicationModel = new ApplicationModel();
			    		applicationModel.setCts_it_app_dtl_pk(rs.getInt("cts_it_app_dtl_pk"));
			    		applicationModel.setApp_cd(rs.getString("app_cd"));
			    		applicationModel.setApp_nm(rs.getString("app_nm"));
			    		applicationModel.setApp_stts(rs.getString("app_stts"));
			    		applicationModel.setApp_lst_upd_ts(rs.getString("app_lst_upd_ts"));
			    		applicationModel.setApp_lst_upd_by(rs.getString("app_lst_upd_by"));
			    		applicationModel.setEdit_ind(rs.getString("edit_ind"));
			    		applicationModel.setDel_ind(rs.getString("del_ind"));
			    		applicationModel.setCts_it_prj_dtl_fk(rs.getInt("cts_it_prj_dtl_fk"));
			    		aLst.add(applicationModel);
		    	}
	    	}
	    	rs.close();
	    	stmt.close();
		    connection.close();
	    }catch(Exception e ){
	    	e.printStackTrace();
	    	connection.rollback();
	    	IssueTrackerLoggerUtil.writeLog(Level.SEVERE, "Database exception :-"+e.getMessage(),req);
	    }
	    finally{	    	
	    	IssueTrackerLoggerUtil.writeLog(Level.INFO, "Exiting from getAllApplicationListForQueryParam method of ApplicationDAO.",req);
		    return aLst;
	    }
	}

This method when executes the query :-

SELECT cts_it_app_dtl_pk,app_cd,app_nm,app_stts,app_lst_upd_ts,app_lst_upd_by,cts_it_app_dtl.edit_ind,cts_it_app_dtl.del_ind,cts_it_prj_dtl_fk 
FROM cts_it_app_dtl,cts_it_prj_dtl 
where cts_it_app_dtl.cts_it_prj_dtl_fk = cts_it_prj_dtl.cts_it_prj_dtl_pk and cts_it_app_dtl.del_ind = 'n' and prj_nm like '*project1*'

Gives the : Invalid cursor error.

Database format is : accdb (access 2007)


My getConnection method is :-

@SuppressWarnings("finally")
	public Connection getConnection(String USERID, String PASSWORD, String DRIVER, String URL, HttpServletRequest req) {
	    
/*		String USERNAME = "ID";
		String PASSWORD = "PWD";
		String DRIVER = "sun.jdbc.odbc.JdbcOdbcDriver";
		String URL = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=D:\\mydatabase.accdb;}";
*/		
		
		Connection con = null;
		

		try {
			Class.forName(DRIVER);
			con = DriverManager.getConnection(URL, USERID, PASSWORD);
			}catch(ClassNotFoundException cnfe){
				IssueTrackerLoggerUtil.writeLog(Level.SEVERE, "Database connetion exception: " + cnfe.getMessage(),req);
			}catch(SQLException se){
				IssueTrackerLoggerUtil.writeLog(Level.SEVERE, "Database connetion exception: " + se.getMessage(),req);
			}finally{
				return con;
			}
	}	

ALL other select queries are working fine and the rs!=null in the first method is okay too.

Please revert back

[moderator removed colors, and added code tags]

[Jan Cumps]

Welcome to the Ranch, Vikramjit!

For readability, we have removed the colors from your post, and wrapped code tags around the code.

Good luck.