[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 951
  • Last Modified:

ResultSet is closed

Hi Experts,

I am giving maximum points here because this is a recurring problem. This is something I don't understand and I can't find any logical solution.
The problem ie really simple.

I have a table in my database ( HSQLDB ) that contains only five columns.

Date  Open  High  Low  Close.

I want to get the Close data for a specific date. Simple !

Here is my code :

  public Double getOpenQuote(String symbol, String Date) {
       
        try {
            String query = "SELECT DATE FROM "+symbol+" WHERE DATE='"+Date+"'";
            ResultSet rs = db.query(query);
            rs.first();
            return rs.getDouble("OPEN");  
        } catch (SQLException e) {e.printStackTrace();
            return 0.00;
        }
    }


Here is what I get as result  : ResultSet is Closed !!!!!

java.sql.SQLException: ResultSet is closed
      at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
      at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
      at org.hsqldb.jdbc.jdbcResultSet.checkClosed(Unknown Source)
      at org.hsqldb.jdbc.jdbcResultSet.first(Unknown Source)
      at quotes.DBQuotes.getOpenQuote(DBQuotes.java:221)
      at GUI.EditQuotes$FieldActionListener.actionPerformed(EditQuotes.java:323)
      at javax.swing.JTextField.fireActionPerformed(Unknown Source)
      at javax.swing.JTextField.postActionEvent(Unknown Source)
      at javax.swing.JTextField$NotifyAction.actionPerformed(Unknown Source)
      at javax.swing.SwingUtilities.notifyAction(Unknown Source)
      at javax.swing.JComponent.processKeyBinding(Unknown Source)
      at javax.swing.JComponent.processKeyBindings(Unknown Source)
      at javax.swing.JComponent.processKeyEvent(Unknown Source)
      at java.awt.Component.processEvent(Unknown Source)
      at java.awt.Container.processEvent(Unknown Source)
      at java.awt.Component.dispatchEventImpl(Unknown Source)
      at java.awt.Container.dispatchEventImpl(Unknown Source)
      at java.awt.Component.dispatchEvent(Unknown Source)
      at java.awt.KeyboardFocusManager.redispatchEvent(Unknown Source)
      at java.awt.DefaultKeyboardFocusManager.dispatchKeyEvent(Unknown Source)
      at java.awt.DefaultKeyboardFocusManager.preDispatchKeyEvent(Unknown Source)
      at java.awt.DefaultKeyboardFocusManager.typeAheadAssertions(Unknown Source)
      at java.awt.DefaultKeyboardFocusManager.dispatchEvent(Unknown Source)
      at java.awt.Component.dispatchEventImpl(Unknown Source)
      at java.awt.Container.dispatchEventImpl(Unknown Source)
      at java.awt.Window.dispatchEventImpl(Unknown Source)
      at java.awt.Component.dispatchEvent(Unknown Source)
      at java.awt.EventQueue.dispatchEvent(Unknown Source)
      at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
      at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
      at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
      at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
      at java.awt.EventDispatchThread.run(Unknown Source)

















0
blarouche
Asked:
blarouche
  • 8
  • 6
  • 4
  • +1
1 Solution
 
objectsCommented:
appears the result set is closed, whats the query method doing?
Check it isn't closing the result set before returning.
should also check that at least one row is returned
0
 
blaroucheAuthor Commented:
HERE IS THE QUERY METHOD :

public synchronized ResultSet query(String expression) throws SQLException {

        Statement st = null;
        ResultSet rs = null;

        st = conn.createStatement();
        rs = st.executeQuery(expression);
        st.close();
           
        return rs;
    }
0
 
objectsCommented:
>         st.close();

that will also close the result set
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
blaroucheAuthor Commented:
I am using that method for all database query. It works when my resultSet contains more than 1 row.
I have also notice that when I use resultset.first() method I have problem. When the the first row has to be return the next() works.

It is really weird.

How do you think I can solve that problem ?
0
 
blaroucheAuthor Commented:
     When I use that method to delete quotes it works !! :



               public boolean deleteQuotes(String symbol,String date) {
      
            try {
                  String query = "DELETE FROM "+symbol+" WHERE " +
                        "DATE='"+date+"'";
                  ResultSet r = db.query(query);
                  System.out.println("Line dated "+date+" has been deleted!");
                  return true;
            } catch (SQLException e) {
                  System.err.println(e);
                  System.out.println("Date "+date+" not find. No deleted line.");
                  return false;
            }


      }
0
 
objectsCommented:
that recause u don't access the result set (and a delete does not have one anyway)
0
 
blaroucheAuthor Commented:


In this one I got no problem too !!!




public boolean insertSymbol(String symbol,String fullName,String description,String groupId) {
            
        boolean ret = false;
       
        // TODO: symbol must be a valid set of chars
               
            try {
                  String query;
                  query = "SELECT id FROM symbol WHERE symbol='"+symbol+"'";
                  ResultSet r = db.query(query);
                  if (!r.next()) {
                        query = "INSERT INTO symbol (symbol,fullname,description,groupID) VALUES('"+symbol+"','"+fullName+"','"+description+"',"+groupId+")";
                db.update(query);
                        ret = true;
                  } else {
                        System.out.println("Symbol already exists.");
                        ret = true;
                  }
            } catch (SQLException e) {
                  System.err.println(e);
                  ret = false;
            }
      
        // create table for that symbol
        try {
            db.update("DROP TABLE "+symbol+" IF EXISTS");
            db.update("CREATE "+ TABLE_TYPE +" TABLE "+symbol+" ("  +
                        " DATE  integer NOT NULL,"                           +
                        " TIME  integer DEFAULT '0',"                     +
                        " OPEN  double NOT NULL,"                           +
                        " HIGH  double NOT NULL,"                           +
                        " LOW   double NOT NULL,"                           +
                        " CLOSE double NOT NULL,"                           +
                        " VOLUME double DEFAULT '0',"                          +
                        " OPENINTEREST double DEFAULT '0',"             +
                        " PRIMARY KEY  (DATE,TIME)"                   +
                      ")");
            ret = ret && true;
        } catch (SQLException e) {
            // do nothing is table already there
            ret = ret && false;
        }
       
       
        return ret;
      }
0
 
objectsCommented:
u sure?
check whether the result actually contains anything in your first example.
0
 
owenli27Commented:
Print out ResultSet type to see if it is 1003. If it is, you cannot move cursor backward.

ResultSet rs = db.query(query);
System.out.println("Check ResultSet Type = " + rs.getType());
rs.first();
0
 
achillecarstenCommented:
Greetings sir, try to avoid handing off the query to another method, try this

    public Double getOpenQuote(String symbol, String Date) {
          
          Connection conn = db.getConnection(); // pass your connection here
          PreparedStatement ps = null;
        String query = "SELECT DATE FROM "+symbol+" WHERE DATE=?";
       
        try {
              ps = connection.prepareStatement(query);
              ps.setString(1, Date);
            ResultSet rs = ps.executeQuery();
            rs.first();
            double result = rs.getDouble("OPEN");  
            rs.close();
            ps.close();
            return result;
        } catch (SQLException e) {
                  e.printStackTrace();
              if (ps != null){ try{ ps.close(); } }
            return 0.00;
        }
    }


As you can see, there are two things one should try to do in such a case:
a) save the return value locally, close the resultset, close the statement & then return
b) to avoid leaving an open cursoer on an error, try closing it after an exception
0
 
