[Handler]

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 massmailer.Driver.sortClients(Driver.java:32)
        at massmailer.Driver.<init>(Driver.java:17)
        at massmailer.Driver.main(Driver.java:21)

The code throwing this error:

private void sortClients() {
        ResultSet rs = SqlHandler.handler.executeQuery("SELECT * FROM newsletter WHERE SendWeek = '0'");
        int week1 = Integer.parseInt(propsHandler.getProp("Week1"));
        int week2 = Integer.parseInt(propsHandler.getProp("Week2"));
        int week3 = Integer.parseInt(propsHandler.getProp("Week3"));
        int week4 = Integer.parseInt(propsHandler.getProp("Week4"));

        try {
            while (rs.next()) {
                //SqlHandler.handler.executeUpdate("UPDATE props SET Value='" + value + "' WHERE Property='" + prop + "';");
                if (week1 < week2 && week1 < week3 && week1 < week4) {
                    week1++;
                    sqlHandler.executeUpdate("UPDATE newsletter SET SendWeek='1' WHERE Newsletter_ID=" + rs.getString("Newsletter_ID") + ";");
                } else if (week2 < week3 && week2 < week4 && week2 < week1) {
                    week2++;
                    sqlHandler.executeUpdate("UPDATE newsletter SET SendWeek='2' WHERE Newsletter_ID=" + rs.getString("Newsletter_ID") + ";");
                } else if (week3 < week4 && week3 < week1 && week3 < week2) {
                    week3++;
                    sqlHandler.executeUpdate("UPDATE newsletter SET SendWeek='3' WHERE Newsletter_ID=" + rs.getString("Newsletter_ID") + ";");
                } else {
                    week4++;
                    sqlHandler.executeUpdate("UPDATE newsletter SET SendWeek='4' WHERE Newsletter_ID=" + rs.getString("Newsletter_ID") + ";");

                }
            }
            rs.close();
        } catch (Exception ex) {
            ErrorHandler.handler.handleError(ex);
        }
    }

.executeUpdate code:

executeUpdate(String command) {
        try {
            stmt.executeUpdate(command);
        } catch (SQLException ex) {
            ErrorHandler.handler.handleError(ex);
        }
    }

Anyone have an idea why I'm getting that error?

[g00se]

Could be a good idea to post code of SqlHandler.handler.executeQuery too

[Handler]

Bah ye I posted the wrong method
Here it is

protected ResultSet executeQuery(String command) {
        try {
            return stmt.executeQuery(command);
        } catch (SQLException ex) {
            ErrorHandler.handler.handleError(ex);
            return null;
        }
    }

This post has been edited by Handler: 30 October 2011 - 05:45 AM

[Handler]

Shameful bump ;( but I really need help here

[Handler]

No one?

[Fuzzyness]

Will you post all of your code and not just 2 methods please? Can comment out the password and url/ip for the connection if it is hardcoded into it.

This post has been edited by Fuzzyness: 01 November 2011 - 08:40 AM

[Handler]

package massmailer;

import java.sql.*;
import java.util.HashMap;

public class SqlHandler {

    protected static SqlHandler handler;
    private HashMap<String, PreparedStatement> prep = new HashMap<String, PreparedStatement>();
    private String url = "jdbc:mysql://localhost:3306/<edited>";
    private Statement stmt;
    private Connection con;

    public SqlHandler() {

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(url, "<edited>", "<edited>");
            stmt = con.createStatement();
        } catch (Exception ex) {
            ErrorHandler.handler.handleError(ex);
        }
        handler = this;
    }

    private PreparedStatement getStatement(String stat) {
        PreparedStatement preps = null;
        if (prep.get(stat) == null) {
            try {
                preps = con.prepareStatement(stat);
                prep.put(stat, preps);
                return preps;

            } catch (SQLException ex) {
                ErrorHandler.handler.handleError(ex);
                return preps;
            }
        } else {
            preps = prep.get(stat);
            return preps;
        }
    }

    protected ResultSet executeQuery(String command) {
        try {
            return stmt.executeQuery(command);
        } catch (SQLException ex) {
            ErrorHandler.handler.handleError(ex);
            return null;
        }
    }

    protected void executeUpdate(String command) {
        try {
            stmt.executeUpdate(command);
        } catch (SQLException ex) {
            ErrorHandler.handler.handleError(ex);
        }
    }
}

