MySQL + JAVA Exception: Before start of result set

Question

try
  {
   PreparedStatement  s = (PreparedStatement) conn.prepareStatement("SELECT voters.Check,count(*) FROM voting.voters where FirstName="+first+"and LastName="+last+" and SSN="+voter_ID);
   //java.sql.Statement k = conn.createStatement();

         rs=s.executeQuery();
               //s.executeQuery("SELECT voters.Check,count(*) FROM voting.voters where FirstName="+first+"and LastName="+last+" and SSN="+voter_ID);

         System.out.println(rs.first());
         c=rs.getInt(1);
         d=rs.getInt(2);

         System.out.println(c);
         System.out.println(d);

          if(c==1 && d==1)
          {
           s.executeUpdate("update cand set total=total+1 where ssn="+can_ID);
           System.out.println("Succeful vote");
           System.out.println("after vote");
           s.executeUpdate("update voters set voters.Check=1 where ssn="+voter_ID);
                 toclient=1;



             PreparedStatement  qw = (PreparedStatement) conn.prepareStatement("select FirstName from cand where ssn="+can_ID);

                 // rs=k.executeQuery("select FirstName from cand where ssn="+can_ID);
             rs1 = qw.executeQuery();//Error Here Plz help me
                  String name1= (String) rs1.getString(1);

                  System.out.println(name1);
                  s.executeUpdate("update voters set VTO="+name1+"where ssn="+voter_ID);
           System.out.println(rs.getString(1));

          }
          else
          {
           if(c != -1)
            toclient =2;
           if( d ==0)
            toclient =3;
           if( d>1)
            toclient =4;

          }
           System.out.println("out-----------");
           rs.close();

           s.close();

  }

   catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }

Error IS :

java.sql.SQLException: Before start of result set
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1072)
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:986)
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:981)

Answer 1

Call rs1.first() before using the ResultSet.

Moves the cursor to the first row in this ResultSet object.

Initially the cursor position of the ResultSet is before the start of the set. The first() method returns true if there is data in the set. So preferably:

if (rs1.first()) {
    String name1 = (String) rs1.getString(1);
}

Answer 2

While rs1.first() may work, to avoid exception I would like to avoid it and use rs1.next() instead.

See javadoc of ResultSet.first():

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

SQLFeatureNotSupportedException - if the JDBC driver does not support this method

while next doesn't have this limitation

Code:

if (rs1.next()) {
    String name1 = rs1.getString(1);
}

Tips: avoid useless type casting (your code is full of them)

Answer 3

The common practice is to use rs.next() method with while cycle:

PreparedStatement st = conn.prepareStatement("select 1 from mytable");
ResultSet rs = st.executeQuery();
while (rs.next()) {
  // do something with result set
}
rs.close();
st.close();

I've omitted try/catch/finally clauses for clarity. Note that you should invoke each close() method in separate finally block.

Answer 4

When you get a resultset, the cursor is placed before the first row. Trying to get anything before moving your cursor to the first row will cause the error you received. You need to move the cursor to the first row using this line:

rs1.first();

before calling

String name1 = (String) rs1.getString(1);

Of course, make sure the resultset contains entries before calling rs1.getString(1).

Answer 5

In your code snippet you create PreparedStatements but you do not use them correctly. Prepared statements are meant to be used as a kind of 'statement template' which is bound to values before it executes. To quote the javadoc:

   PreparedStatement pstmt = con.prepareStatement(
                                 "UPDATE EMPLOYEES SET SALARY = ? WHERE ID = ?");
   pstmt.setBigDecimal(1, 153833.00)
   pstmt.setInt(2, 110592)

This has two big advantages over your current usage of PreparedStatement:

• one PreparedStatement can be used for multiple executes
• it prevents a possible SQL injection attack

The second one here is the biggie, if for instance your variables first and last are collected in a user interface and not reformatted, you run the risk of parts of SQL being input for those values, which then end up in your statements! Using bound parameters they will just be used as values, not part of the SQL statement.

Answer 6

So, to be sure the proper use of PreparedStatment, here is your original example adjusted for best practices (note the cast is redundant):

PreparedStatement s = conn.prepareStatement(
    "SELECT voters.Check,count(*) " +
    "FROM voting.voters " +
    "where FirstName=? and LastName=? and SSN=?");
s.setString(1,first);
s.setString(2,last);
s.setString(3,voter_ID);
ResultSet rs = s.executeQuery();
while( rs.next() ) {
    c = rs.getInt(1);
    d = rs.getInt(2);
}

Hope this helps... :)