Page 1 of 2 12 LastLast
Results 1 to 20 of 22
  1. #1
    Rook is offline Member
    Join Date
    Jun 2011
    Posts
    13
    Rep Power
    0

    Default Proper SQL Delete statement

    I would like to know what would be the proper SQL statement to delete something.

    So far I have

    String overall=request.getParameter ("overall");

    Connection=Connection.connection(generic connection not the problem)
    String sql="select from name where overall="+overall;
    connection.executeQuery(sql);

    This will run it just wont delete anything.

  2. #2
    doWhile is offline Moderator
    Join Date
    Jul 2010
    Location
    California
    Posts
    1,642
    Rep Power
    7

    Default

    Do not use executeQuery for operations like this - this method is to perform database Queries. To perform updates, use executeUpdate or execute

  3. #3
    DarrylBurke's Avatar
    DarrylBurke is offline Member
    Join Date
    Sep 2008
    Location
    Madgaon, Goa, India
    Posts
    11,188
    Rep Power
    19

    Default

    Quote Originally Posted by Rook View Post
    I would like to know what would be the proper SQL statement to delete something.
    Apart from what doWhile has already pointed out -- Click here.

    db

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,842
    Rep Power
    19

    Default

    Don't do JDBC until you know SQL.

    That's Rule 1.

  5. #5
    Rook is offline Member
    Join Date
    Jun 2011
    Posts
    13
    Rep Power
    0

    Default

    Quote Originally Posted by DarrylBurke View Post
    Apart from what doWhile has already pointed out -- Click here.

    db
    I did do that it still doesn't work I come here and for your experience not your smartcomments.

  6. #6
    Rook is offline Member
    Join Date
    Jun 2011
    Posts
    13
    Rep Power
    0

    Default

    Quote Originally Posted by doWhile View Post
    Do not use executeQuery for operations like this - this method is to perform database Queries. To perform updates, use executeUpdate or execute
    I have done this and the variable I pass through is an invalid identifier.
    Last edited by Rook; 06-16-2011 at 02:31 PM.

  7. #7
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,842
    Rep Power
    19

    Default

    Come on, the query you posted above is a SELECT...it's not even a DELETE.

    So do as I suggested and learn SQL.
    We're on a teaching environment, at least not to that extent.

  8. #8
    Rook is offline Member
    Join Date
    Jun 2011
    Posts
    13
    Rep Power
    0

    Default

    Quote Originally Posted by Tolls View Post
    Come on, the query you posted above is a SELECT...it's not even a DELETE.

    So do as I suggested and learn SQL.
    We're on a teaching environment, at least not to that extent.
    I posted the wrong page the select goes to a result set and then I have a link that forwards the information I know sql unions joins etc who cares I just dont know why the statement keeps kicking me back

    String naming=request.getParameter ("naming");
    String message="";

    String sql="delete from name where overall="+naming;
    connection.executeUpdate(sql);

  9. #9
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,842
    Rep Power
    19

    Default

    You would do better to post actual code.
    That code won't have a hope in compiling as a Connection object does not have any sort of sql execution methods.

    Next, you would also do well to explain your problem, because I can't tell whether it's a delete you're having problems with or a select or what.
    That would include any exceptions, including full stack traces.

  10. #10
    Rook is offline Member
    Join Date
    Jun 2011
    Posts
    13
    Rep Power
    0

    Default

    Quote Originally Posted by Tolls View Post
    You would do better to post actual code.
    That code won't have a hope in compiling as a Connection object does not have any sort of sql execution methods.

    Next, you would also do well to explain your problem, because I can't tell whether it's a delete you're having problems with or a select or what.
    That would include any exceptions, including full stack traces.
    Page 1
    String naming=request.getParameter ("naming");
    String message1=request.getParameter("message1");
    String message="";

    JspConnection connection = new JspConnection(this);

    String sql="select * from name";
    ResultSet result=connection.executeQuery(sql);
    while(result.next())
    {
    %>
    <%=result.getString("overall")%>
    <a href="deleting.jsp?name=<%=result.getString("overa ll")%>">Delete</a>
    <%
    }
    connection.close(); %>
    Page 2
    <%


    String naming=request.getParameter ("naming");
    String message="";

    JspConnection connection = new JspConnection(this);
    {


    String sql="delete from name where overall="+naming;
    connection.executeUpdate(sql);
    connection.close();
    }
    //try
    //{


    //}
    //catch (SQLException sqlex)
    //{
    //message="ERROR : "+sqlex.getMessage();
    //}


    %>

    <a href="tesing.jsp">Back"</a>

    no stack errors...just wont delete..I made this 2 page item because I am working osmething that doesn't want to add or delete as well. It points to the same database. The connection is controlled by the web inf file.
    Last edited by Rook; 06-16-2011 at 05:05 PM.

  11. #11
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,842
    Rep Power
    19

    Default

    What on earth is a JspConnection?

  12. #12
    Rook is offline Member
    Join Date
    Jun 2011
    Posts
    13
    Rep Power
    0

    Default

    Quote Originally Posted by Tolls View Post
    What on earth is a JspConnection?
    It is my connection statement controlled by the web inf. It is essentially a class file.

    this statment pulls it in...<%@ page import="**.***.***.* ,**.***.***.database.*,java.sql.*,***.***.***.***. *" %>

    this is the url in the web inf

    (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=***) (HOST=**-oracle-**.**.**.**)(PORT=***)))(CONNECT_DATA=(SID=**)(SER VER=****)))
    Last edited by Rook; 06-16-2011 at 05:34 PM.

  13. #13
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,842
    Rep Power
    19

    Default

    But it's not standard...so how can we comment?
    How can we see whether it is logging any exceptions?

  14. #14
    Rook is offline Member
    Join Date
    Jun 2011
    Posts
    13
    Rep Power
    0

    Default

    Quote Originally Posted by Tolls View Post
    But it's not standard...so how can we comment?
    How can we see whether it is logging any exceptions?
    The only exceptions this jspconnection will give is invalid username invalid password invalid url all it handles is the connection. I have had problems correcting this the lead proggrammer says it is connecting to the data base now. What would you want from the class file? Tomcat is not throwing any errors.

    This simpliest way to explain it is that it is standered. It is what you would put in to a jsp. instead they made it all a generic class. so 20 lines go down to 1.
    Last edited by Rook; 06-16-2011 at 06:04 PM.

  15. #15
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,842
    Rep Power
    19

    Default

    So...what's your problem?
    (Apart from writing business logic into a JSP page, of course, and not using PreparedStatements).

    Something is either eating an exception, or the code is working and there's nothing to delete where "overall = naming".

    Neither situation I can comment on as there is no code there.

  16. #16
    Rook is offline Member
    Join Date
    Jun 2011
    Posts
    13
    Rep Power
    0

    Default

    From what is written here why would the delete statement not be deleting from the database? Am I missing something in my sql commands? here I will make it throw an error...

  17. #17
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,842
    Rep Power
    19

    Default

    Assuming the "overall" column is a NUMBER of some sort and naming (though a String) is also a number then that SQL is OK.
    If it wasn't legal then it would throw an exception when you executed it.

    As to whether it should actually delete anything, have you tried to run it against the db directly through SQLPlus (since it looks like you have an Oracle db there)?
    Print out that SQL command so you can be certain you are doing the same as your code above.

  18. #18
    Rook is offline Member
    Join Date
    Jun 2011
    Posts
    13
    Rep Power
    0

    Default

    Overall columns are string based not number based.Checking on sql. This is my issue lol...Need someone with much greater experience than me. The tables I am using have been resurrected from the dead as well. They havent been used in over 2 years I dont think that is a problem. The tables that I am calling from right now though has been made 2 days ago.

    Main thing here is that delete from name where overall='oom' is different from "delete from name where overall="+naming

    this .jsp little program was giving me invalid identifier issues. also it doesn't like the +naming it mans ADDING to tomcat
    Last edited by Rook; 06-16-2011 at 06:40 PM.

  19. #19
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,842
    Rep Power
    19

    Default

    If that column is a VARCHAR then that delete statement will throw a SQL exception.
    No two ways about it.
    DELETE FROM table_name WHERE varchar_field = something
    will throw an exception since there's no quotes around that text.

    If you are not getting an exception then your framework leaves a lot to be desired.

  20. #20
    Rook is offline Member
    Join Date
    Jun 2011
    Posts
    13
    Rep Power
    0

    Default

    Should I change the value of it to something besides varchar? Could you post what the sql format should be in .jsp format? This is what I get when I put it in double qoutes.

    I did not design the framework.

    type Exception report

    message

    description The server encountered an internal error () that prevented it from fulfilling this request.

    exception

    org.apache.jasper.JasperException: An exception occurred processing JSP page /deleting.jsp at line 14

    11:
    12:
    13: String sql="delete from name where overall="+"naming";
    14: connection.executeUpdate(sql);
    15: connection.close();
    16: }
    17: //try


    Stacktrace:
    org.apache.jasper.servlet.JspServletWrapper.handle JspException(JspServletWrapper.java:519)
    org.apache.jasper.servlet.JspServletWrapper.servic e(JspServletWrapper.java:410)
    org.apache.jasper.servlet.JspServlet.serviceJspFil e(JspServlet.java:313)
    org.apache.jasper.servlet.JspServlet.service(JspSe rvlet.java:260)
    javax.servlet.http.HttpServlet.service(HttpServlet .java:717)
    edu.wayne.med.sso.filters.CookieCheckFilter.doFilt er(CookieCheckFilter.java:142)

    root cause

    javax.servlet.ServletException: java.sql.SQLException: ORA-00904: "NAMING": invalid identifier

    org.apache.jasper.runtime.PageContextImpl.doHandle PageException(PageContextImpl.java:865)
    org.apache.jasper.runtime.PageContextImpl.handlePa geException(PageContextImpl.java:794)
    org.apache.jsp.deleting_jsp._jspService(deleting_j sp.java:93)
    org.apache.jasper.runtime.HttpJspBase.service(Http JspBase.java:70)
    javax.servlet.http.HttpServlet.service(HttpServlet .java:717)
    org.apache.jasper.servlet.JspServletWrapper.servic e(JspServletWrapper.java:386)
    org.apache.jasper.servlet.JspServlet.serviceJspFil e(JspServlet.java:313)
    org.apache.jasper.servlet.JspServlet.service(JspSe rvlet.java:260)
    javax.servlet.http.HttpServlet.service(HttpServlet .java:717)
    edu.wayne.med.sso.filters.CookieCheckFilter.doFilt er(CookieCheckFilter.java:142)

    root cause

    java.sql.SQLException: ORA-00904: "NAMING": invalid identifier

    oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:168)
    oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:2 08)
    oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
    oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol .java:1405)
    oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TT C7Protocol.java:822)
    oracle.jdbc.driver.OracleStatement.executeNonQuery (OracleStatement.java:1446)
    oracle.jdbc.driver.OracleStatement.doExecuteOther( OracleStatement.java:1371)
    oracle.jdbc.driver.OracleStatement.doExecuteWithTi meout(OracleStatement.java:1900)
    oracle.jdbc.driver.OracleStatement.executeUpdate(O racleStatement.java:693)
    edu.wayne.med.database.GenericConnection.executeUp date(GenericConnection.java:153)
    edu.wayne.med.database.GenericConnection.executeUp date(GenericConnection.java:146)
    org.apache.jsp.deleting_jsp._jspService(deleting_j sp.java:70)
    org.apache.jasper.runtime.HttpJspBase.service(Http JspBase.java:70)
    javax.servlet.http.HttpServlet.service(HttpServlet .java:717)
    org.apache.jasper.servlet.JspServletWrapper.servic e(JspServletWrapper.java:386)
    org.apache.jasper.servlet.JspServlet.serviceJspFil e(JspServlet.java:313)
    org.apache.jasper.servlet.JspServlet.service(JspSe rvlet.java:260)
    javax.servlet.http.HttpServlet.service(HttpServlet .java:717)
    edu.wayne.med.sso.filters.CookieCheckFilter.doFilt er(CookieCheckFilter.java:142)
    Last edited by Rook; 06-16-2011 at 06:53 PM.

Page 1 of 2 12 LastLast

Similar Threads

  1. Proper if Statements: Help
    By socboy6579 in forum New To Java
    Replies: 3
    Last Post: 12-10-2010, 01:48 PM
  2. while and proper conditions for if
    By Saletra in forum New To Java
    Replies: 11
    Last Post: 08-25-2010, 10:37 AM
  3. proper use of IllegalArgumentException
    By vendetta in forum New To Java
    Replies: 1
    Last Post: 01-16-2010, 07:43 PM
  4. pls expalin me with any proper example
    By javastuden in forum New To Java
    Replies: 1
    Last Post: 11-05-2009, 10:35 AM
  5. Replies: 2
    Last Post: 04-20-2009, 08:00 AM

Posting Permissions

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