title: SQL Server, JTDS causes java.sql.SQLException: Invalid state, the ResultSet object is closed
body: <p>I am using Tomcat 7, Microsoft SQL Server 2008 RC2 and JTDS driver</p>
  
  <p>I am actually using C3P0 as well to try and solve this problem, but it makes no difference at all. I was using Microsoft's driver but it caused me other problems (the requested operation is not supported on forward only result sets)</p>
  
  <p>I get the following error, always at the same point.  I have successfully run other queries before getting to this point:</p>
  
  <blockquote>
    <p>java.sql.SQLException: Invalid state, the ResultSet object is closed.
    at
    net.sourceforge.jtds.jdbc.JtdsResultSet.checkOpen(JtdsResultSet.java:287)
    at
    net.sourceforge.jtds.jdbc.JtdsResultSet.findColumn(JtdsResultSet.java:943)
    at
    net.sourceforge.jtds.jdbc.JtdsResultSet.getInt(JtdsResultSet.java:968)
    at
    com.mchange.v2.c3p0.impl.NewProxyResultSet.getInt(NewProxyResultSet.java:2573)
    at com.tt.web.WebPosition.createPosition(WebPosition.java:863)</p>
  </blockquote>
  
  <p>The code is as follows:</p>
  
  <pre><code>public static List getListPositions(String query) {
      Connection con = null;
      Statement stmt = null;
      ResultSet rs = null;
      List list = null;
      try { //execute the sql query and create the resultSet
          con = DBConnection.getInstance().getConnection();
          stmt = con.createStatement();           
          rs  = stmt.executeQuery(query);
          while(rs.next()) {
              if(rs.isFirst()) {
                  list = new ArrayList();
              }
              WebPosition webPos = null;
              webPos = new WebPosition(rs);
  
              list.add(webPos);
          }           
  
      } catch (java.sql.SQLException e) {
          System.out.println("SQLException in getListPositions");
          System.out.print(query);
          Log.getInstance().write(query);
          Log.getInstance().write(e.toString());
      } catch (Exception ex) {
          System.out.print(ex);
          ex.printStackTrace();
          Log.getInstance().write(ex.toString());
      } finally {
          if (rs != null) {
              try {
                  rs.close();
              } catch (SQLException e) {
                  Log.getInstance().write(e.toString());
              }
          }
          if (stmt != null) {
              try {
                  stmt.close();
              } catch (SQLException e) {
                  Log.getInstance().write(e.toString());
              }
          }
          DBConnection.getInstance().returnConnection(con);
      }
      return list;
  }
  
  public WebPosition(ResultSet rs) {
    createPosition( rs);
  }
  
  public void createPosition(ResultSet rs) {
      try {
  
          this.setCurrentDate4Excel(rs.getString("SYSDATE_4_EXCEL"));
          this.setExerciseType(rs.getInt("EXERCISE_STYLE_CD"));
          ...
  </code></pre>
  
  <p>The code fails in between the above two lines.</p>
  
  <p>I am at a loss to explain why the Result set would be closed in the middle of a function (i.e. it would retrieve rs.getString("SYSDATE_4_EXCEL") but then fail with the error posted at the line rs.getInt("EXERCISE_STYLE_CD"))</p>
  
  <p>Does anyone have any ideas? I imagine that it is some kind of memory issue, and that the connection is automatically closed after a certain amount of data, but I am not sure how to fix this. I tried increasing the heapsize for the JVM.</p>