#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    7
    Rep Power
    0

    Question Use Preparedstatement to delete several rows from database


    Hello everyone,

    I am trying to delete multiple rows from database at one time using Preparedstatement.
    It works well when I tried in SQL directly,the sql query is as follows:

    delete from planners_offices where planner ='somename' and office in ( 'officeone', 'officetwo', 'officethree')

    I want to delete those 3 rows at one time.

    But when I am using preparedstatement to implement this function, it does not work. It did not throw any exception, but just does not work for me, the updated rows value always returns "0".

    Here is my simplified code:


    PreparedStatement ps = null;
    sqlStr = " delete from PLANNERS_OFFICES where planner = ? and office in (?) "



    Connection con = this.getConnection(dbname);

    try
    {
    //set the sql statement into the preparedstatement
    ps = con.prepareStatement(sqlStr);

    ps.setString(1,"somename");
    ps.setString(2,"'officeone','officetwo','officethree'");


    int rowsUpdated =ps.executeUpdate();
    System.out.println(rowsUpdated);


    }
    //catch exception
    catch (SQLException e)
    {
    System.out.println("SQL Error: " + sqlStr);


    e.printStackTrace();
    }
    catch (Exception e) {
    e.printStackTrace();
    } finally {
    this.releaseConnection(dbname, con);
    }


    try{
    ps.close();
    }catch (SQLException e){
    e.printStackTrace();
    }



    rowsUpdated always give me "0".

    I tried only delete one record at one time, "ps.setString(2, "officeone");", it works fine.
    I am guessing the second value I want to bind to the preparedstatement is not right, I tried several formats of that string, it does not work either.


    Can anyone give me a clue?

    Thanks in advance !

    Rachel
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    18
    Rep Power
    0
    I am not sure, but i think you have to seperate the three variables from each other. Have you tried this?
    Code:
    // your code
    
    sqlStr = "delete from PLANNERS_OFFICES where planner = ? and office in (?,?,?) ";
    
    // more of your code
    
    //set the sql statement into the preparedstatement
    ps = con.prepareStatement(sqlStr);
    
    ps.setString(1,"somename");
    ps.setString(2,"officeone");
    ps.setString(3,"officetwo");
    ps.setString(4,"officethree");
    
    // rest of your code
    I think you don't have to use extra quotes ' in ps.setString(). The function does this for you.

IMN logo majestic logo threadwatch logo seochat tools logo