Streaming ResultSet Error

Question

I am trying to run multiple MySQL queries which build up on each other: i.e field value of one element in each row is used as input for another query.

I end up getting the following error:

java.sql.SQLException: Streaming result set com.mysql.jdbc.RowDataDynamic@174cc1f is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries.
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:914)
    at com.mysql.jdbc.MysqlIO.checkForOutstandingStreamingData(MysqlIO.java:2074)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1484)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3170)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3099)
    at com.mysql.jdbc.Statement.executeQuery(Statement.java:1169)
    at Stats.readInterfaces(Stats.java:105)
    at Stats.connect(Stats.java:63)
    at automateExport.main(automateExport.java:15)

I have called .close() after every ResultSet and Statement of the query. I guess we cannot have multiple resultsets open at one time. Is there any way to get around this problem?

Here is the relevant code:

public class Stats {
    public static int UTC = 0;
    public String interfaceId = "no value";
    public String rId = "no value";
    public String NL = System.getProperty("line.separator");
    public String CSV = ",";
    public static String startTime,endTime,performanceTable =null;
    public static int outputType = 1;
    public String pTable = "('2010-7-13 00:00')";
    public String start = "('2010-7-13 09:00')";
    public String end = "('2010-7-13 17:00')";
    Connection conn;
    Statement stmtRouter, stmtInterface, stmtTime, stmtD;
    String query;
    ResultSet rsRouter, rsInterface, rsD, rsTime;
    public Connection connect(String db_connect_str,String db_userid, String db_password) {
      String routerName,routerId = null, routerNetwork = null;
     // inputfile - csv

        try {
         // to bifurcate heap memory error   
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection(db_connect_str,db_userid, db_password);
            stmtRouter = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,  
                    java.sql.ResultSet.CONCUR_READ_ONLY);
            stmtRouter.setFetchSize(Integer.MIN_VALUE); 

            query = "Select r.name,r.rid,r.network FROM router AS r Where r.network = 'ITPN'";



            String append = null;
            // writing to file
            performanceTable = readTime(pTable);
            startTime = readTime(start);
            endTime = readTime(end);
            rsRouter = stmtRouter.executeQuery(query); 
            while (rsRouter.next()) {
                routerName = rsRouter.getString(1);
                System.out.println(routerName);
           //   routerId = rsRouter.getString("rid");
           //   routerNetwork = rsRouter.getString("network");
                append = routerName+CSV+routerId+CSV+routerNetwork;
                readInterfaces(routerId,startTime,endTime,performanceTable, append);

            }

            stmtRouter.close() ;
            rsRouter.close();
            //      output(2,input);
    //              output(outputType , input);
        } catch(Exception e) {
            e.printStackTrace();
            conn = null;
        }
        return conn;
    }
    private String readTime(String time) throws SQLException {
        stmtTime = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,  
                java.sql.ResultSet.CONCUR_READ_ONLY);
        stmtTime.setFetchSize(Integer.MIN_VALUE); 
        query = "Select unix_timestamp"+time;
        rsTime = stmtTime.executeQuery(query); 
        String unixTime = null;
        while(rsTime.next()){
            unixTime = rsTime.getString(1);
            System.out.println(unixTime);
        }

        rsTime.close();
        stmtTime.close();
        return unixTime;
    }

    private void readInterfaces(String routerId, String startTime, String endTime, String performanceTable, String append) throws SQLException, IOException {
        String interfaceId, iDescp, iStatus = null;
        String dtime, ingress, egress = null;
        stmtInterface = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,  
                java.sql.ResultSet.CONCUR_READ_ONLY);
        stmtInterface.setFetchSize(Integer.MIN_VALUE); 
        stmtD = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,  
                java.sql.ResultSet.CONCUR_READ_ONLY);
        stmtD.setFetchSize(Integer.MIN_VALUE); 

        query = " Select i.id,i.description, i.status from interface as i Where i.rid = " + routerId +" And i.status = 'active'";
        rsInterface = stmtInterface.executeQuery(query);
        String input = "inputData.txt";
        BufferedWriter fw = new BufferedWriter(new FileWriter(input));  
        stmtInterface.close();
        while(rsInterface.next()){
            interfaceId = rsInterface.getString("id");
            iDescp = rsInterface.getString("description");
            iStatus = rsInterface.getString("status");

            if(!iStatus.equals("active")){
                /* performance table query*/
                query = " Select d.dtime,d.ifInOctets, d.ifOutOctets from "+performanceTable+"_1_60" +" AS d Where d.id = " +
                interfaceId + "AND dtime BETWEEN " +startTime+ " AND "+ endTime + " Order By d.id";
                rsD = stmtD.executeQuery(query); 

                while(rsD.next()){
                    dtime = rsD.getString("dtime");
                    ingress = rsD.getString("ifInOctets");
                    egress = rsD.getString("ifOutOctets");
                    fw.write(append + CSV + interfaceId+CSV+iDescp+CSV+dtime+CSV+ingress+CSV+egress+NL);
                }// end of while
                rsD.close();
                stmtD.close();

            }
        }
        fw.close();
    //    rsInterface.close() ; 
       // stmtInterface.close();

    }

}

Answer 1

You definitely shouldn't close Statement object before you end working with ResultSet (function readInterfaces).

rsInterface = stmtInterface.executeQuery(query);
String input = "inputData.txt";
BufferedWriter fw = new BufferedWriter(new FileWriter(input));  

stmtInterface.close(); // Don't do this!!! It should be when you are done with rsInterface)
while(rsInterface.next()){

Answer 2

You cannot have more than one result set being open per connection. I believe here is the reason for the failure:

You call readInterfaces(routerId,startTime,endTime,performanceTable, append) (where you open new resultset) before you close Router resultset:

  readInterfaces(routerId,startTime,endTime,performanceTable, append);
}
stmtRouter.close() ;
rsRouter.close();

I would move close statements before the call to readInterfaces(...). I would also close the result set and statement in opposite order (resultset first).

Answer 3

Exactly, as the error says, you cannot issue additional queries over the same connection when you have a streaming result set open. Relevant documentation is here.

So, the obvious solution would be not to use a streaming result set for the driving query. The downside of this is that it will use more memory. The rather obscure comment at the top of your code implies that maybe someone tried this already and had memory issues.

Anyway, there is a better solution. This is a classic example of over-proceduralization. You are doing work in your code that could be better handled by the database engine. Instead of executing single-table queries that drive each other, you can combine those into one query using a join:

Select r.name,r.rid,r.network,i.id,i.description, i.status
FROM router AS r JOIN interface as i ON i.rid = r.rid
Where r.network = 'ITPN'
  AND i.status='active'

Then, you have a third "nested" query, which you can incorporate with an additional join. I think it would be:

Select r.name,r.rid,r.network,i.id,i.description, i.status,d.dtime,d.ifInOctets, d.ifOutOctets
FROM router AS r JOIN interface as i ON i.rid = r.rid
  JOIN <performanceTable>_1_60 as d ON d.id = i.id
Where r.network = 'ITPN'
  AND i.status='active'
  AND dtime BETWEEN <startTime> AND <endTime>
Order By d.id

You probably don't need all those columns in the final select list, but I haven't pored through your code to see what is really being used.