how to resolve error SQL Exception thrown: java.sql.SQLException: Operation not allowed after ResultSet closed [duplicate]

Question

I am trying to run following code but getting error:

SQL Exception thrown: java.sql.SQLException: Operation not allowed after ResultSet closed.

How to resolve this error? I need two result sets for my application.

public static void main(String[] args) {
        String connectionUrl = "jdbc:mysql://127.0.0.1:3306/test";
        String dbUser = "root";
        String dbPwd = "Syntel#92";
        Connection conn;
        ResultSet rs, res1 = null;
        Statement stmt = null;
        int rowcount = 0;
    //  String queryString = "create table job_status_table as select j1.job_id,s1.Source_ID,s1.Source_name from JOb_list j1,SOURCE_DETAILS s1 where s1.Source_ID = j1.Source_ID";
        String queryString = "create table job_status_table as select source_id,source_name from source_details";

        String addcolumn = "alter table job_status_table add column Source_rowcount int";
        String fetchdata = "Select Source_name from job_status_table";
        try {
            conn = DriverManager.getConnection(connectionUrl, dbUser, dbPwd);
            stmt = conn.createStatement();
            // get record count from table job_status_table1
            // stmt.executeQuery("select count() from job_status_table1");
            // create table
            stmt.executeUpdate(queryString);
            System.out.println("Table created in the database");
            stmt.executeUpdate(addcolumn);
            System.out.println("alter table");
            rs = stmt.executeQuery(fetchdata);
            System.out.println("fetch data");
            while (rs.next()) {
                String table_count = null;
                String table_name = null;




                    table_name = rs.getString("Source_name");
                    System.out.println(table_name);


            //  table_name = rs.getString("Source_name");
                //System.out.println(table_name);
                //rs.close();

                    table_count = "select count(*) from " + table_name;
                //table_count = "select count(*) from " + table_name;
                //rs.close();
            //  res1 = stmt.executeQuery(table_count);

                res1 = stmt.executeQuery(table_count);
                //System.out.print(res1);
                if (res1.next()) {

                    rowcount = res1.getInt(1);//res1.getInt(1);
                    System.out.println("Result set values" + rowcount);

                } else {
                    System.out.println("value is not present in resultset");
                }


                System.out.println("Get Row Count");
                    System.out.println(table_count);
            //      int cnt = rcnt(table_count);
                String updaterow = "update job_status_table set Source_rowcount ="
                        + rowcount
                        + " where Source_name = '"
                        + table_name
                        + "'";

                System.out.println("updateoutput" +stmt.executeUpdate(updaterow));

                System.out.println("Update Complete");

            }

    /*      if (conn != null) {

                rs.close();
                res1.close();
                stmt.close();
                conn.close();
                conn = null;
            }
            */
        }
        catch (SQLException sqle) {
            System.out.println("SQL Exception thrown: " + sqle);
        }
    }
}**

You should use a different statement object for executing the query in the loop (and you should disable auto commit). — Mark Rotteveel
– Mark Rotteveel, Commented Jul 16, 2016 at 10:29

Vladimir Vagaytsev · Accepted Answer · 2016-07-16 13:37:45Z

0

You could try this:

First copy the ResultSet rs in an ArrayList and close it.

Iterate over the ArrayList and close res1 before the update.

And I don't think the else with "value is not present in resultset" is reachable, but if you should set the rowcount to 0.

EDIT

After reading the referenced question the second time: The problem is the reusing of stmt for res1 and the update

edited Jul 16, 2016 at 13:37

Vladimir Vagaytsev

2,8919 gold badges36 silver badges38 bronze badges

answered Jul 16, 2016 at 9:38

Turo

5,0442 gold badges17 silver badges30 bronze badges

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

Collectives™ on Stack Overflow

how to resolve error SQL Exception thrown: java.sql.SQLException: Operation not allowed after ResultSet closed [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related