achillecarstenCommented:
Also: this code is wrong.
public synchronized ResultSet query(String expression) throws SQLException {

        Statement st = null;
        ResultSet rs = null;

        st = conn.createStatement();
        rs = st.executeQuery(expression);
        st.close();
           
        return rs;
    }

See:
http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Statement.html#close()

"A Statement object is automatically closed when it is garbage collected. When a Statement object is closed, its current ResultSet object, if one exists, is also closed."

It is important to know that a resultset is not actual data, it's a reference to an open cursor on the database.

0
 
achillecarstenCommented:
And one last thing extending from the other comments. It may be possible that  you access the resultset object before the Garbage Collector has a chance to destory it, thus this becomes a race between the next method and the garbage collector.
0
 
blaroucheAuthor Commented:

I made a lot of changes to accomodate your code.
Here is my new code :


The connection is made when I open the database with object DBQuotes :

   private void openDB(String databaseName) throws Exception{
          
          //Load the HSQL Database Engine JDBC driver
        // hsqldb.jar should be in the class path or made part of the current jar
        Class.forName("org.hsqldb.jdbcDriver");

        // connect to the database.   This will load the db files and start the
        // database if it is not alread running.
        conn = DriverManager.getConnection("jdbc:hsqldb:"+ databaseName,"sa","");
    }


And my method has been rebuild this way :

    public Double getOpenQuote(String symbol, String Date) {
          
        PreparedStatement ps = null;
        String query = "SELECT OPEN FROM "+symbol+" WHERE DATE='"+Date+"'";
        try {
              ps = conn.prepareStatement(query);
              ps.setString(1,Date);
            ResultSet rs = ps.executeQuery();
            rs.first();
            double result = rs.getDouble("OPEN");
            rs.close();
            ps.close();
            return result;  
        } catch (SQLException e) {
              e.printStackTrace();
             return 0.00;
        } finally{
             if(ps!=null){
                   try{
                   ps.close();
                   }catch(SQLException e){System.out.println("PrintWriter not open");}
             }  
        }
       
    }


