Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New to the CF scene
    Join Date
    Jun 2007
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deleting a record created dynamically in a database using Servlets and JDBC

    Hi,

    I have got the codings for inserting and displaying the records in the database. But i couldn't able to delete a specific row, which is created dynamically.

    i.e, if i give any one of the field value in a text box, then using the request.getParameter() method i have to delete the entire record with the value given in the textbox.

    I need the jdbc codings....... for that........

    i have tried the following codings but it does not work.

    Statement st = con.createStatement("DELETE FROM email WHERE email="+'"'+request.getParameter("email1")+'"');
    ResultSet rs=st.executeQuery();


    The solution can also be in MVC pattern.


    It would be so helpful, if u provide me the solution.
    ssdram

  • #2
    ess
    ess is offline
    Regular Coder
    Join Date
    Oct 2006
    Location
    United Kingdom
    Posts
    866
    Thanks
    7
    Thanked 30 Times in 29 Posts
    You should not use the Execute Query statement...as that is suppose to be used with select statements...or other statements that are ought to return record(s) from a given database.

    When updating or deleting records, you should use the Execute Update method. for example
    Code:
    int affectedRecords;
    
    affectedRecords= st.executeUpdate("DELETE FROM email WHERE email="+'"'+request.getParameter("email1")+'"');
    Once you've executed the above statement, you will get an integer value indicating the number of affected row(s)

    Cheers,
    Ess

  • #3
    New to the CF scene
    Join Date
    Jun 2007
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks a lot for your reply. But i still have problem with the coding.
    here is the code for the bean

    dbemailBean.java

    import java.sql.*;

    public class dbemailBean
    {

    Connection con=null;
    String email;


    public void connect()
    {
    try
    {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    con = DriverManager.getConnection("jdbcdbc:emailsource");
    }
    catch(Exception e)
    {
    System.out.println(e);
    }
    }

    public void setEmail(String email)
    {

    this.email=email;
    }



    public void delete()
    {
    try
    {
    Statement st = con.createStatement();

    String email= request.getParameter("email");
    String sql = "DELETE FROM email WHERE email=\'"+email+"\'";
    System.out.println(sql);
    int result =st.executeUpdate(sql);

    }
    catch(Exception e)
    {
    System.out.println(e);
    }
    }

    public String getEmail()
    {
    return email;
    }


    public ResultSet display()
    {
    ResultSet rs=null;
    try
    {
    Statement stmt = con.createStatement();
    rs=stmt.executeQuery("select email from email");
    }
    catch(Exception e)
    {
    System.out.println(e);
    }
    return rs;
    }

    public void close() {
    try {
    if(con != null) {
    con.close();
    }
    }catch(Exception e) {
    System.out.println(e);
    }
    }


    }



    The servlet part is as follows

    dbemailServlet.java

    import javax.servlet.*;
    import javax.servlet.http.*;
    import java.io.*;
    import java.sql.*;

    public class dbemailServlet extends HttpServlet
    {
    public void doGet(HttpServletRequest req,HttpServletResponse res)throws ServletException,IOException
    {
    res.setContentType("text/html");
    PrintWriter pw=res.getWriter();
    try
    {
    dbemailBean bean=new dbemailBean();
    bean.connect();
    RequestDispatcher rd=null;

    if((req.getParameter("action")).equals("display"))
    {
    bean.setEmail(req.getParameter("email1"));

    bean.delete();

    bean.getEmail();

    ResultSet rs=bean.display();
    req.setAttribute("RSObject",rs);
    rd=req.getRequestDispatcher("/displayemail.jsp");
    rd.forward(req,res);

    }
    }
    catch(Exception e)
    {
    System.out.println(e);
    }
    }
    }


    When i use this code errors are not generated but the record is not getting deleted.
    ssdram

  • #4
    ess
    ess is offline
    Regular Coder
    Join Date
    Oct 2006
    Location
    United Kingdom
    Posts
    866
    Thanks
    7
    Thanked 30 Times in 29 Posts
    try and change the following from
    "DELETE FROM email WHERE email=\'"+email+"\'";

    to

    "DELETE FROM email WHERE email='"+email+"'";

    Personally, I usually create SQL statements outside my server side script, test it...check what could possible go wrong i.e. SQL Injection...and once satisfied with the SQL statement...I then add it to the server side script...if the database does not support stored procedures that is.

    Also...I strongly recommend that you make use of regular expressions to validate input before appending parameters to the sql statement. Most SQL Injections attacks occur due to poor server side validation of input parameters.

    If you are not familiar with SQL Injection or regular expressions...do a google search and I am sure you find loads of references.

    By the way...I know it is a daft question...but hopefully...there is no harm in asking. Before adding users to the table...do you check that the supplied email address does not already exist and that emails are unique for every account? if not...than your statement could easily delete a bunch of records if the where clause evaluates to true.

    In any case, let's know if the above suggestion works or not.

    Cheers,
    Ess

  • #5
    New to the CF scene
    Join Date
    Jun 2007
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks a lot.............
    It worked on fine after trying with the coding format u have given.
    ssdram

  • #6
    ess
    ess is offline
    Regular Coder
    Join Date
    Oct 2006
    Location
    United Kingdom
    Posts
    866
    Thanks
    7
    Thanked 30 Times in 29 Posts
    Glad that it worked in the end

    Cheers,
    Ess


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •