UPDATE not committing to mysql

[soniarad]

Can someone help me, I was getting an error message about SQL syntax, now I get no error message, it redirects, but database does not change. I also made sure autocommit was enabled.
Any ideas?

<%@page import="java.sql.*,java.util.*" %>
<%
Connection conn = null;
PreparedStatement stmt = null;

String userName = "xxxx";
String password = "xxxx";
String host = "xxxx";
String port = "3306";
String db = "jobops";

String url = "jdbc:mysql://" + host + ":" + port + "/" + db;

try
{
    Class.forName ("com.mysql.jdbc.Driver").newInstance ();
    conn = DriverManager.getConnection (url, userName, password);

    String sql = "UPDATE ops SET " +
            "firstname=?" +
            ",lastname=?" + 
            ",comments=? " +
          
  
            
            " WHERE id=" + request.getParameter("'id';");

    stmt = conn.prepareStatement(sql);

    stmt.setString(1,request.getParameter("firstname"));
    stmt.setString(2,request.getParameter("lastname"));
   

    stmt.setString(3,request.getParameter("comments"));
 

 out.println("still here?");

out.println("no loop.");

    stmt.executeUpdate();

    response.sendRedirect("../list.jsp");
}
catch (Exception e)
{
   out.println(e.getMessage());
}
finally
{
   if (stmt != null) {
       try {
           stmt.close ();
       }
       catch (Exception e) { /* ignore close errors */ }
   }

   if (conn != null) {
       try {
           conn.close ();
       }
       catch (Exception e) { /* ignore close errors */ }
   }
}

//
%>

[javaAddict]

3 suggestions:

Try printing the query and then run it at your database to see if it actually updates. Maybe the WHERE clause is false

Maybe I am wrong, but try first to close the statement and the connection and redirect

And finally:
THIS IS THE MOST IMPORTANT:
Delete everything and start all over again, but this time follow the instructions from this link:
http://www.daniweb.com/forums/thread141776.html
It is at the top of the JSP forum. You should be reading other posts to see if that have something about your problem and that post tells you exactly how to do things

[stephen84s]

Print the content to see what value you have for this :-

request.getParameter("'id';")

Cause most probably that is coming as "null" as a result of which your where clause does not match any rows in the database and hence does not update any.

But most importantly do not perform database connectivity directly from within a JSP. They are supposed to be the view layer only in the MVC architecture. To know how servlets, JSP and Java beans should be organized and work together check the sticky JSP database connectivity according to Model View Controller (MVC) Model 2. Its a nice tutorial on how servlets,JSPs and Beans should be actually working together in a Web Application.

[soniarad]

stephen84s:

I think you are right about the NULL, but when I use ("id"); I get the following error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

[soniarad]

So is it not even possible to do it this way, because I have been struggling with this all week, I just want to do a simple update and when I take out
[request.getParameter("id"); ]

I get a sql syntax error

[peter_budo]

Please use servlets as you been told and you would not have these problems...

[stephen84s]

As Peter suggested change your approach to use the MVC-2 model mentioned in the Sticky.
Normally we do not help anyone performing database connectivity directly from within a JSP.
I gave you two options a quickfix which would get your code running and a link to the way it is actually supposed to be done. However you opted for the first option and just skipped the second one on good practices.

In short what I want to say is if you want stick to doing this web application the wrong way then you are on your own. Do not expect too much help from us (we already have hinted you on the problems), on the other hand if you want do it the right way as mentioned in the sticky we will definitely not mind going the extra mile to help you out.

[~s.o.s~]

> " WHERE id=" + request.getParameter("'id';"); " WHERE id='" + request.getParameter("id") + "'"; .

Your query has two problems:
- request.getParameter("'id'") tries to grab or look for the value of a form field with the name 'id' which isn't obviously what you are looking for. It should be request.getParameter("id"); .
- If the ID column of your Table is of type string, then you need to have the value of the ID form field in quotes. Better yet, use another placeholder in your prepared statement.

> Class.forName ("com.mysql.jdbc.Driver").newInstance (); .

No need to create a new instance; loading the Driver class with Class.forName() will do the job of registering the Driver with the DriverManager .