Deleting Rows of an Updateable ResultSet

[Jeremy Hooie]

I'm using an Updateable ResultSet to loop through records in the database and if a record's start date occurs before a certain date, I want to delete the record from the ResultSet and the database.

Calendar scheduleDate = Calendar.getInstance();
scheduleDate.clear();
sql = "SELECT * FROM schedule WHERE item = '" + order + "'";
Statement updateable = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet schedule = updateable.executeQuery(sql);
while(schedule.next())
{
  java.sql.Date date = schedule.getDate("schedule_date");
  scheduleDate.setTime(date);
  if(scheduleDate.before(dropDate))
  {
    schedule.deleteRow();
  }
}
schedule.close();
schedule = null;

When the code hits the deleteRow() method, I get a "Degree of derived table does not match column list" SQLException. Any ideas?
Jeremy

[Jamie Robertson]

just wondering why you can't use the following code instead (doesn't seem to be a need for scrollable resultsets..)

String sql = "DELETE FROM schedule WHERE item = '" + order + "' AND schedule_date < ? ";
PreparedStatement updateable = conn.prepareStatement(sql);
//set the drop date parameter
updateable.setDate( new java.sql.Date( dropDate.getTime() ) );//assuming dropDate is a java.util.Date
//execute the delete
int rows_updated = updateable.executeUdpate();
updateable.close();
updateable = null;

Jamie

[Jeremy Hooie]

I can, I just wanted to familiarize myself with the Updateable ResultSet. I guess this is why I haven't done it before now.
Jeremy