java.sql.SLQException: Illegal operation on empty result set

Question

I got a login system in java and i want to get de user, password and type of user from database. But when i run the programe i got the folowing error: java.sql.SLQException: Illegal operation on empty result set Code:

    conn=MysqlConnect.ConnectDB();
    String Sql="Select*from utilizador where Nome='" + Username +"' and Password='" + Password +"' and Permissao'" + Permissao + "'" ;

    try{
        pst=conn.prepareStatement(Sql);
        Statement st = conn.createStatement();
        ResultSet rs = st.executeQuery(Sql);
        int permissao = rs.getInt("Permissao");
        String nome = rs.getString("Nome");
        String password = rs.getString("Password");

        if(rs.next()){

1. use prepared statements. 2. Your query is lacking a ``=`` at the ``permissao`` part. 3. read the exception message. It's trying to tell you something. — f1sh, Dec 22 '16 at 12:59

score 2 · Accepted Answer · edited Dec 22 '16 at 13:36

Your code has various issues:

You missed an = after and Permissao
From your code it seems you are looking for a user filtering by the following fields: Username, Password and Permissao, so you should have 3 variables defined
You are trying to access the ResultSet (using rs.getXXX) before selecting any rows. After the executeQuery method you "fill" a ResultSet but his index is not pointing to any valid "database rows" so you need to call "rs.next()" in order to move the index to the first row. Consecutive calls move the index ahead of 1 position every time until the ResultSet finishes.

Having said so, you should:

Use a prepared statement that prevents sql injection and other typo/character errors as it automatically escapes parameter values.
In the prepared statement use ? to define the parameters you'll need to set using s.set<TypeOfField>
Check if ResultSet has rows before using rs.get
Close connection, statement, and result set in the finally clause, so the resources will be closed either if there is or there is not an exception. Doing so you will prevent memory leak due to opened resources that you are not using anymore.
You should have 3 variable to perform the select: (I suppose)
- Username of type String
- Password of type String
- Permissao of type int/Integer

Try using the following code, adapted to your needs.

    Connection c = DB.dbConnect(null);
    PreparedStatement s = null;
    ResultSet rs = null;
    try {
        final String SQL = " Select * from utilizador where Nome=? and Password=? and Permissao = ? ";

        s = c.prepareStatement(SQL);

        int i = 1;
        s.setString(i++, Username);
        s.setString(i++, Password);
        s.setInt(i++, Permissao);

        rs = s.executeQuery();
        if (rs.next()) {
             int permissao = rs.getInt("Permissao");
             String nome = rs.getString("Nome");
             String password = rs.getString("Password");
        }

    } catch (SQLException e) {          
        // exception handler
    } finally {
        try {
            if (rs != null)
                rs.close();
        } catch (Exception e) {
        }
        try {
            if (s != null)
                s.close();
        } catch (Exception e) {
        }try {
            if (c != null)
                c.close();
         } catch (Exception e) {
        }
    }

I'd look to suggest that you answer the question first (that is why this error occurs (ie: no call to `rs.next()` before using `getXXX`), and then proceed to address the other problems in the code in the question. That makes it more obvious that you are actually answering the question. — Mark Rotteveel, Dec 22 '16 at 13:20
it gives me the folow error at " s.setString(i++, Username);/ s.setString(i++, Password); — Rui Santos, Dec 22 '16 at 13:20
it gives me the folow error at " s.setString(i++, Username);/ s.setString(i++, Password); incompatible types: JPasswordField cannot be converted to String — Rui Santos, Dec 22 '16 at 13:24
You should have `Username, Passord and Permissao` respectivly as `String,String, Integer`. So you need to cast/extract the value before set the statement using `s.setXXX` — Marcx, Dec 22 '16 at 13:31
For your problem about JPasswordField check this out: http://stackoverflow.com/questions/983964/why-does-jpasswordfield-getpassword-create-a-string-with-the-password-in-it — Marcx, Dec 22 '16 at 13:32

Rahul · Answer 2 · 2016-12-22T13:02:25.623

0

Clearly the issue is with your query which is lacking proper quotes and spaces. It should be

   String Sql="Select * from utilizador where Nome = '" + Username +"' and Password='" + Password +"' and Permissao = '" + Permissao + "'" ;

edited Dec 22 '16 at 13:02

answered Dec 22 '16 at 12:59

Rahul

76,197
13
71
125

1

I don't think fixing quotes is the right thing to do here - they should be using prepared statements properly and not dumping unsanitized values directly into the SQL in the first place. – JonK Dec 22 '16 at 13:00
Something like ``SELECT*FROM`` looks wrong, but it works in MySql. The real problem is the missing ``=`` at the last condition. – f1sh Dec 22 '16 at 13:01
@JonK, absolutely but to start with is what I suggested. – Rahul Dec 22 '16 at 13:02
@f1sh, yes nice catch. Thanks for pointing. – Rahul Dec 22 '16 at 13:03
i put the "=", it keeps giving me the same error – Rui Santos Dec 22 '16 at 13:07

score 0 · Answer 3 · answered Dec 22 '16 at 12:59

0

Bad looking, unreadable code.

Here's your problem:

    String nome = rs.getString("Nome");
    String password = rs.getString("Password");

You try to access values from the ResultSet before checking to see if it has any rows.

answered Dec 22 '16 at 12:59

duffymo

305,152
44
369
561

I'm a recent learner, it's the first programe i do by my self – Rui Santos Dec 22 '16 at 13:08
I would say it's unusual for a first program to involve relational databases. Do yourself a favor: learn the Java coding standards, follow them religiously, and start paying attention to formatting and style. Your code is hard to read. It'll make it harder for you and others to read and understand your code. – duffymo Dec 22 '16 at 13:09

java.sql.SLQException: Illegal operation on empty result set

3 Answers3