Now Here are the result of running the code :

ava.sql.SQLException: Invalid argument in JDBC call: parameter index out of range: 1
      at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
      at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
      at org.hsqldb.jdbc.jdbcPreparedStatement.checkSetParameterIndex(Unknown Source)
      at org.hsqldb.jdbc.jdbcPreparedStatement.setParameter(Unknown Source)
      at org.hsqldb.jdbc.jdbcPreparedStatement.setString(Unknown Source)
      at quotes.DBQuotes.getOpenQuote(DBQuotes.java:242)
      at GUI.EditQuotes$FieldActionListener.actionPerformed(EditQuotes.java:323)
      at javax.swing.JTextField.fireActionPerformed(Unknown Source)
      at javax.swing.JTextField.postActionEvent(Unknown Source)
      at javax.swing.JTextField$NotifyAction.actionPerformed(Unknown Source)
      at javax.swing.SwingUtilities.notifyAction(Unknown Source)
      at javax.swing.JComponent.processKeyBinding(Unknown Source)
      at javax.swing.JComponent.processKeyBindings(Unknown Source)
      at javax.swing.JComponent.processKeyEvent(Unknown Source)
      at java.awt.Component.processEvent(Unknown Source)
      at java.awt.Container.processEvent(Unknown Source)
      at java.awt.Component.dispatchEventImpl(Unknown Source)
      at java.awt.Container.dispatchEventImpl(Unknown Source)
      at java.awt.Component.dispatchEvent(Unknown Source)
      at java.awt.KeyboardFocusManager.redispatchEvent(Unknown Source)
      at java.awt.DefaultKeyboardFocusManager.dispatchKeyEvent(Unknown Source)
      at java.awt.DefaultKeyboardFocusManager.preDispatchKeyEvent(Unknown Source)
      at java.awt.DefaultKeyboardFocusManager.typeAheadAssertions(Unknown Source)
      at java.awt.DefaultKeyboardFocusManager.dispatchEvent(Unknown Source)
      at java.awt.Component.dispatchEventImpl(Unknown Source)
      at java.awt.Container.dispatchEventImpl(Unknown Source)
      at java.awt.Window.dispatchEventImpl(Unknown Source)
      at java.awt.Component.dispatchEvent(Unknown Source)
      at java.awt.EventQueue.dispatchEvent(Unknown Source)
      at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
      at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
      at java.awt.EventDispatchThread.pumpEvents(Unknown Source)0.0







0
 
achillecarstenCommented:
You have not changed this line:       String query = "SELECT OPEN FROM "+symbol+" WHERE DATE='"+Date+"'";
0
 
blaroucheAuthor Commented:

With this line  ---> String query = "SELECT DATE FROM "+symbol+" WHERE DATE=?";

Here is the result :

java.sql.SQLException: ResultSet was set to forward only
0
 
owenli27Commented:
If ResultSet type is set to forward only,  you can not call rs.first() because that will try to moves the cursor backward to the first row.
0
 
achillecarstenCommented:
Change rset.first() to rset.next()
0
 
blaroucheAuthor Commented:
Here is the reult with rs.next:

    public double getOpenQuote(String symbol, String Date) {
          
        PreparedStatement ps = null;
        String query = "SELECT DATE FROM "+symbol+" WHERE DATE=?";
        //String query = "SELECT DATE FROM "+symbol+" WHERE DATE='"+Date+"'";
        try {
              System.out.println(query);
              ps = conn.prepareStatement(query);
              ps.setString(1,Date);
            ResultSet rs = ps.executeQuery();
            rs.next();
            double result = rs.getDouble("OPEN");
            rs.close();
            ps.close();
            return result;  
        } catch (SQLException e) {
              e.printStackTrace();
             return 0;
        } finally{
             if(ps!=null){
                   try{
                   ps.close();
                   }catch(SQLException e){System.out.println("PrintWriter not open");}
             }  
        }
       
    }



java.sql.SQLException: Column not found: OPEN


0
 
blaroucheAuthor Commented:
I found it..

the problem was also because this line was wrong ---> String query = "SELECT DATE FROM "+symbol+" WHERE DATE=?";

I should have changed it to : String query = "SELECT OPEN FROM "+symbol+" WHERE DATE=?";
Also I think my initial code was right it is just that I had to change rs.first() by rs.next() because the ResultSet was set to forward only



Thank you all
0
 
achillecarstenCommented:
well, try running the original code with rs.next(), then
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 8
  • 6
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now