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?