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 Getting SQL Error and not sure why. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Getting SQL Error and not sure why." Watch "Getting SQL Error and not sure why." New topic
Author

Getting SQL Error and not sure why.

Scott Updike
Ranch Hand

Joined: Feb 16, 2006
Posts: 92
I'm getting a SQL error in MySQL where I try and build a where clause dynamically (from user suplied fields in web page) and append this where clause string to the existing query in my prepareStatement function call.


String srchCriteria = request.getParameter("searchCriteria");
String srchValue = request.getParameter("searchValue");

//build a dynamic where clause based on user supplied values from web page.

if (srchCriteria.equals("BA Contact")) {
whereClause = "where ba_contact = ?";
} else if (srchCriteria.equals("Priority")) {
whereClause = "where priority = ?";
} else if (srchCriteria.equals("Stage")) {
whereClause = "where stage = ?";
} else if (srchCriteria.equals("Product Line")) {
whereClause = "where product_line = ?";
} else {
whereClause = "";
}

Connection conn1 = (Connection) getServletContext().getAttribute("dbconn");
PreparedStatement stmt = conn1.prepareStatement("select * from table_name" + whereClause);
stmt.setString(1,srchValue);
ResultSet rslt = stmt.executeQuery();
Result myResult = ResultSupport.toResult(rslt);
request.setAttribute("results",myResult);
RequestDispatcher view = request.getRequestDispatcher("main.jsp");
view.forward(request, response);


Everything seems to work (values are getting passed correctly), but I get the following error message from Tomcat when I enter 'High' as the srchValue:

...
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 '= 'High''
...

It seems to think that I'm wrapping my srchValue in a single quote in front and a double in the back.

Does anyone have any suggestions? If more information is needed, let me know.

Thanks,
Scott
Martin Simons
Ranch Hand

Joined: Mar 02, 2006
Posts: 196
put a space in the string after table_name
Scott Updike
Ranch Hand

Joined: Feb 16, 2006
Posts: 92
Ugh. That worked!

Thanks for your help.
Scott
 
 
subject: Getting SQL Error and not sure why.