MySQL, using JDBC in a Java application - Seemingly empty result set, but query is well formed

Question

I receive the error message (java.sql.SQLException) java.sql.SQLException: Before start of result set when I am trying to get a record from a MySQL database. This is the code I am using:

public TimelineJDBC(String name, String startTime){          
      final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
      final String DB_URL = "jdbc:mysql://localhost/TimeLineDB";
      final String USER = "root";
      final String PASS = "XXXX";
    try{
       Class.forName("com.mysql.jdbc.Driver");
       Connection con = DriverManager.getConnection(DB_URL, USER, PASS);

       String sql= "SELECT * FROM TIMELINE WHERE NAME= 'MyLife2' AND STARTTIME= '2008-02-01';";
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery(sql);

        this.timeline.setId(rs.getInt("ID"));
        this.timeline.setName(rs.getString("NAME"));
        this.timeline.setDescriprion(rs.getString("DESCRIPTION"));
        this.timeline.setStartTime(rs.getString("STARTTIME"));
        this.timeline.setEndTime(rs.getString("ENDTIME"));
        this.timeline.setPicture(rs.getString("PICTURE"));
        TimeUnit enumVal =  TimeUnit.valueOf(rs.getString("SCALE"));
        this.timeline.setScale(enumVal);

    }
    catch(SQLException ex){
        System.err.println("Error in retrieving a timeline record from database:"+ ex.getMessage());
    }
    catch (Exception e){
         System.err.println(e.getMessage()); 
    }  
}

Once trying to read the result set this.timeline.setId(rs.getInt("ID")); this SQLException is thrown: (java.sql.SQLException) java.sql.SQLException: Before start of result set

I am aware of the issue where you have to go to the next result set (rs.next();) but this doesn't seem to help, since the next result set is null. Here's a screenshot showing what is returned in the initial result set: https://i.sstatic.net/6NB6b.jpg

When trying to run the query manually on the same database, a correct ResultSet is formed, so it seems the problem doesn't lie with the database.

You have to call ResultSet.next() before you can access the data. ResultSet.next() does not return "the next result set". It returns a boolean indicating if there is another row available. It seems you are confusing this with Statement.getMoreResults() — user330315
– user330315, Commented May 22, 2014 at 13:02
I agree with what the above poster said. Just to add - if the result set comes empty then you may have another problem that. Do what they say first and report back. — kacpr
– kacpr, Commented May 22, 2014 at 13:13
What happens when you use A. Agarwal's solution? It looks correct. — johnny
– johnny, Commented May 22, 2014 at 13:17
By default resultset cursor will be pointing to before first row of the results. When you call ResultSet.next() only the internal resultSet cursor will be moved next row. and this call returns the whether next row is available or not. so please make sure that call the rs.next() — Sudhakar
– Sudhakar, Commented May 22, 2014 at 13:38
According to that comment on user3663519's answer, it doesn't seem to be about retrieving the result set anymore, but rather that the set is empty, even though that query, done manually, apparently returns a proper result. — Lars
– Lars, Commented May 22, 2014 at 17:55

A. Agarwal · Accepted Answer · 2014-05-22 13:10:45Z

4

You should use

while (rs.next()) { // for multiple rows
// get from rs here
}

or

if (rs.next()) { // for single row
// get from rs here
}

answered May 22, 2014 at 13:10

A. Agarwal

4154 silver badges12 bronze badges

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

Comments

Simulant · Accepted Answer · 2014-05-22 13:17:58Z

1

while(rs.next()) {

    this.timeline.setId(rs.getInt("ID"));

    this.timeline.setName(rs.getString("NAME"));
    this.timeline.setDescriprion(rs.getString("DESCRIPTION"));
    this.timeline.setStartTime(rs.getString("STARTTIME"));
    this.timeline.setEndTime(rs.getString("ENDTIME"));
    this.timeline.setPicture(rs.getString("PICTURE"));

    TimeUnit enumVal =  TimeUnit.valueOf(rs.getString("SCALE"));

    this.timeline.setScale(enumVal);

}

if you kept this instead of that it will work

edited May 22, 2014 at 13:17

Simulant

20.2k10 gold badges68 silver badges106 bronze badges

answered May 22, 2014 at 13:12

patnala pavan kumar

214 bronze badges

1 Comment

a_Mehrnaz Over a year ago

I used that loop, the control goes inside the loop but again after the line "this.timeline.setId(rs.getInt("ID"));" there will be an error which is : "e = (java.lang.NullPointerException) java.lang.NullPointerException"

Collectives™ on Stack Overflow

MySQL, using JDBC in a Java application - Seemingly empty result set, but query is well formed

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related