Operation not allowed after ResultSet Closed error Netbeans MySQL Connectivity

Question

I am creating a program to rename databases in mysql. I have succeeded in everything and it successfully happens. But in the end of my script, its shows an error/exception saying "Operation not allowed after ResultSet closed". I really have no idea why this error appears even after researching about this error. Although the full operation is successfully completed and the database is renamed. Here is my code->

String x = (String) jComboBox1.getSelectedItem(); //jComboBox1 contains the name of current database selected
String z = JOptionPane.showInputDialog("Please enter new name for Database"); //Where user enters the name for new database.
new CustComm().setVisible(false);    //Frame that carries the names of tables.
        try{
        Class.forName("java.sql.DriverManager");
        Connection con = (Connection)
        DriverManager.getConnection("jdbc:mysql://localhost:"+GlobalParams.portvar+"/",""+k,""+j);
        Statement stmnt = (Statement) con.createStatement();
        String query = "use "+x;
        stmnt.executeQuery(query);
        String query2 = "show tables";
        ResultSet rs = stmnt.executeQuery(query2);
        while (rs.next()){ 
        String dname = rs.getString("Tables_in_"+x);
        if(CustComm.jTextArea1.getText().equals("")){
        CustComm.jTextArea1.setText(CustComm.jTextArea1.getText()+dname);
        }
        else{
            CustComm.jTextArea1.setText(CustComm.jTextArea1.getText()+"\n"+dname);
        }
        String y = CustComm.jTextArea1.getText();
        Scanner scanner = new Scanner(y);
while (scanner.hasNextLine()) {
        String line = scanner.nextLine();
        String query3 = "Create database "+z;
        stmnt.executeUpdate(query3);
        //alter table my_old_db.mytable rename my_new_db.mytable
        String query4 =  "RENAME TABLE "+x+"."+line+" TO "+z+"."+line;
        stmnt.executeUpdate(query4);
        String query5 = "drop database "+x;
        stmnt.executeUpdate(query5);
}}}

    catch(Exception e){
        JOptionPane.showMessageDialog(this,e.getMessage());
    }

Please help.

instead of e.getMessage use e.printstacktrace and post the error message.Also point out in which line it gives the exception — SpringLearner, Oct 04 '13 at 12:41
Hi and thanks for reply. It throws the following error then (sorry error is too long to be posted here so I gave link)-> http://pastebin.com/wt7UwwuE I am unable to determine the line causing this error :/ Even tried reading and following the error it gave, but no help. It has been a day since I am facing this error and I posted here after my best tries to remove it. Since I am quite new to java, I am unable to figure such error causing line out :( Please help. And yeah, one more thing, the coding for this purpose starts at line 443 and ends at 478. — Hoobla, Oct 04 '13 at 12:56

score 0 · Accepted Answer · edited May 23 '17 at 12:21

You shouldn't execute new queries on statement Statement stmnt = (Statement) con.createStatement(); while you use ResultSet from it, because this will close your ResultSet.

By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists.

You should create 2 different statements: first for query2 and second for queries 3-5.

Also it's better to use PreparedStatement. You can read about the difference here.

score 0 · Answer 2 · answered Oct 04 '13 at 15:28

0

Do you have to do this work via code? Have you looked into tools like Liquibase?

answered Oct 04 '13 at 15:28

hooknc

4,854
5
31
60

Operation not allowed after ResultSet Closed error Netbeans MySQL Connectivity

2 Answers2