6 Replies Latest reply on Oct 18, 2010 3:21 PM by Joe Weinstein-Oracle

    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 person found this helpful
          • 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.