Java and SQLite: Throwing ResultSet closed but proceeding

Question

I've got a following problem: I'm trying to insert data (in this case a username) into a table using the following code:

void AddNewUser(String name, Connection conn){

    if(ret == null){
        ret = new DB_Retriever(conn);
    }

    if(!ret.UserExists(name, conn)){
        try{
            Statement stm = conn.createStatement();
            stm.executeUpdate(DB_OperationalData.insert_new_user[0][0] + name + DB_OperationalData.insert_new_user[0][1]);
            stm.executeUpdate(DB_OperationalData.insert_new_user[1][0] + name + DB_OperationalData.insert_new_user[1][1]);
            stm.close();
        }
        catch(SQLException e){
            e.printStackTrace();
        }
    }
}

By the way: It absolutely doesn't matter what I put in the catch clause, nothing that I put there is executed. Just to make everything clear, here is the content of the DB_OperationalData.insert_new_user String array:

public static final String[][] insert_new_user = {
    {"INSERT INTO User (Username, Status) VALUES ('","','IN');"},
    {"INSERT INTO Statistics (Player_ID) SELECT ID FROM User WHERE Username='","';"}};

The second statement is supposed to copy the ID of the user that is inserted and put it into Player_ID field of the Statistics table (Table User's ID is an autonumbered field).

The exception I get is:

Error while processing the query: java.sql.SQLException: ResultSet closed

What is interesting, is that it works and the data is added correctly but I simply do not want any exceptions thrown.

That's the console output I get:

This is 'data' Package Testing class
Connection to the database established.
The number of tables existing in the database is: 0
All the queries have been processed successfully
Adding new users: 
Error while processing the query: java.sql.SQLException: ResultSet closed

All the lines above the Exception are my own printouts, so I know what has actually happened.

[EDIT]

I have changed the code to use the PreparedStatement instead of ordinary Statement and the current try clause looks as follows:

PreparedStatement pstm = conn.prepareStatement(DB_OperationalData.insert_new_user[0]);
pstm.setString(1, name);
pstm.addBatch();
conn.setAutoCommit(false);
pstm.executeBatch();
conn.setAutoCommit(true);
pstm.close();

And the output is (still regardless of the contents of the catch clause):

This is 'data' Package Testing class
Connection to the database established.
The number of tables existing in the database is: 0
Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: 0
at org.sqlite.PrepStmt.batch(PrepStmt.java:173)
at org.sqlite.PrepStmt.setString(PrepStmt.java:254)
at data.DB_Writer.AddNewUser(DB_Writer.java:28)
at data.DataHandler.AddNewUser(DataHandler.java:94)
at data.Tester.main(Tester.java:18)
All the queries have been processed successfully
Adding new users: 
Error while processing the query: java.sql.SQLException: ResultSet closed

[EDIT 2] With regards to the original version, when I remove the stm.close(); there is absolutely no difference and I still get the 'ResultSet closed' Exception.

[EDIT 3] Here is the code of the method that is calling the above:

public void AddNewUser(String username)throws IllegalUsernameException{
    if(username.length()==0 || username.length()>20){
        throw new IllegalUsernameException();
    }
    writer.AddNewUser(username, conn);

}

The connection to the database is established by this class:

class DB_Connection {


public static Connection getConnection(){
    Connection conn = null;

    try{
        Class.forName("org.sqlite.JDBC");
    }
    catch(ClassNotFoundException e){
        log("Error while loading the database driver: " + e);
        return null;
    }
    try{
        conn = DriverManager.getConnection("jdbc:sqlite:database.db");
    }
    catch(SQLException e){
        log("Unable to connect to the database: " + e);
        return null;
    }
    return conn;
}

public static void log(String msg){
    System.out.println(msg);
}

}

The DB_Retriever's method that is checking for the existing username:

boolean UserExists(String name, Connection conn){

    String result = "";

    try{
        Statement stm = conn.createStatement();
        ResultSet rs = stm.executeQuery(DB_OperationalData.user_exists[0] + name + DB_OperationalData.user_exists[1]);
        result = rs.getString("Username");
    }
    catch(SQLException e){
        System.out.println("Error while processing the query: " + e);
    }

    if(result.equals(name)){
        return true;
    }

    return false;
}

Please add the stacktrace. Also check where exactly the exception is thrown (class and line number). To clarify, the exception is thrown with the empty catch block in place? Then it **cannot** be caused by the code you are showing. By definition a catch block will prevent the exception from propagating. Other interesting questions that may help in finding the cause : Where and how is the connection created? Do you use a connection pool? What is DB_Retriever doing? Why aren't you closing the statement? Please promise to read about java.sql.PreparedStatement immediately after fixing this bug! — Pyranja, Apr 01 '14 at 18:43
The exception is thrown when the catch block is empty but also when it is not. Connection is created in a separate class, which I'll add. DB_Retriever is only reading data from the database and doesn't throw any exceptions. In this case it checks if the given username already exists in the database. Code updated to reflect the suggestions. — domkol, Apr 01 '14 at 18:57
As it stands the console output is useless to us, as we don't know your code. The exception that is referenced in the output *cannot* be thrown from the code inside the try-catch block you have posted. Somewhere else in your code a SQLException is catched and the exception.message() value is printed. Find that catch block and change it to at least print a stacktrace. Then check the class and line number in the stacktrace. If you cannot figure it out, update your question with the code section that triggers the exception. A hint: DML statements do not create ResultSets. SELECTs do. — Pyranja, Apr 01 '14 at 19:29
@Pyranja I do realise that DML statements shouldn't create any ResultSets and that's also why I'm puzzled with this exception. The standard executeUpdate method returns only an integer with the number of rows that were changed. I have inserted some println statements and the result is even more interesting now: When the try block is executed we end up with: ResultSet exception, my string that is printed straight after the declaration of PreparedStatement with assignation of the insert query and then the ArrayOutOfBoundsException is thrown and this exception points to PrepStmt.java — domkol, Apr 01 '14 at 20:51
possible duplicate of [SQLite 3 - JDBC driver throws "ResultsSet Closed" exception on empty resultset](http://stackoverflow.com/questions/1813858/sqlite-3-jdbc-driver-throws-resultsset-closed-exception-on-empty-resultset) — Pyranja, Apr 01 '14 at 21:12
@Pyranja In this case, are you suggesting to set up the connection immediately before the query (that is not to create a separate class that sets up the connection and which could be passed on to different methods) and then to close the connection and do this for all the methods? — domkol, Apr 01 '14 at 21:28

score 0 · Accepted Answer · edited May 23 '17 at 11:57

The only location where Error while processing the query: java.sql.SQLException: ResultSet closed could be printed to the console is in UserExists(..), unless there is another method with a similar catch block. Indeed the ResultSet is not used correctly in UserExists, what may cause the error.

For a more complete description of how to work with JDBC look at this answer or the JDBC documentation. A possible alternative to the existing UserExists is:

boolean userExists(String name, Connection conn) {
   PreparedStatement stmt = null;
   try{
      stmt = conn.prepareStatement("SELECT COUNT(Username) FROM User WHERE Username = ?");
      stmt.setString(1, name);
      ResultSet rs = stmt.executeQuery();
      rs.next();  // set cursor to first row
      int count = rs.getInt(1);
      rs.close();
      return count > 0;
   } catch(SQLException e) {
      // propagate error
      throw new RuntimeException(e);
   } finally {
      // clean up resources
      if (stmt != null) {
        try {
           stmt.close();
        } catch (SQLException ignore) {
           log("error on sql clean up", ignore);
        }
      }
   }
}

Thank you very much. I did realise that it couldn't be the class that is writing to the database but now I see what's going on. Thank you also for introducing me to Prepared Statement! — domkol, Apr 02 '14 at 20:46

Java and SQLite: Throwing ResultSet closed but proceeding

1 Answers1