Why doesn't it Insert Data?

[Stefano Mtangoo]

I wonder why this does not insert data :-O

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@page import = "java.sql.*" %>
<%--@page import = "dbfiles.*" --%>


<%
            //VDbManager mgr = new VDbManager();
            String sqlApp = "INSERT INTO application VALUES (NULL, 810, 2010-08-26, 2010-10-27, 30, \"Dummy Leave\",  \"Dummy Reason \",  \"Applied \")";
            Class.forName("com.mysql.jdbc.Driver");
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/lam", "root", "ilovejesus");
            Statement st = connection.createStatement();

            int i = 1000;

            out.println("Entering the loop...");
            while (i > 0) {
                /*mgr.dbChange(sqlApp);
                mgr.dbChange(sqlApproval);
                mgr.dbChange(sqlUser);
                 */
                try {
                    st.executeUpdate(sqlApp);
                    st.executeUpdate(sqlApproval);
                    st.executeUpdate(sqlUser);
                    out.println(i + "<br />");
                } catch (Exception e) {
                    e.printStackTrace();
                }
                i--;
            }
            out.println("Done!");
            connection.close();
%>

[jwenting]

don't do database access from jsp, use a servlet instead.
and it will insert the data just fine, the database is just discarding the insert because you never commit the insert.

[Stefano Mtangoo]

Any simple illustration/Example?

[Stefano Mtangoo]

I have done this and doesn't work either
it throws errors:

HTTP Status 500 -

type Exception report

message

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

exception

java.lang.RuntimeException: Uncompilable source code - doGet(javax.servlet.http.HttpServletRequest,javax.servlet.http.HttpServletResponse) in dummy.FillGazillionData cannot override doGet(javax.servlet.http.HttpServletRequest,javax.servlet.http.HttpServletResponse) in javax.servlet.http.HttpServlet; overridden method does not throw java.sql.SQLException
dummy.FillGazillionData.doGet(FillGazillionData.java:74)
javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:390)

note The full stack trace of the root cause is available in the Apache Tomcat/6.0.20 logs.
Apache Tomcat/6.0.20

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dummy;

import java.sql.*;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 *
 * @author smtangoo
 */
public class FillGazillionData extends HttpServlet {

    /** 
     * Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException, SQLException{
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        Connection connection = null;
        try {

            String sqlApp = "INSERT INTO application VALUES (NULL, 810, 2010-08-26, 2010-10-27, 30, \"Dummy Leave\",  \"Dummy Reason \",  \"Applied \")";
            String sqlUser = "INSERT INTO user VALUES(810,  \"Tomcat Jerry \",  \"Virus Eater \",  \"Cats \",  \"Tomcat \",  \"Hunter \", 0754710410,  \" [e-mail]\",  \"YES \", 89,  \"Manager \",   \"Approving \",  \"Bigger Tomcat \",  \"HOD \" )";
            String sqlApproval = "INSERT INTO approval VALUES(NULL, [e-mail],  \"This approval comment \", 2010-7-30,  \"HOD \",  \"Approved \")";

            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/lam", "root", "ilovejesus");
            Statement st = connection.createStatement();

            int i = 1000;

            out.println("Entering the loop...");
            while (i > 0) {
                /*mgr.dbChange(sqlApp);
                mgr.dbChange(sqlApproval);
                mgr.dbChange(sqlUser);
                 */
                st.executeUpdate(sqlApp);
                st.executeUpdate(sqlApproval);
                st.executeUpdate(sqlUser);
                out.println(i + "<br />");
                i--;
            }
            out.println("Done!");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            out.close();
            connection.close();
        }
    }

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /** 
     * Handles the HTTP <code>GET</code> method.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException, SQLException {
        processRequest(request, response);
    }

    /** 
     * Handles the HTTP <code>POST</code> method.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException, SQLException {
        processRequest(request, response);
    }

    
}

[~s.o.s~]

Instead of getting your hands busy with the libraries/API's provided by Java, you should concentrate on getting the language basics right. The stack trace in the previous post provides the exact reason as to *why* the code fails.

As far as your code is concerned, look into the DAO pattern for acessing databases. Centralize your connection related logic so that changing the database details doesn't involve sifting through thousands of Java files. Have you looked at this tutorial for at least a starting point?

[Stefano Mtangoo]

Cool I wrote servlet to do Db manip issues and DBClass for database conn issues.
Thanks