MySql 5.0.67 sql statement, command line versus jdbc PreparedStatement

[Mary Taylor]

This simple sql statement works at the command line but not when executed through a PreparedStatement. I have looked at several examples on the web but have not found the error. Can anyone suggest something to try?

mysql> select userName, password, userType from registered_users;
+------------+----------+---------------+
| userName | password | userType |
+------------+----------+---------------+

| tuser | tpwd | Student |
| doug | dpwd | Professor |
| MIchael | mpwd | Administrator |
| Ella | euser | Student |
| Mira | muser | Student |
| Kyla | kuser | Student |
+------------+----------+---------------+
7 rows in set (0.00 sec)

mysql> select userName from papers.registered_users where userName = "tuser";
+----------+
| userName |
+----------+
| tuser |
+----------+
1 row in set (0.01 sec)


==============================================

Here is the code from a java class:

public static boolean findUserInDatabase(String nameToFind)
{
System.out.print("\nUtilities.findUserInDatabase nameToFind = " + nameToFind);
boolean foundInDb = false;
String name = nameToFind;
Connection conn = null;
PreparedStatement pstmt = null;
method = "nUtilities.findUserInDatabase";
try
{
conn = getDBConnection();
System.out.print("\nUtilities.findUserInDatabase conn = " + conn);
System.out.print("\nUtilities.findUserInDatabase name = " + name);
String query =
"select userName from papers.registered_users where userName = ?";

pstmt = conn.prepareStatement(query); // create a statement
pstmt.setString(1, name); // set input parameter
ResultSet result = pstmt.executeQuery(query);

while (result.next())
{
foundInDb = true;
String nameInDB = result.getString(1);

System.out.print ("\n" + method + "foundInDb = " + foundInDb);
System.out.print("result userName = " + result.findColumn("userName") + "\n" +
result.findColumn("password"));
}
}
catch (Exception e)
{
System.out.println("\n" + method + "\n User not found in database, name = " + name + " \n") ;
System.out.println("e.getMessage() = \n" + e.getMessage());
}
return foundInDb;
}


==================================================

Here is the debug information:
Utilities.findUserInDatabase conn = jdbc:mysql://localhost:3306/papers?autoReconnect=true, UserName=root@localhost, MySQL-AB JDBC Driver
Utilities.findUserInDatabase name = tuser
getDBConnection
User not found in database, name = tuser

e.getMessage() =
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

I am able to insert values in this database so the connection is good.

Thanks for any advice.

[Jeanne Boyarsky]

Mary,
The line pstmt.executeQuery(query); should be pstmt.executeQuery();

The method taking a query is from the Statement superclass. Meaning it is ignoring your prepared statement parameter and looking for a user named question mark. Confusing, I know.

[Mary Taylor]

Thank you so much for your prompt reply. I made the change and now have other problems; but I cannot work on this more until Tuesday. I will try to determine what the problem is then. At least I am no longer stuck in the same spot; I have been here a couple of days and just did not see what you told me.

Here is my current error; the server does not appear to be stopped so I am not certain how to debug this problem.

In Login.jsp, the user = tuser
In Login.jsp, the request.getParameter("userID") = tuser
Utilities.findUserInDatabase nameToFind = tuser

Method = getDBConnection

initCtx = javax.naming.InitialContext@19facbc

envCtx = org.apache.naming.NamingContext@9b5c95

ds = org.apache.tomcat.dbcp.dbcp.BasicDataSource@7208e4

conn = jdbc:mysql://localhost:3306/papers?autoReconnect=true, UserName=root@localhost, MySQL-AB JDBC Driver
Utilities.findUserInDatabase conn = jdbc:mysql://localhost:3306/papers?autoReconnect=true, UserName=root@localhost, MySQL-AB JDBC Driver
Utilities.findUserInDatabase name = tuser
Nov 16, 2008 5:52:40 PM org.apache.catalina.loader.WebappClassLoader loadClass
INFO: Illegal access: this web application instance has been stopped already. Could not load javax.servlet.http.HttpSession. The eventual following stack trace is caused by an error thrown for debugging purposes as well as to attempt to terminate the thread which caused the illegal access, and has no functional impact.
java.lang.IllegalStateException
at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1246)
at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1206)
at org.apache.jasper.servlet.JasperLoader.loadClass(JasperLoader.java:128)
at org.apache.jasper.servlet.JasperLoader.loadClass(JasperLoader.java:66)
at java.lang.ClassLoader.loadClassInternal(Unknown Source)
at org.apache.jsp.Login_jsp._jspService(Login_jsp.java:86)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:337)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:266)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Unknown Source)

[Mary Taylor]

Is it possible the problem is that I have not closed a statement? I am looking at the pstmt in debug, and it appears to be correct:

com.mysql.jdbc.PreparedStatement@12efcfe: select userName from papers.registered_users where userName = 'tuser'

I do not know how to proceed with debugging.

Thanks for any help.

[Mary Taylor]

The offending statment seems to be the two lines I have now commented out in the following code:

  try
{
conn  = getDBConnection();
    System.out.print("\nUtilities.findUserInDatabase conn = " + conn);
    System.out.print("\nUtilities.findUserInDatabase name = " + name);
    String query = 
    "select userName from papers.registered_users where userName = ?";

    pstmt = conn.prepareStatement(query); // create a statement
pstmt.setString(1, name);             // set input parameter
        ResultSet  result = pstmt.executeQuery();
          
    while (result.next())
        {
    foundInDb =  true;
    String nameInDB = result.getString(1);
    
    System.out.print ("\n" + method + "foundInDb = " + foundInDb);
//    System.out.print("result userName = " + result.findColumn("userName") + "\n" + 
//                                         result.findColumn("password"));         

Can you tell me what the problem would be with these lines?

[Mary Taylor]

Once again, I am having a similar problem with the following output; if ajyone has some guidance on how I might determine the cause myself, I would be happy to hear that advice. I realize I am asking a lot of questions, but I do not know the procedure to resolve these issues myself.

Thanks for any feedback.

Nov 18, 2008 9:17:59 PM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet jsp threw exception
java.lang.IllegalStateException: getOutputStream() has already been called for this response
at org.apache.catalina.connector.Response.getWriter(Response.java:610)
at org.apache.catalina.connector.ResponseFacade.getWriter(ResponseFacade.java:198)
at org.apache.jasper.runtime.JspWriterImpl.initOut(JspWriterImpl.java:125)
at org.apache.jasper.runtime.JspWriterImpl.flushBuffer(JspWriterImpl.java:118)
at org.apache.jasper.runtime.PageContextImpl.release(PageContextImpl.java:186)
at org.apache.jasper.runtime.JspFactoryImpl.internalReleasePageContext(JspFactoryImpl.java:118)
at org.apache.jasper.runtime.JspFactoryImpl.releasePageContext(JspFactoryImpl.java:77)
at org.apache.jsp.Login_jsp._jspService(Login_jsp.java:103)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:337)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:266)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Unknown Source)


The code being exectued that threw this exception is:

   if  ( Utilities.findUserInDatabase(user))
            {       
            RequestDispatcher dispatcher = 
            request.getRequestDispatcher("ProcessFile.html");
            dispatcher.forward(request, response);
            }