java mysql resultset error in for loop

Question

In the below code:

String GET_DISTINCT_LISTENER_IDS_QUERY = "select distinct(listener_id) from ";
ResultSet rs = null;
ResultSet rs2 = null;
String temp_table = "CREATE TABLE IF NOT EXISTS temp (id INT NOT NULL COMMENT '',"
        + "date_start DATETIME NULL COMMENT ''," + "listener_id VARCHAR(45) NULL COMMENT '',"
        + "gateway_id VARCHAR(45) NULL COMMENT ''," + "entryway_id VARCHAR(45) NULL COMMENT '')";

stmt.executeUpdate(temp_table);

for (int i = 0; i < table_names_list.length; i++) {
    GET_DISTINCT_LISTENER_IDS_QUERY += table_names_list[i];
    PreparedStatement ps1 = conn.prepareStatement(GET_DISTINCT_LISTENER_IDS_QUERY);
    rs = ps1.executeQuery();

    while (rs.next()) {
        String query = "Select * from " + table_names_list[i] + " where listener_id = ? order by date_start LIMIT 1";
        PreparedStatement ps2 = conn.prepareStatement(query);
        ps2.setString(1, rs.getString("listener_id"));
        rs2 = ps2.executeQuery();
        String temp_insert = "INSERT INTO " + table_names_list[i]
                + " (id, date_start, listener_id, gateway_id, entryway_id)" + " values(?,?,?,?,?)";
        PreparedStatement ps = conn.prepareStatement(temp_insert);
        while (rs2.next()) {
            ps.setInt(1, rs2.getInt("id"));
            ps.setDate(2, rs2.getDate("date_start"));
            ps.setString(3, rs2.getString("listener_id"));
            ps.setString(4, rs2.getString("gateway_id"));
            ps.setString(5, rs2.getString("entryway_id"));
            System.out.println("Updating statement...");
            ps.executeUpdate();
            System.out.println("Record is inserted into DBUSER table!");
        }
    }
}

In the above piece of code i am getting a list of table names from the user and then i am iterating over them in the for loop. The code is working but it is not inserting the data into the temp table and ending with an error.

Error:

java.sql.SQLException: Operation not allowed after ResultSet closed
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
    at com.mysql.jdbc.ResultSet.checkClosed(ResultSet.java:666)
    at com.mysql.jdbc.ResultSet.next(ResultSet.java:7274)
    at database.database.main(database.java:62)

62: while (rs.next()) { (this line number 62)

Please help me out finding why this error is coming?

Luiggi Mendoza · Accepted Answer · 2015-07-26 16:00:52Z

2

ResultSet rs reference depends on the Statement stmt reference that generated it. Since you're changing the statement to execute in stmt, rs will be closed behind the scenes and won't be able to work anymore.

To solve the problem, just use a single PreparedStatement for each rs and rs2.

edited Jul 26, 2015 at 16:00

answered Jul 26, 2015 at 15:49

Luiggi Mendoza

85.9k16 gold badges160 silver badges354 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

nikunj2512 Over a year ago

No.. i think it is opposite. As you can see i get the list of listener_id in rs for different tables and then use that for rs2 and the result of rs2 goes into ps

nikunj2512 Over a year ago

You said use another PreparedStatement for my update by i am not updating anything in the code. i am just selecting data and inserting data. And for insertion i am already using preparedStatement.

Luiggi Mendoza Over a year ago

Fixed the contents. You should use proper names for your variables in the code. Still, the solution is the same: use a single PreparedStatement object reference for each ResultSet you have/need.

nikunj2512 Over a year ago

Ohhhh... Ok... Let me Try. Thanks.

nikunj2512 Over a year ago

Code is working and not throwing any errors but still it is not inserting data into the table.

|

Community · Accepted Answer · 2017-05-23 11:45:12Z

0

When you run rs2 = stmt.executeQuery(query); the previous resultset i.e. rs get closed. More detail can be found here

edited May 23, 2017 at 11:45

CommunityBot

11 silver badge

answered Jul 26, 2015 at 15:50

TheCodingFrog

3,5343 gold badges24 silver badges27 bronze badges

2 Comments

nikunj2512 Over a year ago

Then how can i fix that?

Utkarsh Over a year ago

As Luiggi said use preparedStatement

Collectives™ on Stack Overflow

java mysql resultset error in for loop

2 Answers 2

11 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related