SQL error using MySQL Database

Question

I'm trying to read datafrom the MySQL database. I'm getting the following error message:

2011   1   21333333
2011   2   13500000
2011   3   17285714
2011   4   15500000
2011   5   15800000
2011   6   15900000
2011   7   15875000
2011   8   15600000
2011   9   17666666
2011   10   20000000
2011   11   15958333
2011   12   21583333
2012   1   21519230
2012   2   25450000
2012   3   21400000
2012   4   34166666
2012   5   27928571
2012   6   29250000
2012   7   17550000
2012   8   19111111
2012   9   18200000
2012   10   15181818
2012   11   14455555
2012   12   16900000
2013   1   13500000
2013   2   13600000
2013   3   12812500
java.sql.SQLException: After end of result set//and this one too
0   2.1333333E7    21333333
1   1.35E7    13500000
2   1.7285714E7    17285714
3   1.55E7    15500000
4   1.58E7    15800000
5   1.59E7    15900000
6   1.5875E7    15875000
7   1.56E7    15600000
8   1.7666666E7    17666666
9   2.0E7    20000000
10   1.5958333E7    15958333
11   2.1583333E7    21583333
12   2.151923E7    21519230
13   2.545E7    25450000
14   2.14E7    21400000
15   3.4166666E7    34166666
16   2.7928571E7    27928571
17   2.925E7    29250000
18   1.755E7    17550000
19   1.9111111E7    19111111
20   1.82E7    18200000
21   1.5181818E7    15181818
22   1.4455555E7    14455555
23   1.69E7    16900000
24   1.35E7    13500000
25   1.36E7    13600000
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
    at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImpl.java:841)
    at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2672)
    at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2813)
    at system.HOME.avgr.makeAvg(avgr.java:104)
    at system.HOME.avgr.<init>(avgr.java:64)
    at system.HOME.avgr.main(avgr.java:164)

this are the codes for line 104 i have indicated by comment

public long[] makeAvg() {
    double sum = 0.0;
    Connection conn;
    ResultSet rs1;
    PreparedStatement pstmt;
    try {
        String sql = "SELECT count( 'month' ) AS yt, `Month` , `Year` , avg( `asking_price` ) AS av FROM `tbl_p`WHERE `sale` LIKE 'S' AND b <7 and year between 2011 and 2013  GROUP BY `Year` , `Month` ORDER BY `tbl_p`.`year` ASC";
        conn = (Connection) DBConnection.getDBConnection();
        pstmt = (PreparedStatement) conn.prepareStatement(sql);
        rs1 = pstmt.executeQuery();
        System.out.println(count_of_set + "nmklnmkl");
        for (; count <= count_of_set; count++) {
            if (count == 0) {
                rs1.first();
                avgNumerator[count] = rs1.getInt("av");
                year[count] = rs1.getInt("year");
                month[count] = rs1.getInt("Month");
                System.out.println(year[count] + "   " + month[count] + "   " + avgNumerator[count]);
            } else {
                rs1.next();
                avgNumerator[count] = rs1.getInt("av");
                year[count] = rs1.getInt("year");
                month[count] = rs1.getInt("Month");
                System.out.println(year[count] + "   " + month[count] + "   " + avgNumerator[count]);
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return avgNumerator;
}

You only tell us that you get a java.sql.SQLException. Please include more details. — user1907906
– user1907906, Commented Sep 13, 2013 at 11:15
Does count start at 0? Then for (; count <= count_of_set;count++) should be for (; count < count_of_set;count++) — vikingsteve
– vikingsteve, Commented Sep 13, 2013 at 11:17
I don't see the commented line 104... (though I assume it is the rs1.next() in the else block.) — ppeterka
– ppeterka, Commented Sep 13, 2013 at 11:17

Community · Accepted Answer · 2017-05-23 11:53:08Z

1

This line does smell cheesy:

for (; count <= count_of_set;count++){

If count starts from 0 (as the if(count==0) line would suggest), this tries to iterate (count+1) times...

This would fix the immediate problem:

for (; count < count_of_set;count++){

However, the real solution to the problem would be to use the JDBC API properly, as seen in the answer of @javaBeginner

edited May 23, 2017 at 11:53

CommunityBot

11 silver badge

answered Sep 13, 2013 at 11:16

ppeterka

20.8k6 gold badges67 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SpringLearner · Accepted Answer · 2013-09-13 11:20:58Z

0

Instead of using for loop to retrieve use while loop this way

while(rs1.next())
{
//your code
}

As per your loop,when the last is row is completed still it tries to retrieve data.

edited Sep 13, 2013 at 11:20

answered Sep 13, 2013 at 11:15

SpringLearner

13.9k20 gold badges82 silver badges117 bronze badges

2 Comments

user1907906 Over a year ago

How will this help with a java.sql.SQLException?

SpringLearner Over a year ago

@Tichodroma The op is facing problem when the last row comes.So either while loop will work or ppteka_66 answer

Collectives™ on Stack Overflow

SQL error using MySQL Database

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related