In Driver class:

 private void sortClients() {

        int week1 = Integer.parseInt(propsHandler.getProp("Week1"));
        int week2 = Integer.parseInt(propsHandler.getProp("Week2"));
        int week3 = Integer.parseInt(propsHandler.getProp("Week3"));
        int week4 = Integer.parseInt(propsHandler.getProp("Week4"));
        ResultSet rs = SqlHandler.handler.executeQuery("SELECT * FROM newsletter WHERE SendWeek = '0'");

        try {
            while (rs.next()) {
                //SqlHandler.handler.executeUpdate("UPDATE props SET Value='" + value + "' WHERE Property='" + prop + "';");
                if (week1 < week2 && week1 < week3 && week1 < week4) {
                    week1++;
                    sqlHandler.executeUpdate("UPDATE newsletter SET SendWeek='1' WHERE Newsletter_ID=" + rs.getString("Newsletter_ID") + ";");
                } else if (week2 < week3 && week2 < week4 && week2 < week1) {
                    week2++;
                    sqlHandler.executeUpdate("UPDATE newsletter SET SendWeek='2' WHERE Newsletter_ID=" + rs.getString("Newsletter_ID") + ";");
                } else if (week3 < week4 && week3 < week1 && week3 < week2) {
                    week3++;
                    sqlHandler.executeUpdate("UPDATE newsletter SET SendWeek='3' WHERE Newsletter_ID=" + rs.getString("Newsletter_ID") + ";");
                } else {
                    week4++;
                    sqlHandler.executeUpdate("UPDATE newsletter SET SendWeek='4' WHERE Newsletter_ID=" + rs.getString("Newsletter_ID") + ";");

                }
            }
            rs.close();
        } catch (Exception ex) {
            ErrorHandler.handler.handleError(ex);
        }
    }

Thats all the code relating to the problem(I think)

[pbl]

As the error clearly says

at com.mysql.jdbc.ResultSet.next(ResultSet.java:7274)
at massmailer.Driver.sortClients(Driver.java:32)


your error is at line 32 in the Driver class in the sortClients() methods where you probably do a next() on a closed ResultSet.
If you don't post all your code from the class, with the import statements and the class declaration and the instance variables, we won't be able to identify which line is line 32. So when we ask for code, don't just post what you think, is pertinent to post

[Handler]

I was doing .next() but I had not closed the ResultSet, but moved some code round and it seems to be working now. Thanks for being awesome though pbl

EDIT: now its not working again going to check my MySql settings maybe its something in there

This post has been edited by Handler: 03 November 2011 - 09:45 AM

[Handler]

Ok

package massmailer;

import java.sql.*;
import java.util.HashMap;

public class SqlHandler {

    protected static SqlHandler handler;
    private HashMap<String, PreparedStatement> prep = new HashMap<String, PreparedStatement>();
    private String url = "jdbc:mysql://localhost:3306/<EDIT>";
    private Statement stmt;
    private Connection con;

    public SqlHandler() {

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(url, "<EDIT>", "<EDIT>");
            stmt = con.createStatement();

        } catch (Exception ex) {
            ErrorHandler.handler.handleError(ex);
        }
        handler = this;
    }

    private PreparedStatement getStatement(String stat) {
        PreparedStatement preps = null;
        if (prep.get(stat) == null) {
            try {
                preps = con.prepareStatement(stat);
                prep.put(stat, preps);
                return preps;

            } catch (SQLException ex) {
                ErrorHandler.handler.handleError(ex);
                return preps;
            }
        } else {
            preps = prep.get(stat);
            return preps;
        }
    }

    protected ResultSet executeQuery(String command) {
        try {
            return stmt.executeQuery(command);
        } catch (SQLException ex) {
            ErrorHandler.handler.handleError(ex);
            return null;
        }
    }

    protected void executeUpdate(String command) {
        try {
            stmt.executeUpdate(command);
        } catch (SQLException ex) {
            ErrorHandler.handler.handleError(ex);
        }
    }

    protected void sortClients() {
        PropertyHandler propsHandler = PropertyHandler.handler;
        int week1 = Integer.parseInt(propsHandler.getProp("Week1"));
        int week2 = Integer.parseInt(propsHandler.getProp("Week2"));
        int week3 = Integer.parseInt(propsHandler.getProp("Week3"));
        int week4 = Integer.parseInt(propsHandler.getProp("Week4"));
        ResultSet rs = executeQuery("SELECT * FROM newsletter WHERE SendWeek = '0'");

        try {
            while (rs.next()) {
                String ID = rs.getString("Newsletter_ID");
                //SqlHandler.handler.executeUpdate("UPDATE props SET Value='" + value + "' WHERE Property='" + prop + "';");
                if (week1 < week2 && week1 < week3 && week1 < week4) {
                    week1++;
                    propsHandler.setProp("Week1", week1 + "");
                    executeUpdate("UPDATE newsletter SET SendWeek='1' WHERE Newsletter_ID=" + ID + ";");
                } else if (week2 < week3 && week2 < week4 && week2 < week1) {
                    week2++;
                    propsHandler.setProp("Week2", week2 + "");
                    executeUpdate("UPDATE newsletter SET SendWeek='2' WHERE Newsletter_ID=" + ID + ";");
                } else if (week3 < week4 && week3 < week1 && week3 < week2) {
                    week3++;
                    propsHandler.setProp("Week3", week3 + "");
                    executeUpdate("UPDATE newsletter SET SendWeek='3' WHERE Newsletter_ID=" + ID + ";");
                } else {
                    week4++;
                    propsHandler.setProp("Week4", week4 + "");
                    executeUpdate("UPDATE newsletter SET SendWeek='4' WHERE Newsletter_ID=" + ID + ";");

                }
            }
            rs.close();
        } catch (Exception ex) {
            ErrorHandler.handler.handleError(ex);
        }
    }
}

