Cannot insert data in MYSQL Using Java

Question

Need help with the below. I am unable to understand what is incorrect in the below code. I have also check the MYSQL names and HTML Form. No errors there.

<%@page import="java.sql.*"%>
<%
String Week=request.getParameter("Week");
String Metric=request.getParameter("Metric");
String Method=request.getParameter("Method");
String Region=request.getParameter("Region");
String TI=request.getParameter("TI");
String ASSA=request.getParameter("ASSA");
String TM=request.getParameter("TM");
String Skilled=request.getParameter("Skilled");
String center=request.getParameter("center");
String caller_id=request.getParameter("caller_id");
String observer=request.getParameter("observer");
String Observations=request.getParameter("Observations");

try{
    Class.forName("com.mysql.cj.jdbc.Driver");
    Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/compliance_form","root","mypass");
    Statement st=con.createStatement();
    st.executeUpdate("insert into form1 (Week,Metric,Method,Region,TI,ASSA,TM,Skilled,center,caller_id,observer,Observations) values('"+Week+"','"+Metric+"','"+Method+"','"+Region+"','"+TI+"','"+ASSA+"','"+TM+"','"+Skilled+"','"+center+"','"+caller_id+"','"+observer+"','"+Observations+"')");
    response.sendRedirect("save.html");
}
catch(Exception e)
{
    response.sendRedirect("error.html");
}

%>

First things first: You are leaving yourself open to Sql Injection attacks; you should be using prepared statements. Question: With this particular driver, is AutoCommit initially true or false? If if is false, you will have to either call commit on the connection after the call to executeUpdate or call setAutoCommit(true) on the connection before calling executeUpdate. But ultimately what is the problem? An error message? No update? Something else? — Booboo
– Booboo, Commented Mar 1, 2020 at 13:07
There is no error message. The Mysql Table doesn't get updated and the code is executing the below code only- catch(Exception e) { response.sendRedirect("error.html"); } — Rahul Patil
– Rahul Patil, Commented Mar 1, 2020 at 13:25
Then you definitely need to output the exception (perhaps in a log file?) before redirecting. Also, look at tutorials.jenkov.com/jdbc/preparedstatement.html for an example on how to use a prepared statement. It not only defends against SQL Injection attacks but also handles situations where an input field has a quote (') character, which would be a problem for you with your current code. — Booboo
– Booboo, Commented Mar 1, 2020 at 13:38

Roland Kreuzer · Accepted Answer · 2020-03-01 13:27:32Z

1

If the data was not inserted there was probably an exception. I'd start by trying to capture possible error messages.

catch(Exception e)
{
    e.printStackTrace();
    response.sendRedirect("error.html");
}

This should give you additional error information in your IDE console or application server log you can proceed on.

Code in JSP is generally pretty hard to debug. If you have the option you might want to migrate the logic into a Java class.

answered Mar 1, 2020 at 13:27

Roland Kreuzer

1,0127 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rahul Patil Over a year ago

Thanks Roland. There was one additional 's' in the field name. I was able to find that after using e.printStackTrace();

Collectives™ on Stack Overflow

Cannot insert data in MYSQL Using Java

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related