ResultSet errors in app(MySql)

  • (2 Pages)
  • +
  • 1
  • 2

16 Replies - 3583 Views - Last Post: 04 November 2011 - 02:41 PM Rate Topic: -----

#1 Handler  Icon User is offline

  • D.I.C Head

Reputation: 15
  • View blog
  • Posts: 199
  • Joined: 01-April 10

ResultSet errors in app(MySql)

Posted 30 October 2011 - 04:05 AM

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?

Is This A Good Question/Topic? 0
  • +

Replies To: ResultSet errors in app(MySql)

#2 g00se  Icon User is online

  • D.I.C Lover
  • member icon

Reputation: 2657
  • View blog
  • Posts: 11,207
  • Joined: 20-September 08

Re: ResultSet errors in app(MySql)

Posted 30 October 2011 - 05:26 AM

Could be a good idea to post code of SqlHandler.handler.executeQuery too
Was This Post Helpful? 0
  • +
  • -

#3 Handler  Icon User is offline

  • D.I.C Head

Reputation: 15
  • View blog
  • Posts: 199
  • Joined: 01-April 10

Re: ResultSet errors in app(MySql)

Posted 30 October 2011 - 05:45 AM

Bah ye I posted the wrong method :P
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

Was This Post Helpful? 0
  • +
  • -

#4 Handler  Icon User is offline

  • D.I.C Head

Reputation: 15
  • View blog
  • Posts: 199
  • Joined: 01-April 10

Re: ResultSet errors in app(MySql)

Posted 31 October 2011 - 07:12 AM

Shameful bump ;( but I really need help here
Was This Post Helpful? 0
  • +
  • -

#5 Handler  Icon User is offline

  • D.I.C Head

Reputation: 15
  • View blog
  • Posts: 199
  • Joined: 01-April 10

Re: ResultSet errors in app(MySql)

Posted 01 November 2011 - 08:34 AM

No one? :(
Was This Post Helpful? 0
  • +
  • -

#6 Fuzzyness  Icon User is offline

  • Comp Sci Student
  • member icon

Reputation: 669
  • View blog
  • Posts: 2,438
  • Joined: 06-March 09

Re: ResultSet errors in app(MySql)

Posted 01 November 2011 - 08:40 AM

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

Was This Post Helpful? 0
  • +
  • -

#7 Handler  Icon User is offline

  • D.I.C Head

Reputation: 15
  • View blog
  • Posts: 199
  • Joined: 01-April 10

Re: ResultSet errors in app(MySql)

Posted 03 November 2011 - 08:00 AM

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)
Was This Post Helpful? 0
  • +
  • -

#8 pbl  Icon User is offline

  • There is nothing you can't do with a JTable
  • member icon

Reputation: 8325
  • View blog
  • Posts: 31,857
  • Joined: 06-March 08

Re: ResultSet errors in app(MySql)

Posted 03 November 2011 - 09:15 AM

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 :)
Was This Post Helpful? 0
  • +
  • -

#9 Handler  Icon User is offline

  • D.I.C Head

Reputation: 15
  • View blog
  • Posts: 199
  • Joined: 01-April 10

Re: ResultSet errors in app(MySql)

Posted 03 November 2011 - 09:37 AM

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

Was This Post Helpful? 0
  • +
  • -

#10 Handler  Icon User is offline

  • D.I.C Head

Reputation: 15
  • View blog
  • Posts: 199
  • Joined: 01-April 10

Re: ResultSet errors in app(MySql)

Posted 03 November 2011 - 09:55 AM

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)

Was This Post Helpful? 0
  • +
  • -

#11 Fuzzyness  Icon User is offline

  • Comp Sci Student
  • member icon

Reputation: 669
  • View blog
  • Posts: 2,438
  • Joined: 06-March 09

Re: ResultSet errors in app(MySql)

Posted 03 November 2011 - 10:18 AM

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.
Was This Post Helpful? 0
  • +
  • -

#12 Handler  Icon User is offline

  • D.I.C Head

Reputation: 15
  • View blog
  • Posts: 199
  • Joined: 01-April 10

Re: ResultSet errors in app(MySql)

Posted 03 November 2011 - 12:21 PM

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
Was This Post Helpful? 0
  • +
  • -

#13 pbl  Icon User is offline

  • There is nothing you can't do with a JTable
  • member icon

Reputation: 8325
  • View blog
  • Posts: 31,857
  • Joined: 06-March 08

Re: ResultSet errors in app(MySql)

Posted 03 November 2011 - 07:47 PM

View PostHandler, 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

Was This Post Helpful? 0
  • +
  • -

#14 Handler  Icon User is offline

  • D.I.C Head

Reputation: 15
  • View blog
  • Posts: 199
  • Joined: 01-April 10

Re: ResultSet errors in app(MySql)

Posted 03 November 2011 - 09:59 PM

>.< 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);
        }
    }
}


Was This Post Helpful? 0
  • +
  • -

#15 pbl  Icon User is offline

  • There is nothing you can't do with a JTable
  • member icon

Reputation: 8325
  • View blog
  • Posts: 31,857
  • Joined: 06-March 08

Re: ResultSet errors in app(MySql)

Posted 04 November 2011 - 03:50 AM

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. :^:
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2