JDBC.SQLServerException: The result set has no current row

Question

So, a solution I created threw this exception: jdbc.SQLServerException: The result set has no current row on the line marked in the below code.

public String get64BitEncodedImageBySiteID(int siteID){
    try {           
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        Connection conn = DriverManager.getConnection(url, userName, password);

        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery( "SELECT SitePicture FROM SiteTable WHERE SiteID ="+siteID );

        rs.next();
        // The above line has since been moved to the if statement below where you can see it commented out,
        // which prevents the exception from occuring but still doesn't fix the fact that the row is not being found. 

        if(/*rs.next() &&*/ rs.getBytes("SitePicture")!=null){ // EXCEPTION THROWN HERE!
            byte ba[] = rs.getBytes("SitePicture");            
            return new sun.misc.BASE64Encoder().encodeBuffer(ba);
        }
        else {return null;}
    } catch (Exception ex) {
        ex.printStackTrace();
    }
    return null;
}

The method above, in the instance the exception was thrown, is taking a genuine siteID (22379) from an Entity object pulled directly from the same table. When using System.out.println(siteID); during this method, it declared that number to still be correct, ie still 22379. I've checked directly with the SQL server by running an identical statement in SQL Server, so I know the row exists in the table, but for some reason it is not being found. Image below.

So the problem is, the ResultsSet rs is not finding the row even though I know that it's there. Does anyone have any helpful insights?

Clarification: Just to be clear, I know that the ResultsSet contains no rows and that is why I am getting the exception. I also know that putting the rs.next() into the if statement will prevent the exception (as already stated in the comments). What is puzzling me is that the fact the ResultsSet contains no rows even though a row with the ID being parsed to it verifiably does exists because I have checked it directly with the SQL server.

Solution 1

This turned out to be a local mistake, but I'll post the solution anyway because this situation has some educational value.

As I've learned from @Ralph's comment to this answer, eliminating "the impossible" is a good way for such problems.

After avoiding the risk of siteID being wrong (by hardcoding it), we have a following situation:

• the same exact query worked in one environment, but not the other, for only one particular SiteID, 2184
• it's impossible that ResultSet just doesn't work for this particular value (I claim it is, because I always assume errors are in my code, not in language libraries)
• if so, the databases must differ

Solution 2

Adding result statements inside while loop helped in my case. while(rs.next) { rs.getString("your column name"); }

Solution 3

The most likely explanation is that your ResultSet contains no rows. Have you checked that?

If that's the case, rs.next() will return false, but you are not checking the return value any more. Put rs.next() back into the if block, it was OK in there.

You can make sure by:

if (rs.next()) {

  if(rs.getBytes("SitePicture")!=null){ 
     byte ba[] = rs.getBytes("SitePicture");            
     return new sun.misc.BASE64Encoder().encodeBuffer(ba);
  }

} else {

   System.out.println("No rows returned");

}

EDIT:

what column type is siteID? Your method takes an int, but your SQL wraps it in quotes, as if it were a string.

EDIT 2:

Using a PreparedStatement might solve your problem.

PreparedStatement ps = conn.prepareStatement("SELECT SitePicture FROM SiteTable WHERE SiteID = ?");
ps.setInt(1, siteId);
ResultSet rs = ps.executeQuery();

Comments

[NickJ]

Instead of putting the parameter directly into the SQL string (not recommended anyway), try using parameter placeholders in a prepared statement: docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

[NickJ]

Edited my answer to include PreparedStatement example

[Brooklyn99]

I faced the same issue today. but discovered that without if (rs.next) condition it got failed and when added this condition it worked.