0

I'm fairly new to Java servlet and web app development, but this is puzzling. I have built a project in Eclipse (Mars) that consists mostly of static HTML pages with one JSP that contains a form that does a POST to a servlet. This servlet simply will take the parameters from the request and insert a row into a table in a MySQL database. Below is the servlet code:

package boyd.apps.k9;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import java.util.*;
import javax.mail.*;
import javax.mail.internet.*;
import javax.activation.*;

/**
 * Servlet implementation class ContactUs
 */
@WebServlet("/ContactUs")
public class ContactUs extends HttpServlet {
    private static final long serialVersionUID = 1L;

    /**
     * @see HttpServlet#HttpServlet()
     */
    public ContactUs() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub

        String requestType = request.getParameter("requestType");
        String name = request.getParameter("name").toUpperCase();
        String company = request.getParameter("company").toUpperCase();
        String address = request.getParameter("address").toUpperCase();
        String city = request.getParameter("city").toUpperCase();
        String state = request.getParameter("state").toUpperCase();
        String zip = request.getParameter("zip");
        String phone = request.getParameter("phone");
        String email = request.getParameter("email").toUpperCase();
        String comments = request.getParameter("comments");
        String hrMin = request.getParameter("hr-min");
        String amPm = request.getParameter("am-pm");
        String timeToCall = hrMin + amPm;
        String sqlstr = null;
        Connection conn = null;
        Statement stmt = null;
        String dbuser = this.getServletContext().getInitParameter("dbuser");
        String dbpwd = this.getServletContext().getInitParameter("dbpwd");
        String url = this.getServletContext().getInitParameter("dburl");

        try
        {
            Class.forName("com.mysql.jdbc.Driver");
        }
        catch (ClassNotFoundException e) {
            System.out.println("MySQL JDBC Driver not found");
            return;
        }

        try {
            //conn = ds.getConnection(dbuser, dbpwd, url);
            conn = DriverManager.getConnection(url, dbuser, dbpwd);
        } catch (SQLException se) {
            System.out.println("Error connecting to database in ContactUs");
            se.printStackTrace();
            System.exit(1);
        }

        try {
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            sqlstr = "INSERT INTO LEADS (REQUEST_TYPE, NAME, COMPANY, ADDRESS, CITY, STATE, ZIP, PHONE, TIME_TO_CALL, EMAIL, COMMENTS) VALUES " +
                     "('" + requestType + "','" + name + "','" + company + "','" + address + "','" + city + "','" + state + "','" + zip + 
                     "','" + phone + "','" + timeToCall + "','" + email + "','" + comments + "')";
            stmt.executeUpdate(sqlstr);
        } catch (SQLException se) {
            System.out.println("Error inserting into LEADS");
            se.printStackTrace();
            System.exit(1);
        }

        RequestDispatcher view = null;
        view = request.getRequestDispatcher("index.html");
        view.forward(request, response);

    }

}

The database connection info, as you can see, is coming from the web.xml file as Servlet Context parameters (dbuser: k9_user; password: xxxx; url: jdbc:mysql://localhost:3306/k9). So, the MySQL database name is k9, and there is just the one table set up (LEADS). As the MySQL root, doing as use k9; to switch to the k9 database, I've given all privileges on LEADS to the k9_user. This whole configuration works just fine on my local machine, pointing at a local MySQL, and running through Apache Tomcat 8 on Eclipse.

Then, I deploy this app to the VM at the host site, set up the MySQL database the exact same way, and open a browser to run the app. I navigate to the entry page and submit the form, and not only does the app throw the error below, but it crashes the whole Easy Tomcat 7 app server (which is odd enough on its own, for just what appears to be a "Table not found" error). The thing is, on that host server from the command line, I can connect to the k9 MySQL database as that user, and do a "describe leads;" and insert a row into it just fine. So, even moreso, that's a head-scratcher. Anyway, the error stack from the Tomcat log is as follows:

Nov 05, 2015 9:04:30 AM org.apache.catalina.core.ApplicationContext log
INFO: HTMLManager: list: Listing contexts for virtual host 'localhost'
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'k9.LEADS' doesn't exist
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
        at com.mysql.jdbc.Connection.execSQL(Connection.java:3277)
        at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1402)
        at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1317)
        at boyd.apps.k9.ContactUs.doPost(ContactUs.java:103)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
        at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1023)
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
        at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:310)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
        at java.lang.Thread.run(Thread.java:745)
Service exit with a return value of 1

If anyone has come across this type of inconsistent behavior, or has seen similar error messages like this in general when working with MySQL via Java code, can you help a brutha out?

0

Enable full logging in MySQL or on the JDBC level to capture all the queries that are going between your application and MySQL. Check if k9.LEADS possibly gets dropped and not re-created. Also double-check that all of the connections go to the right server that actually has k9.LEADS.

0

I did a little more digging, and found that the reason for the failure is that the table name in MySQL is case-sensitive; hence, it wasn't actually found. My background is working with Oracle databases, where referencing table names with case-sensitivity in mind is not by default. Thanks for your input, though.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.