problem with ResultSet closed

730895

    hello everyone. i wrote some code that supposed to find info about the user by his id in some db. when i run my program it fails in the second loop on the line:

    while (resultM.next()){

    the error is :

    Exception in thread "main" java.sql.SQLException: Operation not allowed after ResultSet closed
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
    at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:795)
    at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:7146)
    at newpackage.db.findHistoryPatient(db.java:109)
    at newpackage.db.main(db.java:29)

    thanks a lot for helping!!!

    the code is:


    statement = (Statement) connect.createStatement();
    // Result set get the result of the SQL query

    resultSet = statement.executeQuery( "select * from FEEDBACK.visit where patient='"+id+"'");

    while (resultSet.next()) {
    //System.out.println("the user is correct ");
    //numH is a key to search in history_medicines
    String numH = resultSet.getString("num");
    String description=resultSet.getString("description");




    resultM = statement.executeQuery( "select * from FEEDBACK.history_medicines where id_visit='"+Integer.parseInt(numH)+"'");


    while (resultM.next()){
    int id_medicines=resultM.getInt("id_medicines");
    History=History.concat(resultM.getString("startDate").toString()+"-");
    History =History.concat(resultM.getString("endDate")+" ");
    History=History.concat(description+" ");

    ResultSet resultSetH = null;
    resultSetH = statement.executeQuery( "select * from FEEDBACK.medicines where id='"+id_medicines+"'");
    while (resultSetH.next())
    History=History.concat(resultSetH.getString("name")+" ");
    //History=History.concat(resultSetH.getString("name")+" ");
    //String MedicinesName=findMedicinesName(resultM.getInt("id_medicines"));

    System.out.print(History);


    }

    }

    }
    finally {
    close();
    }

      • 1. Re: problem with ResultSet closed
        802889
        Reading the JDK API documentation would have provided you your answer:
        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 are executing a new query with the same statement within the loop, which closes the first resultset.
        1 位用户发现它有用
        • 2. Re: problem with ResultSet closed
          730895
          ok thanks, but how can i fix it?
          if in the first db i get all the id_visit and from the second db i get all all the info.
          • 3. Re: problem with ResultSet closed
            darrylburke
            Moderator action: Moved from Java Programming.
            Moderator advice: 'user12163960', please read the announcement(s) at the top of the forum listings. They're there for a purpose. Then edit your post and format the code correctly.
            • 4. Re: problem with ResultSet closed
              darrylburke
              user12163960 wrote:
              ok thanks, but how can i fix it?
              if in the first db i get all the id_visit and from the second db i get all all the info.
              I don't do JDBC, but it seems to me this is no longer a Java question.

              A single SQL SELECT with WHERE clauses can extract related records from all tables.

              Also, I recommend that you use PreparedStatement and avoid Statement.

              db
              • 5. Re: problem with ResultSet closed
                jschellSomeoneStoleMyAlias
                user12163960 wrote:
                ok thanks, but how can i fix it?
                if in the first db i get all the id_visit and from the second db i get all all the info.
                db means "database".

                If you are in fact using two different databases (general usage) then you need to connection objects.

                If you meant table instead or just as an odd way to refer to two SQL statements then you were already told the answer.

                Use two Statement instances.

                And if you don't understand something in the above sentence then ask about it.
                • 6. Re: problem with ResultSet closed
                  Joe Weinstein-Oracle
                  To fix it, just create and use a second separate statement in the
                  while-loop.