Solved

ResultSet is closed

Posted on 2006-11-24
20
941 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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
 
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 Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
javap bin 2 40
java jdbc batch example 8 39
check java version using powershell 13 164
difference between sorce folder and folder in eclipise 3 28
Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

789 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