Nicky Nicky - 6 months ago 18
SQL Question

Operation not allowed after ResultSet closed?

I understand that this problem occurs when ResultSet can only be open once at a time. So I added
UPDATE

try
{
String sql = "SELECT * FROM test;
ResultSet rs = stmt.executeQuery(sql);

if(rs.next()){
do {
if(rs.getString("sent").equals("0")) {
try {

//update

try {
String psSQL = "UPDATE sent SET ..........";
updateStatement = conn.prepareStatement(psSQL);
updateStatement.setString(1, ....);
updateStatement.setString(2, ....);
updateStatement.setString(3, ...);

updateStatement.executeUpdate();
System.out.println("Record updated");
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
if(updateStatement != null) {
updateStatement.close();
}
if (conn != null) {
conn.close();
}
}

} catch (MessagingException mex) {
mex.printStackTrace();
}
}
} while(rs.next()); **<---- Still same Error here**
} else if (rs.getRow() == 0) {
if (!mailSender.equals(".....")) {
try{
//insert

try {
String psSQL2 = "INSERT INTO sent"
+ "(......) VALUES"
+ "(?,?,?)";
insertStatement = conn.prepareStatement(psSQL2);
insertStatement.setString(1, ...);
insertStatement.setString(2, ....);
insertStatement.setString(3, ....);

insertStatement.executeUpdate();
System.out.println("Insert done!");
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
if(insertStatement != null) {
insertStatement.close();
}
if (conn != null) {
conn.close();
}
}
}catch (MessagingException mex) {
mex.printStackTrace();
}
} else {
return null;
}
}
}
catch (SQLException se)
{
se.printStackTrace();
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
try
{
if (stmt != null) {
stmt.close();
}
}
catch (SQLException se2) {}
try
{
if (conn != null) {
conn.close();
}
}
catch (SQLException se)
{
se.printStackTrace();
}
}


So.. Im getting errors when while(rs.next()) runs. I put in a system.out.println before and after rs.next() while loop and nth sows after that while loop.

I tried to use another resultset rs2 but it is not working either so I have no idea here does this problem come from..

Answer

A better way of doing what you want is using a single ResultSet

  String sql = "SELECT * FROM test";
  ResultSet rs = stmt.executeQuery(sql);

  if(rs.next()){
      do {

        if (rs.getString("sent").equals("0")) {

          try{
             //if there is a row and it is equals to 0.. so update it to 1.. 

             String sql2 = "UPDATE test SET test='1' WHERE test=....";
             Statement updateStatement = <create statement here>;

             int rs3 = updateStatement.executeUpdate();
             updateStatement.close();
          }catch (SQLException sqlex) { 
             sqlex.printStackTrace();
          }
        }
     }while(rs.next());
   }else {
      try{
             //insert if i couldn't get any rows in the table
             String sql2 = "INSERT INTO test......";
             Statement insertStatement = <create statement here>;
             int rs3 = insertStatement.executeUpdate();
             insertStatement.close();
          }catch (SQLException sqlex) {
             sqlex.printStackTrace();
          }
    }

    rs.close();
    stmt.close();
    conn.close(); 

Hope this helps.