shery shery - 4 years ago 154
Java Question

Invalid cursor state - no current row when retrieving integer value from sql table

I have been searching a lot about this but nothing solved my problem, I am trying to get the id (that is

INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY
) of a registered user from sql table called "USERS", when calling
int _id = rs2.getInt("UserID");
it throws me an exception :
Invalid cursor state - no current row.

here is what I have so far:

in Servlet.java:

try{
stmt = conn.prepareStatement(UAppConstants.SELECT_USERID_BY_NAME);
System.out.println("1");
stmt.setString(1, _username);
System.out.println("2");
ResultSet rs2 = stmt.executeQuery();
System.out.println("3");
int _id = rs2.getInt("UserID"); //exception here
System.out.println("the registered user id is" + _id);

}catch (SQLException e) {
System.err.println(e.getMessage()); // print error
}


in UAppConstants.java:

public final String CREATE_USERS_TABLE = "CREATE TABLE USERS (UserID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) PRIMARY KEY,username varchar(10) NOT NULL,password varchar(8) NOT NULL,description varchar(50) DEFAULT NULL,nickname varchar(20) NOT NULL,photo varchar(100) DEFAULT NULL)";
public final String INSERT_USER_STMT = "INSERT INTO USERS (username,password,nickname,description,photo) VALUES (?,?,?,?,?)";
public final String SELECT_USERID_BY_NAME = "SELECT UserID FROM USERS WHERE username=? ";


UserModel.java:

public class User {
private String Username, Password, Description, Photo,Nickname;//user "schema"
private int UserID;


public User(String name, String pass, String nick,String desc,String photo ) {
Username = name;
Password = pass;
Description = desc;
Photo = photo;
Nickname = nick;
}



/* Getters & Setters */

public int getUserID() {
return UserID;
}

public void setUserID(int uid) {
this.UserID = uid;
}

//the rest of getters and setters


Exception:

INFO: Reloading Context with name [/ExampleServletv3] is completed
1
2
3
Invalid cursor state - no current row.


can someone direct me what to do?
thank you

Answer Source

After executeQuery, the cursor is positioned before the first row. So you cannot access any data yet. First, you need to call next() to move the cursor to the first row.

So it should be:

stmt = conn.prepareStatement(UAppConstants.SELECT_USERID_BY_NAME);
stmt.setString(1, _username);
ResultSet rs2 = stmt.executeQuery();
if (rs2.next()) {
    int _id = rs2.getInt("UserID");
    // do something with the id
} else {
    // user not found
}

And a tip for exception handling: Instead of plastering your code with dull print statements and not handling an exception except for a very minimal message, rethrow the exception:

try {
    // code
} catch (SQLException e) {
    throw new RuntimeException(e);
}

That way you get a full error message including all the nested exception and you also get the line where the exception was thrown.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download