Resultset not open

I get following error on Result set

java.sql.SQLException: ResultSet not open. Verify that autocommit is OFF. at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source) at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source) at org.apache.derby.client.am.ResultSet.next(Unknown Source)

    public  ResultSet insertDb(int Id,String firstName,String lastName,String title) throws SQLException{
        try {
            try {
                Class.forName(driver);
                con = DriverManager.getConnection(connectionURL);

                } catch (SQLException ex) {
                Logger.getLogger(Connect.class.getName()).log(Level.SEVERE, null, ex);
                } catch (ClassNotFoundException ex) {
                Logger.getLogger(Connect.class.getName()).log(Level.SEVERE, null, ex);
                }
    System.out.println(con.getAutoCommit());
    statement = con.createStatement() ;
    res = statement.executeQuery("SELECT * FROM CUSTOMER") ;
    con.setAutoCommit(false);
    System.out.println(con.getAutoCommit());

    while(res.next()){
        if(res.getString("ID").equalsIgnoreCase(Integer.toString(Id))){
            UNIQUE = false;
             error= "Duplicate Entry Found Please Enter New Data";
            throw new SQLException("Duplicate info<br>ID " + Id );

        }
    }
    // IF value to be added IS UNIQUE
    if(UNIQUE){
        String qry1= "insert into CUST开发者_开发知识库OMER(ID, FIRSTNAME,LASTNAME,TITLE) values(?,?,?,?)";
        stm  = con.prepareStatement(qry1);
        String ID=Integer.toString(Id);
        stm.setString(1, ID);
        stm.setString(2, firstName);
        stm.setString(3, lastName);
        stm.setString(4, title);
        stm.executeUpdate();
    }
}
catch(Exception e){
    String errorMessage = "Exception caught : ";
    System.out.println(errorMessage + e.toString());

}finally{   
    if (con != null){
    con.close();
    }
        }

return res;
    }  

---

Try moving the setAutoCommit() and getAutoCommit() to before you create and execute the statement. Changing it after you execute the statement may be invalidating the query.

---

The problem is that you have closed your query before reading your resultset. Closing the query, closes the resultset, hence why you get the "ResultSet not open" error. You should close the query right at the end, in a finally block:

i.e. con.setAutoCommit(false);

will close the query and along iwth it it closes the resultset also.

---

Not strictly related, but your code probably doesn't do what you expect. This kind of read-modify-write code doesn't work well when there are multiple concurrent invocations.

If you imagine two invocations running though the code, it becomes clear that sometimes, depending on the execution order, BOTH invocations could reach the insert statement.

In addition, selecting from a table without using a WHERE clause is not generally useful. In this case you select '*', then iterate over all the results to see if "ID" == Id. The database is much much better at that than java is. You should add a where clause. (Note that this still won't solve the above problem)

Its also generally a bad idea to 'select *' from any table. Just pick the columns that you need. This will 'fail fast' if the schema changes and the columns that you need are no longer available, and will allow the database optimiser to do the 'right thing' about its disk accesses.

Finally, if its just a numeric ID that you are looking to assign, its normal practice to use 'autonumber' for these, rather than get the program to pick them. Different databases call them different things, so you might also know them as IDENTITY, or have to use a sequence.

---

In case it helps anyone down the line, I had the same error with Derby 10.5.1.1 that turned out to be a bug in the driver itself that would appear some times and not others depending on the underlying data. Upgrading the driver to a newer version (10.8.2.2) resolved the problem.