0

I have a problem with an empty result set, which are throwing some errors. But it is working fine as long as it is not empty.

String sql = "SELECT M2.fromProfileId, profiles.profileMiniature, profiles.firstName, profiles.lastName, profiles.timeFormat, lastMessages.message, lastMessages.timeStamp " +
                "FROM   (" +
                "       SELECT M1.fromProfileId, " +
                "           max(M1.timeStamp) AS lastMessageTime " +
                "       FROM messages AS M1 " +
                "       WHERE M1.toProfileId = ? " +
                "       GROUP BY M1.fromProfileId " +
                "       ORDER BY max(M1.timeStamp) DESC " +
                "       LIMIT 10 " +//line 60
                "       ) AS M2 " +
                "INNER JOIN messages AS lastMessages " +
                "ON     (" +
                "       lastMessages.timeStamp = M2.lastMessageTime " +
                "AND    lastMessages.fromProfileId = M2.fromProfileId" +
                "       )" +
                "INNER JOIN profiles " +
                "ON M2.fromProfileId = profiles.profileId ";

PreparedStatement statement = con.prepareStatement(sql);

statement.setString(1, profileId);
ResultSet result = statement.executeQuery();

JSONArray messages = new JSONArray();
while(result.next()){
    JSONObject message = new JSONObject();

    message.put("fromProfileId", result.getString("fromProfileId"));
    message.put("profileMiniature", result.getString("profileMiniature"));
    message.put("firstName", result.getString("firstName"));
    message.put("lastName", result.getString("lastName"));
    message.put("lastMessage", result.getString("message"));
    message.put("lastMessageTime", result.getString("timeStamp"));
    message.put("timeFormat", result.getString("timeFormat"));
    messages.put(message);
}

and the error is Illegal operation on empty result set. How shall I fix this?

StackTrace:

Illegal operation on empty result set. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927) at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImpl.java:841) at com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5656) at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5576) at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5616) at messages.GetMessages.doGet(GetMessages.java:60) at javax.servlet.http.HttpServlet.service(HttpServlet.java:707) at javax.servlet.http.HttpServlet.service(HttpServlet.java:820) at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:534) etc..

18
  • 3
    Full stacktrace will be very helpful here Commented Mar 4, 2011 at 21:07
  • Hmmm. It could be a bug in the version of MySQL JDBC driver that you're using! Commented Mar 4, 2011 at 21:13
  • 1
    What are you doing at GetMessages.java line 60 ? Commented Mar 4, 2011 at 21:13
  • Ditto as above comment. I too want to know. Commented Mar 4, 2011 at 21:16
  • 1
    @Woho87 : If there are not many rows returned by the query, you can try to debug step by step, to see exactly what happens. Commented Mar 4, 2011 at 21:42

1 Answer 1

2

I found many bugs in MySQL JDBC driver which seem to cause this exception.

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.