CODEWITHSUNDEEP
javamysqlexception
Evilsithgirl
Evilsithgirl

Reputation: 329

Java SQLException: Result Set closed issue

I have this java function that runs and produces an error. I cannot figure out why this is occurring because this is the first function in the program to run so no other connections, statements, or result sets have been opened. The error is

Operation not allowed after ResultSet closed
java.sql.SQLException: Operation not allowed after ResultSet closed
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
        at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:768)
        at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:7008)
        at equipmentinventoryimporter.Importer.sqlTable(Importer.java:219)
        at equipmentinventoryimporter.Importer.main(Importer.java:58)

and the function is

private static void sqlTable(SQLTableJob sqltableJob) {
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            Connection connMySQL2 = null;
            Statement stntMySQL2 = null;
            try {
                connMySQL2 = DriverManager.getConnection(mysqlAddress, mysqlUsername, mysqlPassword);
                stntMySQL2 = connMySQL2.createStatement();
                //MySQL Transaction
                System.out.println("Starting " + sqltableJob.getMysqlTable() + " transaction");
                stntMySQL2.execute("START TRANSACTION");
                String insertQuery = "";
                try {
                    final String inactiveQuery = "UPDATE `" + sqltableJob.getMysqlSchema() + "`.`" + sqltableJob.getMysqlTable() + "` SET `active`=0";
                    stntMySQL2.executeUpdate(inactiveQuery);
                    final ResultSet rs2 = stntMySQL2.executeQuery(sqltableJob.getSQLSelectQuery());
                    int counter = 0;
                    while (rs2.next()) {
                        counter++;
                        final String mysqlSetClause = sqltableJob.getMysqlSetClause(rs2);
                        insertQuery = "INSERT INTO `" + sqltableJob.getMysqlSchema() + "`.`" + sqltableJob.getMysqlTable() + "` SET " +
                                mysqlSetClause +
                                " ON DUPLICATE KEY UPDATE " +
                                mysqlSetClause;
                        stntMySQL2.executeUpdate(insertQuery);
                        if (counter % 5000 == 0) {
                            System.out.println("Processed " + counter + " rows.");
                        }
                    }
                    rs2.close();

                    if (!sqltableJob.isKeepInactives()) {
                        final String deleteQuery = "DELETE FROM `" + sqltableJob.getMysqlSchema() + "`.`" + sqltableJob.getMysqlTable() + "`" +
                                "WHERE `active`=0";
                        stntMySQL2.executeUpdate(deleteQuery);
                    }

                    stntMySQL2.execute("COMMIT");//last line of try block
                    System.out.println("Committed " + sqltableJob.getMysqlTable() + " transaction");
                } catch (Exception ex) {
                    System.out.println(ex.getMessage());
                    ex.printStackTrace();
                    System.out.println("Transaction level exception thrown.");
                    System.out.println(insertQuery);
                    stntMySQL2.execute("ROLLBACK");
                    System.out.println("MySQL query rolled back.");
                }
            //Another MySQL Transaction goes here
            } catch (Exception ex) {
                System.out.println(ex.getMessage());
                ex.printStackTrace();
                System.out.println("Connection level exception thrown.");
            } finally {
                if (stntMySQL2 != null) {
                    try {
                        stntMySQL2.close();
                    } catch (Exception ex) {
                    }
                }
                if (connMySQL2 != null) {
                    try {
                        connMySQL2.close();
                    } catch (Exception ex) {
                    }
                }
            }
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
            ex.printStackTrace();
            System.out.println("Program level exception thrown.");
        }
    }

SQLTableJob is

package equipmentinventoryimporter;

import java.sql.ResultSet;
import java.sql.SQLException;
/**
 *
 * @author jmgreen
 */
public interface SQLTableJob {

    public String getMysqlSchema();

    public String getMysqlTable();

    public String getSQLSelectQuery();

    public String getMysqlSetClause(ResultSet rs) throws SQLException;

    public boolean isKeepInactives();
}

and the specific SQLTableJob being referenced is

public class SQLTableJob10I implements SQLTableJob{

public boolean isKeepInactives() {
        return true;
    }

