Solved

ResultSet is closed

Posted on 2006-11-24
20
937 Views
Last Modified: 2012-05-05
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
Comment
Question by:blarouche
  • 8
  • 6
  • 4
  • +1
20 Comments
 
LVL 92

Expert Comment

by:objects
ID: 18009587
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
 

Author Comment

by:blarouche
ID: 18009633
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
 
LVL 92

Expert Comment

by:objects
ID: 18009674
>         st.close();

that will also close the result set
0
 

Author Comment

by:blarouche
ID: 18009697
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
 

Author Comment

by:blarouche
ID: 18009718
     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
 
LVL 92

Expert Comment

by:objects
ID: 18009803
that recause u don't access the result set (and a delete does not have one anyway)
0
 

Author Comment

by:blarouche
ID: 18009886


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
 
LVL 92

Expert Comment

by:objects
ID: 18009969
u sure?
check whether the result actually contains anything in your first example.
0
 
LVL 9

Expert Comment

by:owenli27
ID: 18010316
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
 
LVL 1

Expert Comment

by:achillecarsten
ID: 18010327
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 1

Expert Comment

by:achillecarsten
ID: 18010344
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
 
LVL 1

Expert Comment

by:achillecarsten
ID: 18010351
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
 

Author Comment

by:blarouche
ID: 18012279

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
 
LVL 1

Expert Comment

by:achillecarsten
ID: 18012358
You have not changed this line:       String query = "SELECT OPEN FROM "+symbol+" WHERE DATE='"+Date+"'";
0
 

Author Comment

by:blarouche
ID: 18012486

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
 
LVL 9

Expert Comment

by:owenli27
ID: 18013331
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
 
LVL 1

Accepted Solution

by:
achillecarsten earned 500 total points
ID: 18013834
Change rset.first() to rset.next()
0
 

Author Comment

by:blarouche
ID: 18014188
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
 

Author Comment

by:blarouche
ID: 18014262
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
 
LVL 1

Expert Comment

by:achillecarsten
ID: 18014487
well, try running the original code with rs.next(), then
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

For customizing the look of your lightweight component and making it look opaque like it was made of plastic.  This tip assumes your component to be of rectangular shape and completely opaque.   (CODE)
After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now