CODEWITHSUNDEEP
javasqlsqlitejdbc
john smith
john smith

Reputation: 185

How can I keep the resultSet open, or make calls to different resultSets?

I am running queries on 2 separate databases in the same .java file, using JDBC and SQLite, but I am getting an error that the Result Set is closed when I try to make my second call to a separate result set. I set up the connections and everything and then do 3 separate calls, like this....followed by calls to the ResultSet objects to get data. I get an error on the first call to mealSet

        Class.forName("org.sqlite.JDBC");
        Connection conn1 = null;
        conn1 = DriverManager.getConnection("jdbc:sqlite:workoutLog.db");
        @SuppressWarnings("null")
        PreparedStatement workoutQuery= conn1.prepareStatement("SELECT * FROM workoutLog");// ORDER BY t desc limit 1");
        ResultSet workoutSet = workoutQuery.executeQuery();


        Class.forName("org.sqlite.JDBC");
        Connection conn2 = null;
        conn2 = DriverManager.getConnection("jdbc:sqlite:foodLog.db");
        PreparedStatement getFoodLog = conn2.prepareStatement("SELECT * FROM foodLog");// ORDER BY t desc limit 3");
        ResultSet mealSet = getFoodLog.executeQuery();
        mealSet.next();

        meals.add(breakfast);
        meals.add(lunch);
        meals.add(dinner);

        weekStart = workoutSet.getInt(ID_COLUMN);
        weekEnd = weekEnd + 6;
        Class.forName("org.sqlite.JDBC");
        Connection conn3 = null;
        conn3 = DriverManager.getConnection("jdbc:sqlite:projectLog.db");
        PreparedStatement getProjectLog = conn3.prepareStatement("SELECT * FROM projectLog");
        ResultSet projectSet = getProjectLog.executeQuery();
        ResultSetMetaData meta = projectSet.getMetaData();


        for (int i=3;i<meta.getColumnCount()-1;i++){
            String prj="";
            projects.add(prj);
        }


        while ( projectSet.next()){

            for (int i=3;i<=meta.getColumnCount()-1;i++){
                int arrayPos = 0;
                String temp =projectSet.getString(i) + "\n";
                projects.set(arrayPos, projects.get(arrayPos).concat(temp));
                projectNames.add(meta.getColumnName(i));
                totalHours+=projectSet.getInt(3);
                totalMinutes+=projectSet.getInt(meta.getColumnCount());
                arrayPos++;
            }


            for (int k=0;k<=2;k++){
            breakfast=mealSet.getString(BREAKFAST_COLUMN)+" \n";
            lunch=mealSet.getString(LUNCH_COLUMN)+" \n"; 
            dinner=mealSet.getString(DINNER_COLUMN)+" \n";
            meals.set(0, meals.get(0).concat(breakfast));
            meals.set(1, meals.get(1).concat(lunch));
            meals.set(2, meals.get(2).concat(dinner));
            mealSet.next(); 
        }

error being...

Exception in thread "main" java.sql.SQLException: ResultSet closed
    at org.sqlite.RS.checkOpen(RS.java:63)
    at org.sqlite.RS.markCol(RS.java:77)
    at org.sqlite.RS.getString(RS.java:313)
    at WeeklyReport.main(WeeklyReport.java:99)

Upvotes: 2

Views: 5640

Answers (1)

Adam Siemion
Adam Siemion

Reputation: 16029

You cannot reuse the same ResultSet instance for numerous queries. One ResultSet instance can only be used only to fetch the results of one query.

Moreover as written in the ResultSet JavaDoc:

ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.

Upvotes: 1

Related Questions