    public String getMysqlSchema() {
        return "Temp_Equipment_Inventory";
    }

    public String getMysqlTable() {
        return "PC_Combined";
    }

    public String getSQLSelectQuery() {
        final String sqlSelectQuery = "SELECT *" +
                " FROM Temp_Equipment_Inventory.PC_Table10i";
        return sqlSelectQuery;
    }

    public String getMysqlSetClause(ResultSet rs) throws SQLException {
        final String mysqlSetClause =
                "`Account_No`=" + Importer.sqlChar(rs.getString("Account_No")) +
                ",`Inventory_No`=" + Importer.sqlChar(rs.getString("Inventory_No")) +
                ",`Building_No`=" + Importer.sqlChar(rs.getString("Building_No")) +
                ",`Location`=" + Importer.sqlChar(rs.getString("Location")) +
                ",`FYYR_No`=" + Importer.sqlChar(rs.getString("FYYR_No")) +
                ",`Cost`=" + Importer.sqlChar(rs.getString("Cost")) +
                ",`Name`=" + Importer.sqlChar(rs.getString("Name")) +
                ",`Desc1`= ''" +
                ",`Desc2`= ''" +
                ",`Desc3`= ''" +
                ",`CDCATY`=" + Importer.sqlChar(rs.getString("CDCATY")) +
                ",`CDSRCE`=" + Importer.sqlChar(rs.getString("CDSRCE")) +
                ",`FLDCAL`=" + Importer.sqlChar(rs.getString("FLDCAL")) +
                ",`CDACQN`=" + Importer.sqlChar(rs.getString("CDACQN")) +
                ",`FLOWNR`=" + Importer.sqlChar(rs.getString("FLOWNR")) +
                ",`FLSHAR`=" + Importer.sqlChar(rs.getString("FLSHAR")) +
                ",`CDDELT`=" + Importer.sqlChar(rs.getString("CDDELT")) +
                ",`CNYTDT`=" + Importer.sqlChar(rs.getString("CNYTDT")) +
                ",`NOPURO`=" + Importer.sqlChar(rs.getString("NOPURO")) +
                ",`NOPIMO`=" + Importer.sqlChar(rs.getString("NOPIMO")) +
                ",`CDPREI`=" + Importer.sqlChar(rs.getString("CDPREI")) +
                ",`Original_Amount`=" + Importer.sqlChar(rs.getString("Original_Amount")) +
                ",`Serial_Code`=" + Importer.sqlChar(rs.getString("Serial_Code")) +
                ",`CDCOMP`=" + Importer.sqlChar(rs.getString("CDCOMP")) +
                ",`NOCHECK`=" + Importer.sqlChar(rs.getString("NOCHECK")) +
                ",`CDCOMM`=" + Importer.sqlChar(rs.getString("CDCOMM")) +
                ",`Last_Update`=" + Importer.sqlDate(rs.getString("Last_Update")) +
                ",`CDDEPT`=" + Importer.sqlChar(rs.getString("CDDEPT")) +
                ",`Room_No`=" + Importer.sqlChar(rs.getString("Room_No")) +
                ",`Date_Scanned`=" + Importer.sqlDate(rs.getString("Date_Scanned")) +
                ",`Date_Acquired`=" + Importer.sqlDate(rs.getString("Date_Acquired")) +
                ",`Manufacturer_Name`=" + Importer.sqlChar(rs.getString("Manufacturer_Name")) +
                ",`Expiry_Date`=" + Importer.sqlDate(rs.getString("Expiry_Date")) +
                ",`Active`='1'";
        return mysqlSetClause;
    }
}

Upvotes: 1

Views: 6569

Answers (1)

NPE
NPE

Reputation: 500367

From the Javadoc:

A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.

From your code:

                stntMySQL2.executeUpdate(inactiveQuery);
                final ResultSet rs2 = stntMySQL2.executeQuery(sqltableJob.getSQLSelectQuery());
                while (rs2.next()) {
                    ...
                    stntMySQL2.executeUpdate(insertQuery);

You are re-executing stntMySQL2 inside the loop. This automatically closes rs2.

To fix, use a different statement object inside the loop.

Upvotes: 5

Related Questions