Can I change the Fetch Mode in a Resultset from a DatabaseMetaData query?

Question

I'm inspecting a Database using an DatabaseMetaData instance. I get all the information of the tables in the DB and I iterate all the Resultset without anyproblem.

At the end of the iteration, I want to return to the begin of the ResultSet, so I call the beforeFirst() method followed by the next() to get the first element of the ResultSet. Here's my code:

connect(request.getParameter("source"));
DatabaseMetaData patrol = link.getMetaData();
answer = patrol.getTables(null, null, null, null);
while (answer.next()) {
    String nomTable = answer.getString("TABLE_NAME");
    System.out.println(nomTable)
}

answer.beforeFirst();
answer.next();
String table = answer.getString("TABLE_NAME");
answer.close();

I've got all my results but then I've got this exception:

java.sql.SQLException: Result set type is TYPE_FORWARD_ONLY
        at sun.jdbc.odbc.JdbcOdbcResultSet.beforeFirst(Unknown Source)
        at InspectDB.doPost(InspectDB.java:59)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:747)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:840)
        at com.sun.web.core.ServletWrapper.handleRequest(ServletWrapper.java:155
)
        at com.sun.web.core.InvokerServlet.service(InvokerServlet.java:168)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:840)
        at com.sun.web.core.ServletWrapper.handleRequest(ServletWrapper.java:155
)
        at com.sun.web.core.Context.handleRequest(Context.java:414)
        at com.sun.web.server.ConnectionHandler.run(ConnectionHandler.java:139)

'Till here everything seems normal. According to my connection (a JDBC-OBDC to a MSAcsess), my ResultSet's fetch mode is ONLY_FORWARD, so I tried to change it using

answer.setFetchDirection(ResultSet.FETCH_REVERSE);
answer.beforeFirst();
answer.next();

in order to allow the reserve mode... but it doesn't allow me. New exception:

java.lang.NullPointerException
    at sun.jdbc.odbc.JdbcOdbcResultSet.setFetchDirection(Unknown Source)
    at InspectDB.doPost(InspectDB.java:58)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:747)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:840)
    at com.sun.web.core.ServletWrapper.handleRequest(ServletWrapper.java:155)
    at com.sun.web.core.InvokerServlet.service(InvokerServlet.java:168)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:840)
    at com.sun.web.core.ServletWrapper.handleRequest(ServletWrapper.java:155)
    at com.sun.web.core.Context.handleRequest(Context.java:414)
    at com.sun.web.server.ConnectionHandler.run(ConnectionHandler.java:139)

Is it possible to set the fetch mode to a Resultset comming from a DatabaseMetaData? How can I do it??

Thanks.

Answer 1

The different fetch types supported by a ResultSet object can vary depending on implementation and the database you're querying. It is not possible possible to scroll twice through a FORWARD_ONLY resultset, either by setting the fetch direction or by trying to reset the ResultSet before the first item(although setFetchDirection should throw an SQLException not an NPE). From the ResultSet API.

void setFetchDirection(int direction)
                       throws SQLException

    Gives a hint as to the direction in which the rows in this ResultSet object will be processed. The initial value is determined by the Statement object that produced this ResultSet object. The fetch direction may be changed at any time.

    Parameters:
        direction - an int specifying the suggested fetch direction; one of ResultSet.FETCH_FORWARD, ResultSet.FETCH_REVERSE, or ResultSet.FETCH_UNKNOWN 
    Throws:
        SQLException - if a database access error occurs; this method is called on a closed result set or the result set type is TYPE_FORWARD_ONLY and the fetch direction is not FETCH_FORWARD
    Since:
        1.2
    See Also:
        Statement.setFetchDirection(int), getFetchDirection()

The standard way of dealing with a ResultSet that you need to iterator over multiple times is to iterate through it once and copy it into a List. You can then scroll through the list safely as many times as you want.