File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes MySql  5.0.67 sql statement, command line versus jdbc PreparedStatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "MySql  5.0.67 sql statement, command line versus jdbc PreparedStatement" Watch "MySql  5.0.67 sql statement, command line versus jdbc PreparedStatement" New topic
Author

MySql 5.0.67 sql statement, command line versus jdbc PreparedStatement

Mary Taylor
Ranch Hand

Joined: Sep 11, 2000
Posts: 319
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30085
    
149

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Mary Taylor
Ranch Hand

Joined: Sep 11, 2000
Posts: 319
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
Ranch Hand

Joined: Sep 11, 2000
Posts: 319
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
Ranch Hand

Joined: Sep 11, 2000
Posts: 319
The offending statment seems to be the two lines I have now commented out in the following code:



Can you tell me what the problem would be with these lines?
Mary Taylor
Ranch Hand

Joined: Sep 11, 2000
Posts: 319
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:
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: MySql 5.0.67 sql statement, command line versus jdbc PreparedStatement
 
Similar Threads
login problem
"Function sequence error!" what is it mean
Getting a SQL Exception ?
connectivity in jsp
Strange behaviour of ResultSet next method