Invalid Cursor State, what it is and how to avoid it

[tim]

Greetings everyone.

What is an Invalid Cursor State?
The Invalid Cursor State exception thrown by a ResultSet from an MS Access database is an infamous one. Surprisingly, there aren't many forums that discuss this problem in depth. But there are many people asking the same questions. I've been searching Google on this topic and made the following conclusions. This exception is thrown if a result set is requested to access records with invalid positions in that set. For example, the record before the first, if the set is nonempty. Or the record after the last, if the set is nonempty. Or, if the set is empty, any record position.
What causes it, how can I fix it?
The most common source of this exception, is calling one of the data type getters from a result set without calling next(). The correct way to handle a result set is to traverse its records once. Always close the result set's statement when done. It is good practice to use a new statement for each result set. This avoids other exceptions caused by closed result sets or closed statements.

while (resultSet.next()) {
    Object[] record = new Object[columnCount];
    for (int i = 1; i <= columnCount; i++) {
        record[i - 1] = resultSet.getObject(i);
    }
    addRecord(record);
}
resultSet.getStatement().close();

Another cause is the easy navigation solution most forum members recommend. Scroll Insensitive result sets.

Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

So why is this a problem? Well, it turns out that this is partially supported. For most queries, result sets are scrollable. But, occasionally the Invalid Cursor State exception is thrown when least expected. Complicated queries, or queries executing concurrently will cause this exception to be thrown. So how can we fix this? If the result set is relatively small, cache it. In other words, represent the result set in some data structure in memory by reading the result set linearly and then closing its statement. Now you can access the data in any direction or manner. The obvious problem occurs when the result set is large. In this case, it would be better not to cache it, but restrict access to a linear fashion. That is, one record accessed directly after the previous one.

Thanks for reading. ; )