ResultSet: Exception: set type is TYPE_FORWARD_ONL

Question

I have very simple code:

pstat=con.prepareStatement("select typeid from users where username=? and password=?");             
pstat.setString(1, username);
pstat.setString(2, password);
rs=pstat.executeQuery();
int rowCount=0;
while(rs.next())
{       
    rowCount++;         
}
rs.beforeFirst();
if(rowCount>=1)
{
while(rs.next())
{
    typeID=rs.getInt(1);
}

But when execute this code I am getting...

java.sql.SQLException: Result set type is TYPE_FORWARD_ONLY
at sun.jdbc.odbc.JdbcOdbcResultSet.beforeFirst(Unknown Source)
at server.ClientImpl.login(ClientImpl.java:57)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at sun.rmi.server.UnicastServerRef.dispatch(Unknown Source)
at sun.rmi.transport.Transport$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at sun.rmi.transport.Transport.serviceCall(Unknown Source)
at sun.rmi.transport.tcp.TCPTransport.handleMessages(Unknown Source)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run0(Unknown Source)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)

What is causing this and how can I fix it?

Answer 1

You can only do this with a resultset that is of type TYPE_SCROLL_SENSITIVE, which is defined as "The constant indicating the type for a ResultSet object that is scrollable and generally sensitive to changes made by others."

You need to do something like the following ...

Statement statement = 
 connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
    ResultSet.CONCUR_READ_ONLY);

Answer 2

This question is pretty old. I believe the solution would already have been found. However, i would like to suggest here something different from what Aditya did.

pstat=con.prepareStatement("select typeid from users where username=? and password=?",
                                ResultSet.TYPE_SCROLL_INSENSITIVE, 
                            ResultSet.CONCUR_UPDATABLE);

Instead of ResultSet.TYPE_SCROLL_SENSITIVE, i would use INSENSITIVE

Check this link for refernce

Answer 3

the rowCount variable is not necessary . you are performing two loops on the rs . only the second loop is necessary to get the number of rows which is done by this part of code:

 while (rs.next()){
 typeID=rs.getInt(1); //typeID is the number of rows in the ResultSet
}

Answer 4

Change your first statement to this

pstat=con.prepareStatement("select typeid from users where username=? and password=?",
                            ResultSet.TYPE_SCROLL_SENSITIVE, 
                        ResultSet.CONCUR_UPDATABLE);

This way you can move forward and backward, so less things to worry about

Answer 5

Though this question is old, answers do not age, encountered a similar problem today, this is how I approached it, as here This is the functionality provided by Java JDBC driver, and PostgreSQL database. This case create a Statement object using the default parameters, the system-generated data sets can only be a one-way move the pointer forward, and not two-way mobile data record pointer, the former

Statement stmt = dbConn.createStatement ();
Result rs = stmt.executeQuery (sql);

Changed to
Statement stmt = dbConn.createStatement (ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
Result rs = stmt.executeQuery (sql);

Generated at this time rs can use rs.first () reverse move the pointer operation

Answer 6

Like the exception says: You can't scroll your result set in any other direction than forwards. So when you loop through your result set to get the row count (I don't even understand why you do it), then this row will throw that exception:

rs.beforeFirst();

because that would scroll backwards.

Either create your statement so that you can scroll it (Google for examples) or remove that row counting. I'd suggest the latter, since the count seems unnecessary.