All geek questions in one place

Stream Resulting Error

I am trying to run several MySQL queries that accumulate on top of each other: i.e. the field value of one element in each row is used as input for another query.

As a result, I get 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 called .close()after each ResultSetand Statementrequest. Probably, we cannot simultaneously open several results. Is there any way 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();

    }

}
+3
source share
4 answers

You cannot open more than one result set for each connection. I believe that here is the reason for the failure:

readInterfaces (routerId, startTime, endTime, performanceTable, append) ( ) , Router:

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

readInterfaces (...). ( ).

+1

, , , . .

, . , . , , , - .

, . . , . , , , :

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'

"" , . , :

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

, , , , .

+3

You should not close the Statement object before you finish working with the ResultSet (readInterfaces function).

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()){
0
source

ConnectionAn object must not contain multiple objects ResultSetat a time.

After creating the objects ResultSetand Statementeach of them should be explicitly closed, for example,

resultSet.close()
statement.close()
-1
source

Source: https://habr.com/ru/post/1761156/

More articles:


All Articles