How to fix 'Invalid Cursor SQL Exception' on SqlRowSet.Next() in Java

Question

After getting SqlRowSet object from H2-database, before retrieving data from the rows with SqlRowSet.getDouble(); I call SqlRowSet.next();

I've tried doing while(SqlRowSet.next()){} but this shouldn't be needed as I always only have 1 row in the data .

public SqlRowSet findBySkuSize(String sku, String size) {
    return jdbcTemplate.queryForRowSet("SELECT * from PRODUCT_DIMENSIONS where SKU = '" + sku + "' and SIZE = '" + size + "'");
}

the above method populates the SqlRowSet object (in the debugger i can see that there is 1 row shown in the image below.

        if(CountRows(dimensionResults) < 1) 
        {

        }
        else 
        {
            dimensionResults.next();
            someObject.objectSetterMethod(dimensionResults.getDouble("DEPTH"));

        }

The above code is what throws an InvalidCursorException The stack trace is too long to paste I guess but this is the error message

Caused by: java.sql.SQLException: Invalid cursor position
at com.sun.rowset.CachedRowSetImpl.next(CachedRowSetImpl.java:1460)
at org.springframework.jdbc.support.rowset.ResultSetWrappingSqlRowSet.next(ResultSetWrappingSqlRowSet.java:697)
... 67 more

org.springframework.jdbc.InvalidResultSetAccessException: Invalid cursor position; nested exception is java.sql.SQLException: Invalid cursor position
at org.springframework.jdbc.support.rowset.ResultSetWrappingSqlRowSet.next(ResultSetWrappingSqlRowSet.java:700)
at com.footlocker.commerce.shipping.service.FreightCalculationService.UpdateProductDimensions(FreightCalculationService.java:72)
at com.footlocker.commerce.shipping.service.FreightCalculationService.UpdateProductList(FreightCalculationService.java:50)
at com.footlocker.commerce.shipping.service.FreightCalculationService.ProcessFreightCalculationRequest(FreightCalculationService.java:41)
at com.footlocker.commerce.shipping.controller.FreightController.Test(FreightController.java:34)

For some reason it's telling me my cursorPosition is at 2? should it start at 0? and how can it be 2 if I only have 1 Row.

Edit: I was able to change the cursor position in the debuger menu from 2 to 0 and dimensionResults.next(); worked.. So why is it that when I instantiate the object it starts at cursor position 2? Im royally confused as to why Java has to be so complicated compared to C# and finding resources to make sense of everything is difficult as well lol.

What does `CountRows` do ? Chances are that this method brings you to the end of the resultset. In that case you may want to call `beforeFirst()` to go back to the initial position of the resultset. — Arnaud, Jan 15 '19 at 15:35
Wow I didn't even think of that! I guess im so used to not having to do it in such a way. I guess I was thinking that when I passed the object to a Method that it would pass a copy and whatever happened within the method to that Object wouldn't reflect. Thank you so much, you should make that the answer. — boo, Jan 15 '19 at 15:43

Arnaud · Accepted Answer · 2019-01-15T16:12:00.377

1

Apparently your CountRows method brings you to the end of the resultset.

In that case you may want to call beforeFirst() to go back to the initial position of the resultset.

cf. how to reset the result set to the first row after looping through a while loop

Also your position 2 is where you are after the resulset has been browsed, this is the "after last" position, the same one where you would be after calling afterLast().

Finally, you may want to have a look at the following question : How do I get the size of a java.sql.ResultSet?

edited Jan 15 '19 at 16:12

answered Jan 15 '19 at 15:45

Arnaud

17,229
3
31
44

1

Thank you! I simply added your suggestion of rows.beforeFirst(); before returning to the calling method. – boo Jan 15 '19 at 15:47

How to fix 'Invalid Cursor SQL Exception' on SqlRowSet.Next() in Java

1 Answers1