but now i get this 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 massmailer.SqlHandler.sortClients(SqlHandler.java:71)
        at massmailer.Driver.<init>(Driver.java:23)
        at massmailer.Driver.main(Driver.java:30)

[Fuzzyness]

at massmailer.Driver.main(Driver.java:30)

it is in the Driver class main method, so please for the 3rd time, post the entire Driver class so we can help you get this taken care of.

[Handler]

Line 30

 Driver con = new Driver();

line 23

sqlHandler.sortClients();

you dont need the code from the Driver class since all the code that is running is in the SqlHandler class

[pbl]

Handler, on 03 November 2011 - 03:21 PM, said:

you dont need the code from the Driver class since all the code that is running is in the SqlHandler class

I seriously doubt

        at massmailer.Driver.<init>(Driver.java:23)
        at massmailer.Driver.main(Driver.java:30)

We are wasting our time beging for code post after post
You want it fix or not ?

This post has been edited by pbl: 03 November 2011 - 07:48 PM

[Handler]

ok well here is the driver class:

package massmailer;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.ResultSet;

public class Driver {

    ErrorHandler errors;
    SqlHandler sqlHandler;
    PropertyHandler propsHandler;
    private boolean running = true;
    InputStreamReader isr = new InputStreamReader(System.in);
    BufferedReader br = new BufferedReader(isr);
    MailSender test = new MailSender();

    public Driver() {
        System.out.println("Aldes Newsletter Mailer loading...");
        errors = new ErrorHandler();
        sqlHandler = new SqlHandler();
        propsHandler = new PropertyHandler();
        sqlHandler.sortClients();

        begin();

    }

    public static void main(String[] args) {
        Driver con = new Driver();
    }

    private void begin() {
        while (running) {
            mainMenu();
        }
    }

    private void mainMenu() {
        try {
            System.out.println("" + "1)Newsletter\n" + "2)Details\n" + "3)Exit");
            String choice = br.readLine();
            switch (Integer.parseInt(choice)) {
                case 1: {
                    newsletter();
                }
                case 2: {
                    details();
                }
                case 3: {
                    System.exit(0);
                }
                default: {
                }
            }
        } catch (IOException ex) {
            ErrorHandler.handler.handleError(ex);
        }
    }

    private void details() {
        try {
            System.out.println(""
                    + "Number of subs:" + propsHandler.getProp("Subscribed")
                    + "\nTotal Clients: " + propsHandler.getProp("Clients")
                    + "\nLast sent: " + propsHandler.getProp("LastSent")
                    + "\nLast Week: " + propsHandler.getProp("LastWeek")
                    + "\nOutstanding Requests: " + propsHandler.getProp("Requests")
                    + "\nTotal sent: " + propsHandler.getProp("Sent")
                    + "\nPress enter to continue.");
            String choice = br.readLine();
            mainMenu();
        } catch (IOException ex) {
            ErrorHandler.handler.handleError(ex);
        }

    }

    private void newsletter() {
        try {
            System.out.println("" + "What week is it?");
            String choice = br.readLine();
            switch (Integer.parseInt(choice)) {
                case 1: {
                    System.out.println("Have you added the new Newsletter?");
                    br.readLine();
                }
                case 2: {
                }
                case 3: {
                }
                case 4: {
                }
                default: {
                }
            }
        } catch (IOException ex) {
            ErrorHandler.handler.handleError(ex);
        }
    }
}

[pbl]

Strange. And we are sur the ErrorHandler would display something is there is an error ?

P.S.
love your idea of storing the PreparedStatement in a HashMap. Never though of that one.